RMAN恢复案例之--不完全恢复错误案例

RMAN恢复案例：

不完全恢复，由于在先前的数据文件备份有误，导致恢复失败。

[oracle@rh7~]$rmantarget/RecoveryManager:Release11.2.0.1.0-ProductiononSatSep2015:54:252014Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase:TEST1(DBID=1195893416)RMAN>run{2>shutdownimmediate;3>startupmount;4>setuntiltime'2014-09-2015:49:36';5>restoredatabase;6>recoverdatabase;7>alterdatabaseopenresetlogs;8>sql'altersystemswitchlogfile';9>}usingtargetdatabasecontrolfileinsteadofrecoverycatalogdatabasecloseddatabasedismountedOracleinstanceshutdownconnectedtotargetdatabase(notstarted)OracleinstancestarteddatabasemountedTotalSystemGlobalArea175767552bytesFixedSize1335276bytesVariableSize142606356bytesDatabaseBuffers29360128bytesRedoBuffers2465792bytesexecutingcommand:SETuntilclauseStartingrestoreat20-SEP-14allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=18devicetype=DISKflashingbackcontrolfiletoSCN9601534channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00002to/u01/app/oracle/oradata/test1/sysaux01.dbfchannelORA_DISK_1:restoringdatafile00004to/u01/app/oracle/oradata/test1/users01.dbfchannelORA_DISK_1:restoringdatafile00005to/u01/app/oracle/oradata/test1/perfs.dbfchannelORA_DISK_1:restoringdatafile00007to/u01/app/oracle/oradata/test1/undotbs2.dbfchannelORA_DISK_1:restoringdatafile00010to/u01/app/oracle/oradata/test1/index01.dbfchannelORA_DISK_1:readingfrombackuppiece/dsk1/backup/test1/TEST1_240.bakchannelORA_DISK_1:piecehandle=/dsk1/backup/test1/TEST1_240.baktag=TAG20140920T152720channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:35channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00013to/u01/app/oracle/oradata/test1/tbs_16.dbfchannelORA_DISK_1:readingfrombackuppiece/dsk1/backup/test1/TEST1_241.bakchannelORA_DISK_1:piecehandle=/dsk1/backup/test1/TEST1_241.baktag=TAG20140920T152720channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:01channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00001to/u01/app/oracle/oradata/test1/system01.dbfchannelORA_DISK_1:restoringdatafile00003to/u01/app/oracle/oradata/test1/test1.dbfchannelORA_DISK_1:restoringdatafile00006to/u01/app/oracle/oradata/test1/dict1.dbfchannelORA_DISK_1:readingfrombackuppiece/dsk1/backup/test1/TEST1_239.bakchannelORA_DISK_1:piecehandle=/dsk1/backup/test1/TEST1_239.baktag=TAG20140920T152720channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:45Finishedrestoreat20-SEP-14Startingrecoverat20-SEP-14usingchannelORA_DISK_1startingmediarecovery

recover 失败......

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/20/2014 15:59:01

RMAN-20505: create datafile during recovery

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

start until time 'SEP 20 2014 15:49:36'

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 8: '/u01/app/oracle/oradata/test1/tbs2.dbf'

查看备份信息：

RMAN>listbackupofdatabase;ListofBackupSets===================BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------229Full158.89MDISK00:01:1820-SEP-14BPKey:229Status:AVAILABLECompressed:NOTag:TAG20140920T152720PieceName:/dsk1/backup/test1/TEST1_240.bakListofDatafilesinbackupset229FileLVTypeCkpSCNCkpTimeName---------------------------------2Full960153420-SEP-14/u01/app/oracle/oradata/test1/sysaux01.dbf4Full960153420-SEP-14/u01/app/oracle/oradata/test1/users01.dbf5Full960153420-SEP-14/u01/app/oracle/oradata/test1/perfs.dbf7Full960153420-SEP-14/u01/app/oracle/oradata/test1/undotbs2.dbf10Full960153420-SEP-14/u01/app/oracle/oradata/test1/index01.dbfBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------230Full1.06MDISK00:00:0120-SEP-14BPKey:230Status:AVAILABLECompressed:NOTag:TAG20140920T152720PieceName:/dsk1/backup/test1/TEST1_241.bakListofDatafilesinbackupset230FileLVTypeCkpSCNCkpTimeName---------------------------------13Full960153420-SEP-14/u01/app/oracle/oradata/test1/tbs_16.dbfBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------231Full609.73MDISK00:01:3220-SEP-14BPKey:231Status:AVAILABLECompressed:NOTag:TAG20140920T152720PieceName:/dsk1/backup/test1/TEST1_239.bakListofDatafilesinbackupset231FileLVTypeCkpSCNCkpTimeName---------------------------------1Full960153420-SEP-14/u01/app/oracle/oradata/test1/system01.dbf3Full960153420-SEP-14/u01/app/oracle/oradata/test1/test1.dbf6Full960153420-SEP-14/u01/app/oracle/oradata/test1/dict1.dbf8Full960153420-SEP-14/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008

