SEO

Hi,

We had some corrupt blocks in some of our data files. I have managed
to sort these out. However, I used DBV to check all data files that
were on the same disk and it came back with 1 corrupt page in the
SYSTEM table space.

The segment is called IDL_UB2$

The DB concerned is a test DB with customer databases, therefore we do
not back up the database. I could just export all the user data,
recreate the instance and re-import, but if I can avoid this I would
like to.

I have looked in metalink but I cannot really find out what this table
is, or what it is used for.
Can anybody explain it to me, and tell me whether I can just use a
script to rebuild the table?

Thanks in advance!

Paul

On Jul 5, 12:51 pm, Paul <paulwragg2...@hotmail.com> wrote:
> Hi,
>
> We had some corrupt blocks in some of our data files. I have managed
> to sort these out. However, I used DBV to check all data files that
> were on the same disk and it came back with 1 corrupt page in the
> SYSTEM table space.
>
> The segment is called IDL_UB2$
>
> The DB concerned is a test DB with customer databases, therefore we do
> not back up the database. I could just export all the user data,
> recreate the instance and re-import, but if I can avoid this I would
> like to.
>
> I have looked in metalink but I cannot really find out what this table
> is, or what it is used for.
> Can anybody explain it to me, and tell me whether I can just use a
> script to rebuild the table?
>
> Thanks in advance!
>
> Paul

IDL tables store PL/SQL, and are in the SYS schema. The SYS schema on
its own is not exported EVER, as it contains the datadictionary, which
is maintained automagically. You can look at the utlip.sql script in
$ORACLE_HOME/rdbms/admin to see them in action.
My bet is you won't be capable to make a valid export.
Your hope should be you have a valid ascii dump for your PL/SQL
floating around 'somewhere'
Otherwise you could administer a suicide pill to the database.

--
Sybrand Bakker
Senior Oracle DBA

I think the pill is probably the best idea - we have dumps of all the
data anyway so it's not too much of a problem. Not sure if it was a
typo (I supsect not) but I like the word "automagically" - very
appropriate. I've had a look at the script you mention - funny because
I ended up searching the entire $ORACLE_HOME directly and found no
reference to the table. Probably a Windows bug as usual! Script was
useful to help me gain an understanding so thank you.

Paul

On Thu, 05 Jul 2007 03:51:56 -0700, Paul wrote:

> Hi,
>
> We had some corrupt blocks in some of our data files. I have managed to
> sort these out. However, I used DBV to check all data files that were on
> the same disk and it came back with 1 corrupt page in the SYSTEM table
> space.
>
> The segment is called IDL_UB2$
>
> The DB concerned is a test DB with customer databases, therefore we do
> not back up the database. I could just export all the user data,
> recreate the instance and re-import, but if I can avoid this I would
> like to.
>
> I have looked in metalink but I cannot really find out what this table
> is, or what it is used for.
> Can anybody explain it to me, and tell me whether I can just use a
> script to rebuild the table?
>
> Thanks in advance!
>
> Paul

Paul, this table is used to store descriptors of various things
in PL/SQL procedures. The "UB2" stands for "Unsigned Byte -2", an
equivalent of the "unsigned short" declaration in C language. There
are also sb2 and sb4 tables. IDL stands for "Internal Data Loader".
That being said, I believe that the database recovery is your only
option.

--
http://www.mladen-gogala.com

Thanks - very nicely explained - I'd love to know where you found that
info though as I couldn't seem to find too much about it!

Anyway, I have managed to export all the schemas I need, so I'm just
going to trash the instance, replace the disk that appears to be on
its last legs and recreate the db.

Thanks to both of you!

On Jul 5, 5:56 pm, Mladen Gogala <mgogala.SPAM_ME....@verizon.net>
wrote:
> On Thu, 05 Jul 2007 03:51:56 -0700, Paul wrote:
> > Hi,
>
> > We had some corrupt blocks in some of our data files. I have managed to
> > sort these out. However, I used DBV to check all data files that were on
> > the same disk and it came back with 1 corrupt page in the SYSTEM table
> > space.
>
> > The segment is called IDL_UB2$
>
> > The DB concerned is a test DB with customer databases, therefore we do
> > not back up the database. I could just export all the user data,
> > recreate the instance and re-import, but if I can avoid this I would
> > like to.
>
> > I have looked in metalink but I cannot really find out what this table
> > is, or what it is used for.
> > Can anybody explain it to me, and tell me whether I can just use a
> > script to rebuild the table?
>
> > Thanks in advance!
>
> > Paul
>
> Paul, this table is used to store descriptors of various things
> in PL/SQL procedures. The "UB2" stands for "Unsigned Byte -2", an
> equivalent of the "unsigned short" declaration in C language. There
> are also sb2 and sb4 tables. IDL stands for "Internal Data Loader".
> That being said, I believe that the database recovery is your only
> option.
>
> --http://www.mladen-gogala.com

I believe in this context IDL stands for "Interface Definition
Language" as used in DIANA specification. Since IDL_* tables store
parsed DIANA, their content is expendable. Actually, utlip.sql script
happily deletes from these tables before recompiling the PL/SQL, which
will populate them anew.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Thanks Vladimir,

I did wonder whether the data in this table could be binned, and then
recreated by recompiling everything but I wasn't 100% certain and I
didn't want to risk it. I haven't actually got around to trashing the
instance yet, so I may experiment first to see what happens!

Paul

On Jul 6, 10:04 pm, Paul <paulwragg2...@hotmail.com> wrote:
> Thanks Vladimir,
>
> I did wonder whether the data in this table could be binned, and then
> recreated by recompiling everything but I wasn't 100% certain and I
> didn't want to risk it. I haven't actually got around to trashing the
> instance yet, so I may experiment first to see what happens!
>
> Paul

Well, a corrupt block is still a corrupt block. It may get a little
tricky to fix it: if you try to delete from it as utlip.sql does, you
will eventually hit the corrupt block and get an ORA-1578 or ORA-600,
depending on which sort of corruption it is - soft or hard. Now,
Oracle provides DBMS_REPAIR package, which allows you to set "skip
corrupt blocks" flag on a segment and then do a full scan of the
segment to copy all good data out of it skipping bad blocks; and the
package also allows to turn hard corruption into soft for the purpose
of skipping corrupt blocks with FIX_CORRUPT_BLOCKS procedure.

So you could try to set the skip flag on IDL_UB2$, CREATE TABLE
idl_ub2_copy AS SELECT * FROM idl_ub2$; TRUNCATE TABLE idl_ub2$ DROP
STORAGE hoping that the corrupt block is not in the first extent of
the segment; and then INSERT INTO idl_ub2$ SELECT * FROM idl_ub2_copy
followed by utlirp. If the block is in the first extent of the table,
you can try ALTER TABLE IDL_UB2$ MOVE with skip flag on and then
rebuild the index on the table, but I am not sure if skip flag is
honored by the move operation. You can try moving the table first and
see if this works. Remember that the index on the table will get
invalidated and will need to be rebuilt if the move indeed works.

Corrections and additions welcome.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Good explanation Vladimir. M. Zakharychey.