关闭

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

786人阅读 评论(0) 收藏 举报
分类:

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

SQL> startup
ORACLE instance started.

Total System Global Area  510554112 bytes
Fixed Size                  1345968 bytes
Variable Size             171968080 bytes
Database Buffers          331350016 bytes
Redo Buffers                5890048 bytes
ORA-00205: error in identifying control file, check alert log for more info

启动数据库发现数据库已经无法启动,现在我们用两种方法来尝试恢复下:

传统的方法:

RMAN>restore controlfile from autobackup;

Starting restore at 30-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/fra
database name (or database unique name) used for search: PROD2
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/PROD2/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
Finished restore at 30-AUG-16
11g 的快速恢复方法:

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
712        CRITICAL OPEN      30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctl is missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
712        CRITICAL OPEN      30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctl is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Use a multiplexed copy to restore control file /u01/app/oracle/oradata/PROD2/control01.ctl  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm

RMAN><span style="color:#ff0000;"> <strong>repair failure;</strong></span>

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl';
   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 30-AUG-16
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/PROD2/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
Finished restore at 30-AUG-16

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete


从以上方法还看不出自动修复的好处,那我们再增加点难度,删除所有的数据文件(不包括参数文件),对比下吧

SQL> startup
ORACLE instance started.

Total System Global Area  510554112 bytes
Fixed Size                  1345968 bytes
Variable Size             171968080 bytes
Database Buffers          331350016 bytes
Redo Buffers                5890048 bytes
ORA-00205: error in identifying control file, check alert log for more info
传统处理方法,使用以下脚本可以恢复数据库到启动状态,这里就需要比较专业的知识了

 run{
 restore controlfile from autobackup;
 alter database mount;
 restore database;
 recover database until cancel;
 alter database open resetlogs;
 };
接下来是11g的恢复方法:list-advise-repair

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
958        CRITICAL OPEN      30-AUG-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' is missing
915        CRITICAL OPEN      30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctl is missing
838        CRITICAL OPEN      30-AUG-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' needs media recovery
835        CRITICAL OPEN      30-AUG-16     Control file needs media recovery
415        HIGH     OPEN      30-AUG-16     One or more non-system datafiles are missing
841        HIGH     OPEN      30-AUG-16     One or more non-system datafiles need media recovery
可以发先已经告诉我们这些文件丢失了

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
958        CRITICAL OPEN      30-AUG-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' is missing
915        CRITICAL OPEN      30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctl is missing
838        CRITICAL OPEN      30-AUG-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' needs media recovery
835        CRITICAL OPEN      30-AUG-16     Control file needs media recovery
415        HIGH     OPEN      30-AUG-16     One or more non-system datafiles are missing
841        HIGH     OPEN      30-AUG-16     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
915        CRITICAL OPEN      30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctl is missing

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Use a multiplexed copy to restore control file /u01/app/oracle/oradata/PROD2/control01.ctl  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm

rman已经给出建议及执行的脚本。

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl';
   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)?yes
executing repair script

Starting restore at 30-AUG-16
using channel ORA_DISK_1


channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/PROD2/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
Finished restore at 30-AUG-16


sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1230       CRITICAL OPEN      30-AUG-16     Redo log group 3 is unavailable
1224       CRITICAL OPEN      30-AUG-16     Redo log group 2 is unavailable
1218       CRITICAL OPEN      30-AUG-16     Redo log group 1 is unavailable
958        CRITICAL OPEN      30-AUG-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' is missing
838        CRITICAL OPEN      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.log is missing
1227       HIGH     OPEN      30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo02.log is missing
1221       HIGH     OPEN      30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo01.log is missing
415        HIGH     OPEN      30-AUG-16     One or more non-system datafiles are missing
841        HIGH     OPEN      30-AUG-16     One or more non-system datafiles need media recovery
RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1230       CRITICAL OPEN      30-AUG-16     Redo log group 3 is unavailable
1224       CRITICAL OPEN      30-AUG-16     Redo log group 2 is unavailable
1218       CRITICAL OPEN      30-AUG-16     Redo log group 1 is unavailable
958        CRITICAL OPEN      30-AUG-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' is missing
838        CRITICAL OPEN      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.log is missing
1227       HIGH     OPEN      30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo02.log is missing
1221       HIGH     OPEN      30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo01.log is missing
415        HIGH     OPEN      30-AUG-16     One or more non-system datafiles are missing
841        HIGH     OPEN      30-AUG-16     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD2/redo03.log was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD2/redo02.log was unintentionally renamed or moved, restore it
3. If file /u01/app/oracle/oradata/PROD2/redo01.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform incomplete database recovery to SCN 1206859  
  Strategy: The repair includes point-in-time recovery with some data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm
RMAN> repair failure;

Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm

contents of repair script:
   # database point-in-time recovery
   reset database to incarnation 5;
   restore database until scn 1206859;
   recover database until scn 1206859;
   alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

database reset to incarnation 5

Starting restore at 30-AUG-16
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD2/users01.dbf
channel ORA_DISK_1: reading from backup 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, elapsed time: 00:00:15
Finished restore at 30-AUG-16

Starting recover at 30-AUG-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc
archived log file name=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:02
Finished recover at 30-AUG-16

database opened
repair failure complete
修复完毕后还帮你把库open了。基本上适用于所有类型的文件丢失。

妈妈再也不担心我记不住指令了哭哭哭


1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:29689次
    • 积分:714
    • 等级:
    • 排名:千里之外
    • 原创:41篇
    • 转载:1篇
    • 译文:1篇
    • 评论:4条
    最新评论