Re: corrupt system tablespace - no backups

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_at_innout.com ; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeff Chirco
Sent: Thursday, August 09, 2012 12:13 PM To: oracle-l_at_freelists.org
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 Received on Fri Aug 10 2012 - 01:27:31 CDT