Oracle data recovery consultant (DRA) use test

Have you ever handled the problem of data corruption or data loss on the hard disk? As a result, even though you are still in the process of diagnosing and analyzing the problem, the end user and manager have already contacted you and want to know the approximate time (ETA) for solving the problem. Do you want to have a simple and more important way to quickly diagnose and fix such faults?

Continue to read about how Oracle Data Recovery Advisor (DRA) helps us in this situation!

Data Recovery Advisor is an Oracle database tool. The tool automatically diagnoses data faults, determines and proposes corresponding repair solutions, and carries out customer-requested repairs. The above repair is based on data failure, which is the damage or loss of persistent data on a hard disk.
 
The data recovery consultant automatically collects data fault information when encountering problems and assists in fault repair. You can manually repair a data fault or ask the data recovery consultant to perform the repair action for you.
 
DRA can be accessed through Enterprise Manager (EM) Grid Control and/or recovery Manager (RMAN. This document describes the commands used in RMAN.

In RMAN, there are four commands for DRA:
• List Failure-List fault assessment results that have been previously performed. If possible, re-verify existing faults and disable them.
• Advise Failure-proposed manual and automatic repair solutions.
• Repair Failure-automatically repairs faults by running the best Repair solution recommended by advise failure. After completion, the system will re-verify the existing fault.
• Change Failure-enables you to Change the fault status.

For example, the following steps involve an accident. When a data file has been deleted from the operating system, the database reports an error.
 
If a select statement fails to be executed, the following error is returned:
SQL> select * from emp;
Select * from emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/v1__oradata/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

The DRA command 'list failure' provides detailed information about this issue:
Recovery Manager: Release 11.2.0.3.0-Production on Wed Aug 21 10:12:36 2013
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.

Connected to target database: V112 (DBID = 2335388877)

RMAN> list failure;

Using target database control file instead of recovery catalog
List of Database Failures
======================================
Failure ID Priority Status Time Detected Summary
--------------------------------------------------------------------------------
82 high open 21-AUG-13 One or more non-system datafiles are missing

As the next operation of DRA, run the 'advise failure 'command to obtain the recommended solution to fix the problem:

RMAN> advise failure;

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

Failure ID Priority Status Time Detected Summary
-----------------------------------------------
82 high open 21-AUG-13 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 = 28 device type = DISK
Analyzing automatic repair options complete

Mandatory Manual Actions
======================================
No manual actions available

Optional Manual Actions
======================================
1. If file/u01/v1__oradata/users01.dbf was unintentionally renamed or moved, restore it
2. If file/u01/v1__oradata/test01.dbf was unintentionally renamed or moved, restore it

Automatic Repair Options
======================================
Option Repair Description
------------------------
1 Restore and recover datafile 4; Restore and recover datafile 5
Strategy: The repair complete media recovery with no data loss
Repair script:/u02/app/oracle/diag/rdbms/v112/V112/hm/reco_1311249797.hm

The DRA information describes the two missing data files, which are recommended for restoration and recovery.

As mentioned above, DRA can repair faults. You can preview the recommended command before executing the fix script:
 
RMAN> repair failure preview;

Strategy: The repair complete media recovery with no data loss
Repair script:/u02/app/oracle/diag/rdbms/v112/V112/hm/reco_1311249797.hm

Contents of repair script:
# Restore and recover datafile
SQL 'alter database datafile 4, 5 offline ';
Restore datafile 4, 5;
Recover datafile 4, 5;
SQL 'alter database datafile 4, 5 online ';

Now, you can decide whether to manually run the above script or let DRA execute the fix:

RMAN> repair failure;

Strategy: The repair complete media recovery with no data loss
Repair script:/u02/app/oracle/diag/rdbms/v112/V112/hm/reco_1311249797.hm

Contents of repair script:
# Restore and recover datafile
SQL 'alter database datafile 4, 5 offline ';
Restore datafile 4, 5;
Recover datafile 4, 5;
SQL 'alter database datafile 4, 5 online ';

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

SQL statement: alter database datafile 4, 5 offline

Starting restore at 21-AUG-13
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 00004 to/u01/v1__oradata/users01.dbf
Channel ORA_DISK_1: restoring datafile 00005 to/u01/v1__oradata/test01.dbf
Channel ORA_DISK_1: reading from backup piece
/U01/temp/flash_areas/V112/V112/backupset/2013_08_21/o1_mf_nnndf_tag20130821t10025213919l4f2p _. bkp
Channel ORA_DISK_1: piece
Handle =/u01/temp/flash_areas/V112/V112/backupset/2013_08_21/o1_mf_nnndf_TAG20130821T100251_919l4f2p _. bkp
Tag = tag20130821t100133
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-AUG-13

Starting recover at 21-AUG-13
Using channel ORA_DISK_1

Archived log for thread 1 with sequence 79 is already on disk as file
/U01/temp/flash_areas/V112/V112/archivelog/2013_08_21/o1_mf_10979_919lm6y2 _. arc
Channel ORA_DISK_1: starting archived log restore to default destination
Channel ORA_DISK_1: restoring archived log
Archived log thread = 1 sequence = 77
Channel ORA_DISK_1: restoring archived log
Archived log thread = 1 sequence = 78
Channel ORA_DISK_1: reading from backup piece
/U01/temp/flash_areas/V112/V112/backupset/2013_08_21/o1_mf_annnn_tag20130821t101028_919lo3n _. bkp
Channel ORA_DISK_1: piece
Handle =/u01/temp/flash_areas/V112/V112/backupset/2013_08_21/o1_mf_annnn_tag20130821t101028_919lo3n _. bkp
Tag = TAG20130821T101028
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Archived log file name =/u01/temp/flash_areas/V112/V112/archivelog/2013_08_21/o1_mf_1_77_919ltj5h _. arc
Thread = 1 sequence = 77
Channel default: deleting archived log (s)
Archived log file name =/u01/temp/flash_areas/V112/V112/archivelog/2013_08_21/o1_mf_1_77_919ltj5h _. arc
RECID = 50 STAMP = 824033680
Archived log file name =/u01/temp/flash_areas/V112/V112/archivelog/2013_08_21/o1_mf_201778_919ltj7o _. arc
Thread = 1 sequence = 78
Channel default: deleting archived log (s)
Archived log file name =/u01/temp/flash_areas/V112/V112/archivelog/2013_08_21/o1_mf_201778_919ltj7o _. arc
RECID = 51 STAMP = 824033680.
Media recovery complete, elapsed time: 00:00:00
Finished recover at 21-AUG-13

SQL statement: alter database datafile 4, 5 online
Repair failure complete

Once the script is successfully fixed, the problem should be resolved and the select statement returns the expected results:
 
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------------------------------------
7900 james clerk 7698 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10

For more details, please continue to read the highlights on the next page:

1 2 Next Page