Re: corrupt system tablespace - no backups

Hi all,

Looking at the alert log it seems instance recovery was finished and no redo 
needed to
be applied.
The error(s) occur during bootstrapping of the instance.
So the '_allow_..redo...corruption' type parameters won't work.
You could have a look at that corrupt block - dump it using another 
'working' database instance :
- copy system datafile to another server running oracle
- in other oracle instance as SYS
alter system dump datafile 'D:\DATA\ORACLE\ORADATA\XE\SYSTEM.DBF' block 39 ;
It's not going to help you - just to see what's in the block and to possible 
determine if it's
physical or logical corruption...

cheers,
Kurt




-----Original Message----- 
From: Mark W. Farnham
Sent: Thursday, August 09, 2012 7:04 PM
To: JChirco@xxxxxxxxxx ; oracle-l@xxxxxxxxxxxxx
Subject: RE: corrupt system tablespace - no backups

Typically the first object in file#1 is the system rollback segment
(typically starting at block 128).

So you're in internal block metadata land. Likely if that is trashed that is
not the only problem you have, and anything you do to get the database up
and limping would be part of a recovery operation to get the data into an
undamaged database.

Sometimes it is possible to use a binary editor to patch the contents of a
block to match what Oracle is expecting. That was effective, for example in
the old hot backup days when the machine crashed during hot backup and there
was no end backup command. You wanted to resume from the original image, but
it was marked fuzzy (so that the backup image would also be marked fuzzy,
not because the original was fuzzy.) In that narrow case, editing the binary
original image of the file to make it not fuzzy was a reliable fix to
proceed. (That is actually what the "end backup command" does, and of course
the manual patch is not supported, because how could Oracle rely on an
external party to get that correct!)

So I suppose it is possible that just that one block experienced a spastic
write or a missed write on crash and that if you patch so the header and
tail consistency match you *might* be able to get up and limping. But even
if everything seems to work at that point, you're not supported and there
very well could be many not immediately apparent problems.

mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jeff Chirco
Sent: Thursday, August 09, 2012 12:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: corrupt system tablespace - no backups

Ok so a colleague of mine manages a large number of Oracle 10g XE databases
and one of them is failing to open this morning because of a corrupt SYSTEM
tablespace.  And unfortunately the backups for this database are gone as
well.  We do have some full database exports from a couple weeks ago so it
is not a complete loss.  Does anybody know of maybe some undocumented
parameter or some way to recover from this database?  Below is the alert
log.


ALTER DATABASE   MOUNT
Thu Aug 09 08:49:28 2012
Setting recovery target incarnation to 2 Thu Aug 09 08:49:28 2012 Successful
mount of redo thread 1, with mount id 2672004948 Thu Aug 09 08:49:28 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Aug 09 08:49:50 2012
alter database open upgrade
Thu Aug 09 08:49:50 2012
Beginning crash recovery of 1 threads
Thu Aug 09 08:49:50 2012
Started redo scan
Thu Aug 09 08:49:50 2012
Completed redo scan
0 redo blocks read, 0 data blocks need recovery Thu Aug 09 08:49:50 2012
Started redo application at Thread 1: logseq 84298, block 3, scn 10745981851
Thu Aug 09 08:49:50 2012 Recovery of Online Redo Log: Thread 1 Group 4 Seq
84298 Reading mem 0
  Mem# 0 errs 0: D:\DATA\ORACLE\ORADATA\XE\REDO04A.LOG
  Mem# 1 errs 0: D:\DATA\ORACLE\ORADATA\XE\REDO04B.LOG
Thu Aug 09 08:49:50 2012
Completed redo application
Thu Aug 09 08:49:50 2012
Completed crash recovery at
Thread 1: logseq 84298, block 3, scn 10746001852
0 data blocks read, 0 data blocks written, 0 redo blocks read Thu Aug 09
08:49:51 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid, OS id%24
Thu Aug 09 08:49:51 2012
ARC0: Archival started
ARC1 started with pid , OS id)56
Thu Aug 09 08:49:52 2012
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thread 1 advanced to log sequence 84299
Thread 1 opened at log sequence 84299
  Current log# 2 seq# 84299 mem# 0: D:\DATA\ORACLE\ORADATA\XE\REDO02A.LOG
  Current log# 2 seq# 84299 mem# 1: D:\DATA\ORACLE\ORADATA\XE\REDO02B.LOG
Successful open of redo thread 1
Thu Aug 09 08:49:52 2012
ARC1: STARTING ARCH PROCESSES
Thu Aug 09 08:49:52 2012
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Aug 09 08:49:52 2012
SMON: enabling cache recovery
Thu Aug 09 08:49:52 2012
ARC2: Archival started
Thu Aug 09 08:49:52 2012
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid!, OS id916
Thu Aug 09 08:49:54 2012
Successfully onlined Undo Tablespace 1.
Thu Aug 09 08:49:54 2012
SMON: enabling tx recovery
Thu Aug 09 08:49:54 2012
Database Characterset is WE8MSWIN1252
Thu Aug 09 08:49:56 2012
Hex dump of (file 1, block 39) in trace file
c:\oraclexe\app\oracle\admin\xe\bdump\xe_smon_876.trc
Corrupt block relative dba: 0x00400027 (file 1, block 39) Bad check value
found during buffer read Data in bad block:
type: 6 format: 2 rdba: 0x00400027
last change scn: 0x0002.7fee0b69 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x0b690601
check value in block header: 0x8f6c
computed block checksum: 0x1000
Reread of rdba: 0x00400027 (file 1, block 39) found same corrupted data Thu
Aug 09 08:49:56 2012 Stopping background process MMNL Thu Aug 09 08:49:56
2012 Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_smon_876.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 39)
ORA-01110: data file 1: 'D:\DATA\ORACLE\ORADATA\XE\SYSTEM.DBF'

Thu Aug 09 08:49:57 2012
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
MMON started with pid, OS id12
Thu Aug 09 08:49:58 2012
ALTER SYSTEM enable restricted session;
MMNL started with pid, OS id584
Thu Aug 09 08:49:58 2012
ALTER SYSTEM SET _system_trig_enabledúLSE SCOPE=MEMORY; Thu Aug 09 08:49:58
2012 ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; Thu Aug 09 08:49:58
2012 Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_324.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 39)
ORA-01110: data file 1: 'D:\DATA\ORACLE\ORADATA\XE\SYSTEM.DBF'

Thu Aug 09 08:49:58 2012
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604 Thu Aug 09 08:49:59 2012 Errors
in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_pmon_3120.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:49:59 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_psp0_3444.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:49:59 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_mman_2592.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:49:59 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_dbw0_932.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:49:59 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_ckpt_1156.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:50:00 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_lgwr_1508.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:50:00 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_reco_2700.trc:
ORA-00604: error occurred at recursive SQL level

Thu Aug 09 08:50:00 2012
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_smon_876.trc:
ORA-00604: error occurred at recursive SQL level

Jeff

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l


Other related posts: