Skip to content

Recover missing datafile using RMAN Data Recoveray advisor

by Mohamed Azar on January 8, 2011

This is one Oracle 11g New feature. Here I’m go to show How to recover missing datafile using RMAN data recovery advisor without taking previous backup.

SQL> create tablespace datarec datafile 'd:\backupnew\datarec01.dbf' size 50m;

Tablespace created.

SQL> create user datarec identified by datarec default tablespace datarec;

User created.

SQL> grant connect,resource to datarec;

Grant succeeded.

SQL> conn datarec/datarec;
Connected.
SQL> create table test(empno number,name varchar2(20));

Table created.

SQL> insert into test values(001,'azar');

1 row created.

SQL> insert into test values(002,'kareem');

1 row created.

SQL> commit;

Commit complete.
SQL> conn datarec/datarec;
Connected.
SQL> select * from test;

     EMPNO NAME
---------- --------------------
         1 azar
         2 kareem

SQL>

Shutdown Database

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete particular DBF file using OS command manualy.

d:\backupnew>dir
Volume in drive D has no label.
Volume Serial Number is 3861-730C

Directory of d:\backupnew

08-Jan-11 11:35 AM .
08-Jan-11 11:35 AM ..
08-Jan-11 11:33 AM 2,002,329,600 ACE01.DBF
08-Jan-11 10:13 AM 130,838 archive.txt
08-Jan-11 10:12 AM 148,590 archivelog.txt
01-Jan-11 04:50 PM AZARDB
08-Jan-11 11:35 AM 52,436,992 DATAREC01.DBF
03-Jan-11 10:06 AM 1,116 initazar.ora
03-Jan-11 10:19 AM 1,115 initazardb.ora
03-Jan-11 09:11 AM New folder
03-Jan-11 09:16 AM 104,865,792 TESTTBS01.DBF
01-Jan-11 05:12 PM 86,016 TSPITR_EINS_53941.DMP
01-Jan-11 05:57 PM 86,016 TSPITR_LBFD_89576.DMP
9 File(s) 2,160,086,075 bytes
4 Dir(s) 106,103,545,856 bytes free

d:\backupnew>del datarec01.dbf

Startup database

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 335544784 bytes
Database Buffers 192937984 bytes
Redo Buffers 5804032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: 'D:\BACKUPNEW\DATAREC01.DBF'

Recover missing datafile using RMAN Data Recovery Advisor

C:\Users\mazar>set oracle_sid=azardb

C:\Users\mazar>rman target sys/Admin123

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 8 11:40:07 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: AZARDB (DBID=1652383192, not open)

RMAN> list failure;

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

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
67468 HIGH OPEN 08-JAN-11 One or more non-system datafiles are missing

RMAN> advise failure;

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

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
67468 HIGH OPEN 08-JAN-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=136 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file D:\BACKUPNEW\DATAREC01.DBF was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 15
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_3190867713.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_3190867713.hm

contents of repair script:
# restore and recover datafile
restore datafile 15;
recover datafile 15;

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

Starting restore at 08-JAN-11
using channel ORA_DISK_1

creating datafile file number=15 name=D:\BACKUPNEW\DATAREC01.DBF
restore not done; all files read only, offline, or already restored
Finished restore at 08-JAN-11

Starting recover at 08-JAN-11
using channel ORA_DISK_1

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

Finished recover at 08-JAN-11
repair failure complete
RMAN>SQL> alter database open;

Database altered.

SQL> select name,status from v$datafile where file#=15;

NAME
--------------------------------------------------------------------------------
STATUS
-------
D:\BACKUPNEW\DATAREC01.DBF
ONLINE

SQL>SQL> conn datarec/datarec;
Connected.
SQL> select * from test;

EMPNO NAME
---------- --------------------
1 azar
2 kareem

SQL>

Controfile Recover :

And also Checking For Control File. I just moved controlfile and startup database ,It shows error,
then i just recovered Controlfile using RMAN data Recovery Advisor.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
C:\app\oracle\mazar\oradata\azardb>move control01.ctl C:\APP\ORACLE\MAZAR\ORADATA\
        1 file(s) moved. 
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
ORA-00205: error in identifying control file, check alert log for more info

 

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
75867      CRITICAL OPEN      08-JAN-11     Control file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL is missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
75867      CRITICAL OPEN      08-JAN-11     Control file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Use a multiplexed copy to restore control file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_2373618729.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_2373618729.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from 'C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL';
   sql 'alter database mount';

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

Starting restore at 08-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
output file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL
Finished restore at 08-JAN-11

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

RMAN>
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL

SQL>
Just for My understanding
http://forums.oracle.com/forums/thread.jspa?threadID=2155786&tstart=0
From: Backup & Recovery
←
→
7 Comments Post a comment
  1. zafar #

    nice one azar.
    have you done a datafile recovery test without shutting down the database.
    i,e manually deleting the datafile and then using Data Recovery Advisor. the reason i ask is i am planning this test on exadata and was wandering if I would have to do anything differant besides recovering the tablespace.
    cheers
    zafar

    September 9, 2011
    Reply
  2. zafar #

    well – list failure does not work in RAC
    RMAN> list failure;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of list command at 09/10/2011 12:24:45
    RMAN-05533: LIST FAILURE is not supported on RAC database

    so i guess u r testing on a standalone.

    September 10, 2011
    Reply
  3. Ulfet #

    Excellent!

    January 12, 2012
    Reply

Trackbacks & Pingbacks

  1. harddrive recovery
  2. Data Recovery Advisor 11g

Leave a Reply to azardba Cancel reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

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

Cancel

Connecting to %s

%d bloggers like this: