So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf).
After recover via RMAN I saw strange error.
RMAN> recover datafile 5 block 443;
Starting recover at 24-MAR-13
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 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233
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:04
Finished recover at 24-MAR-13
RMAN>
SQL> select count(1) from block_corrupt_test;
select count(1) from block_corrupt_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 443)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ulfet_db/example01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
col segment_name format a20
col tablespace_name format a15
SQL> select owner, segment_name, tablespace_name from dba_extents where FILE_ID=5 and 443 between BLOCK_ID and BLOCK_ID+BLOCKS;
OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------ -------------------- ---------------
SYS BLOCK_CORRUPT_TEST EXAMPLE
Using dbv utility to clarify datafile corruption
[oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/ulfet_db/example01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Sun Mar 24 22:57:06 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ulfet_db/example01.dbf
DBV-00201: Block, DBA 20971963, marked corrupt for invalid redo application
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 6586
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1261
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3251
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1702
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1377097 (0.1377097)
[oracle@localhost ~]$
Use below query to get block error and file_name:
SQL> select dbms_utility.data_block_address_block('20971963') block_no,
dbms_utility.data_block_address_file('20971963') file_no
from dual;
BLOCK_NO FILE_NO
---------- ----------
443 5
SQL>
Now follow up below statements:
Create the repair table (in tablespace where corrupted table resides)
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'example');
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL>
Identify corrupted blocks:
SQL> set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SYS',
OBJECT_NAME => 'BLOCK_CORRUPT_TEST',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/SQL> 2 3 4 5 6 7 8 9 10 11
number corrupt: 1
PL/SQL procedure successfully completed.
SQL>
Show corrupted blocks:
SQL> select BLOCK_ID, CORRUPT_TYPE from REPAIR_TABLE;
BLOCK_ID CORRUPT_TYPE
---------- ------------
443 6148
SQL>
Mark the identified blocks as corrupted:
SQL> DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SYS',
OBJECT_NAME=> 'BLOCK_CORRUPT_TEST',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/ 2 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL>
Allow in the future DML statements to skip the corrupted blocks:
SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SYS',
OBJECT_NAME => 'BLOCK_CORRUPT_TEST',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL>
Now, check table again:
SQL> select count(1) from block_corrupt_test;
COUNT(1)
------------
10
