Oracle 11g Data Recovery Advisor

October 12, 2016 by S4

Filed under Oracle

Last modified October 12, 2016

1.1 Introduction

 

Oracle 11g has introduced the new tool for automatically diagnose and repair data failures and corruption, which is called as Data Recovery Advisor. It analyzes failures and determines optimal repair strategies.

 

1.2 Scope

The scope of this document is restricted to the Data Recovery Advisor feature of Oracle 11g with RMAN.

 

1.3 Overview

In oracle 11g Data Recovery Advisor is used to fix problems such as lost data files, data block corruption, I/O failures, and a Crashed database. The Health Monitor automatically runs a data integrity check whenever it finds an error in the database. The data integrity check searches the database for any failures that caused the errors. A failure is defined as a problem such as data corruption that is diagnosed by the database. All diagnosed failures are stored in the ADR.

 

 

 

 

 

1.4 Using Data Recovery Advisor with RMAN.

 

To describe this new feature I am considering a scenario in which Users tablespace is corrupted and assuming that we are having a valid backup available taken through rman.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  368263168 bytes

Fixed Size                  1347384 bytes

Variable Size             343933128 bytes

Database Buffers           16777216 bytes

Redo Buffers                6205440 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 – see DBWR trace file

ORA-01110: data file 4: ‘E:\ORACLE\ORADATA\BANK\USERS01.DBF’

 

 

 

 

1) Connect to rman and give the below command to list the database failures.

 

RMAN> list failure;

 

using target database control file instead of recovery catalog

List of Database Failures

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

 

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

1062       HIGH     OPEN      12-APR-09     One or more non-system datafiles are missing

 

 

You can view the details by giving the below command.

 

2)   To list the details about the failure, give the below command.

 

RMAN> list failure 1062 detail;

 

List of Database Failures

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

 

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

1062       HIGH     OPEN      12-APR-09     One or more non-system datafiles are

missing

Impact: See impact for individual child failures

List of child failures for parent failure ID 1062

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

1612     HIGH    OPEN    12-APR-09   Datafile 4:’E:\ORACLE\ORADATA\BANK\USERS01.DBF’  is missing

Impact: Some objects in tablespace USERS might be unavailable

 

To rectify this error just give the below command.

 

3)   Run advice failure command to get recommendations from the DRA to fix the missing datafile problem.

 

RMAN> advise failure;

 

List of Database Failures

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

 

Failure ID Priority Status    Time Detected Summary

———- ——– ——— ————- ——-

1062       HIGH     OPEN      12-APR-09     One or more non-system datafiles are

missing

 

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=153 device type=DISK

analyzing automatic repair options complete

 

Mandatory Manual Actions

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

no manual actions available

 

Optional Manual Actions

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

  1. If file E:\ORACLE\PRODUCT\10.2.0\ORADATA\BANK\USERS01.DBF was unintentionally

renamed or moved, restore it

  1. If a standby database is available, then consider a Data Guard switchover or

failover

 

Automated Repair Options

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

Option Repair Description

—— ——————

1      Restore and recover datafile 4

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

Repair script: e:\oracle\product\diag\rdbms\bank\bank\hm\reco_702451002.hm

 

4)

 

RMAN> repair failure preview;

 

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

Repair script: e:\oracle\product\diag\rdbms\bank\bank\hm\reco_3550825907.hm

 

contents of repair script:

# restore and recover datafile

restore datafile 4;

recover datafile 4;

 

5) Use the repair failure command to implement the above suggested advise.

 

RMAN> repair failure;

 

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

Repair script: e:\oracle\product\diag\rdbms\bank\bank\hm\reco_702451002.hm

 

contents of repair script:

# restore and recover datafile

restore datafile 4;

recover datafile 4;

 

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

executing repair script

 

Starting restore at 12-APR-09

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 00004 to E:\ORACLE\ORADATA\BANK\USERS01.DBF

channel ORA_DISK_1: reading from backup piece E:\ORACLE\PRODUCT\10.2.0\FLASH_REC

OVERY_AREA\BANK\BACKUPSET\2009_04_12\O1_MF_NNNDF_TAG20090412T123305_4Y3NCNJ3_.BK

P

channel ORA_DISK_1: piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BA

NK\BACKUPSET\2009_04_12\O1_MF_NNNDF_TAG20090412T123305_4Y3NCNJ3_.BKP tag=TAG2009

0412T123305

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 12-APR-09

 

Starting recover at 12-APR-09

using channel ORA_DISK_1

 

starting media recovery

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

 

Finished recover at 12-APR-09

repair failure complete

 

 

 


1.5 Proactive Health Check using RMAN.

 

In oracle database 11g we Can use VALIDATE command to check for corrupt blocks and missing files or to check the consistency of the backup. If failure is detected by the VALDATE command, the RMAN logs it into the Automated Diagnostic Repository.

 

Following are the list of Validate Command which can be used for Proactive Health Check.

 

1) VALIDATE DATABAE- Check for the database and its components. It detects database failure, initiates a failure assessment and logs the failure in the ADR.

 

RMAN> validate database;

 

2) VALIDATE ARCHIVELOG ALL; — Validate the archivelog for consistency.

 

3) VALIDATE CHECK LOGICAL CURRENT CONTROLFILE; — Validate the current control file.

 

4) VALIDATE CHECK LOGICAL BACKUPSET 3; — Validates the Backupset.

 

5) VALIDATE CHECK LOGICAL TABLESPACE USERS; — Validate the tablespace users.

 

 

6)Use the below two commands to check for Physical and Logical corruption of files to be backed up.

 

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

 

7)Use the the following command to check for Physical and logical corruption of files to be restored.

 

RESTORE VALIDATE DATABASE;

RESTORE VALIDATE CHECK LOGICAL DATABASE;

Leave a Comment