Oracle 11g introduces a new feature called the Data Recovery Advisor. The goal of this advisor is to provide a mechanism for guided error detection and recovery for the oracle database. DRA automatically detects persistent data failures, presents repair options to the user and executes repair options upon user request.
When a user executes a database operation that results in an error, a database integrity check is triggered, which then checks for failures related to that error in the database. If failures are diagnosed they are recorded in the Automatic Diagnostics Repository (ADR).
There following major commands in rman that would be used to detect and correct errors.
- list failure
- advise failure
- repair failure
Let us look at an example where the data recovery advisor is used to recover from database block corruptions.
Create a 11gR1 Database RK01 using DBCA. Set the database to be in archivelog mode.
Use the script $ORACLE_HOME/demo/schema/human_resources/hr_main.sql to create the human resources schema in the database. Use the tablespace “USERS” for the hr schema tables.
Take a full rman backup of the database
export ORACLE_SID=RK01
rman target /run {
allocate channel d1 type disk;
setlimit channel d1 kbytes 2097150;
backup incremental level 0 format ‘/u01/app/oraback/df_%U’ database
include current controlfile;
sql “alter system archive log current”;
backup
archivelog all format ‘/u01/app/oraback/al_%U’;
}
Introduce corruption in a datafile by overwriting 3 oracle database blocks.
dd of=/u01/app/oradata/RK01/users01.dbf bs=8192 conv=notrunc seek=12 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt
EOFdd of=/u01/app/oradata/RK01/users01.dbf bs=8192 conv=notrunc seek=84 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt
EOFdd of=/u01/app/oradata/RK01/users01.dbf bs=8192 conv=notrunc seek=36 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt
EOF
Trigger Error Detection by running a validate database
rman target /
validate database;
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 FAILED 0 473 640 555120
File Name: /u01/app/oradata/RK01/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 43
Index 0 20
Other 3 104validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/rk01/RK01/trace/RK01_ora_7452.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
———— —— ————– —————
Control File OK 0 594
Finished validate at 27-MAY-08
List all the failures
RMAN> list failure;
List of Database Failures
=========================Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
146 HIGH OPEN 27-MAY-08 Datafile 4: ‘/u01/app/oradata/RK01/users01.dbf’ contains one or more corrupt blocks
List the details for the failure
RMAN> list failure detail;
List of Database Failures
=========================Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
146 HIGH OPEN 27-MAY-08 Datafile 4: ‘/u01/app/oradata/RK01/users01.dbf’ contains one or more corrupt blocks
Impact: Some objects in tablespace USERS might be unavailable
List of child failures for parent failure ID 146
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
161 HIGH OPEN 27-MAY-08 Block 84 in datafile 4: ‘/u01/app/oradata/RK01/users01.dbf’ is media corrupt
Impact: Object LOCATIONS owned by HR might be unavailable
155 HIGH OPEN 27-MAY-08 Block 36 in datafile 4: ‘/u01/app/oradata/RK01/users01.dbf’ is media corrupt
Impact: Object PK_EMP owned by SCOTT might be unavailable
149 HIGH OPEN 27-MAY-08 Block 12 in datafile 4: ‘/u01/app/oradata/RK01/users01.dbf’ is media corrupt
Impact: Object DEPT owned by SCOTT might be unavailable
Ask Rman to provide recovery advice
RMAN> advise failure;
….….
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Recover multiple corrupt blocks in datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/rk01/RK01/hm/reco_2336332336.hmRMAN>
Ask Rman to do the necessary to repair the failure
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/rk01/RK01/hm/reco_2336332336.hmcontents of repair script:
# block media recovery for multiple blocks
recover datafile 4 block 12, 36, 84;Do you really want to execute the above repair (enter YES or NO)? YES
executing repair scriptStarting recover at 27-MAY-08
using channel ORA_DISK_1channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oraback/df_01jhdo2k_1_1
channel ORA_DISK_1: piece handle=/u01/app/oraback/df_01jhdo2k_1_1 tag=TAG20080527T093723
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01starting media recovery
media recovery complete, elapsed time: 00:00:03Finished recover at 27-MAY-08
repair failure complete
Check that the database is Fine after recovery
RMAN> list failure;
no failures found that match specification
