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

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！