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

原创 2016年08月30日 07:56:41

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了。基本上适用于所有类型的文件丢失。

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


版权声明:本文为博主原创文章,未经博主允许不得转载。

几种RMAN恢复场景

前期准备: 做一次全库备份: [oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Productio...
  • cscscscsc
  • cscscscsc
  • 2016年09月10日 22:39
  • 1647

利用RMAN恢复整个数据库

利用RMAN恢复整个数据库案例一 适合场合:恢复的目录一致,同时备份的过程中有归档日志 恢复的数据库目录和down机的数据库一致,还有一个就是RMAN备份的时候已经备份了归档日志。 备份脚本:...
  • u013012406
  • u013012406
  • 2017年02月25日 09:27
  • 1437

Oracle数据库备份与恢复 - RMAN恢复

RMAN恢复原理首先还是得理解Oracle数据库恢复的一个原理。数据库恢复是指将数据库恢复到一个一致性的状态,整个恢复操作可以分为两个步骤,数据库修复(RESTORE)和恢复(RECOVER)。Ora...
  • pan_tian
  • pan_tian
  • 2015年07月15日 17:16
  • 6875

Oracle DBA 数据库备份与恢复学习笔记

Oracle DBA数据库备份与恢复 1.Oracle数据库文件     ·初始化参数文件(Instance Parameter File)     ·控制文件(control files)     ...
  • gyming
  • gyming
  • 2013年11月20日 21:11
  • 1661

Oracle数据库备份与恢复 - RMAN备份

如果要说DBA工作中最重要的职责(没有之一),那无疑就是保证客户数据的安全和完整,可以看到几乎任何一本Oracle DBA的技术书籍一定都会把大篇幅来介绍数据库的备份与恢复,从中也可以看到备份和恢复的...
  • pan_tian
  • pan_tian
  • 2015年07月05日 23:10
  • 4150

迁移数据库——rman异地恢复

数据库版本是:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 1、记录一下源数据库的DBID ...
  • zq9017197
  • zq9017197
  • 2013年07月02日 11:00
  • 2308

[RMAN]使用RMAN备份将数据库不完全恢复到指定时间点

原文地址:http://blog.itpub.net/519536/viewspace-709383/ RMAN作为Oracle强大的备份恢复工具,可以协助我们恢复数据库到指定时间点,这便是Oracl...
  • u011364306
  • u011364306
  • 2015年11月24日 15:32
  • 311

ORACLE11G RMAN备份恢复到异机数据库

orapwd file='/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwprb' password=oracle entries=10 force=y ...
  • xiegh2014
  • xiegh2014
  • 2016年08月17日 17:28
  • 1712

oracle学习系列之RMAN异地恢复数据

一:通过RMAN备份恢复数据库到其他服务器     设定环境:              源库:10.0.17.220   SID:zsddb          获取SID的方式:   ...
  • zhenglib18
  • zhenglib18
  • 2015年06月10日 11:28
  • 390

ORACLE 11G 中采用rman备份异机恢复数据库详细过程

场景:        有一个生产库的用户下面所有的表都不见了,怀疑人为被删除了,现在需要用备份去恢复下,找出原来的表,线上是oracle dataguard环境,有全库备份文件,准备去测试库恢复一下。...
  • mchdba
  • mchdba
  • 2015年02月03日 16:29
  • 7427
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:RMAN快速恢复数据库(DBA再也不担心记不住指令了)
举报原因:
原因补充:

(最多只允许输入30个字)