在备份中发现:

'/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008',而在数据库中并没有这个数据文件！

解决方法：

将datafile offline，open数据库：

16:02:43SYS@test1>alterdatabasedatafile8offline;Databasealtered.

recover database跳过此表空间：

RMAN> recover database skip tablespace tbs2;

Startingrecoverat20-SEP-14usingchannelORA_DISK_1Executing:alterdatabasedatafile8offlinestartingmediarecoverymediarecoverycomplete,elapsedtime:00:00:30Finishedrecoverat20-SEP-14

查看告警日志：

alterdatabasedatafile8offlineCompleted:alterdatabasedatafile8offlinealterdatabaserecoverdatafilelistclearCompleted:alterdatabaserecoverdatafilelistclearalterdatabaserecoverdatafilelist1,2,3,4,5,6,7,10,13Completed:alterdatabaserecoverdatafilelist1,2,3,4,5,6,7,10,13alterdatabaserecoverifneededstartMediaRecoveryStartSerialMediaRecoverystartedRecoveryofOnlineRedoLog:Thread1Group5Seq15Readingmem0Mem#0:/dsk1/oradata/test1/redo05a.logSatSep2016:06:512014MediaRecoveryComplete(test1)Completed:alterdatabaserecoverifneededstart

打开数据库删除此表空间：

16:04:42SYS@test1>conn/assysdba;Connected.16:10:10SYS@test1>alterdatabaseopen;Databasealtered.Elapsed:00:00:21.6316:10:54SYS@test1>selectnamefromv$datafile;NAME--------------------------------------------------/u01/app/oracle/oradata/test1/system01.dbf/u01/app/oracle/oradata/test1/sysaux01.dbf/u01/app/oracle/oradata/test1/test1.dbf/u01/app/oracle/oradata/test1/users01.dbf/u01/app/oracle/oradata/test1/perfs.dbf/u01/app/oracle/oradata/test1/dict1.dbf/u01/app/oracle/oradata/test1/undotbs2.dbf/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008/u01/app/oracle/oradata/test1/index01.dbf/u01/app/oracle/oradata/test1/tbs_16.dbf10rowsselected.16:11:10SYS@test1>droptablespacetbs2includingcontentsanddatafiles;Tablespacedropped.16:11:32SYS@test1>selectnamefromv$datafile;NAME--------------------------------------------------/u01/app/oracle/oradata/test1/system01.dbf/u01/app/oracle/oradata/test1/sysaux01.dbf/u01/app/oracle/oradata/test1/test1.dbf/u01/app/oracle/oradata/test1/users01.dbf/u01/app/oracle/oradata/test1/perfs.dbf/u01/app/oracle/oradata/test1/dict1.dbf/u01/app/oracle/oradata/test1/undotbs2.dbf/u01/app/oracle/oradata/test1/index01.dbf/u01/app/oracle/oradata/test1/tbs_16.dbf9rowsselected.

对数据库做重新做全备，再次做数据恢复！

恢复过程又出错：

RMAN>run{2>shutdownimmediate;3>startupmount;4>setuntiltime'2014-09-2016:19:30';5>restoredatabase;6>recoverdatabase;7>alterdatabaseopenresetlogs;8>}databasecloseddatabasedismountedOracleinstanceshutdownconnectedtotargetdatabase(notstarted)OracleinstancestarteddatabasemountedTotalSystemGlobalArea175767552bytesFixedSize1335276bytesVariableSize142606356bytesDatabaseBuffers29360128bytesRedoBuffers2465792bytesexecutingcommand:SETuntilclauseStartingrestoreat20-SEP-14allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=18devicetype=DISKflashingbackcontrolfiletoSCN9603237RMAN-00571:===========================================================RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============RMAN-00571:===========================================================RMAN-03002:failureofrestorecommandat09/20/201416:21:46RMAN-06085:mustuseSETNEWNAMEcommandtorestoredatafile/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008

