Home > Oracle > Oracle Block Corruption and ORA-08103

Oracle Block Corruption and ORA-08103


This specific example shows,   

  • Resolution for ORA-08103 ( because of Segment Header block corruption)
  • Recover Oracle Blocks having no backups
  • Recovering data files having no backups.

 Most of the times the ORA-08103 is misleading and there are so many Oracle Bugs associated with this. 

To prepare the I have created a new tablespace TBS01 and user ‘THOMAS’

SQL> create tablespace tbs01 datafile ‘/oradata/ctgr/tbs01.dbf’  size 200m
extent management local uniform size 64k
segment space management auto;  2    3

Tablespace created.

SQL> create user thomas identified by thomas default tablespace tbs01;

User created.

SQL> grant connect,resource to thomas;

Grant succeeded.

Login to te user and created the test table with just one row,

SQL> conn thomas/thomas
Connected.

SQL> create table test as select * from user_objects where rownum < 2;

Table created.

SQL> select distinct dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
                                  12

SQL> select distinct dbms_rowid.ROWID_RELATIVE_FNO(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
————————————
                                  16

SQL> select count(*) from test;

  COUNT(*)
———-
         1

So, now I have just one row in my TEST table.  Login as SYS user and identify the file name and DB_BLOCK_SIZE

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_size                        integer     8192

SQL> select file_name from dba_data_files where file_id=16;

FILE_NAME
——————————————————————————–
/oradata/ctgr/tbs01.dbf

And corrupt the block, ( oops don’t try in your environments…  Reason to lose your job!!!)

$  dd of=/oradata/ctgr/tbs01.dbf bs=8192 seek=12 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

Now again login as SYS user and flush the buffer cache to force a disk read and SELECT the table TEST.

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-08103: object no longer exists

 Oops there you are.  Initially I am confused for a while with the error, what happened  to my table?  I just corrupted a block, but now the table itself is missing!  But a close look at the error message showed, that is not the usual “ORA-00942: table or view does not exist”  error rather, table is existing but not able to read.    I should have got following error if the  table is non-existing (Eg: Test1 is not existing).   

SQL> select count(*) from test1;
select count(*) from test1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

  That means, my table is existing, but not able to read as the block is corrupted, well there could be a relation to ORA-08103 and block corruption.  In fact I had  corrupted the table segment header block  itself!    TEST table header block has a corrupted block and not able to map segment reference in the dictionary, hence oracle reports a  “ORA-08103: object no longer exists”.  Because Oracle is not able refer the table segment header, I am not getting the real block corruption error.

Also, I checked the DBA_SEGMENTS, i can see the TEST table segment details there in the dictionary,

SQL> select bytes/1024/1024 ,extents from dba_segments
where segment_name=’TEST’ and
owner=’THOMAS’;  2    3

BYTES/1024/1024    EXTENTS
————— ———-
          .0625          1

Well, let me force Oracle to say that  there is a corrupted block, let us take a RMAN backup of the data file.  Here is the output,

 $ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Oct 15 17:36:06 2010

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

connected to target database: CTGR (DBID=1764603925)

RMAN> backup datafile 16;

Starting backup at 15-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=49 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00016 name=/oradata/ctgr/tbs01.dbf
channel ORA_DISK_1: starting piece 1 at 15-OCT-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/15/2010 17:36:19
ORA-19566: exceeded limit of 0 corrupt blocks for file /oradata/ctgr/tbs01.dbf

Good, now RMAN helped me to prove that there is a block corruption as the backup is failed with “ORA-19566: exceeded limit of 0 corrupt blocks”

Also, now Oracle reported the error in alert log as

Fri Oct 15 17:36:18 2010
Hex dump of (file 16, block 12) in trace file /oradata/oracle/product/10204/admin/ctgr/udump/ctgr_ora_11030.trc
Corrupt block relative dba: 0x0400000c (file 16, block 12)
Completely zero block found during backing up datafile
Reread of blocknum=12, file=/oradata/ctgr/tbs01.dbf. found same corrupt data
Reread of blocknum=12, file=/oradata/ctgr/tbs01.dbf. found same corrupt data
Reread of blocknum=12, file=/oradata/ctgr/tbs01.dbf. found same corrupt data
Reread of blocknum=12, file=/oradata/ctgr/tbs01.dbf. found same corrupt data
Reread of blocknum=12, file=/oradata/ctgr/tbs01.dbf. found same corrupt data

And that is the exact place we corrupted the block ( file : 16 and block : 12)

Now, we know what is the real reason behind the ORA-08103 error.  It is very simple now, just recover the block using RMAN – but i stuck again.  My block recovery failed with…

$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Oct 15 17:37:53 2010

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

connected to target database: CTGR (DBID=1764603925)

RMAN> blockrecover datafile 16 block 12;

Starting blockrecover at 15-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 10/15/2010 17:38:24
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 16 found to restore

 Now that is a problem, i have created the tablespace just now, after the last backup.  So RMAN not able to restore the ‘good’ copy of a corrupted block, but I have all the archive logs.

Now we need to take the tablespace OFFLINE to recover the block – not just the block, but the complete tablespace.  Taken the tablespace offline first and renamed the existing datafile.

 RMAN> sql ‘alter database datafile 16 offline’;

sql statement: alter database datafile 16 online

$ mv /oradata/ctgr/tbs01.dbf /oradata/ctgr/tbs01.dbf.old

From RMAN, restored the data file.  Since there is no data file backup existing and all the archive files are available, RMAN re-created the data files and recovered.   

$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Oct 15 17:40:30 2010

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

connected to target database: CTGR (DBID=1764603925)

RMAN> restore datafile 16;

Starting restore at 15-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=20 devtype=DISK

creating datafile fno=16 name=/oradata/ctgr/tbs01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 15-OCT-10

RMAN> recover datafile 16;

Starting recover at 15-OCT-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 15-OCT-10

 
RMAN> sql ‘alter database datafile 16 online’;

sql statement: alter database datafile 16 online

Now, my data files back to normal with recovered block.. what happened to my table..?  Let me try..
SQL> conn thomas/thomas
Connected.

SQL> select count(*) from test;

  COUNT(*)
———-
         1

 wow.. I got my valuable row back…  🙂

More to come on block corruption…

Update 1:

If you look closely, my assumption that the “Segment Block Corruption” is not really correct!  Becasue a segment header block is different from a data block, and can’t exist both  in the same block.  Here I selected the Block ID from a ROWID, means I am corrupting a Data Block, not a Segment Header.  

Also, the extent map shows,

SQL> select FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=’TEST’ and OWNER=’THOMAS’;

   FILE_ID   BLOCK_ID     BLOCKS
———-        ———-            ———-
        16           9                            8

  The Block ID starts from 9, but I am corrupting the Block ID 12.  So the blocks  9, 10 and 11 are the (I assume so) real Segment Header Blocks.   This brings new questions …

  • What will happen if I corrupt the segment Header Blocks?
  • Why did I get a ORA-08103 instead of a block corruption error?

Update 2:

When I corrupt the block using ‘dd’ command, it will completely wipe out the data from the data block.  Oracle reads a data block and expect the Block type 6 ( for a data block) but in this case the block type is undetermined as the complete data block information was wiped out by the ‘dd’ command.  This is leading to  ORA-08103, rather a simple block corruption error. 

A simple block corruption error may occur, if the the Data block type is valid and any other part of the block is corrupted (remember an Oracle block is multiples of OS blocks).

Advertisements
  1. Romil
    October 15, 2012 at 3:21 pm

    very well documented teh issue and the steps to solve it ………Thanks a lot for helping me with this……………

  2. Munna
    August 30, 2013 at 7:07 pm

    Same issue I am getting and we are not using the rman backup so how can i resolve the issue. Plesae helpe me.

  3. Munna
    August 30, 2013 at 7:11 pm

    let me give the more information on it

    we have two part in single script xvr.sql
    1.)insert statement
    2.)select statement : No issue
    But when will run in single command insert+select then will get

    17:06:30 SQL> @xvr.sql
    17:06:46 128 /
    insert into S.I$_HST
    *
    ERROR at line 1: ORA-08103: object no longer exists

    and we are not using any rman backup for this database. Please suggest me to resolve this issue.

    • August 31, 2013 at 3:42 am

      Hello, I do not think this is the block corruption problem you are hitting. Please check out the metalink note 421897.1, that might help. Post complete steps, if that is not helping.

      Thanks
      Thomas

  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: