Home > Oracle > RMAN Block Recovery – 11g using Recovery Advisor

RMAN Block Recovery – 11g using Recovery Advisor


Oracle now introduced RMAN Recovery Advisor to repair a number of Oracle issues.  The below example explains how easy now to manage Block recovery in Oracle 11g.

Let us backup the data files.

RMAN> backup datafile 4,7,8;

Starting backup at 08-OCT-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 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=00004 name=/oradata/bmk/bmk/users01.dbf
input datafile file number=00007 name=/oradata/bmk/bmk/test01.dbf
input datafile file number=00008 name=/oradata/bmk/bmk/test001.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-10
channel ORA_DISK_1: finished piece 1 at 08-OCT-10
piece handle=/oradata/bmk/flash/BMK/backupset/2010_10_08/o1_mf_nnndf_TAG20101008T203148_6bydlxmn_.bkp tag=TAG20101008T203148 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-OCT-10

Starting Control File and SPFILE Autobackup at 08-OCT-10
piece handle=/oradata/bmk/flash/BMK/autobackup/2010_10_08/o1_mf_s_731881934_6bydmqy8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-OCT-10

Create a new test table
SQL>  create table test as select * from dba_objects  where rownum < 50;

Table created.

Now identify all the information required to corrupt a single block.
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
                               32187
SQL> select distinct dbms_rowid.ROWID_RELATIVE_FNO(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
————————————
                                   4
SQL> select file_name from dba_data_files where file_id=4;

FILE_NAME
——————————————————————————–
/oradata/bmk/bmk/users01.dbf
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_size                        integer     8192

 Armed with the details, let us corrupt the block.
oracle@:/home/oracle>$ dd of=/oradata/bmk/bmk/users01.dbf bs=8192 seek=32187 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

Now the block is corrupted.

Flush the shared pool to force a disk read.

SQL> alter system flush buffer_cache;

System altered.

Now read the table TEST

SQL> conn ora600tom/ora600tom
Connected.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 32187)
ORA-01110: data file 4: ‘/oradata/bmk/bmk/users01.dbf’

Now we have the real problem, data blcok is corrupted for the table TEST.  Let us query the view V$DATABASE_BLOCK_CORRUPTION

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS      CORRUPTION_CHANGE#    CORRUPTIO
———-   ———-        ———-      ——————                              ———
         4        32187             1                      0                                                       ALL ZERO

Let us see what information we have in the alert log

Fri Oct 08 20:41:06 2010
ALTER SYSTEM: Flushing buffer cache
Fri Oct 08 20:42:26 2010
Hex dump of (file 4, block 32187) in trace file /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/trace/bmk_ora_5380.trc
Corrupt block relative dba: 0x01007dbb (file 4, block 32187)
Completely zero block found during buffer read
Reading datafile ‘/oradata/bmk/bmk/users01.dbf’ for corruption at rdba: 0x01007dbb (file 4, block 32187)
Reread (file 4, block 32187) found same corrupt data
Fri Oct 08 20:42:26 2010
Corrupt Block Found
         TSN = 4, TSNAME = USERS
         RFN = 4, BLK = 32187, RDBA = 16809403
         OBJN = 78302, OBJD = 78302, OBJECT = TEST, SUBOBJECT =
         SEGMENT OWNER = ORA600TOM, SEGMENT TYPE = Table Segment
Errors in file /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/trace/bmk_ora_5380.trc  (incident=20569):
ORA-01578: ORACLE data block corrupted (file # 4, block # 32187)
ORA-01110: data file 4: ‘/oradata/bmk/bmk/users01.dbf’
Incident details in: /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/incident/incdir_20569/bmk_ora_5380_i20569.trc
Fri Oct 08 20:42:32 2010
Sweep [inc][20569]: completed
Errors in file /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/trace/bmk_ora_5380.trc  (incident=20570):
ORA-01578: ORACLE data block corrupted (file # 4, block # 32187)
ORA-01110: data file 4: ‘/oradata/bmk/bmk/users01.dbf’
Incident details in: /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/incident/incdir_20570/bmk_ora_5380_i20570.trc
Fri Oct 08 20:42:32 2010
Trace dumping is performing id=[cdmp_20101008204232]
Hex dump of (file 4, block 32187) in trace file /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/incident/incdir_20569/bmk_m000_5386_i20569_a.trc
Corrupt block relative dba: 0x01007dbb (file 4, block 32187)
Completely zero block found during validation
Reread of blocknum=32187, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=32187, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=32187, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=32187, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=32187, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Checker run found 1 new persistent data failures
Trace dumping is performing id=[cdmp_20101008204235]
Fri Oct 08 20:43:32 2010
Sweep [inc][20570]: completed
Sweep [inc2][20570]: completed
Sweep [inc2][20569]: completed

Now Oracle documented the incident and the failure details are available to RMAN Advisor. 

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
442        HIGH     OPEN      08-OCT-10     Datafile 4: ‘/oradata/bmk/bmk/users01.dbf’ contains one or more corrupt blocks
RMAN>  list failure detail;

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

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
442        HIGH     OPEN      08-OCT-10     Datafile 4: ‘/oradata/bmk/bmk/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 442
  Failure ID Priority Status    Time Detected Summary
  ———- ——– ——— ————- ——-
  445        HIGH     OPEN      08-OCT-10     Block 32187 in datafile 4: ‘/oradata/bmk/bmk/users01.dbf’ is media corrupt
    Impact: Object TEST owned by ORA600TOM might be unavailable

 

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
442        HIGH     OPEN      08-OCT-10     Datafile 4: ‘/oradata/bmk/bmk/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 442
  Failure ID Priority Status    Time Detected Summary
  ———- ——– ——— ————- ——-
  445        HIGH     OPEN      08-OCT-10     Block 32187 in datafile 4: ‘/oradata/bmk/bmk/users01.dbf’ is media corrupt
    Impact: Object TEST owned by ORA600TOM might be unavailable

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

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

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

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Perform block media recovery of block 32187 in file 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/hm/reco_3346247611.hm

 
RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/product/diag/rdbms/bmk/bmk/hm/reco_3346247611.hm

contents of repair script:
   # block media recovery
   recover datafile 4 block 32187;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting recover at 08-OCT-10
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 00004
channel ORA_DISK_1: reading from backup piece /oradata/bmk/flash/BMK/backupset/2010_10_08/o1_mf_nnndf_TAG20101008T203148_6bydlxmn_.bkp
channel ORA_DISK_1: piece handle=/oradata/bmk/flash/BMK/backupset/2010_10_08/o1_mf_nnndf_TAG20101008T203148_6bydlxmn_.bkp tag=TAG20101008T203148
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

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

Finished recover at 08-OCT-10
repair failure complete

The new RMAN Advisor component explains the Block Corruption issue and recovers it from the backup online.   And the DBA issued just two or three standard commands, so that DBA becomes DBO – Database Operators!

Advertisements
Categories: Oracle Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: