RMAN备份与恢复之基于时间点的不完全恢复

ゞ浴缸里的玫瑰 2022-09-20 12:45 210阅读 0赞

## 一 不完全恢复讲解 ##

在上一篇文章（[RMAN备份与恢复之UNDO表空间丢失][RMAN_UNDO]）中，我们讲到UNDO表空间丢失怎么处理。UNDO表空间是不可脱机的，同样，SYSTEM表空间也是不可脱机的，所以SYSTEM表空间丢失的处理办法和UNDO表空间丢失的处理办法类似，在此不赘述。

RMAN恢复分为完全恢复和不完全恢复。完全恢复，顾名思义，就是基于全备的基础上对数据进行恢复。但是真实环境中这样的恢复用得很少。相反，不完全恢复使用较多。不完全恢复，也就是通过备份文件，恢复某一个误操作或者是某一段时间丢失的数据。不完全恢复可以基于时间，基于SCN，基于日志序列号、基于备份控制文件。本文讲解基于时间点的不完全恢复。

## 二 基于时间点的不完全恢复讲解 ##

与前面的文章一样，既然要做恢复，我们首先要有备份文件，我们使用RMAN工具对数据库进行备份。备份完全后，我们切换到SQL Plus中，打开显示完整时间的选项，为什么要显示完整时间呢？因为基于时间点的恢复需要一个时间点，打开这个选项后，一是可以使我们更方便的查看到操作过程中经历的时间，二是保证时间点选取的精确性。然后我们登录到SCOTT用户，创建一张测试表，插入数据，手动提交。注意，我们为了保证数据的一致性，最好切换到SYS用户下手动切换日志（ALTER SYSTEM SWITCH LOGFILE）和手动触发CKPT（ALTERSYSTEM CHECKPOINT）。好了，数据准备完成，我们此时删除表，并同时清空回收站，记下删除表之前的时间点，后面恢复操作要使用到。现在我们强制关闭数据库，登录到RMAN中，启动数据库到MOUNT状态，然后查看当前的INCARNATION号，至于INCARNATION号是什么东西。我们可以这样理解，每一次RESETLOGS就会使INCARNATION+1，如果想要恢复到之前INCARNATION的scn（或者时间点），就需要先跳转到之前的INCARNATION。跳转到之前的INCARNATION，我们可以使用如下命令：RESET DATABASE TO INCARNATION <INCARNATION号>。举一个不太恰当的比喻，这个INCARNATION就像我们在十字路口，此时我们不知道应该往哪个方向走，如果你一旦走错，还得回到这个十字路口选取下一条路。此时我们需要编辑恢复脚本，内容就是告诉ORACLE恢复到哪个时间点，并且还原和恢复数据库。恢复完成后，我们需要使用RESETLOGS方式打开数据库，此时我们登录到SQL Plus中，查看表只中的数据，如果能够成功查询到，证明恢复成功。如果不能查询到，第一考虑时间点是否选择正确，第二确认INCARNATION号是否正确，第三数据是否保持了一致性。因为我们重置了日志，所以需要重新备份数据库。至此，完整的恢复操作完成。

## 三 基于时间点的恢复模拟 ##

Step 1，SQL Plus中确保数据库在归档模式，RMAN中备份数据库

