Sometimes, in alert log file it's appears this message (ORA-01578: ORACLE data block corrupted). You can know which object is corrputed whit these steps and to decide if fix it deleting/recreating object (index, table.. ) or restore object using RMAN BLOCKRECOVER.
ORA-01578: ORACLE data block corrupted (file # 15, block # 608562)
ORA-01110: data file 15: '/software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'
1.- DBVERIFY
Using oracle dbverify tool in system prompt to confirm corruption
dbv file=/software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'
DBVERIFY: Release 11.2.0.2.0 - Production on Tue Oct 16 09:45:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'
DBV-00200: Block, DBA 63523122, already marked corrupt
csc(0x0000.3de058f1) higher than block scn(0x0000.00000000)
Page 608562 failed with check code 6054
DBVERIFY - Verification complete
Total Pages Examined : 2621440
Total Pages Processed (Data) : 2268458
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 45544
Total Pages Failing (Index): 0
Total Pages Processed (Other): 8958
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 298480
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1109032205 (0.1109032205)
2.- Script SQLPLus to confirm the block marked as corrupted with dbverify is the same it's appears in alert log file
select dbms_utility.data_block_address_file(&&rdba) RFN,
2 dbms_utility.data_block_address_block(&&rdba) BL
3 from dual;
Enter value for rdba: 63523122
old 1: select dbms_utility.data_block_address_file(&&rdba) RFN,
new 1: select dbms_utility.data_block_address_file( 63523122) RFN,
old 2: dbms_utility.data_block_address_block(&&rdba) BL
new 2: dbms_utility.data_block_address_block( 63523122) BL
RFN BL
---------- ----------
15 608562
3.- Script SQLPlus to confirm the tablespace
select file_id AFN, relative_fno, tablespace_name
from dba_data_files
where relative_fno=&RFN;
Enter value for rfn: 15
old 3: where relative_fno=&RFN
new 3: where relative_fno=15
AFN RELATIVE_FNO TABLESPACE_NAME
---------- ------------ ------------------------------
15 15 USERS
4.- Script SQLPlus to know the object corrupted
select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1;
Enter value for afn: 15
Enter value for bl: 608562
old 1: select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1
new 1: select * from dba_extents where file_id = 15 and 608562 between block_id AND block_id + blocks - 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
ORCLUSER
PAGERESULTS
TABLE USERS
0 15 607872 8388608 1024 15
In this case it's a table, now you must to decide if restore the block using RMAN, or if the table is used as repository temporary table in operations and if it can be deleted and recreated or if the data contained can be restore with oracle utility data pump.
No comments:
Post a Comment