Re: DBMS_REPAIR, 8.1.7

"John Oakes" <john_at_networkproductions.net> wrote in message news:Eg6wb.28538$M31.695669_at_twister.tampabay.rr.com...
>
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:3fc057e1$0$20482$afc38c87_at_news.optusnet.com.au...
> >
> > "John Oakes" <john_at_networkproductions.net> wrote in message
> > news:zqYvb.27269$M31.625393_at_twister.tampabay.rr.com...
> > > "Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in
message
> > > news:bth0svoa2a05bkhtpq40qbr13ctitmu1tv_at_4ax.com...
> > > > On Sun, 23 Nov 2003 04:24:02 GMT, "John Oakes"
> > > > <john_at_networkproductions.net> wrote:
> > > >
> > > > >Ok, I was able to find the corrupted blocks succesfully. But now,
I
> > want
> > > to
> > > > >use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark them as bad, and I am
> > getting:
> > > > >
> > > > >DECLARE num_fix INT;
> > > > >*
> > > > >ERROR at line 1:
> > > > >ORA-00600: internal error code, arguments: [kdrp_chkblk_1], [0],
[],
> > [],
> > > [],
> > > > >[], [], []
> > > > >ORA-06512: at "SYS.DBMS_REPAIR", line 287
> > > > >ORA-06512: at line 4
> > > > >
> > > > >Any ideas? Thanks!
> > > >
> > > >
> > > > ora-600 ----------> CALL SUPPORT
> > >
> > > Thanks, I have located the exact bug that causes this (found it in
> > > Metalink), it is number 2021123. According to the description it is
> fixed
> > > in product version 10.0. I'm not familiar with the Oracle versions,
if
> I
> > > upgrade from 8.1.7.0 to 8.1.7.4 is this version 10? What does version
> 10
> > > relate to? Thanks!
> >
> > No, 8i is version 8.
> > 9i is version 9.
> > 10g is version 10.
> >
> > And 10g has been out about a month and a half, so is still extremely new
> > (and likely has bugs all of its own to contend with).
> >
> > I would have thought that it was a bit extreme to upgrade two versions
of
> > the database merely to fix a bug in dbms_repair. Especially when
(reading
> > the bug report) the fix is likely to be nothing more than to signal a
> proper
> > ORA- error rather than throw an ORA-600. The issue is that your block
> which
> > is corrupt is not a data block nor an index block (it could therefore be
a
> > rollback segment header block, or a tablespace bitmap block, etc etc).
> > DBMS_REPAIR is not going to fix such blocks up, whatever version you
have.
> > So the issue is simply how dbms_repair reports the problem.
> >
> > Regards
> > HJR
>
> Thanks for the info. Any ideas how to recover the data from this table
that
> isn't bad? Now I guess I can't mark the bad blocks, so everytime it gets
to
> a bad block it errors and my query dies.

You know the file and block number of the offending block? In that case, you should select your data into another table using a rowid in the where clause, and use the rowid to manually exclude the corrupt block.

For example, suppose I had 100 blocks of emp on file 6, and block 48 was corrupt and irreperable.

create table newemp as select * from emp where rowid < 6.48.1

And then...

insert into newemp select * from emp where rowid >= 6.49.1

Now, that's just pseudo-code really, because rowid is a base-64 encoded number which looks like complete gibberish to mere mortals, so unless you happen to be unusually fluent in base-64, it's a bit unlikely you'll be able to type in the right native rowid. It's also the case that real rowids are in the format Block-File-Row, not File-Block-Row as I've indicated in the example above (and it's also the case that this is only the restricted rowid in anycase, and the extended rowid includes a reference to object number within it).

But, fortunately, Oracle realised the issue would be a complex one and invented dbms_rowid to allow you to convert decimal components of a rowid into its base-64 equivalent. Do a desc dbms_rowid (as SYS) to see what the various procedures within it are. You'll have to use several of them, concatenated together, to produce a complete rowid.

But the principle is sound: make Oracle manually select from around the corrupted block(s) by using rowid where clauses to avoid the problem.

Regards
HJR Received on Sun Nov 23 2003 - 14:08:56 CST