ORACLE跨越时间点的恢复

在oracle10g之前使用resetlogs打开数据库之后，之前的的备份就不能用于恢复了。即不能进行跨resetlogs时间点的恢复。所以要求执行完之后马上进行全库备份。

Oracle10g以后允许跨越resetlogs时间点进行完全或者不完全恢复。实验如下：

1. 执行全库备份。

   RMAN> backup database plus archivelog delete all input;

   insert into test select * from test;

   SQL> alter system switch logfile;

   SQL> commit;

   Commit complete.

   SQL> alter system switch logfile;

   System altered.

   SQL> truncate table test;

   Table truncated.

   SQL> alter system switch logfile;

   System altered.

   SQL> select * from v$log_history;

   SQL> select recid,stamp,sequence#,first_change#,first_time,next_change# from v$log_history

   2 where recid >80;

   RECIDSTAMP FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

   ---------- ---------- ------------- ------------ ------------

   81 856723148 2130643 27-AUG-14 2130889

   82 856752323 2130889 27-AUG-14 2155374

   83 856753004 2155375 28-AUG-14 2175380

   84 856753636 2175380 28-AUG-14 2176030

   85 856754916 2176030 28-AUG-14 2177334

   86 856754945 2177334 28-AUG-14 2177347

   87 856755046 2177347 28-AUG-14 2178414

   88 856755142 2178414 28-AUG-14 2178471

   89 856755199 2178471 28-AUG-14 2178508

   90 856755243 2178508 28-AUG-14 2178543

   91 856755424 2178543 28-AUG-14 2178823

   RECIDSTAMP FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

   ---------- ---------- ------------- ------------ ------------

   92 856755456 2178823 28-AUG-14 2178836

   93 856755483 2178836 28-AUG-14 2178940

   13 rows selected.

2. 关闭数据库、删除数据模拟故障

   删除之后，执行如下

   RMAN> startup mount;

   Oracle instance started

   database mounted

   Total System Global Area 599785472 bytes

   Fixed Size 2022600 bytes

   Variable Size 268436280 bytes

   Database Buffers 322961408 bytes

   Redo Buffers 6365184 bytes

   RMAN> run{

   set until sequence 12 thread 1;

   restore database;

   recover database;

   }

   这里判断具体到那个sequence，当前查询如下，历史查看v$log_history的sequence#：

   Sql>archive log list;

   如果出现这个错误：

   SQL> recover database until cancel;

   ORA-00283: recovery session canceled due to errors

   ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

   SQL> recover database BACKUP CONTROLFILE

   ORA-00905: missing keyword

   解决：

   recover database using backup controlfile until cancel;

   RMAN> alter database open resetlogs;

   查看序号是否复位：

   SQL> archive log list;

   Database log mode Archive Mode

   Automatic archival Enabled

   Archive destination USE_DB_RECOVERY_FILE_DEST

   Oldest online log sequence 0

   Next log sequence to archive 1

   Current log sequence 1

   SQL> create conn xzsp create tt able tt as select * from dba_users;

   Table created.

   SQL> alter system switch logfile;

   System altered.

   SQL> select count(*) from tt;

   COUNT(*)

   ----------

   26

   SQL> inert in sert into tt select * from tt;

   26 rows created.

   SQL> /

   52 rows created.

   SQL> /

   104 rows created.

   SQL> commit

   2 ;

   Commit complete.

   SQL> alter system switch logfile;

   System altered.

   SQL> select recid,stamp,first_change#,first_time,next_change# from v$log_history;

   RECID STAMP SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

   ---------- ---------- ---------- ------------- ------------ ------------

   100 856759449 1 2179277 28-AUG-14 2179707

   101 856759476 2 2179707 28-AUG-14 2179788

   102 856761071 1 2179621 28-AUG-14 2179920

   103 856761108 2 2179920 28-AUG-14 2179945

   103 rows selected.

   Oracle保留了resetlogs之前的日志序列号，并且日志recid继续增长，控制文件也保留了归档日志的序列。所以可以跨RESETLOGS进行恢复。10g以前 是不能够使用当前控制文件恢复之前的备份的。

   RMAN> run {

   2> restore database;

   3> recover database;

   4> }

   SQL> select count(*) from tt;

   COUNT(*)

   ----------

   208

   恢复能够完成，还有log_archive_format有关系。

   Sql>show parameter log_archive_format

   log_archive_format string archive_%t_%s_%r.log

   这里的%R是oracle新增的参数,是resetlogs的标志号.这一归档日志格式可以是不同Incarnation的数据库归档日志避免相互覆盖，从而跨越resetlogs恢复的日志基础得以保证。

   ?

   ?

   思考：这里看到rman备份只有一次，是在resetlogs之前，第二次完全恢复是基于之前的备份。这里看到oracle10确实是越过了resetlog执行了恢复。