[Recovery Manager] Data Recovery Advisor (11g New Feature)

- 11g 부터 추가된 기능

- 장애를 발생했을 경우 장애를 해결하는데 도움을 주는 기능

- OEM 또는 command line 사용가능

[주요 명령어]

RMAN > list failure; --장애확인

RMAN > list failure failure_ID detail; -- 상세한 에러내용 확인

RMAN > advise failure; --해결방법찾기

RMAN > repaire failure preview; --해결방법찾기

RMAN > repair failure; --복구(장애해결)

[실습]

## 테이블스페이스 생성 후 offline

SYS>create tablespace ts_b datafile '/app/oracle/oradata/testdb/ts_b01.dbf' size 10M;

Tablespace created.

SYS>alter tablespace ts_b offline;

Tablespace altered.

SYS>@dd

TABLESPACE         MB FILE_NAME

---------- ---------- ---------------------------------------------

USERS              10 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1           10 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX            600 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM            710 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE       345.625 /app/oracle/oradata/testdb/example01.dbf

TS_B                  /app/oracle/oradata/testdb/ts_b01.dbf --offline되어있음

6 rows selected.

## data file을 삭제하여 장애발생

SYS>!rm -rf /app/oracle/oradata/testdb/ts_b01.dbf

SYS>!ls /app/oracle/oradata/testdb/ts_b01.dbf

ls: /app/oracle/oradata/testdb/ts_b01.dbf: 그런 파일이나 디렉토리가 없음

SYS>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

## RMAN으로 접속하여 에러내용 확인

[oracle@localhost ~]$ rman target sys/oracle

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Nov 17 18:12:51 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2581382545)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

94         HIGH     OPEN      17-NOV-14     Tablespace 8: 'TS_B' is offline

88         HIGH     OPEN      17-NOV-14     One or more non-system datafiles are missing

82         HIGH     OPEN      17-NOV-14     One or more non-system datafiles are offline

## 상세한 에러 내용 확인

RMAN> list failure 88 detail;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

88         HIGH     OPEN      17-NOV-14     One or more non-system datafiles are missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 88

  Failure ID Priority Status    Time Detected Summary

  ---------- -------- --------- ------------- -------

  91         HIGH     OPEN      17-NOV-14     Datafile 6: '/app/oracle/oradata/testdb/ts_b01.dbf' is missing

    Impact: Some objects in tablespace TS_B might be unavailable

## 에러내용에 관해서 해결방법을 찾아줌

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

88         HIGH     OPEN      17-NOV-14     One or more non-system datafiles are missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 88

  Failure ID Priority Status    Time Detected Summary

  ---------- -------- --------- ------------- -------

  91         HIGH     OPEN      17-NOV-14     Datafile 6: '/app/oracle/oradata/testdb/ts_b01.dbf' is missing

    Impact: Some objects in tablespace TS_B might be unavailable

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /app/oracle/oradata/testdb/ts_b01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Restore and recover datafile 6

  Strategy: The repair includes complete media recovery with no data loss 

--> 완전복구가 가능하고 데이터 손상이 없다고 조언해줌

  Repair script: /app/oracle/diag/rdbms/testdb/testdb/hm/reco_2438082205.hm 

--> 해당경로에 repair script 생성해줌

## Repair script 바로 확인하기 

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /app/oracle/diag/rdbms/testdb/testdb/hm/reco_2279735495.hm

contents of repair script:

   # restore and recover datafile

   restore datafile 6;

   recover datafile 6;

## RMAN에서 나가서 vi로 script를 열어 확인

RMAN> exit

Recovery Manager complete.

[oracle@localhost ~]$ vi /app/oracle/diag/rdbms/testdb/testdb/hm/reco_2438082205.hm

   # restore and recover datafile

   restore datafile 6;

   recover datafile 6;

## Data Recovery Advisor가 제시한 방법대로 복구 수행

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /app/oracle/diag/rdbms/testdb/testdb/hm/reco_2279735495.hm

contents of repair script:

   # restore and recover datafile

   restore datafile 6;

   recover datafile 6;

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

executing repair script

Starting restore at 17-NOV-14

using channel ORA_DISK_1

creating datafile file number=6 name=/app/oracle/oradata/testdb/ts_b01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 17-NOV-14

Starting recover at 17-NOV-14

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 17-NOV-14

repair failure complete

RMAN> exit

## 복구확인 -> 데이터파일 복구되어 생성됨

Recovery Manager complete.

[oracle@localhost ~]$

[oracle@localhost ~]$ cd /app/oracle/oradata/testdb/

[oracle@localhost testdb]$ ls

control01.ctl  redo01_b.log  redo03_a.log  system01.dbf  undo01.dbf

example01.dbf  redo02_a.log  redo03_b.log  temp01.dbf    undotbs01.dbf

redo01_a.log   redo02_b.log  sysaux01.dbf  ts_b01.dbf    users01.db