The Data Recovery Advisor is available from Enterprise Manager (EM) Database Control and Grid Control. You can also use it via the RMAN command-line.
In this example you will see examples of via the RMAN command line utilising the DRA commands:
Three DRA commands are available within RMAN:
List Failure - lists the results of previously executed failure assessments. Revalidates existing failures and closes them, if possible.
Advise Failure - presents manual and automatic repair options
Repair Failure - automatically fix failures by running optimal repair option, suggested by ADVISE FAILURE. Revalidates existing failures when completed.
Below is a demonstration of DRA.
1. Take Full RMAN backup;
2. RMAN> validate check logical datafile 2;
Show that the datafile is free from corruption.
RMAN> validate check logical datafile 2;
Starting validate at 2008/11/10 09:52:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/oradata/V11/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 28279 104896 2885460
File Name: /oradata/V11/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21440
Index 0 24664
Other 0 30513
Finished validate at 2008/11/10 09:53:02
3. Corrupt several Blocks within the sysaux tablespace
SQL> @corrupt_blocks.sql
4. RMAN> validate check logical datafile 2;
This should show the corruption and indicate the # of blocks corrupted.
RMAN> validate check logical datafile 2;
Starting validate at 2008/11/10 09:57:05
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=00002 name=/oradata/V11/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 FAILED 0 28273 104896 2894632
File Name: /oradata/V11/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21440
Index 0 24663
Other 10 30520
validate found one or more corrupt blocks
See trace file /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/trace/V11_ora_22522.trc for details
Finished validate at 2008/11/10 09:57:12
Review: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/trace/V11_ora_22522.trc
Open the trace file to show the errors that have been picked up.
Corrupt block relative dba: 0x00800024 (file 2, block 36)
Completely zero block found during validation
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Hex dump of (file 2, block 37)
We can now issue list failure & list failure
5. RMAN> list failure;
RMAN> list failure ### detail; ( where ### equlas the failure number)
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
RMAN> list failure 351261 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
352028 HIGH OPEN 2008/11/10 09:56:35 Block 37 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
At this point you are able to issue the advise keyword which will prompt DBA will be advise on how to solve the current scenario.
6. RMAN> Advise Failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
352028 HIGH OPEN 2008/11/10 09:56:35 Block 37 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
..
..
..
Impact: Object I_SMB$CONFIG_PKEY owned by SYS might be unavailable
351974 HIGH OPEN 2008/11/10 09:56:33 Block 28 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
Impact: Object I_SMB$CONFIG_PKEY owned by SYS might be unavailable
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If a standby database is available, then consider a Data Guard switchover or failover
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Recover multiple corrupt blocks in datafile 2
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_189110358.hm
RMAN>
Review File: : /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_189110358.hm
[oracle@aulnx7 hm]$ vi reco_189110358.hm
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;
As can be seen above one option is to perform rman block recovery. The next step for the DBA is to proceed with the repair preview.
7. RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_42
19629556.hm
contents of repair script:
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;
Then you are ready to perform the fix.
8. RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_42
19629556.hm
contents of repair script:
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting recover at 2008/11/10 10:18:48
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece /recovery_area/V11/backupset/2008_
11_10/o1_mf_nnndf_TAG20081110T093808_4kgslm1w_.bkp
channel ORA_DISK_1: piece handle=/recovery_area/V11/backupset/2008_11_10/o1_mf_n
nndf_TAG20081110T093808_4kgslm1w_.bkp tag=TAG20081110T093808
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 2008/11/10 10:19:27
repair failure complete
RMAN>
At this point the the blocks have been fully recovered.
No comments:
Post a Comment