溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下RMAN如何快速恢復(fù)數(shù)據(jù)庫，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
10g會使用RMAN備份恢復(fù)一般是DBA的工作，對技術(shù)要求較高，且對oracle的組織結(jié)構(gòu)有較深的理解才可以進行操作，而且由于數(shù)據(jù)庫故障不易發(fā)生，大部分DBA也不會記住命令，需要的手查一下，各種文件丟失的腳本又都不一樣，例如
控制文件丟失恢復(fù)指令：restore controlfile from autobackup;
redolog 丟失的情況：alter database clear (unarchived) logfile;
不完全恢復(fù)指令：recover database until cancel;
11g后rman有了更豐富的指令集和修復(fù)方法，使得普通運維人員也能迅速快速修復(fù)數(shù)據(jù)庫故障，（list 、advise、repair）
見如下實驗。
第一種情況，模擬控制文件丟失，刪除controlfile
1 2 3 4 5 6 7 8 9 | SQL> startup ORACLE instance started.
Total SystemGlobalArea 510554112 bytes FixedSize 1345968 bytes VariableSize 171968080 bytes DatabaseBuffers 331350016 bytes Redo Buffers 5890048 bytes ORA-00205: errorinidentifying control file,checkalert logformore info |
啟動數(shù)據(jù)庫發(fā)現(xiàn)數(shù)據(jù)庫已經(jīng)無法啟動，現(xiàn)在我們用兩種方法來嘗試恢復(fù)下：
傳統(tǒng)的方法：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | RMAN>restore controlfile from autobackup;
Starting restoreat30-AUG-16 using targetdatabasecontrol fileinsteadofrecovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK
recovery area destination: /u01/app/oracle/fra databasename(ordatabaseuniquename) usedforsearch: PROD2 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp foundinthe recovery area AUTOBACKUP searchwithformat"%F"notattempted because DBID wasnotset channel ORA_DISK_1: restoring control filefromAUTOBACKUP /u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp channel ORA_DISK_1: control file restorefromAUTOBACKUP complete outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl Finished restoreat30-AUG-16 |
11g 的快速恢復(fù)方法：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | RMAN> list failure;
using targetdatabasecontrol fileinsteadofrecovery catalog ListofDatabaseFailures =========================
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 712 CRITICALOPEN 30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing
RMAN> advise failure;
ListofDatabaseFailures =========================
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 712 CRITICALOPEN 30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing
analyzing automatic repair options; this may takesometime allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK analyzing automatic repair options complete
Mandatory Manual Actions ======================== nomanual actions available
Optional Manual Actions ======================= nomanual actions available
Automated Repair Options ======================== OptionRepair Description ------ ------------------ 1 Use a multiplexed copytorestore control file /u01/app/oracle/oradata/PROD2/control01.ctl Strategy: The repair includes complete media recoverywithnodata loss Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm
RMAN> repair failure;
Strategy: The repair includes complete media recoverywithnodata loss Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm
contentsofrepair script: # restore control file using multiplexed copy restore controlfilefrom'/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'; sql'alter database mount';
Do you really wanttoexecutethe above repair (enter YESorNO)? yes executing repair script
Starting restoreat30-AUG-16 using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl Finished restoreat30-AUG-16
sql statement:alterdatabasemount released channel: ORA_DISK_1 repair failure complete |
從以上方法還看不出自動修復(fù)的好處，那我們再增加點難度，刪除所有的數(shù)據(jù)文件（不包括參數(shù)文件），對比下吧
1 2 3 4 5 6 7 8 9 | SQL> startup ORACLE instance started.
Total SystemGlobalArea 510554112 bytes FixedSize 1345968 bytes VariableSize 171968080 bytes DatabaseBuffers 331350016 bytes Redo Buffers 5890048 bytes ORA-00205: errorinidentifying control file,checkalert logformore |
傳統(tǒng)處理方法，使用以下腳本可以恢復(fù)數(shù)據(jù)庫到啟動狀態(tài)，這里就需要比較專業(yè)的知識了
1 2 3 4 5 6 7 | run{ restore controlfile from autobackup; alter database mount; restore database; recover database until cancel; alter database open resetlogs; }; |
接下來是11g的恢復(fù)方法：list-advise-repair
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | RMAN> list failure;
using targetdatabasecontrol fileinsteadofrecovery catalog ListofDatabaseFailures =========================
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing 915 CRITICALOPEN 30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing 838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery 835 CRITICALOPEN 30-AUG-16 Control file needs media recovery 415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing 841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery |
可以發(fā)先已經(jīng)告訴我們這些文件丟失了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | RMAN> advise failure;
ListofDatabaseFailures =========================
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing 915 CRITICALOPEN 30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing 838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery 835 CRITICALOPEN 30-AUG-16 Control file needs media recovery 415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing 841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery
analyzing automatic repair options; this may takesometime allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK analyzing automatic repair options complete
Notallspecified failures can currently be repaired. The following failures must be repaired before adviseforothers can be given.
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 915 CRITICALOPEN 30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing
Mandatory Manual Actions ======================== nomanual actions available
Optional Manual Actions ======================= nomanual actions available
Automated Repair Options ======================== OptionRepair Description ------ ------------------ 1 Use a multiplexed copytorestore control file /u01/app/oracle/oradata/PROD2/control01.ctl Strategy: The repair includes complete media recoverywithnodata loss Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm |
rman已經(jīng)給出建議及執(zhí)行的腳本。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | RMAN> repair failure;
Strategy: The repair includes complete media recoverywithnodata loss Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm
contentsofrepair script: # restore control file using multiplexed copy restore controlfilefrom'/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'; sql'alter database mount';
Do you really wanttoexecutethe above repair (enter YESorNO)?yes executing repair script
Starting restoreat30-AUG-16 using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl Finished restoreat30-AUG-16
sql statement:alterdatabasemount released channel: ORA_DISK_1 repair failure complete |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | RMAN> list failure;
ListofDatabaseFailures =========================
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 1230 CRITICALOPEN 30-AUG-16 Redo loggroup3isunavailable 1224 CRITICALOPEN 30-AUG-16 Redo loggroup2isunavailable 1218 CRITICALOPEN 30-AUG-16 Redo loggroup1isunavailable 958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing 838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery 1233 HIGH OPEN 30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo03.logismissing 1227 HIGH OPEN 30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo02.logismissing 1221 HIGH OPEN 30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo01.logismissing 415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing 841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | RMAN> advise failure;
ListofDatabaseFailures =========================
Failure ID Priority Status TimeDetected Summary ---------- -------- --------- ------------- ------- 1230 CRITICALOPEN 30-AUG-16 Redo loggroup3isunavailable 1224 CRITICALOPEN 30-AUG-16 Redo loggroup2isunavailable 1218 CRITICALOPEN 30-AUG-16 Redo loggroup1isunavailable 958 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing 838 CRITICALOPEN 30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery 1233 HIGH OPEN 30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo03.logismissing 1227 HIGH OPEN 30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo02.logismissing 1221 HIGH OPEN 30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo01.logismissing 415 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles are missing 841 HIGH OPEN 30-AUG-16 Oneormore non-system datafiles need media recovery
analyzing automatic repair options; this may takesometime allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK analyzing automatic repair options complete
Mandatory Manual Actions ======================== nomanual actions available
Optional Manual Actions ======================= 1. If file /u01/app/oracle/oradata/PROD2/redo03.log was unintentionally renamedormoved, restore it 2. If file /u01/app/oracle/oradata/PROD2/redo02.log was unintentionally renamedormoved, restore it 3. If file /u01/app/oracle/oradata/PROD2/redo01.log was unintentionally renamedormoved, restore it
Automated Repair Options ======================== OptionRepair Description ------ ------------------ 1 Perform incompletedatabaserecoverytoSCN 1206859 Strategy: The repair includes point-in-timerecoverywithsomedata loss Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | RMAN> repair failure;
Strategy: The repair includes point-in-timerecoverywithsomedata loss Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm
contentsofrepair script: #databasepoint-in-timerecovery resetdatabasetoincarnation 5; restoredatabaseuntil scn 1206859; recoverdatabaseuntil scn 1206859; alterdatabaseopenresetlogs;
Do you really wanttoexecutethe above repair (enter YESorNO)? YES executing repair script
databaseresettoincarnation 5
Starting restoreat30-AUG-16 using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupsetrestore channel ORA_DISK_1: specifying datafile(s)torestorefrombackupset channel ORA_DISK_1: restoring datafile 00001to/u01/app/oracle/oradata/PROD2/system01.dbf channel ORA_DISK_1: restoring datafile 00002to/u01/app/oracle/oradata/PROD2/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003to/u01/app/oracle/oradata/PROD2/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004to/u01/app/oracle/oradata/PROD2/users01.dbf channel ORA_DISK_1: readingfrombackup piece /u01/app/oracle/fra/PROD2/backupset/2016_08_24/o1_mf_nnndf_TAG20160824T111405_cvt47yrv_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/PROD2/backupset/2016_08_24/o1_mf_nnndf_TAG20160824T111405_cvt47yrv_.bkp tag=TAG20160824T111405 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsedtime: 00:00:15 Finished restoreat30-AUG-16
Starting recoverat30-AUG-16 using channel ORA_DISK_1
starting media recovery
archived logforthread 1withsequence3isalreadyondiskasfile /u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc archived logforthread 1withsequence4isalreadyondiskasfile /u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc archived logforthread 1withsequence5isalreadyondiskasfile /u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc thread=1sequence=3 archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc thread=1sequence=4 archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc thread=1sequence=5 media recovery complete, elapsedtime: 00:00:02 Finished recoverat30-AUG-16
databaseopened repair failure complete |
看完了這篇文章，相信你對“RMAN如何快速恢復(fù)數(shù)據(jù)庫”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
