Yeah we tried _allow_resetlogs_corruption but that didn't work. We will try the _allow_ready_only_corruption parameter though. From: Andrew Kerber [mailto:andrew.kerber@xxxxxxxxx] Sent: Thursday, August 09, 2012 9:30 AM To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx Cc: Jeff Chirco; oracle-l@xxxxxxxxxxxxx Subject: [QUARANTINE] Re: corrupt system tablespace - no backups Importance: Low I think the parameter is _allow_resetlogs_corruption. Of course Oracle says to do a complete export of the entire database when you do this. As I recall, you also need to do a create controlfile command and such, try googling the the parameter. On Thu, Aug 9, 2012 at 11:21 AM, Taylor, Chris David <ChrisDavid.Taylor@xxxxxxxxxxxxxxx<mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx>> wrote: _allow_read_only_corruption - This parameter allows the database to be opened even if it has corruption. This should only be used to export as much data from a corrupted database as is possible before re-creating a database. A database that has been opened in this manner should not be used in a normal manner, as it will not be supported. Chris Taylor "Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900) Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees. -----Original Message----- From: Taylor, Chris David Sent: Thursday, August 09, 2012 11:20 AM To: 'JChirco@xxxxxxxxxx<mailto:JChirco@xxxxxxxxxx>' Cc: 'oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>' Subject: RE: corrupt system tablespace - no backups Do you have access to Oracle Support? I think there is an hidden parameter to _allow_db_corruption (something to that effect) that will allow you to open the database. You can find the tables affected by the corrupt block - some tables aren't as "bad" as others if they are corrupted. Getting the database open and identifying where the corruption is will determine whether you want to take an additional export or not. (For example, I once had corruption in the ACCESS$ table which holds grant information I believe - exporting the other data was easy and we felt confident that no other data was impacted - and it was a very non-critical system - hence no hot backups, no archivelogs) Chris Taylor "Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900) Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> [mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On Behalf Of Jeff Chirco Sent: Thursday, August 09, 2012 11:13 AM To: oracle-l@xxxxxxxxxxxxx<mailto: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 id 12 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- //www.freelists.org/webpage/oracle-l