一次从resetlogs之前的备份恢复的试验.doc

一次从resetlogs之前的备份恢复的试验参考文献：Oracle9i Recover Manager Users Guide Recovering Through a RESETLOGS Operation with RMAN情景假设：假设，数据库最近经历过一次不完全恢复，并以open resetlogs的操作打开，redo的sequence被清0了，然后open resetlogs之后并没有对数据库进行备份，所有的备份都是resetlogs之前的备份，数据库现在出现了介质损坏（假设是全部数据文件和控制文件损坏），需要恢复。环境：catalog模式思路：由于我们只有resetlogs之前的备份，所以只能使用这个备份来进行restore，因此不能使用当前的控制文件来mount数据库（我们先把当前的控制文件进行保存），使用备份的控制文件（早于resetlogs）mount数据库，进行restore，恢复到resetlogs的时候，然后再换成当前的控制文件，进行后续的恢复。准备工作：1。数据库关闭，进行一次冷备份2。启动数据库到open，用RMAN进行一次完全备份：3。数据库关闭，模拟一次不完全恢复，然后open resetlogs重启到mount状态runrestore database;set until sequence 33 thread 1;recover database;sqlalter database open resetlogs;模拟介质损坏：1。假设所有文件都损坏，需要restore整个database恢复步骤：1。找到resetlogs SCN（有三种方法）a.从alert.log里找RESETLOGS after incomplete recovery UNTIL CHANGE 55682b.select (resetlogs_change#)-1 from v$database; 55682c.RMAN下list incarnation发现并没有当前的incarnation，于是试着做一下同步RMAN resync catalog;RMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03009: failure of resync command on default channel at 03/21/2005 15:41:42RMAN-20003: target database incarnation not found in recovery catalog发现不能和控制文件里的信息进行同步，搜了一下metalink，参照了/metal . amp;p_id=334876.996帖子，执行了一下reset database（目的是为了把target库的新的incarnation注册到catalog库）；RMAN reset database;new incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete同步成功RMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 NO1 29-MAR-051 61 TRACY 1008055455 YES 55682 29-MAR-052。关闭数据库:SQL shutdown abort;ORACLE instance shut down.3。备份当前控制文件：（os命令备份）3。启动RMAN，打开数据库到nomount状态4。查看上次resetlogs时的信息，找到上次resetlogs的Inc Key：RMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 NO1 29-MAR-051 61 TRACY 1008055455 YES 55682 29-MAR-055。reset数据库到incarnation 2RMAN reset database to incarnation 2;database reset to incarnation 2 in recovery catalogRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 YES 1 29-MAR-051 61 TRACY 1008055455 NO55682 29-MAR-05注意：是把incarnation reset到上一个incarnation（即之前的一个）6。recover到step 1找到的SCN55682RMAN run2 set until scn 55682;3 allocate channel c1 type disk;4 restore controlfile;5 alter database mount;6 restore database;7 recover database;8 executing command: SET until clauseallocated channel: c1channel c1: sid=8 devtype=DISKStarting restore at 29-MAR-05channel c1: starting datafile backupset restorechannel c1: restoring controlfileoutput filename=/u01/app/oracle/product/9.2.0/oradata/tracy/control1.ctlchannel c1: restored backup piece 1piece handle=/u01/app/oracle/product/9.2.0/dbs/02gghn1v_1_1 tag=TAG20050329T162638 params=NULLchannel c1: restore completereplicating controlfileinput filename=/u01/app/oracle/product/9.2.0/oradata/tracy/control1.ctlFinished restore at 29-MAR-05database mountedStarting restore at 29-MAR-05channel c1: starting datafile backupset restorechannel c1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/app/oracle/product/9.2.0/oradata/tracy/system01.dbfrestoring datafile 00002 to /u01/app/oracle/product/9.2.0/oradata/tracy/undo01.dbfrestoring datafile 00003 to /u01/app/oracle/product/9.2.0/oradata/tracy/users01.dbfrestoring datafile 00004 to /u01/app/oracle/product/9.2.0/oradata/tracy/data01.dbfrestoring datafile 00005 to /u01/app/oracle/product/9.2.0/oradata/tracy/index01.dbfchannel c1: restored backup piece 1piece handle=/u01/app/oracle/product/9.2.0/dbs/02gghn1v_1_1 tag=TAG20050329T162638 params=NULLchannel c1: restore completeFinished restore at 29-MAR-05Starting recover at 29-MAR-05starting media recoveryarchive log thread 1 sequence 32 is already on disk as file /u01/app/oracle/admin/tracy/arc/tracy_32.arcarchive log filename=/u01/app/oracle/admin/tracy/arc/tracy_32.arc thread=1 sequence=32unable to find archive logarchive log thread=1 sequence=33released channel: c1RMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of recover command at 03/29/2005 16:41:13RMAN-06054: media recovery requesting unknown log: thread 1 scn 55681注意：由于这里当前只归档了日志sequence 32，sequence为33的还没有归档，reod信息都还在online redo logs里，因此RMAN会报上述错误，没有关系，我们从上面可以看到一直到sequence为32的所有归档日志都已经恢复了7。关闭数据库再重启到nomountRMAN shutdown immediate;database dismountedOracle instance shut downRMAN startup nomount;connected to target database (not started)Oracle instance startedTotal System Global Area 236434532 bytesFixed Size 451684 bytesVariable Size 100663296 bytesDatabase Buffers 134217728 bytesRedo Buffers 1101824 bytesRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 YES 1 29-MAR-051 61 TRACY 1008055455 NO55682 29-MAR-058。把恢复过resetlogs点的数据库重新置于最新的incarnation 61：RMAN reset database to incarnation 61;database reset to incarnation 61 in recovery catalogRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 NO1 29-MAR-051 61 TRACY 1008055455 YES 55682 29-MAR-059。mount数据库，并进行recovery（如果online redo logs里的信息都还在的话，就可以进行完全恢复了，如本例）RMAN alter database mount;database mountedRMAN recover database;Starting recover at 29-MAR-05allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=13 devtype=DISKstarting media recoverymedia recovery completeFinished recover at 29-MAR-05RMAN alter database open;database opened总结，这样的恢复需要用到recovery catalog ！试验步骤保留：RMAN backup current controlfile format $ORACLE_HOME/dbs/controlfile.backup;Starting backup at 29-MAR-05allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=17 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current controlfile in backupsetchannel ORA_DISK_1: starting piece 1 at 29-MAR-05channel ORA_DISK_1: finished piece 1 at 29-MAR-05piece handle=/u01/app/oracle/product/9.2.0/dbs/controlfile.backup comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 29-MAR-05RMAN list backup;List of Backup Sets=BS KeyType LV Size Device Type Elapsed Time Completion Time- - - - - - -38 Full 3M DISK 00:00:01 29-MAR-05 BP Key: 39 Status: AVAILABLE Tag: TAG20050329T162603 Piece Name: /u01/app/oracle/product/9.2.0/dbs/controlfile.backupControlfile Included: Ckp SCN: 55545 Ckp time: 29-MAR-05RMAN backup database; Starting backup at 29-MAR-05using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current SPFILE in backupsetincluding current controlfile in backupsetinput datafile fno=00003 name=/u01/app/oracle/product/9.2.0/oradata/tracy/users01.dbfinput datafile fno=00004 name=/u01/app/oracle/product/9.2.0/oradata/tracy/data01.dbfinput datafile fno=00005 name=/u01/app/oracle/product/9.2.0/oradata/tracy/index01.dbfinput datafile fno=00001 name=/u01/app/oracle/product/9.2.0/oradata/tracy/system01.dbfinput datafile fno=00002 name=/u01/app/oracle/product/9.2.0/oradata/tracy/undo01.dbfchannel ORA_DISK_1: starting piece 1 at 29-MAR-05channel ORA_DISK_1: finished piece 1 at 29-MAR-05piece handle=/u01/app/oracle/product/9.2.0/dbs/02gghn1v_1_1 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:46Finished backup at 29-MAR-05RMAN backup archivelog all;Starting backup at 29-MAR-05current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=31 recid=1 stamp=554228660input archive log thread=1 sequence=32 recid=2 stamp=554228919channel ORA_DISK_1: starting piece 1 at 29-MAR-05channel ORA_DISK_1: finished piece 1 at 29-MAR-05piece handle=/u01/app/oracle/product/9.2.0/dbs/03gghn5n_1_1 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 29-MAR-05RMAN shutdown immediate;database closeddatabase dismountedOracle instance shut down这个时候保留一份当前的控制文件，后面步骤需要用到RMAN run2 restore database;3 set until sequence 33 thread 1;4 recover database;5 sqlalter database open resetlogs;6 Starting restore at 29-MAR-05allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=8 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/app/oracle/product/9.2.0/oradata/tracy/system01.dbfrestoring datafile 00002 to /u01/app/oracle/product/9.2.0/oradata/tracy/undo01.dbfrestoring datafile 00003 to /u01/app/oracle/product/9.2.0/oradata/tracy/users01.dbfrestoring datafile 00004 to /u01/app/oracle/product/9.2.0/oradata/tracy/data01.dbfrestoring datafile 00005 to /u01/app/oracle/product/9.2.0/oradata/tracy/index01.dbfchannel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/product/9.2.0/dbs/02gghn1v_1_1 tag=TAG20050329T162638 params=NULLchannel ORA_DISK_1: restore completeFinished restore at 29-MAR-05executing command: SET until clauseStarting recover at 29-MAR-05using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 32 is already on disk as file /u01/app/oracle/admin/tracy/arc/tracy_32.arcarchive log filename=/u01/app/oracle/admin/tracy/arc/tracy_32.arc thread=1 sequence=32media recovery completeFinished recover at 29-MAR-05sql statement: alter database open resetlogsRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 YES 1 29-MAR-05RMAN reset database;new incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync completeRMAN resync catalog;starting full resync of recovery catalogfull resync completeRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 NO1 29-MAR-051 61 TRACY 1008055455 YES 55682 29-MAR-05以上步骤，模拟了一次不完全恢复，并open resetlogs，把新的incarnation注册到catalog库RMAN shutdown abort;Oracle instance shut down此处假设全部数据文件包括控制文件丢失RMAN startup nomount;connected to target database (not started)Oracle instance startedTotal System Global Area 236434532 bytesFixed Size 451684 bytesVariable Size 100663296 bytesDatabase Buffers 134217728 bytesRedo Buffers 1101824 bytesRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 NO1 29-MAR-051 61 TRACY 1008055455 YES 55682 29-MAR-05RMAN reset database to incarnation 2;database reset to incarnation 2 in recovery catalogRMAN list incarnation;List of Database IncarnationsDB KeyInc Key DB NameDB ID CUR Reset SCNReset Time- - - - - - -1 2 TRACY 1008055455 YES 1 29-MAR-051 61 TRACY 1008055455 NO55682 29-MAR-05RMAN run2 set until scn 55682;3 allocate channel c1 type disk;4 restore controlfile;5 alter database mount;6 restore database;7 recover database;8 executing command: SET until clauseallocated channel: c1channel c1: sid=8 devtype=DISKStarting restore at 29-MAR-05channel c1: starting datafile backupset restorechannel c1: restoring controlfileoutput filename=/u01/app/oracle/product/9.2.0/oradata/tracy/control1.ctlchannel c1: restored backup piece 1piece handle=/u01/app/oracle/product/9.2.0/dbs/02gghn1v_1_1 tag=TAG20050329T162638 params=NULLchannel c1: restore completereplicating controlfileinput filename=/u01/app/oracle/product/9.2.0/oradata/tracy/control1.ctlFinished restore at 29-MAR-05database mountedStarting restore at 29-MAR-05channel c1: starting datafile backupset restorechannel c1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/app/oracle/product/9.2.0/oradata/tracy/system01.dbfrestoring datafile 00002 to /u01/app/oracle/product/9.2.0/oradata/tracy/undo01.dbfrestoring datafile 00003 to /u01/app/oracle/product/9.2.0/oradata/tracy/users01.dbfrestoring datafile 00004 to /u01/app/oracle/product/9.2.0/oradata/tracy/data01.dbfrestoring datafile 00005 to /u01/app/oracle/product/9.2.0/oradata/tracy/index01.dbfchannel c1: restored backup piece 1piece handle=/u01/app/oracle/product/9.2.0/dbs/02gghn1v_1_1 tag=TAG20050329T162638 params=NULLchannel c1: restore completeFinished restore at 29-MAR-05Starting recover at 29-MAR-05starting media recoveryarchive log thread 1 sequence 32 is already on disk as file /u01/app/oracle/admin/tracy/arc/tracy_32.arcarchive log filename=/u01/app/oracle/admin/tracy/arc/tracy_32.arc thread=1 sequenc