查看备份信息：

RMAN>listbackup;ListofBackupSets===================BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------233Full164.45MDISK00:01:0420-SEP-14BPKey:233Status:AVAILABLECompressed:NOTag:TAG20140920T161544PieceName:/dsk1/backup/test1/TEST1_244.bakListofDatafilesinbackupset233FileLVTypeCkpSCNCkpTimeName---------------------------------2Full960322020-SEP-14/u01/app/oracle/oradata/test1/sysaux01.dbf5Full960322020-SEP-14/u01/app/oracle/oradata/test1/perfs.dbf7Full960322020-SEP-14/u01/app/oracle/oradata/test1/undotbs2.dbf10Full960322020-SEP-14/u01/app/oracle/oradata/test1/index01.dbfBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------234Full1.06MDISK00:00:0220-SEP-14BPKey:234Status:AVAILABLECompressed:NOTag:TAG20140920T161544PieceName:/dsk1/backup/test1/TEST1_245.bakListofDatafilesinbackupset234FileLVTypeCkpSCNCkpTimeName---------------------------------13Full960322020-SEP-14/u01/app/oracle/oradata/test1/tbs_16.dbfBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------235Full613.10MDISK00:01:2820-SEP-14BPKey:235Status:AVAILABLECompressed:NOTag:TAG20140920T161544PieceName:/dsk1/backup/test1/TEST1_243.bakListofDatafilesinbackupset235FileLVTypeCkpSCNCkpTimeName---------------------------------1Full960322020-SEP-14/u01/app/oracle/oradata/test1/system01.dbf3Full960322020-SEP-14/u01/app/oracle/oradata/test1/test1.dbf4Full960322020-SEP-14/u01/app/oracle/oradata/test1/users01.dbf6Full960322020-SEP-14/u01/app/oracle/oradata/test1/dict1.dbfBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------236Full11.33MDISK00:00:0520-SEP-14BPKey:236Status:AVAILABLECompressed:NOTag:TAG20140920T161714PieceName:/dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkpSPFILEIncluded:Modificationtime:03-SEP-14SPFILEdb_unique_name:TEST1ControlFileIncluded:CkpSCN:9603237Ckptime:20-SEP-14

restore controlfile：

RMAN>listbackupofcontrolfile;ListofBackupSets===================BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------236Full11.33MDISK00:00:0520-SEP-14BPKey:236Status:AVAILABLECompressed:NOTag:TAG20140920T161714PieceName:/dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkpControlFileIncluded:CkpSCN:9603237Ckptime:20-SEP-14RMAN>shutdownimmediate;databasedismountedOracleinstanceshutdownRMAN>startupnomount;connectedtotargetdatabase(notstarted)OracleinstancestartedTotalSystemGlobalArea175767552bytesFixedSize1335276bytesVariableSize142606356bytesDatabaseBuffers29360128bytesRedoBuffers2465792bytesRMAN>restorecontrolfilefromautobackup;Startingrestoreat20-SEP-14allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=19devicetype=DISKrecoveryareadestination:/dsk4/backupdatabasename(ordatabaseuniquename)usedforsearch:TEST1channelORA_DISK_1:AUTOBACKUP/dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkpfoundintherecoveryareachannelORA_DISK_1:lookingforAUTOBACKUPonday:20140920channelORA_DISK_1:restoringcontrolfilefromAUTOBACKUP/dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkpchannelORA_DISK_1:controlfilerestorefromAUTOBACKUPcompleteoutputfilename=/u01/app/oracle/oradata/test1/control01.ctloutputfilename=/dsk1/oradata/test1/control02.ctloutputfilename=/dsk2/oradata/test1/control03.ctlFinishedrestoreat20-SEP-14

重新做恢复：

