We are running an Oracle 8.1.7.4.1 SE database and a datafile has a
corrupt block. The database has been restored and we have attempted
to roll forward using the current control files and archive logs but
it has failed.

The table concerned is fairly large at ~150Gb so selecting/inserting
around the corrupt 2K block could be a bit of a nightmare!

Has anybody else had a similar problem and been able to resolve it?
If so, what actions did you take?

Any feedback would be appreciated.

Many thanks

"Andrew Riley" <himsdba@yahoo.co.uk> wrote in message
news:30e7f1c6.0411021834.34706262@posting.google.c om...
> We are running an Oracle 8.1.7.4.1 SE database and a datafile has a
> corrupt block. The database has been restored and we have attempted
> to roll forward using the current control files and archive logs but
> it has failed.
>
> The table concerned is fairly large at ~150Gb so selecting/inserting
> around the corrupt 2K block could be a bit of a nightmare!
>
> Has anybody else had a similar problem and been able to resolve it?
> If so, what actions did you take?
>
> Any feedback would be appreciated.
>
> Many thanks

Yes. Do an export on the object. It should fail which allows you to
identify the object. If the object is an index then drop the index and
recreate it. Construct a select that does a full table scan and determine
what block it is by including the rowid in the select and spooling it to a
file. Then do a create table as select excluding the rows (via rowid) into
another area and get the good data into the new table. Then you are going
to have to figure out what to do with the missing rows.
Jim

himsdba@yahoo.co.uk (Andrew Riley) wrote in
news:30e7f1c6.0411021834.34706262@posting.google.c om:

> We are running an Oracle 8.1.7.4.1 SE database and a datafile has a
> corrupt block. The database has been restored and we have attempted
> to roll forward using the current control files and archive logs but
> it has failed.
>
> The table concerned is fairly large at ~150Gb so selecting/inserting
> around the corrupt 2K block could be a bit of a nightmare!
>
> Has anybody else had a similar problem and been able to resolve it?
> If so, what actions did you take?
>
> Any feedback would be appreciated.
>
> Many thanks
>

http://metalink.oracle.com explains how to handle this situation.

"Andrew Riley" <himsdba@yahoo.co.uk> wrote in message
news:30e7f1c6.0411021834.34706262@posting.google.c om...
> We are running an Oracle 8.1.7.4.1 SE database and a datafile has a
> corrupt block. The database has been restored and we have attempted
> to roll forward using the current control files and archive logs but
> it has failed.
>
> The table concerned is fairly large at ~150Gb so selecting/inserting
> around the corrupt 2K block could be a bit of a nightmare!
>
> Has anybody else had a similar problem and been able to resolve it?
> If so, what actions did you take?
>
> Any feedback would be appreciated.
>
> Many thanks

DBMS_REPAIR is your friend in this situation. It will scan a table and
report which blocks it is that are corrupt. You can also use the package to
makr those blocks as corrupt, and hence make them skippable by full table
scans performed by users. It means you lose the data in those blocks (some
new meaning of the word 'repair', methinks!), but it's better than not
having a working table at all.

Otherwise, the only thing I can think of is that you didn't restore froma
suffcieintly old backup, and that the corruption was in the backup you just
restored from. In which case (a) why don't you verify your backups using
DBVerify (dbv) -because then you would have known of the problem in your
backup set. And (b) how about restoring from a much older backup and
recovering that?

Regards
HJr

"Howard J. Rogers" <hjr@dizwell.com> wrote in message news:<41884f24$0$32593$afc38c87@news.optusnet.com. au>...

> Otherwise, the only thing I can think of is that you didn't restore froma
> suffcieintly old backup, and that the corruption was in the backup you just
> restored from. In which case (a) why don't you verify your backups using
> DBVerify (dbv) -because then you would have known of the problem in your
> backup set. And (b) how about restoring from a much older backup and
> recovering that?

.... and also how about using RMAN utility for future backups to ensure
that something like this (unnoticed corrupted backup set) wouldn't
happen again?

--
Dusan Bolek
http://www.db-support.com

Email: spambin@seznam.cz
Pls add "Not Guilty" to the subject, otherwise your email will face an
unpleasant end as SPAM.

"Dusan Bolek" <pagesflames@usa.net> wrote in message
news:1e8276d6.0411030648.6ae1d70e@posting.google.c om...
> "Howard J. Rogers" <hjr@dizwell.com> wrote in message
> news:<41884f24$0$32593$afc38c87@news.optusnet.com. au>...
>
>> Otherwise, the only thing I can think of is that you didn't restore froma
>> suffcieintly old backup, and that the corruption was in the backup you
>> just
>> restored from. In which case (a) why don't you verify your backups using
>> DBVerify (dbv) -because then you would have known of the problem in your
>> backup set. And (b) how about restoring from a much older backup and
>> recovering that?
>
> ... and also how about using RMAN utility for future backups to ensure
> that something like this (unnoticed corrupted backup set) wouldn't
> happen again?

Excellent point.

Regards
HJR

Thanks for the feedback. We have made some progress and here is a
brief outline.

We used Metalink to identify the object, block number and relevant
file. After cloning the database on SAN we restored a good copy of
the datafile from an early backup, checked it was OK with DBVerify and
recovered. It all completed BUT repicated the corrupt block. We think
this was caused by extremely intense I/O on the database (bit of
conjecture).

Although the database is 81741, legacy data types forces us to use 804
compatibility meaning we are unable to use DBMS_REPAIR. It also means
we are unable to use the data salvage SQL on Metalink (the long raw
column data type is explicitly unsupported).

Anyone any idea how we may recover/salvage data other than using
select/insert based on low/hi rowid?

We are also registered Oracle users and hope to get assistance from
the 'experts' (although they have ignored the 804 compatibility!!!)

Thanks in advance
Andrew Riley

"Howard J. Rogers" <hjr@dizwell.com> wrote in message news:<4188f6e6$0$24942$afc38c87@news.optusnet.com. au>...
> "Dusan Bolek" <pagesflames@usa.net> wrote in message
> news:1e8276d6.0411030648.6ae1d70e@posting.google.c om...
> > "Howard J. Rogers" <hjr@dizwell.com> wrote in message
> > news:<41884f24$0$32593$afc38c87@news.optusnet.com. au>...
> >
> >> Otherwise, the only thing I can think of is that you didn't restore froma
> >> suffcieintly old backup, and that the corruption was in the backup you
> >> just
> >> restored from. In which case (a) why don't you verify your backups using
> >> DBVerify (dbv) -because then you would have known of the problem in your
> >> backup set. And (b) how about restoring from a much older backup and
> >> recovering that?
> >
> > ... and also how about using RMAN utility for future backups to ensure
> > that something like this (unnoticed corrupted backup set) wouldn't
> > happen again?
>
> Excellent point.
>
> Regards
> HJR

It seems to me today, that I shouldn't be so concise when posting
this. To elaborate this futher, even with RMAN it can still happen,
although not on the reported version. However, in general yes you can
have an unnoticed corrupted backup set even when using RMAN. It can
happen only in case of using 9.2.0.5 together with ASSM tablespaces
(see bug no. 3785200). Everyone with this combination should apply the
patch that is already available, becase otherwise he could encounter a
data loss. Beware, this one is quite nasty!

--
Dusan Bolek
http://www.db-support.com

Email: spambin@seznam.cz
Pls add "Not Guilty" to the subject, otherwise your email will face an
unpleasant end as SPAM.