Tales From A Lazy Fat DBA

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