RMAN>run{2>shutdownimmediate;3>startupmount;4>setuntiltime'2014-09-2016:19:30';5>restoredatabase;6>recoverdatabase;7>alterdatabaseopenresetlogs;8>}Oracleinstanceshutdownconnectedtotargetdatabase(notstarted)OracleinstancestarteddatabasemountedTotalSystemGlobalArea175767552bytesFixedSize1335276bytesVariableSize142606356bytesDatabaseBuffers29360128bytesRedoBuffers2465792bytesexecutingcommand:SETuntilclauseStartingrestoreat20-SEP-14Startingimplicitcrosscheckbackupat20-SEP-14allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=18devicetype=DISKCrosschecked3objectsFinishedimplicitcrosscheckbackupat20-SEP-14Startingimplicitcrosscheckcopyat20-SEP-14usingchannelORA_DISK_1Finishedimplicitcrosscheckcopyat20-SEP-14searchingforallfilesintherecoveryareacatalogingfiles...catalogingdoneListofCatalogedFiles=======================FileName:/dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkpFileName:/dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858262943_b1bf156d_.bkpFileName:/dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858259416_b1b9lv2j_.bkpFileName:/dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858261087_b1bc76lg_.bkpFileName:/dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858260002_b1bb58sq_.bkpFileName:/dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831135549_97ycp30c_.bkpFileName:/dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831134795_97yc0vk0_.bkpFileName:/dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831136532_97ydnsbl_.bkpFileName:/dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831135962_97yd2z3r_.bkpusingchannelORA_DISK_1channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00002to/u01/app/oracle/oradata/test1/sysaux01.dbfchannelORA_DISK_1:restoringdatafile00005to/u01/app/oracle/oradata/test1/perfs.dbfchannelORA_DISK_1:restoringdatafile00007to/u01/app/oracle/oradata/test1/undotbs2.dbfchannelORA_DISK_1:restoringdatafile00010to/u01/app/oracle/oradata/test1/index01.dbfchannelORA_DISK_1:readingfrombackuppiece/dsk1/backup/test1/TEST1_244.bakchannelORA_DISK_1:piecehandle=/dsk1/backup/test1/TEST1_244.baktag=TAG20140920T161544channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:25channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00013to/u01/app/oracle/oradata/test1/tbs_16.dbfchannelORA_DISK_1:readingfrombackuppiece/dsk1/backup/test1/TEST1_245.bakchannelORA_DISK_1:piecehandle=/dsk1/backup/test1/TEST1_245.baktag=TAG20140920T161544channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:01channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00001to/u01/app/oracle/oradata/test1/system01.dbfchannelORA_DISK_1:restoringdatafile00003to/u01/app/oracle/oradata/test1/test1.dbfchannelORA_DISK_1:restoringdatafile00004to/u01/app/oracle/oradata/test1/users01.dbfchannelORA_DISK_1:restoringdatafile00006to/u01/app/oracle/oradata/test1/dict1.dbfchannelORA_DISK_1:readingfrombackuppiece/dsk1/backup/test1/TEST1_243.bakchannelORA_DISK_1:piecehandle=/dsk1/backup/test1/TEST1_243.baktag=TAG20140920T161544channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:55Finishedrestoreat20-SEP-14Startingrecoverat20-SEP-14usingchannelORA_DISK_1startingmediarecoveryarchivedlogforthread1withsequence17isalreadyondiskasfile/dsk1/oradata/test1/redo04a.logarchivedlogforthread1withsequence18isalreadyondiskasfile/dsk1/oradata/test1/redo05a.logarchivedlogfilename=/dsk1/oradata/test1/redo04a.logthread=1sequence=17archivedlogfilename=/dsk1/oradata/test1/redo05a.logthread=1sequence=18mediarecoverycomplete,elapsedtime:00:00:08Finishedrecoverat20-SEP-14Startingrecoverat20-SEP-14usingchannelORA_DISK_1startingmediarecoveryarchivedlogforthread1withsequence17isalreadyondiskasfile/dsk1/oradata/test1/redo04a.logarchivedlogforthread1withsequence18isalreadyondiskasfile/dsk1/oradata/test1/redo05a.logarchivedlogfilename=/dsk1/oradata/test1/redo04a.logthread=1sequence=17archivedlogfilename=/dsk1/oradata/test1/redo05a.logthread=1sequence=18mediarecoverycomplete,elapsedtime:00:00:08Finishedrecoverat20-SEP-14databaseopened

@至此，不完全恢复成功！