Oracle Faq

Hi All,

With each newer version of Oracle from Oracle 8i to Oracle 11g rman features are enchanced making it more and more powerful and thereby simplifying backup and recovery process to large extent.

The new feature of Oracle 11g makes rman more powerful by automatically detecting the failure, advising how to recover from failure and then actually recovering from failure.

For e.g.

Letz say my database consists of following datafiles

-bash-3.00$ cd /oracle/product/oradata/OTM/datafile/

-bash-3.00$ ls -lart

total 2096616

drwxr-x—  5 oracle oinstall      4096 Oct 15 16:30 ..

-rw-r—–  1 oracle oinstall  40902656 Nov 23 11:26 o1_mf_temp_3k6l2q8q_.tmp

-rw-r—–  1 oracle oinstall   5251072 Nov 23 13:11 o1_mf_users_3ndztqcd_.dbf

drwxr-x—  2 oracle oinstall      4096 Nov 23 16:42 .

-rw-r—–  1 oracle oinstall 492838912 Nov 23 16:42 o1_mf_undotbs1_3k6l2hkr_.dbf

-rw-r—–  1 oracle oinstall 104865792 Nov 23 16:43 o1_mf_test_3nffdoxh_.dbf

-rw-r—–  1 oracle oinstall 734011392 Nov 23 16:44 o1_mf_system_3k6l1shq_.dbf

-rw-r—–  1 oracle oinstall 770514944 Nov 23 16:44 o1_mf_sysaux_3k6l2bqf_.dbf 

Let us say by mistake someone deletes the o1_mf_test_3nffdoxh_.dbf or the file gets corrupted.

-bash-3.00$ rm -rf o1_mf_test_3nffdoxh_.dbf

Now I detect the failure when I try to perform a transaction accessing that database or when I try to shutdown the database , I get the following error

-bash-3.00$ sqlplus

SQL*Plus: Release 11.1.0.6.0 – Production on Fri Nov 23 16:46:00 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/oracle/product/oradata/OTM/datafile/o1_mf_test_3nffdoxh_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now we can use rman to detect the failure as shown below

-bash-3.00$ rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Fri Nov 23 16:46:15 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: OTM (DBID=811910212)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
1242       HIGH     OPEN      23-NOV-07     One or more non-system datafiles are missing

Now we can ask rman to advise us to repair the failure as shown.

 RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
1242       HIGH     OPEN      23-NOV-07     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=1640 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /oracle/product/oradata/OTM/datafile/o1_mf_test_3nffdoxh_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Restore and recover datafile 5 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /oracle/product/diag/rdbms/otm/OTM/hm/reco_1550806931.hm

As shown above rman by itself prepares the script which can be used to repair the failure. To view the content of the script use the following commandd

RMAN> repair failure  preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/product/diag/rdbms/otm/OTM/hm/reco_1550806931.hm

contents of repair script:
   # restore and recover datafile
   sql ‘alter database datafile 5 offline’;
   restore datafile 5;
   recover datafile 5;
   sql ‘alter database datafile 5 online’; 

Finally to repair the failure using rman we use the following command

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/product/diag/rdbms/otm/OTM/hm/reco_1550806931.hm

contents of repair script:
   # restore and recover datafile
   sql ‘alter database datafile 5 offline’;
   restore datafile 5;
   recover datafile 5;
   sql ‘alter database datafile 5 online’;

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

sql statement: alter database datafile 5 offline

Starting restore at 23-NOV-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /oracle/product/oradata/OTM/datafile/o1_mf_test_3nffdoxh_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/1mj173au_1_1
channel ORA_DISK_1: piece handle=/oracle/1mj173au_1_1 tag=TAG20071116T164612
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 23-NOV-07

Starting recover at 23-NOV-07
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 111 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_111_3n9o59by_.arc
archived log for thread 1 with sequence 112 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_112_3nbdyxbh_.arc
archived log for thread 1 with sequence 113 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_113_3nc2wt57_.arc
archived log for thread 1 with sequence 114 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_114_3nccpz0p_.arc
archived log for thread 1 with sequence 115 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_115_3ncmyrhb_.arc
archived log for thread 1 with sequence 116 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_116_3nd5stp7_.arc
archived log for thread 1 with sequence 117 is already on disk as file /oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_117_3ndxlsp4_.arc
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_111_3n9o59by_.arc thread=1 sequence=111
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_112_3nbdyxbh_.arc thread=1 sequence=112
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_113_3nc2wt57_.arc thread=1 sequence=113
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_22/o1_mf_1_114_3nccpz0p_.arc thread=1 sequence=114
archived log file name=/oracle/product/flash_recovery_area/OTM/archivelog/2007_11_23/o1_mf_1_115_3ncmyrhb_.arc thread=1 sequence=115
media recovery complete, elapsed time: 00:00:47
Finished recover at 23-NOV-07

sql statement: alter database datafile 5 online
repair failure complete

RMAN> exit

Recovery Manager complete.