Thursday, September 29, 2011

Repair block corruptions using Data Recovery Advisor in Oracle 11g

The data recovery advisor introduced in Oracle 11g and using this tool you check the block corruptions and loss of data files and control files. The GUI feature also available from Enterprise manager.

By default RMAN checks for physically corrupted blocks automatically with every backup (backup set or image copy) it creates. You can check the logical corruption using CHECK LOGICAL command. Using VALIDATE keyword it checks all blocks and it will not create any backup.

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
Starting backup at 28-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
. . .

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
16 OK 0 194907 2048000 1045096118
File Name: /dbs1/orcl/users.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 877717
Index 0 201038
Other 0 774338

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
21 FAILED 0 169474 2048000 1046708826
File Name: /dbs1/orcl/tools.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 847248
Index 0 358482
Other 1 672796

The above command populates the information of all the corrupted blocks in v$database_block_corruption view with and you can query this view to find all corrupted blocks.

Here we are using Data Recovery Advisor to find the possible repair options

Use LIST FAILURE command to find the any failures with a status OPEN and a priority of CRITICAL or HIGH in order of importance. You can use CHANGE FAILURE command to change the priority of a failure or close an open failure.

RMAN> LIST FAILURE
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1041 HIGH OPEN 23-SEP-11 Datafile 21: '/dbs1/orcl/tools.dbf ' contains one or more corrupt blocks

Use ADVISE FAILURE command to for repair advise based up on failures listed by LIST FAILURE command.

RMAN> ADVISE FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1041 HIGH OPEN 23-SEP-11 Datafile 21:''/dbs1/orcl/tools.dbf ' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=319 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. No backup of block 986359 in file 21 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
1 Recover multiple corrupt blocks in datafile 21
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/home/oracle/diag/rdbms/orcl/orcl/hm/reco_3435353455.hm
In this case there are no manual actions available, if any actions available then you can use to repair the database.

Use automated repair options to fix the block corruptions and use the repair script generated by RMAN. You can run the script manually or you can use REPAIR FAILURE command.

Use REPAIR FAILURE PREVIEW command to list the contents of repair script without applying it.
RMAN> Repair failure preview;

Use REPAIR FAILURE command to apply the repair script generated by ADVISE FAILURE command and fix the block corruptions.
RMAN> REPAIR FAILURE;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/home/oracle/diag/rdbms/orcl/orcl/hm/reco_3435353455.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 21 block 120;

The REPAIR FAILURE command promts you to confirm the preair and if you want to prevent that use NOPROMPT keyword.

RMAN> REPAIR FAILURE NO PROMPT;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

4 comments:

  1. شركات منطقة مكة المكرمة التي تعمل في مجال تنظيف وتعقيم المنازل تلك الشركة الجيدة التي تقدم خدمات جيدة في تنظيف المنازل من الداخل ومن الخارج في ارخص شركة تنظيف منازل بجدة تختص بأعمال التنظيف للمنازل الجديدة والمفروشة في مدينة جدة وقد تقوم تلك الشركات في جدة ايضا بتقديم خدمات غسيل المفروشات والكنب والسجاد تحت اسم افضل شركة غسيل سجاد بجدة تلك الشركات بتقديم تلك الخدمات بأسعار جيدة وخدمات مقبولة
    والتي تقوم بها افضل شركه تنظيف بجده لها خبرة طويلة بالمجال
    وقد عملت افضل شركة تنظيف منازل بجدة علي تقديم خدمات التنظيف داخل المنازل والفلل بمنتهى الاحترافية والدقة
    في العاصمة المقدسة التي هي مكة المكرمة توجد لدينا امكانيات كبيرة في مجال تنظيف المنازل والشقق والفلل كما في شركة تنظيف منازل بمكة المكرمة فلو تريد تنظيف بيتك او منزلك وانت في مكة تواصل معنا في حالة ان كان المنزل جديد
    اما في حال ان كان المنزل مفروش واصاب الفرش من الأوساخ والأتربة مثل الكنب والمجالس والسجاد وتريد التنظيف بالبخار تواصل مع شركة غسيل موكيت بمكة المكرمة وهي شركة متميزة في أعمال الغسيل والتنظيف بالبخار في نفس الموقع

    ReplyDelete