corrupt system tablespace - no backups

From: Jeff Chirco <JChirco_at_innout.com>
Date: Thu, 9 Aug 2012 16:12:51 +0000
Message-ID: <6D9F00643B733E489E419CB485C9662733307A_at_IRVMBX01.innout.corp>



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
Received on Thu Aug 09 2012 - 11:12:51 CDT

Original text of this message