Re: DBMS_REPAIR, 8.1.7

"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!

>
> 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 - 15:06:26 CST