simple (?) recovery question

From: <grant.g.holyoake_at_centrelink.gov.au>
Date: Tue, 5 Sep 2000 11:40:32 +1100
Message-Id: <10609.116176@fatcity.com>

Hi,

Forgive my ignorance but I have only had a couple of months exposure to Oracle (Sybase background!):

We are using Oracle 8i (8.1.6.0.0) on Solaris 2.6.

Situation is that yesterday afternoon around 15:30 our server complete with Oracle instance shutdown without warning. Upon startup one of the (large) datafiles was identified as corrupt. Unfortunately we don't keep backups of our user datafiles only system datafiles.....the database is considered non-critical and the backup routines were impeding batch processes....although I'm sure that situation will change from today ;-) The database is in ARCHIVELOG mode.

Anyhow, I took the offending datafile offline (used immediate option) and opened the database to enable our developers to continue with their processing. A dbverify of the datafile gave me very little insight into the cause of corruption let alone how to fix it....are there any integrity/consistency checks/fixes that can be performed on corrupt datafiles such as the dbcc commands used by Sybase (dare I say that word again!!) even if they are offline? SQL Navigator was able to offer some useful log info and identified that many large inserts were occuring on one of the tables located on this tablespace at the time the server went down.

I then attempted to recover the datafile (set autorecovery on) recover datafile # and the following info was displayed:

ORA-00279: change 5741944 generated at 09/04/2000 15:25:04 needed for thread 1 ORA-00289: suggestion :
/data/oracle12/app/oracle/admin/ora3/arch/arch_1_72457.arc

ORA-00280: change 5741944 for thread 1 is in sequence #72457
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [46468570], [1], [72457],

[2], [16], [], []

I am assuming that the only options for recovery now are to recover the database up to the last consistent SCN or to rebuild the datafile from scratch (I do have a copy of the ddl) and to then manually copy all the data back in. Is this assumption correct or is/are there other options I should consider?? Received on Mon Sep 04 2000 - 19:40:32 CDT