RMAN快速恢复数据库（DBA再也不担心记不住指令了）

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

修复完毕后还帮你把库open了。基本上适用于所有类型的文件丢失。
妈妈再也不担心我记不住指令了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29578568/viewspace-2143635/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29578568/viewspace-2143635/