RE: check for (empty?) blocks which leads to ORA-8103 in a datafile?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Sep 2009 12:01:26 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Sent: Tuesday, September 08, 2009 4:19 PM
To: martin.a.berger@xxxxxxxxx; 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
