ORA-19566 with Free Extents

This is a very interesting case – you got a block corruption on a free block, not associated with any objects extents.  You have no way to recover this block as RMAN will not mind free extents.  I hit this corruption while was doing an RMAN copy.  My file copy failed with,

RMAN> backup INCREMENTAL LEVEL 0 as copy datafile ‘/oradata/bmk/bmk/users01.dbf’  format ‘/oradata/bmk/dbcopy/users01.dbf’;

Starting backup at 14-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/bmk/bmk/users01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/14/2010 14:35:19
ORA-19566: exceeded limit of 0 corrupt blocks for file /oradata/bmk/bmk/users01.dbf

The alert file shows there is a block corrupt block  – block number 1867 in file 4. 

Tue Dec 14 14:35:12 2010
Hex dump of (file 4, block 1867) in trace file /oradata/oracle/product/10204/diag/rdbms/bmk/bmk/trace/bmk_ora_538.trc
Corrupt block relative dba: 0x0100074b (file 4, block 1867)
Completely zero block found during backing up datafile
Reread of blocknum=1867, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=1867, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=1867, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=1867, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data
Reread of blocknum=1867, file=/oradata/bmk/bmk/users01.dbf. found same corrupt data

Iwent and checked the DBA_EXTENTS, but there were no extents with the block 1867.

SQL>  select SEGMENT_NAME,SEGMENT_TYPE from dba_extents
where FILE_ID=4 and BLOCK_ID <=1867
and block_id+blocks >= 1867;

no rows selected
SQL> select tablespace_name ,bytes/1024/1024
from dba_data_files where file_id=4;

TABLESPACE_NAME                BYTES/1024/1024
—————————— —————
USERS                                    21.25

More over I could track down the block in a free extent.

SQL> select TABLESpace_name from dba_free_space where FILE_ID=4 and BLOCK_ID <=1867
and block_id+blocks >= 1867;

TABLESPACE_NAME
——————————
USERS

I know if I use this block for some objects, Oracle will re-format while re-using.   So, I tried my luck.  I created a new table on the USERS tablespace, as

SQL>  create table test tablespace users  as select * from dba_objects;
Table created.
SQL> select SEGMENT_NAME,SEGMENT_TYPE from dba_extents where FILE_ID=4 and BLOCK_ID <=1867 and block_id+blocks >= 1867;

no rows selected

 No luck,  as Oracle did not use the problem block in the free extent.  Then I moved my TEST table till Oracle used the problem block.

SQL> alter table test move ;

Table altered.

SQL> select TABLESpace_name from dba_free_space where FILE_ID=4 and BLOCK_ID <=1867
and block_id+blocks >= 1867;

TABLESPACE_NAME
——————————
USERS

SQL>  alter table test move ;

Table altered.

SQL> select TABLESpace_name from dba_free_space where FILE_ID=4 and BLOCK_ID <=1867 and block_id+blocks >= 1867;

no rows selected
SQL> select SEGMENT_NAME,SEGMENT_TYPE from dba_extents where FILE_ID=4 and BLOCK_ID <=1867 and block_id+blocks >= 1867;
SEGMENT_NAME              SEGMENT_TYPE
————————- ——————
TEST                      TABLE

Good, now the corrupted block is with in an extent.  I just droped the table as Oracle was successful in re-formating the block and created my TEST table on this block.

SQL> drop table test;

Table dropped.

Now I am good to go with the backup, and my copy went successfull.

RMAN> backup INCREMENTAL LEVEL 0 as copy datafile ‘/oradata/bmk/bmk/users01.dbf’  format ‘/oradata/bmk/dbcopy/users01.dbf’;

Starting backup at 14-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/bmk/bmk/users01.dbf
output file name=/oradata/bmk/dbcopy/users01.dbf tag=TAG20101214T145207 RECID=3 STAMP=737736736
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-DEC-10

Advertisements
  1. July 31, 2013 at 8:24 pm

    Excellent post, this is the exact issue that I have run into, also with the USERS tablespace. What if moving the table doesn’t ever cause it to use the corrupt block? Is there any way you know of to force this to happen?

  2. Syed Rafi Ullah
    June 30, 2015 at 11:09 am

    Interesting case…..

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: