Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 148,961
  • Archives

  • Categories

  • Subscribe

  • Advertisements

11g Data Recovery Advisor: Diagnosing and Repairing Failures

Posted by FatDBA on December 9, 2012

Oracle’s 11g New Feature ‘Data Recovery Advisor’ is one of the most important tool introduced by The Red Giants with the release of r2. I will of course consider this functionality as one of the most impressive and best among others added in 11g r2 package. The Repair Advisor can take away lot of the stress associated with peforming backup and recovery by diagnosing what is wrong as well as presenting us with the syntax as well to execute the commands to restore and recover as the case may be. Under pressure, everyone can make mistakes and it is comforting to know that there is a tool which can really he;p the DBA.

The Data Recovery Advisor can be used via OEM Database or Grid Control or via the RMAN command line interface.

 

Let me Explain it using a real time issue: (Missed one of the control file).
Here I’ve intentionally deleted Control File 2 (Below provided was past status)

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

 

Once the file is removed or corrupted it will start throwing below provided error message with code to check error log (Alert Log)

ORA-00205: error in identifying control file, check alert log for more info

Launch RMAN console and connect to the database (NO MOUNT Mode – Pretty Obvious!!) and check failures using ‘list failure’ command and will show you problem detected by DB engine.

 

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missi                                                                     ng

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Use a multiplexed copy to restore control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

 

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘/u01/app/oracle/oradata/orcl/control01.ctl’;
sql ‘alter database mount’;

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

Starting restore at 09-DEC-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 09-DEC-12

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

It’s back!!!!

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: