RE: off the wall ideas welcomed

Niall, 

 

Thanks for posting back.  This is the type of thing I'd like to see others
do, to bring closure to posted issues..

 

This is a great back pocket reference fix I am sure will come up again and
maybe even again and again..

 

 

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Niall Litchfield
Sent: Friday, February 06, 2009 10:29 AM
To: dbvision@xxxxxxxxxxxx
Cc: ORACLE-L
Subject: Re: off the wall ideas welcomed

 

I thought I'd post back an update on this, and yep blog it as well. 

 

Nuno looks to be correct that this was a lob that was subject to updates.
The fact that this table turned out to have files with .mov, .mpg and .avi
extensions as well as .htm was a wee bit interesting too. 

 

anyway, export/import did not resolve the issue when more space was
procured. This confirmed to me that the issue was with corrupt data - in
addition dbv showed that 

 

a) there was only one page affected "in flux" 

b) it was different from the block pre drop/recreate

 

I then took a block dump of the affected block and got a row listing
including just a single row from my table back. There was an update
transaction in the ITL slots as well, pretty much along with Nuno's
comments about updates on his blog. I then determined which row we were
talking about, deleted the row, and took a second block dump of the same
block to confirm that it was now empty of data. finally I reran dbv again
and this time the file came up clean. 

 

thanks for all the kind suggestions - it's a shame that this database is
not 10g and block checking & recovery is unavailable.  

 

Niall



 

On Thu, Feb 5, 2009 at 10:30 AM, Nuno Souto <dbvision@xxxxxxxxxxxx> wrote:

Niall Litchfield wrote,on my timestamp of 5/02/2009 2:04 AM: 

 

   I'm pretty much out of (cheap) ideas for dealing with this
   corruption right now, so would welcome some.  

 

What is the output of this:

select dbms_metadata.get_ddl('TABLE','<table_name>') from dual;

Also, some ideas on what to look for and where things are, here:
http://dbasrus.blogspot.com/2007_02_01_archive.html

HTH
-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxx
--
http://www.freelists.org/webpage/oracle-l






-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: