Home > RMAN Backup and Recovery > List Failure, Advise Failure and Repair Failure with Oracle 11g RMAN

List Failure, Advise Failure and Repair Failure with Oracle 11g RMAN

List Failure, Advise Failure and Repair Failure with Oracle 11g RMAN (DRA)

If you lost your datafile or a block is corrupted or you lost a tablespace, no more worrying about how to spin back. Talk to RMAN that takes care of the rest. The Data Recovery Advisor,one of the RMAN beauties come with 11g (DRA). I wanted to know as closely as practical.

Here’s operations:

I removed /oracle/ora11g/data_TALIP/talip/myts01.dbf  datafile on OS. Try to open the database . You will get an error.

SQL> startup;

ORACLE instance started.

Total System Global Area 2042241024 bytes

Fixed Size 1337548 bytes

Variable Size 1224738612 bytes

Database Buffers 805306368 bytes

Redo Buffers 10858496 bytes

Database mounted.

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

ORA-01110: data file 7: ‘/oracle/ora11g/data_TALIP/talip/myts01.dbf’

SQL> select status from v$instance;

STATUS

————

MOUNTED

In this case we can start our database in “MOUNTED” mode. Now let’s move on RMAN. Let us list a list of problems we face with failure.

RMAN> list failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

78802 HIGH OPEN 14-JUL-11 One or more non-system datafiles are missing

RMAN reported as one of datafile is missing. Let us ask what we should do about this to RMAN. RMAN please give advice to us🙂

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

78802 HIGH OPEN 14-JUL-11 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=129 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

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

no manual actions available

Optional Manual Actions

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

1. If file /oracle/ora11g/data_TALIP/talip/myts01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

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

Option Repair Description

—— ——————

1 Restore and recover datafile 7

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

Repair script: /oracle/diag/rdbms/talip/TALIP/hm/reco_3655040472.hm

RMAN suggested to us a script to solve the problem. Let’s see what the contents of this script;

RMAN> repair failure preview;

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

Repair script: /oracle/diag/rdbms/talip/TALIP/hm/reco_3655040472.hm

contents of repair script:

# restore and recover datafile

restore datafile 7;

recover datafile 7;

Yes, we are looking for exactly that suggested the recovery script. I agree with RMAN and let’s recover our datafile.

RMAN> repair failure;

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

Repair script: /oracle/diag/rdbms/talip/TALIP/hm/reco_3655040472.hm

contents of repair script:

# restore and recover datafile

restore datafile 7;

recover datafile 7;

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

executing repair script

Starting restore at 14-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00007

input datafile copy RECID=7 STAMP=756487675 file name=/oracle/yedek/TALIP/data_D-TALIP_I-1561456315_TS-MYTS_FNO-7_09mhe5f3

destination for restore of datafile 00007: /oracle/ora11g/data_TALIP/talip/myts01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00007

output file name=/oracle/ora11g/data_TALIP/talip/myts01.dbf RECID=0 STAMP=0

Finished restore at 14-JUL-11

Starting recover at 14-JUL-11

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 14-JUL-11

repair failure complete

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

database opened

RMAN>exit;

Everything is fine. RMAN took care of everything. Our database is in good hands🙂

SQL> select status from v$instance;

STATUS

————

OPEN

Talip Hakan Öztürk

  1. March 7, 2012 at 8:20 pm

    Nice article Talip. I’ve also written an article on Data Recovery Advisor on my blog last year:
    http://kamranagayev.com/2010/05/21/rman-data-recovery-advisor-11g/

  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: