DOYENSYS Knowledge Portal




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




Thursday, January 12, 2017

Facing ORA-19566 with Free Extents While running RMAN Backup

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

No comments: