Oracle RMAN恢复测试的方法教程

2025-03-15 技术教程 151

本篇内容主要讲解“Oracle RMAN恢复测试的方法教程”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle RMAN恢复测试的方法教程”吧!

1 启动归档

[oracle@chendb~]$sqlplus/assysdbaSQL>altersystemsetlog_archive_dest_1='location=/arch';SQL>altersystemsetlog_archive_format="chendb_%t_%s_%r.arc"scope=spfile;SQL>shutdownimmediate;SQL>startupmount;SQL>alterdatabasearchivelog;SQL>alterdatabaseopen;SQL>archiveloglist;

2 创建测试数据

SQL>conncjc/***SQL>createtablet1(idnumber,ttimevarchar2(100));CREATEORREPLACEPROCEDUREtest_proisbeginforiin1..10loopinsertintot1values(i,to_char(sysdate,'yyyy/mm/ddhh34:mi:ss'));commit;endloop;end;/

3 创建0级备份脚本

[oracle@cjcos01script]$vim/rmanbak/script/0_rmanbak.sh###0_rmanbak.shbegin###exportNLS_DATE_FORMAT='yyyy-mm-ddhh34:mi:ss'exportORACLE_SID=chendbrmantarget/log=/rmanbak/db/0_rmanbak.log<<EOFrun{allocatechannelch2typediskmaxpiecesize1000M;allocatechannelch3typediskmaxpiecesize1000M;backupincrementallevel=0filesperset=32format'/rmanbak/db/lev0_%d_%T_%U.bak'skipinaccessibledatabaseincludecurrentcontrolfiletag'0_rmanbak_cjcdb';releasechannelch2;releasechannelch3;}EOFexit###0_rmanbak.shend###

执行0级备份

[oracle@cjcos01script]$chmoda+x0_rmanbak.sh[oracle@cjcos01script]$./0_rmanbak.sh

执行备份期间，生成测试数据，切换归档日志

SQL>conncjc/***SQL>exectest_pro();SQL>createtablet2asselect*fromt1;SQL>altersystemswitchlogfile;

4 创建1级备份

[oracle@cjcos01script]$cat1_rmanbak.sh###1_rmanbak.shbegin###exportNLS_DATE_FORMAT='yyyy-mm-ddhh34:mi:ss'exportORACLE_SID=chendbrmantarget/log=/rmanbak/db/1_rmanbak.log<<EOFrun{allocatechannelch2typediskmaxpiecesize1000M;allocatechannelch3typediskmaxpiecesize1000M;backupincrementallevel=1filesperset=32format'/rmanbak/db/lev1_%d_%T_%U.bak'skipinaccessibledatabaseincludecurrentcontrolfiletag'1_rmanbak_cjcdb';releasechannelch2;releasechannelch3;}EOFexit###1_rmanbak.shend###

执行1级备份

[oracle@cjcos01script]$chmoda+x1_rmanbak.sh[oracle@cjcos01script]$./1_rmanbak.sh

执行备份期间，生成测试数据，切换归档日志

SQL>conncjc/***SQL>exectest_pro();SQL>createtablet3asselect*fromt2;SQL>altersystemswitchlogfile;

5 执行归档备份

[oracle@cjcos01script]$catarch_rmanbak.sh###arch_rmanbak.shbegin###exportNLS_DATE_FORMAT='yyyy-mm-ddhh34:mi:ss'exportORACLE_SID=chendbrmantarget/log=/rmanbak/db/arch_rmanbak.log<<EOFrun{allocatechannelch2typediskmaxpiecesize1000M;allocatechannelch3typediskmaxpiecesize1000M;sql"altersystemarchivelogcurrent";backupfilesperset=32format'/rmanbak/db/arch_%d_%T_%U.bak'skipinaccessiblearchivelogalldeleteinputtag'arch_cjcdb';backupformat='/rmandata/db/ctl_%d_%T_%U.ctl'currentcontrolfile;releasechannelch2;releasechannelch3;}EOFexit###arch_rmanbak.shend###

执行归档备份

[oracle@cjcos01script]$./arch_rmanbak.shSQL>createtablet5asselect*fromt1;

查看备份信息

SQL>altersessionsetnls_date_format='yyyymmddhh34:mi:ss';coltagfora20setlin200pages200colhandlefora50colSIZE_BYTES_DISPLAYfora10selectBS.RECID,BS.START_TIME,BS.PIECES,BS.INCREMENTAL_LEVEL,BS.BACKUP_TYPE,BP.PIECE#,BP.HANDLE,BP.TAG,BP.STATUS,ROUND(BP.bytes/1024/1024/1024,2)gbfromv$backup_setbs,v$backup_piecebpwherebs.recid=bp.recid;RECIDSTART_TIMEPIECESINCREMENTAL_LEVELBPIECE#HANDLETAGSGB--------------------------------------------------------------------------------------------------------------------------------------------------12020080114:31:5410D1/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak0_RMANBAK_CJCDBA.4122020080114:31:5410D1/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak0_RMANBAK_CJCDBA.7332020080114:33:3010D1/rmanbak/db/lev0_CHENDB_20200801_04v6p8hq_1_1.bak0_RMANBAK_CJCDBA042020080114:33:2310D1/rmanbak/db/lev0_CHENDB_20200801_03v6p8hj_1_1.bak0_RMANBAK_CJCDBA.0152020080115:07:3211I1/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bak1_RMANBAK_CJCDBA062020080115:07:3211I1/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bak1_RMANBAK_CJCDBA072020080115:08:4911I1/rmanbak/db/lev1_CHENDB_20200801_08v6pak1_1_1.bak1_RMANBAK_CJCDBA082020080115:08:4811I1/rmanbak/db/lev1_CHENDB_20200801_07v6pak0_1_1.bak1_RMANBAK_CJCDBA.0192020080115:13:261L1/rmanbak/db/arch_CHENDB_20200801_09v6pasm_1_1.bakARCH_CJCDBA.51102020080115:13:261L1/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakARCH_CJCDBA.45112020080115:14:121L1/rmanbak/db/arch_CHENDB_20200801_0bv6pau4_1_1.bakARCH_CJCDBA0122020080115:14:141D1/rmandata/db/ctl_CHENDB_20200801_0cv6pau6_1_1.ctlTAG20200801T151414A.0112rowsselected

BACKUP_TYPE:

Type of files that are in this backup.

If the backup contains archived redo logs, the value is L.

If this is a datafile full backup, the value is D.

If this is an incremental backup, the value is I.

查看备份信息

[oracle@cjcos01script]$exportNLS_DATE_FORMAT='YYYYMMDDHH24:MI:SS'[oracle@cjcos01script]$rmantarget/RecoveryManager:Release11.2.0.4.0-ProductiononSatAug115:24:202020Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase:CHENDB(DBID=1831901477)

RMAN>listbackupsummary;usingtargetdatabasecontrolfileinsteadofrecoverycatalogListofBackups===============KeyTYLVSDeviceTypeCompletionTime#Pieces#CopiesCompressedTag-------------------------------------------------------------------1B0ADISK2020080114:33:0611NO0_RMANBAK_CJCDB2B0ADISK2020080114:33:2611NO0_RMANBAK_CJCDB3B0ADISK2020080114:33:3011NO0_RMANBAK_CJCDB4B0ADISK2020080114:33:3111NO0_RMANBAK_CJCDB5B1ADISK2020080115:08:4111NO1_RMANBAK_CJCDB6B1ADISK2020080115:08:4811NO1_RMANBAK_CJCDB7B1ADISK2020080115:08:4911NO1_RMANBAK_CJCDB8B1ADISK2020080115:08:5011NO1_RMANBAK_CJCDB9BAADISK2020080115:14:0711NOARCH_CJCDB10BAADISK2020080115:14:0811NOARCH_CJCDB11BAADISK2020080115:14:1311NOARCH_CJCDB12BFADISK2020080115:14:1511NOTAG20200801T151414

######恢复######

场景一：使用0级备份恢复数据库

手动将增量和归档备份设置失效，测试只有0级备份进行恢复数据库

---生成unavailable脚本

SQL>setpagesize2000SQL>setheadingoffSQL>spool/home/oracle/unavailable.logSQL>select'changebackupset'||BS.RECID||'unavailable;'fromv$backup_setbs,v$backup_piecebpwherebs.recid=bp.recidandBS.BACKUP_TYPE!='D'SQL>spooloff'CHANGEBACKUPSET'||BS.RECID||'UNAVAILABLE;'----------------------------------------------------------------------changebackupset5unavailable;changebackupset6unavailable;changebackupset7unavailable;changebackupset8unavailable;changebackupset9unavailable;changebackupset10unavailable;changebackupset11unavailable;RMAN>changebackupset5unavailable;changebackupset6unavailable;changebackupset7unavailable;changebackupset8unavailable;changebackupset9unavailable;changebackupset10unavailable;changebackupset11unavailable;

查看状态，5-12备份集已经失效

RMAN>listbackupsummary;ListofBackups===============KeyTYLVSDeviceTypeCompletionTime#Pieces#CopiesCompressedTag-------------------------------------------------------------------1B0ADISK2020080114:33:0611NO0_RMANBAK_CJCDB2B0ADISK2020080114:33:2611NO0_RMANBAK_CJCDB3B0ADISK2020080114:33:3011NO0_RMANBAK_CJCDB4B0ADISK2020080114:33:3111NO0_RMANBAK_CJCDB5B1UDISK2020080115:08:4111NO1_RMANBAK_CJCDB6B1UDISK2020080115:08:4811NO1_RMANBAK_CJCDB7B1UDISK2020080115:08:4911NO1_RMANBAK_CJCDB8B1UDISK2020080115:08:5011NO1_RMANBAK_CJCDB9BAUDISK2020080115:14:0711NOARCH_CJCDB10BAUDISK2020080115:14:0811NOARCH_CJCDB11BAUDISK2020080115:14:1311NOARCH_CJCDB12BFADISK2020080115:14:1511NOTAG20200801T151414

0级恢复

SQL>shutdownimmediate

冷备

[oracle@cjcos01oradata]$pwd/u01/app/oracle11/oradata[oracle@cjcos01oradata]$cpchendbchendb_0801bak-rSQL>startupmount

开始0级恢复

默认执行完全恢复，归档备份已经设置失效，所有会提示找不到归档

RMAN>run{allocatechanneld1typedisk;restoredatabase;recoverdatabase;releasechanneld1;}

恢复日志如下：

usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrestoreat2020080115:30:43channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00002to/u01/app/oracle11/oradata/chendb/sysaux01.dbfchanneld1:restoringdatafile00004to/u01/app/oracle11/oradata/chendb/users01.dbfchanneld1:restoringdatafile00006to/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:01:05channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00001to/u01/app/oracle11/oradata/chendb/system01.dbfchanneld1:restoringdatafile00003to/u01/app/oracle11/oradata/chendb/undotbs01.dbfchanneld1:restoringdatafile00005to/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:01:15Finishedrestoreat2020080115:33:04Startingrecoverat2020080115:33:05startingmediarecoveryreleasedchannel:d1RMAN-00571:===========================================================RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============RMAN-00571:===========================================================RMAN-03002:failureofrecovercommandat08/01/202015:33:06RMAN-06053:unabletoperformmediarecoverybecauseofmissinglogRMAN-06025:nobackupofarchivedlogforthread1withsequence39andstartingSCNof2280954foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence38andstartingSCNof2280793foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence37andstartingSCNof2280705foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence36andstartingSCNof2278640foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence35andstartingSCNof2278619foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence34andstartingSCNof2277662foundtorestore

查看控制文件记录的数据文件scn

SQL>selectfile#,checkpoint_change#fromv$datafile;FILE#CHECKPOINT_CHANGE#----------------------------1228186322281863322818634228186352281863622818636rowsselected.

查看数据文件头记录的SCN

SQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#CHECKPOINT_CHANGE#----------------------------1227859022278591322785904227859152278590622785916rowsselected.

0级别恢复，执行不完全恢复，恢复到数据文件头最大的scn

RMAN>run{allocatechanneld1typedisk;restoredatabaseuntilscn2278591;recoverdatabaseuntilscn2278591;releasechanneld1;}releasedchannel:d1RMAN-00571:===========================================================RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============RMAN-00571:===========================================================RMAN-03002:failureofrecovercommandat08/01/202015:41:12RMAN-06053:unabletoperformmediarecoverybecauseofmissinglogRMAN-06025:nobackupofarchivedlogforthread1withsequence34andstartingSCNof2277662foundtorestore

提示恢复需要34号归档文件，查看34号归档所在备份集

RMAN>listbackupsetofarchivelogsequence34;ListofBackupSets===================BSKeySizeDeviceTypeElapsedTimeCompletionTime---------------------------------------------------------10463.58MDISK00:00:422020080115:14:08BPKey:10Status:AVAILABLECompressed:NOTag:ARCH_CJCDBPieceName:/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakListofArchivedLogsinbackupset10ThrdSeqLowSCNLowTimeNextSCNNextTime---------------------------------------------------------13422776622020080114:16:5522786192020080114:32:15

手动将10号备份置为有效

RMAN>changebackupset10available;usingchannelORA_DISK_1changedbackuppieceavailablebackuppiecehandle=/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakRECID=10STAMP=1047309207Changed1objectstoAVAILABLEstatus

解压出34号归档文件

RMAN>restorearchivelogsequence34;Startingrestoreat2020080115:46:11usingchannelORA_DISK_1channelORA_DISK_1:startingarchivedlogrestoretodefaultdestinationchannelORA_DISK_1:restoringarchivedlogarchivedlogthread=1sequence=34channelORA_DISK_1:readingfrombackuppiece/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakchannelORA_DISK_1:piecehandle=/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.baktag=ARCH_CJCDBchannelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:07Finishedrestoreat2020080115:46:18[oracle@cjcos01db]$ls/arch/chendb_1_34_1042210216.arc

再次执行恢复

此次使用了0级备份+34号归档完成了恢复

RMAN>run{allocatechanneld1typedisk;restoredatabaseuntilscn2278591;recoverdatabaseuntilscn2278591;releasechanneld1;}releasedchannel:ORA_DISK_1allocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrestoreat2020080115:47:12skippingdatafile1;alreadyrestoredtofile/u01/app/oracle11/oradata/chendb/system01.dbfskippingdatafile3;alreadyrestoredtofile/u01/app/oracle11/oradata/chendb/undotbs01.dbfskippingdatafile5;alreadyrestoredtofile/u01/app/oracle11/oradata/chendb/example01.dbfskippingdatafile2;alreadyrestoredtofile/u01/app/oracle11/oradata/chendb/sysaux01.dbfskippingdatafile4;alreadyrestoredtofile/u01/app/oracle11/oradata/chendb/users01.dbfskippingdatafile6;alreadyrestoredtofile/u01/app/oracle11/oradata/chendb/cjctbs01.dbfrestorenotdone;allfilesreadonly,offline,oralreadyrestoredFinishedrestoreat2020080115:47:12Startingrecoverat2020080115:47:12startingmediarecoveryarchivedlogforthread1withsequence34isalreadyondiskasfile/arch/chendb_1_34_1042210216.arcarchivedlogfilename=/arch/chendb_1_34_1042210216.arcthread=1sequence=34mediarecoverycomplete,elapsedtime:00:00:00Finishedrecoverat2020080115:47:12releasedchannel:d1

恢复成功

查看当前数据文件scn

SQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#CHECKPOINT_CHANGE#----------------------------1227859122278591322785914227859152278591622785916rowsselected.

场景二：使用0级+1级备份恢复数据库

将增量备份设置为有效

RMAN>changebackupset5available;changebackupset6available;changebackupset7available;changebackupset8available;

查看备份状态

RMAN>listbackupsummary;ListofBackups===============KeyTYLVSDeviceTypeCompletionTime#Pieces#CopiesCompressedTag-------------------------------------------------------------------1B0ADISK2020080114:33:0611NO0_RMANBAK_CJCDB2B0ADISK2020080114:33:2611NO0_RMANBAK_CJCDB3B0ADISK2020080114:33:3011NO0_RMANBAK_CJCDB4B0ADISK2020080114:33:3111NO0_RMANBAK_CJCDB5B1ADISK2020080115:08:4111NO1_RMANBAK_CJCDB6B1ADISK2020080115:08:4811NO1_RMANBAK_CJCDB7B1ADISK2020080115:08:4911NO1_RMANBAK_CJCDB8B1ADISK2020080115:08:5011NO1_RMANBAK_CJCDB9BAUDISK2020080115:14:0711NOARCH_CJCDB10BAUDISK2020080115:14:0811NOARCH_CJCDB11BAUDISK2020080115:14:1311NOARCH_CJCDB12BFADISK2020080115:14:1511NOTAG20200801T151414

删除解压的归档

[oracle@cjcos01db]$rm-rf/arch/chendb_1_34_1042210216.arc

恢复冷备份

SQL>shutdownimmediate[oracle@cjcos01oradata]$rm-rfchendb/*[oracle@cjcos01oradata]$cpchendb_0801bak/*chendb/-rSQL>startupmount

开始0+1级恢复，默认执行完全恢复

归档备份已经设置失效，所有会提示找不到归档

RMAN>run{allocatechanneld1typedisk;restoredatabase;recoverdatabase;releasechanneld1;}allocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrestoreat2020080116:15:57channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00002to/u01/app/oracle11/oradata/chendb/sysaux01.dbfchanneld1:restoringdatafile00004to/u01/app/oracle11/oradata/chendb/users01.dbfchanneld1:restoringdatafile00006to/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:25channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00001to/u01/app/oracle11/oradata/chendb/system01.dbfchanneld1:restoringdatafile00003to/u01/app/oracle11/oradata/chendb/undotbs01.dbfchanneld1:restoringdatafile00005to/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:35Finishedrestoreat2020080116:16:58Startingrecoverat2020080116:16:58channeld1:startingincrementaldatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetdestinationforrestoreofdatafile00002:/u01/app/oracle11/oradata/chendb/sysaux01.dbfdestinationforrestoreofdatafile00004:/u01/app/oracle11/oradata/chendb/users01.dbfdestinationforrestoreofdatafile00006:/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.baktag=1_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:01channeld1:startingincrementaldatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetdestinationforrestoreofdatafile00001:/u01/app/oracle11/oradata/chendb/system01.dbfdestinationforrestoreofdatafile00003:/u01/app/oracle11/oradata/chendb/undotbs01.dbfdestinationforrestoreofdatafile00005:/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.baktag=1_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:01startingmediarecoveryreleasedchannel:d1RMAN-00571:===========================================================RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============RMAN-00571:===========================================================RMAN-03002:failureofrecovercommandat08/01/202016:17:00RMAN-06053:unabletoperformmediarecoverybecauseofmissinglogRMAN-06025:nobackupofarchivedlogforthread1withsequence39andstartingSCNof2280954foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence38andstartingSCNof2280793foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence37andstartingSCNof2280705foundtorestoreRMAN-06025:nobackupofarchivedlogforthread1withsequence36andstartingSCNof2278640foundtorestore

查看数据文件头scn

SQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#CHECKPOINT_CHANGE#----------------------------1228068322280684322806834228068452280683622806846rowsselected.

执行不完全恢复

RMAN>run{allocatechanneld1typedisk;###restoredatabaseuntilscn2280684;recoverdatabaseuntilscn2280684;releasechanneld1;}allocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrecoverat2020080116:18:27startingmediarecoveryreleasedchannel:d1RMAN-00571:===========================================================RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============RMAN-00571:===========================================================RMAN-03002:failureofrecovercommandat08/01/202016:18:28RMAN-06053:unabletoperformmediarecoverybecauseofmissinglogRMAN-06025:nobackupofarchivedlogforthread1withsequence36andstartingSCNof2278640foundtorestore

提示需要36号归档文件

查看sequence 36

RMAN>listbackupsetofarchivelogsequence36;ListofBackupSets===================BSKeySizeDeviceTypeElapsedTimeCompletionTime---------------------------------------------------------10463.58MDISK00:00:422020080115:14:08BPKey:10Status:UNAVAILABLECompressed:NOTag:ARCH_CJCDBPieceName:/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakListofArchivedLogsinbackupset10ThrdSeqLowSCNLowTimeNextSCNNextTime---------------------------------------------------------13622786402020080114:33:0122807052020080115:07:56

需要备份片10

RMAN>changebackupset10available;

解压归档

RMAN>restorearchivelogsequence36;[oracle@cjcos01chendb]$ls/arch/chendb_1_36_1042210216.arc

恢复数据库

RMAN>run{allocatechanneld1typedisk;restoredatabaseuntilscn2280684;recoverdatabaseuntilscn2280684;releasechanneld1;}allocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrestoreat2020080116:53:20channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00002to/u01/app/oracle11/oradata/chendb/sysaux01.dbfchanneld1:restoringdatafile00004to/u01/app/oracle11/oradata/chendb/users01.dbfchanneld1:restoringdatafile00006to/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:25channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00001to/u01/app/oracle11/oradata/chendb/system01.dbfchanneld1:restoringdatafile00003to/u01/app/oracle11/oradata/chendb/undotbs01.dbfchanneld1:restoringdatafile00005to/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:35Finishedrestoreat2020080116:54:21Startingrecoverat2020080116:54:21channeld1:startingincrementaldatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetdestinationforrestoreofdatafile00002:/u01/app/oracle11/oradata/chendb/sysaux01.dbfdestinationforrestoreofdatafile00004:/u01/app/oracle11/oradata/chendb/users01.dbfdestinationforrestoreofdatafile00006:/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.baktag=1_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:03channeld1:startingincrementaldatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetdestinationforrestoreofdatafile00001:/u01/app/oracle11/oradata/chendb/system01.dbfdestinationforrestoreofdatafile00003:/u01/app/oracle11/oradata/chendb/undotbs01.dbfdestinationforrestoreofdatafile00005:/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.baktag=1_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:01startingmediarecoveryarchivedlogforthread1withsequence36isalreadyondiskasfile/arch/chendb_1_36_1042210216.arcarchivedlogfilename=/arch/chendb_1_36_1042210216.arcthread=1sequence=36mediarecoverycomplete,elapsedtime:00:00:01Finishedrecoverat2020080116:54:26releasedchannel:d1

恢复成功

SQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#CHECKPOINT_CHANGE#----------------------------1228068422280684322806844228068452280684622806846rowsselected.

场景三：使用0级+归档备份恢复数据库

设置增量备份失效

RMAN>changebackupset5unavailable;changebackupset6unavailable;changebackupset7unavailable;changebackupset8unavailable;changebackupset9available;changebackupset10available;changebackupset11available;

查看备份状态

执行完全恢复

RMAN>run{allocatechanneld1typedisk;restoredatabase;recoverdatabase;releasechanneld1;}releasedchannel:ORA_DISK_1allocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrestoreat2020080117:12:20channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00002to/u01/app/oracle11/oradata/chendb/sysaux01.dbfchanneld1:restoringdatafile00004to/u01/app/oracle11/oradata/chendb/users01.dbfchanneld1:restoringdatafile00006to/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:25channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00001to/u01/app/oracle11/oradata/chendb/system01.dbfchanneld1:restoringdatafile00003to/u01/app/oracle11/oradata/chendb/undotbs01.dbfchanneld1:restoringdatafile00005to/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:35Finishedrestoreat2020080117:13:20Startingrecoverat2020080117:13:20startingmediarecoveryarchivedlogforthread1withsequence36isalreadyondiskasfile/arch/chendb_1_36_1042210216.arcchanneld1:startingarchivedlogrestoretodefaultdestinationchanneld1:restoringarchivedlogarchivedlogthread=1sequence=34channeld1:restoringarchivedlogarchivedlogthread=1sequence=35channeld1:readingfrombackuppiece/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakchanneld1:piecehandle=/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.baktag=ARCH_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:15archivedlogfilename=/arch/chendb_1_34_1042210216.arcthread=1sequence=34archivedlogfilename=/arch/chendb_1_35_1042210216.arcthread=1sequence=35archivedlogfilename=/arch/chendb_1_36_1042210216.arcthread=1sequence=36channeld1:startingarchivedlogrestoretodefaultdestinationchanneld1:restoringarchivedlogarchivedlogthread=1sequence=37channeld1:restoringarchivedlogarchivedlogthread=1sequence=38channeld1:restoringarchivedlogarchivedlogthread=1sequence=39channeld1:readingfrombackuppiece/rmanbak/db/arch_CHENDB_20200801_0bv6pau4_1_1.bakchanneld1:piecehandle=/rmanbak/db/arch_CHENDB_20200801_0bv6pau4_1_1.baktag=ARCH_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:01archivedlogfilename=/arch/chendb_1_37_1042210216.arcthread=1sequence=37mediarecoverycomplete,elapsedtime:00:00:01Finishedrecoverat2020080117:13:44releasedchannel:d1

恢复成功

场景四：完全恢复

将所有备份设置为可用

RMAN>changebackupset5available;changebackupset6available;changebackupset7available;changebackupset8available;

查看备份状态

RMAN>listbackupsetsummary;ListofBackups===============KeyTYLVSDeviceTypeCompletionTime#Pieces#CopiesCompressedTag-------------------------------------------------------------------1B0ADISK2020080114:33:0611NO0_RMANBAK_CJCDB2B0ADISK2020080114:33:2611NO0_RMANBAK_CJCDB3B0ADISK2020080114:33:3011NO0_RMANBAK_CJCDB4B0ADISK2020080114:33:3111NO0_RMANBAK_CJCDB5B1ADISK2020080115:08:4111NO1_RMANBAK_CJCDB6B1ADISK2020080115:08:4811NO1_RMANBAK_CJCDB7B1ADISK2020080115:08:4911NO1_RMANBAK_CJCDB8B1ADISK2020080115:08:5011NO1_RMANBAK_CJCDB9BAADISK2020080115:14:0711NOARCH_CJCDB10BAADISK2020080115:14:0811NOARCH_CJCDB11BAADISK2020080115:14:1311NOARCH_CJCDB12BFADISK2020080115:14:1511NOTAG20200801T151414releasedchannel:ORA_DISK_1allocatedchannel:d1channeld1:SID=20devicetype=DISKStartingrestoreat2020080117:25:24channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00002to/u01/app/oracle11/oradata/chendb/sysaux01.dbfchanneld1:restoringdatafile00004to/u01/app/oracle11/oradata/chendb/users01.dbfchanneld1:restoringdatafile00006to/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:25channeld1:startingdatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetchanneld1:restoringdatafile00001to/u01/app/oracle11/oradata/chendb/system01.dbfchanneld1:restoringdatafile00003to/u01/app/oracle11/oradata/chendb/undotbs01.dbfchanneld1:restoringdatafile00005to/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.baktag=0_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:35Finishedrestoreat2020080117:26:24Startingrecoverat2020080117:26:24channeld1:startingincrementaldatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetdestinationforrestoreofdatafile00002:/u01/app/oracle11/oradata/chendb/sysaux01.dbfdestinationforrestoreofdatafile00004:/u01/app/oracle11/oradata/chendb/users01.dbfdestinationforrestoreofdatafile00006:/u01/app/oracle11/oradata/chendb/cjctbs01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.baktag=1_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:01channeld1:startingincrementaldatafilebackupsetrestorechanneld1:specifyingdatafile(s)torestorefrombackupsetdestinationforrestoreofdatafile00001:/u01/app/oracle11/oradata/chendb/system01.dbfdestinationforrestoreofdatafile00003:/u01/app/oracle11/oradata/chendb/undotbs01.dbfdestinationforrestoreofdatafile00005:/u01/app/oracle11/oradata/chendb/example01.dbfchanneld1:readingfrombackuppiece/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bakchanneld1:piecehandle=/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.baktag=1_RMANBAK_CJCDBchanneld1:restoredbackuppiece1channeld1:restorecomplete,elapsedtime:00:00:01startingmediarecoveryarchivedlogforthread1withsequence36isalreadyondiskasfile/arch/chendb_1_36_1042210216.arcarchivedlogforthread1withsequence37isalreadyondiskasfile/arch/chendb_1_37_1042210216.arcarchivedlogforthread1withsequence38isalreadyondiskasfile/arch/chendb_1_38_1042210216.arcarchivedlogforthread1withsequence39isalreadyondiskasfile/arch/chendb_1_39_1042210216.arcarchivedlogfilename=/arch/chendb_1_36_1042210216.arcthread=1sequence=36archivedlogfilename=/arch/chendb_1_37_1042210216.arcthread=1sequence=37mediarecoverycomplete,elapsedtime:00:00:01Finishedrecoverat2020080117:26:28releasedchannel:d1

恢复完成，启动数据库

SQL>alterdatabaseopen;

查看数据

SQL>conncjc/cjcSQL>select*fromtab;TNAMETABTYPECLUSTERID-----------------------------------------------T1TABLET2TABLET3TABLET5TABLET6TABLE6rowsselected.

场景五：异地恢复常用命令

1 恢复参数文件

RMAN>startupnomountRMAN>restorespfileto'/rmandata/spfilecjcdb01.ora'from'/0712/rman/db/lev0_CJCDB01_20200711_04v52r5k_1_1.bak';SQL>shutdownimmedaiteSQL>createpfile='/rmandata/initcjcdb01.ora'fromspfile='/rmandata/spfilecjcdb01.ora';---修改initcjcdb01.ora参数SQL>startupnomountpfile='/rmandata/initcjcdb01.ora'

2 恢复控制文件

RMAN>listbackupsetofcontrolfile;run{allocatechannelc1typedisk;restorecontrolfilefrom'/0712/rman/db/ctl_CJCDB01_20200712_1vv54j7m_1_1.ctl';releasechannelc1;}

3 查看文件路径

selectnamefromv$datafileunionallselectnamefromv$tempfileunionallselectnamefromv$controlfileunionallselectmemberfromv$logfile;

4 生成转换文件位置的语句

SQL>setline150setpage100setheadingoffselect'setnewnamefordatafile'||''''||name||''''||'to'||chr(39)||replace(name,'+DATA/cjcdb/datafile/','/rmandata/db/')||''';'from(selectnamefromv$datafileunionallselectnamefromv$tempfileunionallselectnamefromv$controlfileunionallselectmemberfromv$logfile);

###setnewnamefordatafile'+DATA/cjcdb/datafile/system.256.1039343535'to'/rmandata/db/system.256.1039343535';setnewnamefordatafile'+DATA/cjcdb/datafile/sysaux.257.1039343537'to'/rmandata/db/sysaux.257.1039343537';setnewnamefordatafile'+DATA/cjcdb/datafile/undotbs1.258.1039343539'to'/rmandata/db/undotbs1.258.1039343539';setnewnamefordatafile'+DATA/cjcdb/datafile/users.259.1039343539'to'/rmandata/db/users.259.1039343539';setnewnamefordatafile'+DATA/cjcdb/datafile/undotbs2.264.1039343919'to'/rmandata/db/undotbs2.264.1039343919';setnewnamefordatafile'+DATA/cjcdb/datafile/cjctbs.268.1043622893'to'/rmandata/db/cjctbs.268.1043622893';setnewnamefordatafile'+DATA/cjcdb/datafile/chentbs.267.1046799153'to'/rmandata/db/chentbs.267.1046799153';setnewnamefordatafile'+DATA/cjcdb/datafile/chentbs01.272.1046799329'to'/rmandata/db/chentbs01.272.1046799329';setnewnamefordatafile'+DATA/cjcdb/tempfile/temp.263.1039343731'to'+DATA/cjcdb/tempfile/temp.263.1039343731';setnewnamefordatafile'/rmandata/control01.ctl'to'/rmandata/control01.ctl';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_2.262.1039343705'to'+DATA/cjcdb/onlinelog/group_2.262.1039343705';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_1.261.1039343703'to'+DATA/cjcdb/onlinelog/group_1.261.1039343703';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_3.265.1039344119'to'+DATA/cjcdb/onlinelog/group_3.265.1039344119';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_4.266.1039344121'to'+DATA/cjcdb/onlinelog/group_4.266.1039344121';###

5 修改文件路径

如果Restore执行后执行了SWITCH DATAFILE ALL;语句，可以不用执行下面的操作。

SQL>setline150setpage100setheadingoffselect'alterdatabaserenamefile'||''''||name||''''||'to'||chr(39)||replace(name,'+DATA/cjcdb/datafile/','/rmandata/db/')||''';'from(selectnamefromv$datafileunionallselectnamefromv$tempfileunionallselectnamefromv$controlfileunionallselectmemberfromv$logfile);###alterdatabaserenamefile'+DATA/cjcdb/datafile/system.256.1039343535'to'/rmandata/db/system.256.1039343535';alterdatabaserenamefile'+DATA/cjcdb/datafile/sysaux.257.1039343537'to'/rmandata/db/sysaux.257.1039343537';alterdatabaserenamefile'+DATA/cjcdb/datafile/undotbs1.258.1039343539'to'/rmandata/db/undotbs1.258.1039343539';alterdatabaserenamefile'+DATA/cjcdb/datafile/users.259.1039343539'to'/rmandata/db/users.259.1039343539';alterdatabaserenamefile'+DATA/cjcdb/datafile/undotbs2.264.1039343919'to'/rmandata/db/undotbs2.264.1039343919';alterdatabaserenamefile'+DATA/cjcdb/datafile/cjctbs.268.1043622893'to'/rmandata/db/cjctbs.268.1043622893';alterdatabaserenamefile'+DATA/cjcdb/datafile/chentbs.267.1046799153'to'/rmandata/db/chentbs.267.1046799153';alterdatabaserenamefile'+DATA/cjcdb/datafile/chentbs01.272.1046799329'to'/rmandata/db/chentbs01.272.1046799329';alterdatabaserenamefile'+DATA/cjcdb/tempfile/temp.263.1039343731'to'+DATA/cjcdb/tempfile/temp.263.1039343731';alterdatabaserenamefile'/rmandata/control01.ctl'to'/rmandata/control01.ctl';alterdatabaserenamefile'+DATA/cjcdb/onlinelog/group_2.262.1039343705'to'+DATA/cjcdb/onlinelog/group_2.262.1039343705';alterdatabaserenamefile'+DATA/cjcdb/onlinelog/group_1.261.1039343703'to'+DATA/cjcdb/onlinelog/group_1.261.1039343703';alterdatabaserenamefile'+DATA/cjcdb/onlinelog/group_3.265.1039344119'to'+DATA/cjcdb/onlinelog/group_3.265.1039344119';alterdatabaserenamefile'+DATA/cjcdb/onlinelog/group_4.266.1039344121'to'+DATA/cjcdb/onlinelog/group_4.266.1039344121';###

6 异机恢复示例(RAC备份恢复到单机)：

RMAN>run{allocatechanneld1typedisk;setnewnamefordatafile'+DATA/cjcdb/datafile/system.256.1039343535'to'/rmandata/db/system.256.1039343535';setnewnamefordatafile'+DATA/cjcdb/datafile/sysaux.257.1039343537'to'/rmandata/db/sysaux.257.1039343537';setnewnamefordatafile'+DATA/cjcdb/datafile/undotbs1.258.1039343539'to'/rmandata/db/undotbs1.258.1039343539';setnewnamefordatafile'+DATA/cjcdb/datafile/users.259.1039343539'to'/rmandata/db/users.259.1039343539';setnewnamefordatafile'+DATA/cjcdb/datafile/undotbs2.264.1039343919'to'/rmandata/db/undotbs2.264.1039343919';setnewnamefordatafile'+DATA/cjcdb/datafile/cjctbs.268.1043622893'to'/rmandata/db/cjctbs.268.1043622893';setnewnamefordatafile'+DATA/cjcdb/datafile/chentbs.267.1046799153'to'/rmandata/db/chentbs.267.1046799153';setnewnamefordatafile'+DATA/cjcdb/datafile/chentbs01.272.1046799329'to'/rmandata/db/chentbs01.272.1046799329';setnewnamefordatafile'+DATA/cjcdb/tempfile/temp.263.1039343731'to'+DATA/cjcdb/tempfile/temp.263.1039343731';setnewnamefordatafile'/rmandata/control01.ctl'to'/rmandata/control01.ctl';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_2.262.1039343705'to'+DATA/cjcdb/onlinelog/group_2.262.1039343705';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_1.261.1039343703'to'+DATA/cjcdb/onlinelog/group_1.261.1039343703';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_3.265.1039344119'to'+DATA/cjcdb/onlinelog/group_3.265.1039344119';setnewnamefordatafile'+DATA/cjcdb/onlinelog/group_4.266.1039344121'to'+DATA/cjcdb/onlinelog/group_4.266.1039344121';restoredatabaseuntiltime"to_date('2020-07-3122:17:47','yyyy-mm-ddhh34:mi:ss')";SWITCHDATAFILEALL;recoverdatabaseuntiltime"to_date('2020-07-3122:17:47','yyyy-mm-ddhh34:mi:ss')";releasechanneld1;}

7 在某些特定场景下：

先执行了

restoredatabaseuntiltimexxx;recoverdatabaseuntiltimexxx;

recover结束后没有启动数据库，数据库处于mouont状态。

这时如果想将数据库recover到更新的时间，不需要在执行

restoredatabaseuntiltimexxx;

数据量大时，restore database耗时，耗空间。

只需要执行

recoverdatabaseuntiltimexxx+time;

但是如果在这个时间段内生成了新的数据文件，不能直接进行recover。

需要单独restore新增的数据文件，或通过下面的命令生成一个新的文件。

alterdatabasecreatedatafile'+DATA/cjcdb/datafile/cjc_data_5.dbf'as'/oradata/cjcdb/datafile/cjc_data_5.dbf';