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

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！