>     SQL> ARCHIVE LOG LIST;
>     Database log mode        Archive Mode
>     Automatic archival         Enabled
>     Archive destination        USE_DB_RECOVERY_FILE_DEST
>     Oldest online log sequence     68
>     Next log sequence to archive   70
>     Current log sequence         70
>     [oracle@orcl ~]$ uniread rman target /
>     [uniread] Loaded history (2 lines)
>     
>     Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 17 09:44:40 2014
>     
>     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
>     
>     connected to target database: LARRRDB (DBID=3428598070)
>     
>     RMAN> BACKUP DATABASE;
>     
>     Starting backup at 17-JAN-14
>     using target database control file instead of recovery catalog
>     allocated channel: ORA_DISK_1
>     channel ORA_DISK_1: SID=36 device type=DISK
>     channel ORA_DISK_1: starting full datafile backup set
>     channel ORA_DISK_1: specifying datafile(s) in backup set
>     input datafile file number=00001 name=/u01/oracle/oradata/larrrdb/system01.dbf
>     input datafile file number=00003 name=/u01/oracle/oradata/larrrdb/undotbs01.dbf
>     input datafile file number=00002 name=/u01/oracle/oradata/larrrdb/sysaux01.dbf
>     input datafile file number=00004 name=/u01/oracle/oradata/larrrdb/users01.dbf
>     channel ORA_DISK_1: starting piece 1 at 17-JAN-14
>     channel ORA_DISK_1: finished piece 1 at 17-JAN-14
>     piece handle=/u01/oracle/fast_recovery_area/LARRRDB/backupset/2014_01_17/o1_mf_nnndf_TAG20140117T094447_9fk2rhv7_.bkp tag=TAG20140117T094447 comment=NONE
>     channel ORA_DISK_1: backup set complete, elapsed time: 00:04:07
>     channel ORA_DISK_1: starting full datafile backup set
>     channel ORA_DISK_1: specifying datafile(s) in backup set
>     including current control file in backup set
>     including current SPFILE in backup set
>     channel ORA_DISK_1: starting piece 1 at 17-JAN-14
>     channel ORA_DISK_1: finished piece 1 at 17-JAN-14
>     piece handle=/u01/oracle/fast_recovery_area/LARRRDB/backupset/2014_01_17/o1_mf_ncsnf_TAG20140117T094447_9fk309p8_.bkp tag=TAG20140117T094447 comment=NONE
>     channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
>     Finished backup at 17-JAN-14

Step 2，SQL Plus中打开显示完整时间

>     SQL> CONN SCOTT/tiger;
>     Connected.
>     SQL> SET TIME ON;
>     09:53:34 SQL>

Step 3，SQL Plus创建测试表

>     09:53:34 SQL> CREATE TABLE test(id NUMBER(2),name VARCHAR(20));
>     
>     Table created.
>     
>     09:53:59 SQL>

Step 4，SQL Plus插入数据，并提交，为了保持数据一致性，最好使用sys用户手动切换日志（ALTER SYSTEM SWITCHLOGFILE）和触发CKPT（ALTERSYSTEM CHECKPOINT）

>     09:53:59 SQL> INSERT INTO test VALUES(1,'justdb');
>     
>     1 row created.
>     
>     09:54:16 SQL> COMMIT;
>     
>     Commit complete.
>     
>     09:54:25 SQL> SELECT * FROM test;
>     
>       ID NAME
>     ---------- --------------------
>        1 justdb
>     
>     09:54:33 SQL>

Step 5，SQL Plus中删除测试表

>     09:54:33 SQL> DROP TABLE test PURGE;
>     
>     Table dropped.

Step 6，SQL Plus中一致性关闭数据库

>     10:00:09 SQL> SHUTDOWN IMMEDIATE;
>     Database closed.
>     Database dismounted.
>     ORACLE instance shut down.
>     10:01:57 SQL>

Step 7，编辑基于时间点恢复的脚本，注意时间点的选取，此处选取为删除表的时间点（09:54:33），注意时间点尤其重要

>     [oracle@orcl ~]$ vim /home/oracle/recover_data_by_time.sql
>     [oracle@orcl ~]$ cat !$
>     cat /home/oracle/recover_data_by_time.sql
>     RUN { SET UNTIL TIME "to_date('2014-01-17 09:54:33','yyyy-mm-dd hh24:mi:ss')"; RESTORE DATABASE; RECOVER DATABASE; }

Step 8，登录到RMAN，启动数据库到MOUNT状态

>     [oracle@orcl ~]$ uniread rman target /
>     [uniread] Loaded history (6 lines)
>     
>     Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 17 10:02:22 2014
>     
>     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
>     
>     connected to target database (not started)
>     
>     RMAN> STARTUP MOUNT;
>     
>     Oracle instance started
>     database mounted
>     
>     Total System Global Area     839282688 bytes
>     
>     Fixed Size                     2233000 bytes
>     Variable Size                494931288 bytes
>     Database Buffers             339738624 bytes
>     Redo Buffers                   2379776 bytes

Step 9，RMAN中查看当前INCARNATION号，注意：如果有其他的操作，以后恢复时应该选取当前查看到的INCARNATION号，使用RESET DATABASE TO INCARNATION <INCARNATION号>命令

