DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, October 3, 2012

Recovering Block Corruption when the block does not belong to any segment


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

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.