Let me Share one of my recent incident , where im suppose to recover the block which was marked as corrupted and the same was found belongs to free extent [ or does not belong to any segment].
Got an message from alert logfile that the sysaux datafile got some block corrupted.
When i ran the dbverify , got the below message:
DBVERIFY: Release 10.2.0.4.0 – Production on Mon Oct 1 07:06:50 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/oracle/uat/uatdata/sysaux01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 1091072
Total Pages Processed (Data) : 370292
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 682135
Total Pages Failing (Index): 0
Total Pages Processed (Other): 18266
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 20379
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 2431509128 (1389.2431509128)
When i checked database_block_corruption view , it returned no rows selected.
SQL> Select * from v$database_block_corruption;
no rows selected
Then i followed MOS ID : 819533.1 and found that The Corrupted block belongs to a free extent.
SQL> select dbms_utility.data_block_address_file(&&rdba) RFN,dbms_utility.data_block_address_block(&&rdba) BL from dual;
Enter value for rdba: 80451227
old 1: select dbms_utility.data_block_address_file(&&rdba) RFN,dbms_utility.data_block_address_block(&&rdba) BL from dual
new 1: select dbms_utility.data_block_address_file(80451227) RFN,dbms_utility.data_block_address_block(80451227) BL from dual
RFN BL
———- ———-
19 759451
##### You will get RDBA , when you run dbverify.
SQL> select file_id AFN, relative_fno, tablespace_name from dba_data_files where relative_fno=&RFN;
Enter value for rfn: 19
old 3: where relative_fno=&RFN
new 3: where relative_fno=19
AFN RELATIVE_FNO TABLESPACE_NAME
———- ———— ——————————
19 19 SYSAUX
SQL> select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks – 1;
Enter value for afn: 19
old 3: where file_id = &AFN
new 3: where file_id = 19
Enter value for bl: 759451
old 4: and &BL between block_id AND block_id + blocks – 1
new 4: and 759451 between block_id AND block_id + blocks – 1
no rows selected
SQL> select owner, segment_name, segment_type, partition_name from dba_segments where header_file = &AFN and header_block = &BL;
Enter value for afn: 19
old 3: where header_file = &AFN
new 3: where header_file = 19
Enter value for bl: 759451
old 4: and header_block = &BL
new 4: and header_block = 759451
no rows selected
SQL> select * from dba_free_space where file_id = &AFN and &BL between block_id AND block_id + blocks – 1;
Enter value for afn: 19
old 3: where file_id = &AFN
new 3: where file_id = 19
Enter value for bl: 759451
old 4: and &BL between block_id AND block_id + blocks – 1
new 4: and 759451 between block_id AND block_id + blocks – 1
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
————————————————————————————————————————–
SYSAUX 19 749065 246415360 30080 19
Then I Followed the MOS ID : 336133.1 , and got the corrupted block recovered.
conn apps
Password:
SQL> create table s (
n number,
c varchar2(4000)
) nologging tablespace sysaux;
SQL> CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, ‘Corrupt block has been formatted’);
END
SQL> SELECT BLOCK_ID,FILE_ID,BYTES FROM DBA_FREE_SPACE WHERE FILE_ID = 19 AND 759451 BETWEEN BLOCK_ID and BLOCK_ID + BLOCKS – 1;
BLOCK_ID FILE_ID BYTES
———- ———- ———-
749065 19 246415360
SQL> alter table s allocate extent (DATAFILE ‘/u01/oracle/uat/uatdata/sysaux01.dbf’ size 235m);
Table Altered
SQL> alter table s allocate extent (DATAFILE ‘/u01/oracle/uat/uatdata/sysaux01.dbf’ size 235m);
Table Altered
SQL> alter table s allocate extent (DATAFILE ‘/u01/oracle/uat/uatdata/sysaux01.dbf’ size 235m);
Table Altered
SQL> SELECT BYTES FROM DBA_FREE_SPACE WHERE FILE_ID = 19 AND 759451 BETWEEN BLOCK_ID and BLOCK_ID + BLOCKS – 1;
no rows selected
SQL> Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000 loop
Insert into s VALUES(i,’x’);
end loop;
commit;
END LOOP;
END;
2 3 4 5 6 7 8 9
10 /
Begin
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at “APPS.CORRUPT_TRIGGER”, line 10
ORA-04088: error during execution of trigger ‘APPS.CORRUPT_TRIGGER’
ORA-06512: at line 4
########## The above will take time based on where the corrupted block got placed ###
[orauat@dbdata12 10.2]$ dbv file=/u01/oracle/uat/uatdata/sysaux01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 – Production on Mon Oct 1 12:07:33 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/oracle/uat/uatdata/sysaux01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 1091072
Total Pages Processed (Data) : 510461
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 542090
Total Pages Failing (Index): 0
Total Pages Processed (Other): 18861
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 19660
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 2432979198 (1389.2432979198)
sqlplus “apps”
SQL> drop table s;
SQL>Alter system switch logfile ;
SQL>Alter system checkpoint ;############################################
How is it ? Please comment.
Yours,
Narasimha Rao
Recent Posts