>     RMAN> LIST INCARNATION;
>     
>     using target database control file instead of recovery catalog
>     
>     List of Database Incarnations
>     DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
>     ------- ------- -------- ---------------- --- ---------- ----------
>     1       1       LARRRDB  3428598070       CURRENT 1          23-NOV-13

Step 10，RMAN中执行基于时间点的恢复脚本

>     RMAN> @ /home/oracle/recover_data_by_time.sql 
>     @ /home/oracle/recover_data_by_time.sql 
>     
>     RMAN> RUN {
>     2>   SET UNTIL TIME "to_date('2014-01-17 09:54:33','yyyy-mm-dd hh24:mi:ss')";
>     3>   RESTORE DATABASE;
>     4>   RECOVER DATABASE;
>     5> }
>     executing command: SET until clause
>     
>     Starting restore at 17-JAN-14
>     allocated channel: ORA_DISK_1
>     channel ORA_DISK_1: SID=21 device type=DISK
>     
>     channel ORA_DISK_1: starting datafile backup set restore
>     channel ORA_DISK_1: specifying datafile(s) to restore from backup set
>     channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/larrrdb/system01.dbf
>     channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/larrrdb/sysaux01.dbf
>     channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/larrrdb/undotbs01.dbf
>     channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/larrrdb/users01.dbf
>     channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/LARRRDB/backupset/2014_01_17/o1_mf_nnndf_TAG20140117T094447_9fk2rhv7_.bkp
>     channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/LARRRDB/backupset/2014_01_17/o1_mf_nnndf_TAG20140117T094447_9fk2rhv7_.bkp tag=TAG20140117T094447
>     channel ORA_DISK_1: restored backup piece 1
>     channel ORA_DISK_1: restore complete, elapsed time: 00:04:16
>     Finished restore at 17-JAN-14
>     
>     Starting recover at 17-JAN-14
>     using channel ORA_DISK_1
>     
>     starting media recovery
>     media recovery complete, elapsed time: 00:00:01
>     
>     Finished recover at 17-JAN-14
>     
>     RMAN> **end-of-file**

Step 11，RMAN中使用RESETLOGS打开数据库

>     RMAN> ALTER DATABASE OPEN RESETLOGS;
>     
>     database opened

Step 12，SQL Plus中查看数据，如果能够正确地查看到，证明恢复成功

>     [oracle@orcl ~]$ sqlplus 
>     [uniread] Loaded history (66 lines)
>     
>     SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 17 10:10:53 2014
>     
>     Copyright (c) 1982, 2011, Oracle.  All rights reserved.
>     
>     
>     Connected to:
>     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
>     With the Partitioning, Oracle Label Security, OLAP, Data Mining,
>     Oracle Database Vault and Real Application Testing options
>     
>     SQL> SELECT open_mode FROM v$database;
>     
>     OPEN_MODE
>     --------------------
>     READ WRITE
>     
>     #这正是被删除掉的表中的数据
>     SQL> SELECT * FROM scott.test;
>     
>       ID NAME
>     ---------- --------------------
>        1 justdb

Step 13，因为重置了日志，我们需要重新在RMAN中备份

>     [oracle@orcl ~]$ uniread rman target /
>     [uniread] Loaded history (2 lines)
>     
>     Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 17 09:44:40 2014
>     
>     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
>     
>     connected to target database: LARRRDB (DBID=3428598070)
>     
>     RMAN> BACKUP DATABASE;

## 四 相关文章 ##

*  [RMAN备份与恢复之控制文件丢失][RMAN]
 *  [RMAN备份与恢复之可脱机数据文件丢失][RMAN 1]
 *  [RMAN备份与恢复之UNDO表空间丢失][RMAN_UNDO]

>     我的邮箱：wgbno27@gmail.com   新浪微博：@jutdb         
>       微信公众平台：JustOracle（微信号：justoracle）
>       数据库技术交流群：336882565（加群时验证 From CSDN XXX）
>       All is well
>       2014年1月17日
>       By Larry Wen

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[RMAN_UNDO]: http://blog.csdn.net/justdb/article/details/18352573
[RMAN]: http://blog.csdn.net/justdb/article/details/18253717
[RMAN 1]: http://blog.csdn.net/justdb/article/details/18304253