Re: DBMS_REPAIR, 8.1.7

From: John Oakes <john_at_networkproductions.net>
Date: Mon, 24 Nov 2003 05:21:44 GMT
Message-ID: <Izgwb.49199$86.785858@twister.tampabay.rr.com>

"John Oakes" <john_at_networkproductions.net> wrote in message news:mj9wb.43134$86.719470_at_twister.tampabay.rr.com...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:3fc113e7$0$14031$afc38c87_at_news.optusnet.com.au...
> >
> > "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

>

> I used dbv to find the file and block numbers. I think the DBMS_REPAIR
> package also generated me a table with all of this information. I didn't
> now you could select based on a rowid like that. I will try it right
away.
> Thanks!

Thanks for the help, I was able to determine the rowids using dbms_rowid and got the data I needed.

-John

>


> >

> > 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 - 23:21:44 CST