RE: off the wall ideas welcomed
Date: Fri, 6 Feb 2009 10:50:21 -0600 (CST)
Message-ID: <8966337.1371233939021487.JavaMail.root_at_mail.enkitec.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Niall Litchfield
Sent: Friday, February 06, 2009 10:29 AM
To: dbvision_at_iinet.net.au
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
- there was only one page affected "in flux"
- 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_at_iinet.net.au> 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_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 06 2009 - 10:50:21 CST