RE: corrupt system tablespace - no backups
Date: Thu, 9 Aug 2012 11:21:04 -0500
Message-ID: <C5533BD628A9524496D63801704AE56D75B2FD27CC_at_SPOBMEXC14.adprod.directory>
_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_at_innout.com'
Cc: 'oracle-l_at_freelists.org'
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeff Chirco
Sent: Thursday, August 09, 2012 11:13 AM
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-lReceived on Thu Aug 09 2012 - 11:21:04 CDT