Saturday, May 21, 2011

Data Recovery Advisor(DRA)

Oracle Database 11g - Data Recovery Advisor(DRA) is a new tool in Oracle database 11g. It is an integrated solution from Oracle Corporation.It is the primary tool used in preventing database failure.

What is a database failure?

Database failure is the corruption/loss of entire data files. data files are the physical location in which information is stored. Their loss causes database failure.
Data Recovery Advisor as it is popularly called DRA has the following uses :

1) It detects and analyzes database failures in a proactive fashion
2) It determines optimal repair strategy
3) It performs repair optionally
4) It verifies the success after repair


How do we access Data Recovery Advisor/DRA?

We can access the data recovery advisor/DRA using the following methods :

1) RMAN (Recovery Manager) client
2) Oracle Enterprise Manager (OEM)


What is MTTR? How is Data recovery advisor used to enhance database reliability:


MTTR stands for Mean Time To REcovery. Data recovery advisor(DRA) is used toreduce MTTR. It is an automated data repair tool. It is used to enhance database reliability.

-------------  Practicle Demo (Windows Based)   ----------------------------


C:\>set oracle_sid=orcl

C:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 18 11:16:09 2011

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

Enter user-name: /as sysdba

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

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF

7 rows selected.



C:\>rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 18 11:17:24 2011

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

connected to target database: ORCL (DBID=1278854133)


RMAN> backup database plus archivelog;


Starting backup at 18-MAY-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=751214999
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 18-MAY-11

Starting backup at 18-MAY-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set

piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_ANNNN_TAG20110518T111944_6X6V08QG_.BKP tag=TAG2011
ent=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-MAY-11

Starting Control File and SPFILE Autobackup at 18-MAY-11
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2011_05_18\O1_MF_S_751461586_6X6V0C7T_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAY-11

RMAN> quit


Recovery Manager complete.




SQL>
SQL>
SQL> alter system archive log current;

System altered.

SQL>
SQL>
SQL>


Now rename any datafile for failure


SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 318768552 bytes
Database Buffers 452984832 bytes
Redo Buffers 5259264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: 'C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF'


SQL>
SQL>
SQL>




C:\>
C:\>
C:\>
C:\>rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 18 11:22:53 2011

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

connected to target database: ORCL (DBID=1278854133, not open)

RMAN>

RMAN>

RMAN>

RMAN>

RMAN>

RMAN> list failure;

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

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1082 HIGH OPEN 18-MAY-11 One or more non-system datafiles are missing

RMAN>

RMAN>

RMAN>

RMAN>

RMAN> advise failure;

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

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1082 HIGH OPEN 18-MAY-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=10 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file C:\APP\ORACLE\ORADATA\ORCL\TSP.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: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm

RMAN>


RMAN>

RMAN> REPAIR FAILURE PREVIEW;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.hm

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

RMAN>

RMAN>

RMAN>

RMAN>

RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\flash_recovery_area\orcl\diag\rdbms\orcl\orcl\hm\reco_3445165329.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)? y <---- Here it will ask for your choice
executing repair script

Starting restore at 18-MAY-11
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 00007 to C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_NNNDF_TAG20110518
_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_05_18\O1_MF_NNNDF_TAG20110518T111816_6X6TX
20110518T111816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-MAY-11

Starting recover at 18-MAY-11
using channel ORA_DISK_1

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

Finished recover at 18-MAY-11
repair failure complete

Do you want to open the database (enter YES or NO)? y <---- Here it will ask for your choice
database opened

RMAN>




RMAN>

RMAN> change failure 1082 priority low;

no failures found that match specification

RMAN>

RMAN>

RMAN>

RMAN> list failure;

no failures found that match specification

RMAN>

RMAN>

RMAN>

RMAN>

RMAN> validate database;

Starting validate at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile file number=00006 name=C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
input datafile file number=00007 name=C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12511 88326 1585054
File Name: C:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 59446
Index 0 12286
Other 0 4077

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 17836 71711 1585066
File Name: C:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 13936
Index 0 10200
Other 0 29708

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 12802 1585066
File Name: C:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 12799

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 247 640 1059228
File Name: C:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 46
Other 0 256

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1689 12803 1065300
File Name: C:\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4544
Index 0 1261
Other 0 5306

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 121 128 1427089
File Name: C:\APP\ORACLE\ORADATA\ORCL\TEST.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 121 128 1448699
File Name: C:\APP\ORACLE\ORADATA\ORCL\TSP.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 598
Finished validate at 18-MAY-11

RMAN>

No comments:

Post a Comment