OraExplorer

When I tried to duplicate one of the databases using the RMAN duplicate, I’ve encountered the block corruption errors on the SYSAUX tablespace. The root cause of corruptions is still unknown to me. With further investigation, I’ve learned that the corrupt blocks do not belong to any objects. (The query used to identify them will be shown later.) This post is just following the article from MOS on How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1) to resolve the issue.

Since this article lists the step-by-step instructions very clearly, I will just follow it as written and add my inputs if any.

However, before we start, please make sure that you read the article. The important note here is, as the document warns us, that these steps are just one of many possible ways to fix this particular problem when the corrupt block does not belong to any object . And there is no guarantee to work.

Step 1 – Identify corrupt datafile.

Check the ORA-19566 message to identify the corrupt datafile.

The corrupt datafile in this case is file 2 which belongs to the SYSAUX tablespace.

The alert log shows as follows:

Corrupt block relative dba: 0x009499e5 (file 2, block 1350117)
Bad header found during validation
Data in bad block:
type: 40 format: 2 rdba: 0x0093abe5
last change scn: 0x09f1.e2a690d1 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x90d12802
check value in block header: 0xeacf
computed block checksum: 0x0
Reread of blocknum=1350117, file=+DEDW_DG1/dedw/datafile/sysaux.261.673617071. found same corrupt data
Reread of blocknum=1350117, file=+DEDW_DG1/dedw/datafile/sysaux.261.673617071. found same corrupt data

Running RMAN validate on the data file, we’ve seen multiple corrupt block numbers.

RMAN> backup validate datafile 2;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    FAILED 0              90642        1360196         11667810197595
File Name: +DEDW_DG1/dedw/datafile/sysaux.261.673617071
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       16             46391
Index      4              24256
Other      6730           1198907

The v$database_block_corruption shows the block numbers and how many of them.

SQL> select * from v$database_block_corruption;

FILE# BLOCK#  BLOCKS    CORRUPTION_CHANGE# CORRUPTION_TYPE
----  ------- --------- ------------------ ---------------
2     1350117 1         0                  CORRUPT
2     1350053 1         0                  CORRUPT
2     1349989 1         0                  CORRUPT
2     1349925 1         0                  CORRUPT
2     1349861 1         0                  CORRUPT
2     1349797 1         0                  CORRUPT
2     1341568 4         0                  CORRUPT
2     1341440 84        0                  CORRUPT
2     1337344 512       0                  CORRUPT
2     1333248 512       0                  CORRUPT
2     1329152 512       0                  CORRUPT
2     1325056 512       0                  CORRUPT
2     1320960 512       0                  CORRUPT
2     1316864 512       0                  CORRUPT
2     1312768 512       0                  CORRUPT
2     1308672 512       0                  CORRUPT
2     1304576 512       0                  CORRUPT
2     1300480 512       0                  CORRUPT
2     1296384 512       0                  CORRUPT
2     1288192 512       0                  CORRUPT
2     1284096 512       0                  CORRUPT

Note that there are multiple corrupt blocks in multiple ranges. Some ranges have just only one corrupt block while others have more, e.g., 512 blocks. The highest corrupt block number in this case is 1350117. We will use this number in the later step.

Step 2 – Run DBVERIFY (Database Verification Utility) on affected datafile and check for corrupt block.

Run dbv on the datafile which reports corrupt block.

$ dbv file=+DEDW_DG1/dedw/datafile/sysaux.261.673617071 userid=sys logfile=/tmp/dbv_sysaux.log

You can also specify start and end for the utility to run. But here, we will run for the whole data file.

Part of the output:

DBVERIFY: Release 11.1.0.7.0 - Production on Mon Jan 6 12:27:33 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DEDW_DG1/dedw/datafile/sysaux.261.673617071
Page 1284096 is marked corrupt
Corrupt block relative dba: 0x00939800 (file 2, block 1284096)
Bad header found during dbv:
Data in bad block:
type: 40 format: 2 rdba: 0x00938a00
last change scn: 0x09f1.e2a6199c seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x199c2802
check value in block header: 0x482a
computed block checksum: 0x0

