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

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


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

相关文章推荐

Android 查看依赖的方法,妈妈再也不用担心jar包冲突找不到了

前言我们开发过程中,难免会遇到这样的情况:在github等网站上找到了几个不错的项目,高高兴兴的compile过来,然后:UNEXPECTED TOP-LEVEL EXCEPTION: com.and...

Android 3步搞定事件分发机制,再也不用担心onTouch和onTouchEvent&dispatchTouchEvent

事件分发机制分为2种:View事件的分发和ViewGroup事件分发机制 先看简单的View事件分发机制//子控件的ontouch方法影响子控件的函数 //onTouch====onTouchEve...

【FastDev4Android框架开发】神器ViewDragHelper完全解析,妈妈再也不担心我自定义ViewGroup滑动View操作啦~(三十三)

(一).前言:            这几天正在更新录制实战项目,整体框架是采用仿照QQ5.X侧滑效果的。那么我们一般的做法就是自定义ViewGroup或者采用开源项目MenuDrawer或...

自动缩放布局,以后再也不用担心适配问题了

有了这个,android开发再也不用担心适配问题了

android开源框架emojicon,妈妈再也不用担心我没表情了

最近发现了一个emoji表情框架,在githubhttps://github.com/rockerhieu/emojicon中,图片多到不能自理,不过了解了实现原理之后基本可以根据自己需要修改或者自己...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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