RMAN如何快速恢复数据库
小编给大家分享一下RMAN如何快速恢复数据库，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
10g会使用RMAN备份恢复一般是DBA的工作，对技术要求较高，且对oracle的组织结构有较深的理解才可以进行操作，而且由于数据库故障不易发生，大部分DBA也不会记住命令，需要的手查一下，各种文件丢失的脚本又都不一样，例如
控制文件丢失恢复指令：restore controlfile from autobackup;
redolog 丢失的情况：alter database clear (unarchived) logfile;
不完全恢复指令：recover database until cancel;
11g后rman有了更丰富的指令集和修复方法，使得普通运维人员也能迅速快速修复数据库故障，（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 |
启动数据库发现数据库已经无法启动，现在我们用两种方法来尝试恢复下：
传统的方法：
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 的快速恢复方法：
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 |
从以上方法还看不出自动修复的好处，那我们再增加点难度，删除所有的数据文件（不包括参数文件），对比下吧
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 |
传统处理方法，使用以下脚本可以恢复数据库到启动状态，这里就需要比较专业的知识了
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的恢复方法：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 |
可以发先已经告诉我们这些文件丢失了
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已经给出建议及执行的脚本。
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如何快速恢复数据库"有了一定的了解，如果想了解更多相关知识，欢迎关注行业资讯频道，感谢各位的阅读！
