Re: Urgent: System tablespace corruption

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

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



"Amol" <addprabha_at_my-deja.com> wrote in message
news:8qk8rg$rgb$1_at_nnrp1.deja.com...


> Oracle gurus,

>

> I have oracle 7.3.4 and the problem I am facing is block corruption in

> system tablespace. I cannot restore it from any of the backups as the

> corruption exists in all the backups of the DB. I did verify this using

> the tool "dbv". Please suggest if in any way the corruption can be

> removed without loss of any data.

>

> Can I use the export utility for the above problem? What would be the

> implication  if I change the parameter DB_BLOCK_CHECKSUM to false? Will

> it solve this problem? Also suggest how to find which table/index is

> corrupt from the system datafile. This is very urgent and your

> suggestions will be very much appreciated.

>

> Amol

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.