Data Recovery Advisor Oracle 11g

Data Recovery Advisor Oracle 11g

This article present Oracle 11g new features data recovery advisor. As we all DBA know there are a lot of problems occur to be resolved, So Oracle gift us easy and perfect way to find our errors, advise to solve and the best thing automatic repair failures.

At this scenario I will do a example to be useful post.

First i'll delete a .dbf  BE CARE FULL!!

Check there is no failure.

RMAN> LIST FAILURE;

using target database control file instead of recovery catalog

no failures found that match specification..

 i deleted /u01/app/my_tbs.dbf

[oracle@ora ~]$ cd /u01/app/

[oracle@ora app]$ ls –l

total 8996

drwxrwxr-x 12 oracle oinstall      4096 Feb 28 19:43 oracle

drwxrwxr-x  5 oracle oinstall      4096 Feb 22 18:31 oraInventory

-rw-r-----  1 oracle oinstall   9183232 Apr 10 18:25 users01.dbf

drwxrwxr-x  2 root   root          4096 Feb 23 17:30 veysideneme

Now start up our database and see the error.

 SQL> startup;

ORACLE instance started.

Total System Global Area  238530560 bytes

Fixed Size                  1335724 bytes

Variable Size             155192916 bytes

Database Buffers           79691776 bytes

Redo Buffers                2310144 bytes

Database mounted.

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

ORA-01110: data file 14: '/u01/app/my_tbs.dbf'

So you see there is no “MY_TBS.DBF”that’s why I couldn’t opened the database.

Now the game of RMAN J

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

360        HIGH     OPEN      10-APR-14     One or more non-system datafiles are missing

As Rman show as information of missing file. So get the advise of my bro  RMAN J

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

360        HIGH     OPEN      10-APR-14     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=21 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

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

no manual actions available

Optional Manual Actions

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

1. If file /u01/app/my_tbs.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

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

Option Repair Description

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

1      Restore and recover datafile 14 

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

  Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_755524553.hm

Ahaa! My bro show me a script and let me check it.

RMAN> repair failure preview;

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

Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_755524553.hm

contents of repair script:

   # restore and recover datafile

   restore datafile 14;

   recover datafile 14;

So, I have to restore the datafile 14. My bro “RMAN” is ready for it don’t worry J

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_755524553.hm

contents of repair script:

   # restore and recover datafile

   restore datafile 14;

   recover datafile 14;

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

executing repair script

Starting restore at 10-APR-14

using channel ORA_DISK_1

creating datafile file number=14 name=/u01/app/my_tbs.dbf

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

Finished restore at 10-APR-14

Starting recover at 10-APR-14

using channel ORA_DISK_1

starting media recovery

……………………………………….

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

Finished recover at 10-APR-14

repair failure complete

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

database opened

RMAN>

Okay, Now my database is opened and let me check the file is came back or not!

 [oracle@ora ~]$ ls -l /u01/app/

total 111508

-rw-r-----  1 oracle oinstall 104865792 Apr 10 18:49 my_tbs.dbf

drwxrwxr-x 12 oracle oinstall      4096 Feb 28 19:43 oracle

drwxrwxr-x  5 oracle oinstall      4096 Feb 22 18:31 oraInventory

-rw-r-----  1 oracle oinstall   9183232 Apr 10 18:49 users01.dbf

drwxrwxr-x  2 root   root          4096 Feb 23 17:30 veysideneme

Ohhh! I'm glad to use my bro RMAN J So, coffee break!