RE: check for (empty?) blocks which leads to ORA-8103 in a datafile?

From: Powell, Mark D <mark.powell_at_EDS.COM>
Date: Wed, 9 Sep 2009 12:01:26 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90456F1D1_at_usahm208.amer.corp.eds.com>

If you cannot afford to lock the object from DML while you check it out you can try the manual method.  

Select each indexed row of the table via the index. You can do this via a cursor where you fetch via the indexed columns. Code an exception to capture the 08103 error. But if you find corruption in the table itself you are probably going to need a window anyway to completely fix the table and its indexes.  

By using the PK or a unique index we salvaged all but one or two rows of a corrupt table in the past by copying them to a new version of the table.  

• Mark D Powell -- Phone (313) 592-5148

  ---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark

	Sent: Tuesday, September 08, 2009 4:19 PM
	To: martin.a.berger_at_gmail.com; ORACLE-L
	Subject: RE: check for (empty?) blocks which leads to ORA-8103

in a datafile?                  

        How about:

        Analyze table <table_name> validate structure cascade;

        ??          

        Cascade should automatically check all indexes on each table, in addition to the table itself.          

        -Mark          

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger

	Sent: Tuesday, September 08, 2009 3:03 PM
	To: ORACLE-L
	Subject: check for (empty?) blocks which leads to ORA-8103 in a

datafile?          

        Hi List,          

        I am facing a problem in our DWH database (9.2.0.7):

        a query on a partition gives a ORA-8103 (object no longer exists). But I checked last_ddl_time from dba_objects which is 8-Aug-2009.

        During a SR (7694226.993) the explanation was 'This block looks like having been overwritten either in-memory or on-disk by a non-oracle block/page.

        This does not look like an Oracle error but introduced externally.'

        With the suggestion to restore the datafile.

        This is a possibility, but I want to check, how many blocks are affected, before I do anything.

        dbv did not show any errors

        a block dump of one of the blocks is:

• SESSION ID:(225.49695) 2009-09-07 14:15:04.220 Start dump data blocks tsn: 69 file#: 804 minblk 235529 maxblk 235529 buffer tsn: 69 rdba: 0x00039809 (0/235529) scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 frmt: 0x02 chkval: 0x990c type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x0000000103834A00 to 0x0000000103834A14 103834A00 00020000 00039809 00000000 00000105 [................] 103834A10 990C0000 [....] End dump data blocks tsn: 69 file#: 804 minblk 235529 maxblk 235529

        .          

        So my idea is to loop through dba_extents (block_id +
[1..blocks]) and create for every table or partition a 'select rownum
from user.table where rownum=dbms_rowid.rowid_create()'          

        This will give me information about any table block. (I guess)                    

        Does someone knows any method to ckeck all blocks of other objects (e.g. indexes) by accessing them via their rowid?                    

        thnx,

         Martin                                        

        --

        http://www.freelists.org/webpage/oracle-l          

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 09 2009 - 11:01:26 CDT