RMAN Block Recovery ,Skip Corrupted Block

 

Create table under tablespace test2 and check the block# for the first few rows.

SQL> create table oratab.objects2 tablespace test2 as select * from dba_objects;

Table created.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from oratab.objects2) where rownum < 7;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
131
132
133
134
135

SQL> select count(*) from oratab.objects2;

COUNT(*)
———-
18520

No corrupted blocks in the tablespace.

SQL> select * from v$database_block_corruption;

no rows selected

Manually corrupt one of the block containing data.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=133 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.0526e-05 s, 162 MB/s

Flush buffer cache as it may have all blocks currently in memory.

SQL> alter system flush buffer_cache;

System altered.

Query the table, it will through error message with the block# of corrupted block.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

The corrupted block details will be reflected in v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 133 1 0 ALL ZERO

Recover block using RMAN

RMAN> blockrecover datafile 7 block 133;

Starting recover at 21-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Corrupt multiple blocks now.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=134 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.6443e-05 s, 145 MB/s
$ dd of=/u02/oradata/test201.dbf bs=8192 seek=135 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.2078e-05 s, 157 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 134)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

Use RMAN validate to check the corrupted block list.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE TEST2;

Starting validate at 21-APR-16
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=00007 name=/u02/oradata/test201.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 FAILED 0 12410 12800 30691648
File Name: /u02/oradata/test201.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 247
Index 0 0
Other 2 143

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 134 2 0 ALL ZERO
When there are multiple corrupted blocks, use blockrecover corruption list to recover them.

RMAN> blockrecover corruption list;

Starting recover at 21-APR-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Skipping Corrupted Block using event ‘10231’  or DBMS_REPAIR if it can’t be recovered.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.9682e-05 s, 165 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 131 1 0 ALL ZERO

SQL> ALTER SESSION SET EVENTS
‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’; 2

Session altered.

SQL> create table oratab.objects2_recover tablespace test2 as select * from oratab.objects;

Table created.

SQL> SQL> select count(*) from oratab.objects2_recover;

COUNT(*)
———-
18518 — 2 rows decreased.

Corrupted block can be skipped using DBMS_REPAIR package as well

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′);

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME SKIP_CORRUPT
—————————— ——–
OBJECTS2 ENABLED

Skip corrupt flag can be cleared after exporting the table or using CTAS

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′,flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select table_name,SKIP_CORRUPT from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME                               SKIP_CORRUPT
—————————— ——–
OBJECTS2                                             DISABLED

 

ref:Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 (Doc ID 33405.1)

 

Advertisements

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