Rman List Failure, Advise Failure and Repair Failure

List Failure, Advise Failure and Repair Failure with Oracle RMAN

 Rman is very easy tool for recovery. Suppose you lost your datafile or a block is corrupted or you lost a tablespace, Dont worry about it . Talk to RMAN that takes care of the rest.  

The Data Recovery Advisor,one of the RMAN beauties come with 11g (DRA).

Below is the easiest step to recover it.

Firstly I removed one of my datafile.

rm –r  /u01/app/oracle/oradata/TEST/datafile/myts01.dbf 

Now I  try to open the database . I 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: ‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’

Now I open the database in mount stage;

SQL> select status from v$instance;

STATUS

————

MOUNTED

Rman target/

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

282        CRITICAL OPEN      29-JAN-18     myts01.dbf’ datafile 7:

‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

78802 HIGH OPEN 29-JAN-18   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 ‘/u01/app/oracle/oradata/TEST/datafile/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 29-JAN-18

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: ‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’

channel ORA_DISK_1: copied datafile copy of datafile 00007

output file name=‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’ RECID=0 STAMP=0

Finished restore at 29-JAN-18

Starting recover at 29-JAN-18

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 29-JAN-18

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