归档丢失如何恢复数据文件.docx
归档丢失如何恢复数据文件测试说明。一个数据文件损坏，有全库热备，部分归档丢失，如何恢复？如果有备份我们可以直接恢复该数据文件，restore操作再recover，但是此时要看归档是否缺失，如果缺失则无法继续recover,该数据文件也无法上线。1恢复数据文件RMAN>restoredatafile7;Startingrestoreat2021-08-2014:37:19allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=59devicetype=DISKchannelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00007to/oracle/base/oradata/PROD/users01.dbfchannelORA_DISK_1:readingfrombackuppiece/oracle/base/19.3/dbs/0g06vqb8_1_1channelORA_DISK_1:piecehandle=/oracle/base/19.3/dbs/0g06vqb8_1_1tag=TAG20210820T105431channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:01Finishedrestoreat2021-08-2014:37:232recover数据文件RMAN>recoverdatafile7;Startingrecoverat2021-08-2014:41:25usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=73devicetype=DISKstartingmediarecoveryRMAN-00571:=RMAN-00569:=ERRORMESSAGESTACKFOLLOWS=RMAN-00571:=RMAN-03002:failureofrecovercommandat08/20/202114:41:29RMAN-06053:unabletoperformmediarecoverybecauseofmissinglogRMAN-06025:nobackupofarchivedlogforthread1withsequence9andstartingSCNof5576278foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence8andstartingSCNof5576202foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence7andstartingSCNof5576198foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence6andstartingSCNof5576195foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence5andstartingSCNof5576192foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence4andstartingSCNof5576189foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence3andstartingSCNof5570027foundtorestore此时报找不到归档。既然归档丢了，此时该数据文件涉及的数据肯定也丢了，此时有两个方法解决，但是都会丢数据（1）整个库恢复到最新的redo，也就是能前滚多少旧滚多少，尽量追到最新的redo数据。（2）将通过数据字典查找该表空间涉及的对象，将备份恢复到一个新的数据，再迁移表空间或者使用数据泵导出导入数据。我们看第一种，注意此时肯定有数据丢失，如果允许其他表空间也丢失数据就采取整个做法。1restore整个数据库。（此时控制文件没有损坏）RMAN>restoredatabase;Startingrestoreat2021-08-2014:44:29usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=45devicetype=DISKskippingdatafile7;alreadyrestoredtofile/oracle/base/oradata/PROD/users01.dbfchannelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00001to/oracle/base/oradata/PROD/system01.dbfchannelORA_DISK_1:restoringdatafile00002to/oracle/base/oradata/PROD/tbs_u1.dbfchannelORA_DISK_1:restoringdatafile00003to/oracle/base/oradata/PROD/sysaux01.dbfchannelORA_DISK_1:restoringdatafile00004to/oracle/base/oradata/PROD/undotbs01.dbfchannelORA_DISK_1:restoringdatafile00005to/oracle/base/19.3/dbs/cachetblsp.dbfchannelORA_DISK_1:restoringdatafile00008to/oracle/base/oradata/PROD/tbs_lob.dbfchannelORA_DISK_1:restoringdatafile00009to/oracle/base/oradata/PROD/creccoa.dbfchannelORA_DISK_1:readingfrombackuppiece/oracle/base/19.3/dbs/0g06vqb8_1_1channelORA_DISK_1:piecehandle=/oracle/base/19.3/dbs/0g06vqb8_1_1tag=TAG20210820T105431channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:02:16Finishedrestoreat2021-08-2014:46:50此时数据库处于mounted状态，下面恢复数据RMAN>recoverdatabase;Startingrecoverat2021-08-2014:50:39usingchannelORA_DISK_1startingmediarecoveryRMAN-00571:=RMAN-00569:=ERRORMESSAGESTACKFOLLOWS=RMAN-00571:=RMAN-03002:failureofrecovercommandat08/20/202114:50:41RMAN-06053:unabletoperformmediarecoverybecauseofmissinglogRMAN-06025:nobackupofarchivedlogforthread1withsequence9andstartingSCNof5576278foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence8andstartingSCNof5576202foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence7andstartingSCNof5576198foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence6andstartingSCNof5576195foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence5andstartingSCNof5576192foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence4andstartingSCNof5576189foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence3andstartingSCNof5570027foundtorestore发现依然没有归档日志，这个日志之前我们以将删除了，当然也没有备份过。此时不做recover操作，也就是保持restore后的状态，但是这个备份时基于hotbackup所以如果不执行有效的recover，由于数据文件记录的SCN不一致，数据库依然无法打开。我尝试打开。SQL>selectopen_modefromv$database;OPEN_MODE-MOUNTEDSQL>alterdatabaseopen;alterdatabaseopen*ERRORatline1:ORA-01113:file1needsmediarecoveryORA-01110:datafile1:'/oracle/base/oradata/PROD/system01.dbf'SQL>selectstatus,checkpoint_change#,to_char(checkpoint_time,'DD-MON-YYYYHH24:MI:SS')ascheckpoint_time,count(*)fromv$datafile_headergroupbystatus,checkpoint_change#,checkpoint_timeorderbystatus,checkpoint_change#,checkpoint_timeSTATUSCHECKPOINT_CHANGE#CHECKPOINT_TIMECOUNT(*)-ONLINE557171420-AUG-202110:54:328此时的检查的点时间时备份时的时间。此时数据库无法打开，要求所有文件做介质恢复。下面我们基于availableredo做介质恢复RMAN>recoverdatabaseuntilavailableredo;Startingrecoverat2021-08-2014:52:08usingchannelORA_DISK_1startingmediarecoveryarchivedlogforthread1withsequence8isalreadyondiskasfile/oracle/base/oradata/PROD/redo02.logarchivedlogforthread1withsequence9isalreadyondiskasfile/oracle/base/oradata/PROD/redo03.logarchivedlogforthread1withsequence10isalreadyondiskasfile/oracle/base/oradata/PROD/redo01.logwarning:attemptmediarecoveryuntilthread1,sequence3Finishedrecoverat2021-08-2014:52:10此时发现了当前的日志存在，但是注意由于34567丢失所以，waring部分说的很清楚了，恢复到thread1,sequence3为止因为日志需要连续性，缺失则无法跳过执行，从原理讲确实也不允许。下面我们尝试打开数据库SQL>alterdatabaseopen;alterdatabaseopen*ERRORatline1:ORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabaseopen由于时不完全恢复，此时的日志没有意义，需要重置redo,从新的序列号开始，并且会clear旧的redo.下面使用resetlogs打开数据库。SQL>alterdatabaseopenresetlogs;Databasealtered.下面查询日志序列号是否重置。SQL>selectthread#,sequence#,statusfromv$log;THREAD#SEQUENCE#STATUS-11CURRENT10UNUSED10UNUSED最后我们要重新备份数据库，因为之前的备份已经没有意义了，因为日志时新的。RMAN>backupascompressedbackupsetdatabase;Startingbackupat2021-08-2014:56:11usingchannelORA_DISK_1channelORA_DISK_1:startingcompressedfulldatafilebackupsetchannelORA_DISK_1:specifyingdatafile(s)inbackupsetinputdatafilefilenumber=00001name=/oracle/base/oradata/PROD/system01.dbfinputdatafilefilenumber=00003name=/oracle/base/oradata/PROD/sysaux01.dbfinputdatafilefilenumber=00004name=/oracle/base/oradata/PROD/undotbs01.dbfinputdatafilefilenumber=00009name=/oracle/base/oradata/PROD/creccoa.dbfinputdatafilefilenumber=00002name=/oracle/base/oradata/PROD/tbs_u1.dbfinputdatafilefilenumber=00005name=/oracle/base/19.3/dbs/cachetblsp.dbfinputdatafilefilenumber=00008name=/oracle/base/oradata/PROD/tbs_lob.dbfinputdatafilefilenumber=00007name=/oracle/base/oradata/PROD/users01.dbfchannelORA_DISK_1:startingpiece1at2021-08-2014:56:13channelORA_DISK_1:finishedpiece1at2021-08-2014:57:48piecehandle=/oracle/base/19.3/dbs/0j0708gd_1_1tag=TAG20210820T145612comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:01:35Finishedbackupat2021-08-2014:57:48StartingControlFileandSPFILEAutobackupat2021-08-2014:57:48piecehandle=/oracle/base/19.3/dbs/c-492834486-20210820-03comment=NONEFinishedControlFileandSPFILEAutobackupat2021-08-2014:57:50此时测试结束！
