| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Urgent: System tablespace corruption
I have no user tables in the System tablespace, so I would like to know
way of re-create the data dictionary using the sql scripts catalog and
catproc.sql .Also as you rightly said I would not try untill I get it
confirmed from the Oracle support.
Waiting for the reply.
Regards,
Amol
In article <39cdc5c1_at_news.iprimus.com.au>,
"Howard J. Rogers" <howardjr_at_www.com> wrote:
> I seem to recall answering this same call for help earlier today. New
> message: same reply: you're stuffed.
>
> A couple of things to be said, however: as a good dba, you will not
have
> created any of your own tables in the system tablespace, will you??
Which
> means that only the data dictionary tables will have been affected by
the
> corruption, and they can theoretically be re-created by running
catalog and
> catproc.sql once again.... however, I've never tried re-creating the
data
> dictionary on a functioning database, and I don't suggest you try it
until
> someone else here (or, better, Oracle support) advises it.
>
> If you *do* have your own tables in the system tablespace, you're in
> trouble... and hopefully you've learnt one of the reasons why that is
> strongly dis-advised.
>
> Export won't help: in order to do an export, it needs to read the
data -and
> if the data has been corrupted, what do you imagine it will be able
to read?
> (Incidentally, you can't export anything in SYS's schema, either -and
that
> rather rules out exporting the data dictionary tables in the first
place).
>
> DB_BLOCK_CHECKSUM won't help. I hope to God you haven't already had
it set
> to TRUE for long, since that's a performance-crippler if ever I saw
one. It
> means DBWR calculates a checksum for ever block it's about to write to
> disk -it's designed to help you detect corruption (and presumably in
this
> case it has) before it gets very far, not to fix it up once it's
happened.
>
> Presumably you know the file and block number where corruption has
been
> detected? In that case, use SQL*Plus to run this script and enter
using the
> appropriate information when requested:
>
> connect system/manager;
> COL segment_name FORMAT A30
> COL segment_type FORMAT A15
>
> SELECT segment_name,segment_type
> FROM dba_extents
> WHERE file_id=&1
> AND &2 BETWEEN block_id AND (block_id+blocks-1)
> /
>
> (or just replace '&1' with the relevant file number and '&2' with the
the
> relevant block number). That will give you the affected segment
name, and
> you can make decisions from there.
>
> If, as I hope, you've only got data dictionary tables in the system
> tablespace, and if they're the only thing corrupted, you might be
better off
> doing a full database export, creating a brand new database, and
doing a
> full database import.
>
> You might then prevent future woes by using dbverify on a regular
basis on
> your backups (thoroughly recommended), and having an archive log
ageing
> process that keeps stacks of them around for as long as physically
possible,
> gracefully ageing them out onto tape when necessity dictates, and
keeping
> those tapes around for a good long time (I've used a tape rotation
that can
> recover stuff from six months ago). Oh, and make sure that the system
> tablespace is never used to store real user data ever again!
>
> Hope you sort it out
>
> Regards
> HJR
> --
> ----------------------------------------------------------------------
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sun Sep 24 2000 - 07:08:20 CDT
 |
 |