Oracle Data Recovery Advisor

Oracle Data Recovery Advisor

实验说明：
(1)数据库CHENJCH2出现故障(当前Redo logfile和USERS数据文件丢失)，导致CHENJCH2数据库无法OPEN;
(2)数据库CHENJCH2启用归档模式，有RMAN全备和归档备份，Catalog数据库为CHENJCH4；
(3)通过Oracle 11g New Feature: RMAN Data Recovery Advisor进行CHENJCH2数据库修复；

实现具体步骤如下：

---1 CHENJCH2实例无法OPEN；
SYS@CHENJCH2>startup
ORACLE instance started.
Total System Global Area  941600768 bytes
Fixed Size                  1348860 bytes
Variable Size             524290820 bytes
Database Buffers          411041792 bytes
Redo Buffers                4919296 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/CHENJCH2/users01.dbf'

---2 连接RMAN
[oracle@edbjr2p1 Skillset]$ rman target / catalog rc_admin/RC_ADMIN@CHENJCH4
Recovery Manager: Release 11.2.0.3.0 - CHENJCHuction on Sun Jul 9 15:11:52 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CHENJCH2 (DBID=1512727797, not open)
connected to recovery catalog database

---3 通过list failure列出当前数据库故障
RMAN> list failure;
starting full resync of recovery catalog
full resync complete
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
102        CRITICAL OPEN      09-JUL-17     Redo log group 1 is unavailable
108        HIGH     OPEN      09-JUL-17     One or more non-system datafiles are missing
105        HIGH     OPEN      09-JUL-17     Redo log file /u01/app/oracle/oradata/CHENJCH2/redo01.log is missing

---4 通过advise failure列出故障修复建议
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
102        CRITICAL OPEN      09-JUL-17     Redo log group 1 is unavailable
108        HIGH     OPEN      09-JUL-17     One or more non-system datafiles are missing
105        HIGH     OPEN      09-JUL-17     Redo log file /u01/app/oracle/oradata/CHENJCH2/redo01.log is missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 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/CHENJCH2/redo01.log was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform incomplete database recovery to SCN 1015718  
  Strategy: The repair includes point-in-time recovery with some data loss
  Repair script: /u01/app/oracle/diag/rdbms/CHENJCH2/CHENJCH2/hm/reco_3227818300.hm

---5 查看Oracle自动生成的故障修复建议
[oracle@edbjr2p1 Skillset3]$ cat /u01/app/oracle/diag/rdbms/CHENJCH2/CHENJCH2/hm/reco_3227818300.hm
   # database point-in-time recovery  
  restore database until scn 1015718;
   recover database until scn 1015718;
   alter database open resetlogs;

为什么要将SCN恢复到1015718呢？
查看v$log视图可以发现，redo logfile 1,是从 1015718  开始，但是由于 redo logfile 1丢失，导致 redo logfile 1无法继续写入，也就是数据库 最多只能恢复到1015718 ；

SYS@CHENJCH2>select group#,status,first_change#,next_change# from v$log;
    GROUP# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------------- ------------- ------------
         1 CURRENT                1015718   2.8147E+14
         3 INACTIVE               1015635      1015718
         2 INACTIVE               1007775      1015635

---6 执行自动恢复repair failure
RMAN> repair failure;
Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/app/oracle/diag/rdbms/CHENJCH2/CHENJCH2/hm/reco_2527946308.hm

contents of repair script:
   # database point-in-time recovery
   restore database until scn 1015718;
   recover database until scn 1015718;
   alter database open resetlogs;

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

Starting restore at 09-JUL-17
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/CHENJCH2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/CHENJCH2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CHENJCH2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CHENJCH2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/CHENJCH2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CHENJCH2/backupset/2017_07_08/o1_mf_nnndf_TAG20170708T181811_dp1dc4w0_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CHENJCH2/backupset/2017_07_08/o1_mf_nnndf_TAG20170708T181811_dp1dc4w0_.bkp tag=TAG20170708T181811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:29
Finished restore at 09-JUL-17

Starting recover at 09-JUL-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 09-JUL-17

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
repair failure complete

---7 修复完成，CHENJCH2数据库自动OPEN；
SYS@CHENJCH2>select status from v$instance;
STATUS
------------
OPEN

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

转载于:http://blog.itpub.net/29785807/viewspace-2141785/