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;