Page 1284097 is marked corrupt
Corrupt block relative dba: 0x00939801 (file 2, block 1284097)
Bad header found during dbv:
Data in bad block:
type: 40 format: 2 rdba: 0x00938a01
last change scn: 0x09f1.e2a619af seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x19af2802
check value in block header: 0x4809
computed block checksum: 0x0

:

:
[the rest is omitted]

Step 3 – Check whether blocks are part of any object.

Query the dba_extents and  cross check that the block does not belong to any object.

SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupted block number> between block_id
and block_id + blocks -1;

The <Absolute file number> is 2 (SYSAUX). The <corrupted block number> is the corrupt block number.

Sample here:

select segment_name, segment_type, owner
from dba_extents
where file_id = 2
and 1284096 between block_id and block_id + blocks -1;

no rows selected

If there are multiple blocks to check, this query might not be an efficient way to verify.

The other way to confirm is to use Jonathan Lewis’s query from this post to list all the used extents and free space in a tablespace.

The output of this query gives you a visual of the block ranges which makes it easy to confirm that the affected blocks are not part of any objects.

Visual of the block ranges of interest

Step 4 – Create a dummy table as user other than SYS and SYSTEM.

SQL> connect scott/password

Create a dummy table in the tablespace containing datafile which has the corrupt block. Use nologging option to prevent redo records from being generated.

SQL> create table s (
        n number,
        c varchar2(4000)
     ) nologging tablespace SYSMAN;

Step 5 – Create a trigger on the dummy table which throws exception once the corrupt block is reused.

Note that this trigger is fired when the last (highest) block number is reused.

In our case, we will supply 2 for “filenumber” and 1350117 from the step 1 for the “blocknumber”.

Connect as sys and create the following trigger:

CREATE OR REPLACE TRIGGER corrupt_trigger
   AFTER INSERT ON scott.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;
/

Step 6- Allocate space to the table from the affected datafile.

First, find the extent size by querying the dba_free_space.

SQL> select bytes
from dba_free_space
where file_id=2 and 1284096 between block_id and block_id + blocks -1;

BYTES
----------
4160749568

ALTER table scott.s
  allocate extent (DATAFILE '+DEDW_DG1/dedw/datafile/sysaux.261.673617071' SIZE 4160749568);

Keep allocating until the corrupt block is part of the scott.s table – check this with the following query:

SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupt block number> between block_id
and block_id + blocks -1 ;

It is advisable to ensure that AUTOEXTEND is OFF for the datafile to prevent it from growing.

Step 7 -  Insert data into dummy table To format the block.

You can use either one of the three scripts the document provided.

This is the one I used:

BEGIN
FOR i IN 1..1000000000 LOOP
   INSERT INTO scott.s VALUES(i,'x');
END LOOP;
END;
/

The trigger will be fired for every row inserted into the table, and an exception with ORA-20000 will be produced as soon as it inserts row into the last corrupt block.

Step 8 – Verify for any corruption in datafile by running DBV and RMAN backup

Run dbv again on the corrupt datafile.  Hopefully, it will not show any more corruptions.

RMAN backup will not report any errors on these blocks.

RMAN> backup validate datafile 2;
:
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DEDW_DG1/dedw/datafile/sysaux.261.673617071
channel ORA_DISK_1: backup set complete, elapsed time: 00:06:06
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              259802       1638400         11669581253505
File Name: +DEDW_DG1/dedw/datafile/sysaux.261.673617071
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              1335585
Index      0              17394
Other      0              25619

SQL> select * from v$database_block_corruption;
no rows selected

Step 9 – Drop the dummy table created in step 4.

SQL> drop table scott.s;

Tags: 11g, Block, Corrupt, Corrupted, DBV, Duplicate, Failed, RMAN, Segment, Verify