Description:-
- This feature is available form Oracle 11g called the Data Recovery Advisor.
- Data Recovery Advisor tool will automatically diagnose data failures, such as corruption or loss of persistent datafile on disk.
- Prior to Oracle Database 11g,only block corruptions that were detected by RMAN were recorded in V$DATABASE_BLOCK_CORRUPTION.
- Starting with Oracle Database 11g,several database components and utilities, including RMAN and Automatic Diagnostic
Repository (ADR), can detect a corrupt block and record it in that view. - Oracle Database automatically updates this view when block corruptions are detected or repaired.
- Data Recovery Advisor tool is used for recovery process to complete the Mean Time To Recover (MTTR).
Detection and Diagnosis Using Data Recovery Advisor.
Data Recovery Advisor commands are
- List Failure
- Advise Failure
- Repair Failure
- Change Failure
- Validate
- The Data Recovery Advisor automatically diagnoses data failures, determines and presents
appropriate repair options, and performs repair operations at the user’s request. - Data Recovery Advisor improves the manageability and reliability of an Oracle database.
- You can use Data Recovery Advisor to troubleshoot primary databases,logical standby databases, and snapshot
standby databases
Let’s Start the Demo:-
- This article i am going to corrupt the block and recover using ADR Method. The demo is done on 18c database the same way for 12c also.
- I have corrupted blocks on my demo 18c database affecting the oracledba table of the tablespace hari
Step1:-Create tablepsace(Hari) and table(oracledba) for Demo.
New Tablespace
SQL> create tablespace hari datafile ‘/u02/app/oracle/oradata/orcl18c/hari.dbf’ size 5m;
Tablespace created.
Create table and store in the new tablespace
SQL> create table oracledba tablespace hari as select * from all_objects;
Table created.
SQL> select count(*) from oracledba;
COUNT(*)
———-
72884
Step2:-Check the Block Header of the Table
SQL> select segment_name , header_file , header_block from dba_segments where segment_name = ‘ORACLEDBA’;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
———– ———— ————–
ORACLEDBA 17 130
Step3:-Before going to corrupt the block take rman backup
[oracle@orcl18c ~]$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 21 20:25:09 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL18C (DBID=518253543) RMAN> backup tablespace hari; Starting backup at 21-MAY-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=47 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00017 name=/u02/app/oracle/oradata/orcl18c/hari.dbf channel ORA_DISK_1: starting piece 1 at 21-MAY-18 channel ORA_DISK_1: finished piece 1 at 21-MAY-18 piece handle=/u03/app/oracle/fast_recovery_area/ORCL18C/backupset/2018_05_21/o1_mf_nnndf_TAG20180521T202524_fj6bsob8_.bkp tag=TAG20180521T202524 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 21-MAY-18 Starting Control File and SPFILE Autobackup at 21-MAY-18 piece handle=/u03/app/oracle/fast_recovery_area/ORCL18C/autobackup/2018_05_21/o1_mf_s_976739128_fj6bsrrc_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 21-MAY-18
Step4:-Corrupt the block using OS command (DD). (Do Not Try This in Client Database)
cd /u02/app/oracle/oradata/orcl18c/
Step5:-After the Block Corruption to check using DBV Utility
dbv file=hari.dbf
Here we are unable to get the count of the table (oracledba)
[oracle@orcl18c trace]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 18.0.0.0.0 Production on Mon May 21 21:00:18 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.1.0.0.0
SQL> select count(*) from oracledba;
select count(*) from oracledba
*
ERROR at line 1:
ORA-28304: Oracle encrypted block is corrupt (file # 17, block # 130)
ORA-01110: data file 17: ‘/u02/app/oracle/oradata/orcl18c/hari.dbf’
Step6:-Recover the Corrupt block Using Data Recovery Advisor Concept
VALIDATE CHECK LOGICAL TABLESPACE hari;
List Failure:-
Advise failure all:-
Repair failure:-
[oracle@orcl18c trace]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 18.0.0.0.0 Production on Mon May 21 21:27:42 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.1.0.0.0
SQL> select count(*) from oracledba;
COUNT(*)
———-
72884
Reference:-
Catch Me On:- Hariprasath Rajaram
LinkedIn: https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
