Recover datafile & controlfile using RMAN data recovery advisor

In case of worst situation with datafile & controlfile, we can repair/restore it with the help of RMAN data recovery advisor.

Note:
We can use Data Recovery Advisor with the help of Oracle Enterprise Manager, Grid Control & RMAN command prompt.

Lets consider hands-on on on RMAN repair advisor with the help of following case studies:

Case Study 1st: Loss of datafile…

After considering full database RMAN backup, we are intentionally renaming datafile and creating worst situation to open database.

 

Ensure all datafiles with the help of “report schema” RMAN command:

[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
Recovery Manager: Release 11.2.0.1.0 – Production on Mon Jan 13 09:47:40 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
connected to recovery catalog database

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 690 SYSTEM YES /home/oracle/app/oracle/oradata/orcl/system01.dbf
2 610 SYSAUX NO /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3 30 UNDOTBS1 YES /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS NO /home/oracle/app/oracle/oradata/orcl/users01.dbf
5 200 USERTBS NO /home/oracle/data/user1.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 22 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

 

Shutdown database to rename datafile.
SQL> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
Rename datafile with the help of “mv” linux command:
[root@oracle data]# pwd
/home/oracle/data
[root@oracle data]# mv user1.dbf user1.back
After renaming datafile try to open database with the help of sys user as sysdba. Database won’t be able to open due oracle error: ORA-01157 & ORA-01110 as follow.
[oracle@oracle data]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 13 09:57:41 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 192941024 bytes
Database Buffers 109051904 bytes
Redo Buffers 4775936 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/home/oracle/data/user1.dbf’

 

To understand the nature of failure & to restore database, connect to the target ( registered ) database with the help of recovery manager owner:

[oracle@oracle data]$ rman target / catalog recoveryman/recoveryman@catalogdb
Recovery Manager: Release 11.2.0.1.0 – Production on Mon Jan 13 09:58:16 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714, not open)
connected to recovery catalog database

 

List the failure with the help of “list failure” command of RMAN repair advisor, this will show you short summary of database failure.
RMAN> list failure;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
282 HIGH OPEN 13-JAN-14 One or more non-system datafiles are missing

 

List the failure details with the help of “list failure detail”, this will show you in depth details of database failure.
RMAN> list failure detail;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
282 HIGH OPEN 13-JAN-14 One or more non-system datafiles are missing

Impact: See impact for individual child failures
List of child failures for parent failure ID 282
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
285 HIGH OPEN 13-JAN-14 Datafile 5: ‘/home/oracle/data/user1.dbf’ is missing
Impact: Some objects in tablespace USERTBS might be unavailable

 

We can opt advice on failure with the help of “advise failure” RMAN command. This will show you Mandatory Manual Actions, Optional Manual Actions & Automated Repair Options to heal failure.
RMAN> advise failure;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
282 HIGH OPEN 13-JAN-14 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 282
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
285 HIGH OPEN 13-JAN-14 Datafile 5: ‘/home/oracle/data/user1.dbf’ is missing
Impact: Some objects in tablespace USERTBS might be unavailable

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

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

Optional Manual Actions
=======================
1. If file /home/oracle/data/user1.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: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1583217285.hm

 

Following command will restore and recover datafile by restoring RMAN recent backup.
RMAN> repair failure noprompt;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1583217285.hm

contents of repair script:
# restore and recover datafile
restore datafile 5;
recover datafile 5;
executing repair script

Starting restore at 13-JAN-14
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 /home/oracle/data/user1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_13/o1_mf_nnndf_TAG20140113T094941_9f7foow1_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_13/o1_mf_nnndf_TAG20140113T094941_9f7foow1_.bkp tag=TAG20140113T094941
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 13-JAN-14

Starting recover at 13-JAN-14
using channel ORA_DISK_1

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

Finished recover at 13-JAN-14
repair failure complete
database opened

 

Restoration of datafile has been successfully completed with database in open mode.
Ensure this with the help of following:

RMAN> list failure;
no failures found that match specification

OR

Ensure datafile with the help of following SQL query on dba_data_file table. ( data dictionary view )

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/orcl/users01.dbf
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
/home/oracle/app/oracle/oradata/orcl/system01.dbf
/home/oracle/data/user1.dbf

–X–

 

Case study 2nd: Loss of controlfile…

 

As above case study, we are intentionally deleting controlfile and try to startup database.
Note:

RMAN should configured with autobackup controlfile parameter.

Issue following RMAN command to ensure above point.
RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

 

Rename controlfile so that database won’t be open.

[root@oracle orcl]# cd /home/oracle/app/oracle/oradata/orcl
[root@oracle orcl]# ls
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[root@oracle orcl]# mv control01.ctl control01.ctl.backup
[root@oracle orcl]# ls
control01.ctl.backup redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

 

After renaming controlfile try to open database. Database again failed to open due oracle error: ORA-00205: error in identifying control file.

[oracle@oracle data]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 13 10:24:06 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 192941024 bytes
Database Buffers 109051904 bytes
Redo Buffers 4775936 bytes
ORA-00205: error in identifying control file, check alert log for more info

 

List the failure with the help of “list failure” command of RMAN repair advisor, this will show you short summary of database failure.

RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
367 CRITICAL OPEN 13-JAN-14 Control file /home/oracle/app/oracle/oradata/orcl/control01.ctl is missing

 

List the details of failure with the help of “list failure detail” RMAN command.

RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
367 CRITICAL OPEN 13-JAN-14 Control file /home/oracle/app/oracle/oradata/orcl/control01.ctl is missing
Impact: Database cannot be mounted
We can opt advice on failure with the help of “advise failure” RMAN command.

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
367 CRITICAL OPEN 13-JAN-14 Control file /home/oracle/app/oracle/oradata/orcl/control01.ctl is missing
Impact: Database cannot be mounted

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 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 /home/oracle/app/oracle/oradata/orcl/control01.ctl
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4162161626.hm

 

Following command will restore and recover controlfile by restoring RMAN recent backup.
RMAN> repair failure noprompt;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4162161626.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’;
sql ‘alter database mount’;
executing repair script

Starting restore at 13-JAN-14
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 13-JAN-14

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

 

Database has been open with successfully by restoration of controlfile.
Ensure this with the help of following:

RMAN> list failure;
no failures found that match specification

 

Database has been restored in both the case studies with the help of user friendly tool: Data Recovery Advisor.

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Leave a Reply