Re: Corrupted data blocks in tablespace SYSTEM
Kevin Bass wrote: > > I have attempted a full export and received the following message: > > [/u03] $ exp system/ full=y rows=y file=full.dmp > > Export: Release 8.0.5.0.0 - Production on Mon Dec 3 15:3:21 2001 > > (c) Copyright 1998 Oracle Corporation. All rights reserved. > > Connected to: Oracle8 Release 8.0.5.0.0 - Production > PL/SQL Release 8.0.5.0.0 - Production > Export done in US7ASCII character set and US7ASCII NCHAR character set > > About to export the entire database ... > . exporting tablespace definitions > . exporting profiles > . exporting user definitions > . exporting roles > . exporting resource costs > . exporting rollback segment definitions > . exporting database links > . exporting sequence numbers > . exporting directory aliases > . exporting foreign function library names > EXP-8: ORACLE error 1578 encountered > ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) > ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' > > Since the corruption resides in the SYSTEM tablespace, it seems that the > course of action that should be taken is to re-create the database. > > Kevin L. Bass > Database Administrator > Americal Corporation > (252) 762-2199 x2144 Unfortunately, it looks like it. But perhaps you can recover your data by exporting on a per something basis, 'something' being either owner or tables or whatever, just trying to circle around the problem. Which is why you should check dba_extents (somebody posted the query). It will tell you which table is screwed up, and you may be able to use an export function which does NOT export what is held in this table. You have good blocks around the bad one, the problem is to tread carefully around. Kind of minefield with a single mine in it. Of course no need to do this kind of thing if you have a reasonably recent backup and do not care too much about the transactions you might lose. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Corrupted data blocks in tablespace SYSTEM
Title: RE: Corrupted data blocks in tablespace SYSTEM Kevin, Try exporting your schema owners (code, tables, etc.) as a CYA. When you recreate the db, you can import each schema separately. A pain, but it will save you. Lisa -Original Message- From: Kevin Bass [SMTP:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 3:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: Corrupted data blocks in tablespace SYSTEM I have attempted a full export and received the following message: [/u03] $ exp system/ full=y rows=y file=full.dmp Export: Release 8.0.5.0.0 - Production on Mon Dec 3 15:3:21 2001 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting foreign function library names EXP-8: ORACLE error 1578 encountered ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' Since the corruption resides in the SYSTEM tablespace, it seems that the course of action that should be taken is to re-create the database. Kevin L. Bass Database Administrator Americal Corporation (252) 762-2199 x2144 -Original Message- Sent: Monday, December 03, 2001 2:21 PM To: Multiple recipients of list ORACLE-L > Kevin Bass wrote: > > I have just started a project and have encountered a database problem. > The previous DBA did not create any backup scripts or backup and > recovery plans. Is there a way to get around this problem without > re-creating the database? > > > When executing my script to get corrupted data block information , I > get the following: > ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) > ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' > Kevin, Check DBA_EXTENTS to see what kind of segment is corrupted. If it's a table or a cluster, you have lost. Otherwise there is a chance to recover the database. In any case try a full export. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax: +44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Bass INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Corrupted data blocks in tablespace SYSTEM
I have attempted a full export and received the following message: [/u03] $ exp system/ full=y rows=y file=full.dmp Export: Release 8.0.5.0.0 - Production on Mon Dec 3 15:3:21 2001 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting foreign function library names EXP-8: ORACLE error 1578 encountered ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' Since the corruption resides in the SYSTEM tablespace, it seems that the course of action that should be taken is to re-create the database. Kevin L. Bass Database Administrator Americal Corporation (252) 762-2199 x2144 -Original Message- Sent: Monday, December 03, 2001 2:21 PM To: Multiple recipients of list ORACLE-L > Kevin Bass wrote: > > I have just started a project and have encountered a database problem. > The previous DBA did not create any backup scripts or backup and > recovery plans. Is there a way to get around this problem without > re-creating the database? > > > When executing my script to get corrupted data block information , I > get the following: > ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) > ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' > Kevin, Check DBA_EXTENTS to see what kind of segment is corrupted. If it's a table or a cluster, you have lost. Otherwise there is a chance to recover the database. In any case try a full export. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Bass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Corrupted data blocks in tablespace SYSTEM
> Kevin Bass wrote: > > I have just started a project and have encountered a database problem. > The previous DBA did not create any backup scripts or backup and > recovery plans. Is there a way to get around this problem without > re-creating the database? > > > When executing my script to get corrupted data block information , I > get the following: > ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) > ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' > Kevin, Check DBA_EXTENTS to see what kind of segment is corrupted. If it's a table or a cluster, you have lost. Otherwise there is a chance to recover the database. In any case try a full export. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Corrupted data blocks in tablespace SYSTEM
Your error message indicates file #1 associated with the system tablespace and the trace file indicates the file #6. I would do the following 1. Check the OS error message for any hardware errors. 2. Find what objects are corrupted. To get this information use dba_extents and the given file and block#.Here is the script for the same: --- accept h_file_id prompt ' Enter file_id ==>' accept h_block_id prompt ' Enter block_id==>' set verify off column owner format A10 column segment_name format A20 column segment_type format A10 column hdrfile format column curfile format column curblk format column hdrblock format select owner, segment_name, segment_type, file_id,block_id from dba_extents where file_id = &&h_file_id and block_id <= &&h_block_id and block_id + blocks > &&h_block_id; set verify on 3. Since the errors are in two different files, check whether these files are in the same disk or controller.. 4. Decide the course of action depending upon the above outcome.. It is possible for the disk /controller to give back the bad data. If it is an hardware problem, correct the hardware problem and then try again. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Kevin Bass <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/03/01 11:50 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Corrupted data blocks in tablespace SYSTEM I have just started a project and have encountered a database problem. The previous DBA did not create any backup scripts or backup and recovery plans. Is there a way to get around this problem without re-creating the database? When executing my script to get corrupted data block information , I get the following: ORA-01578: ORACLE data block corrupted (file # 1, block # 2660) ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' Trace file information. Dump file /u01/app/oracle/admin/TIE/udump/tie_ora_11859.trc Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production ORACLE_HOME = /u01/app/oracle/product/8.0.5 System name: SunOS Node name: edi-01 Release: 5.7 Version: Generic_106541-17 Machine: sun4u Instance name: TIE Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 11859, image: oracleTIE *** SESSION ID:(13.50289) 2001.11.30.15.00.00.000 *** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952. Bad header found during buffer read Data in bad block - type:0. format:0. rdba:0x00090255 last change scn:0x.3c07e0da seq:0x0 flg:0xb1 consistancy value in tail 0x check value in block header: 0x10, check value not calculated spare1:0x0, spare2:0x0, spare2:0xd40 Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data *** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952. Bad header found during buffer read Data in bad block - type:0. format:0. rdba:0x00090255 last change scn:0x.3c07e0da seq:0x0 flg:0xb1 consistancy value in tail 0x check value in block header: 0x10, check value not calculated spare1:0x0, spare2:0x0, spare2:0xd40 Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data *** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952. Bad header found during buffer read Data in bad block - type:0. format:0. rdba:0x00090255 last change scn:0x.3c07e0da seq:0x0 flg:0xb1 consistancy value in tail 0x check value in block header: 0x10, check value not calculated spare1:0x0, spare2:0x0, spare2:0xd40 Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data *** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952. Bad header found during buffer read Data in bad block - type:0. format:0. rdba:0x00090255 last change scn:0x.3c07e0da seq:0x0 flg:0xb1 consistancy value in tail 0x check value in block header: 0x10, check value not calculated spare1:0x0, spare2:0x0, spare2:0xd40 Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data *** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952. Bad header found during buffer read Data in bad block - type:0. format:0. rdba:0x00090255 last change scn:0x.3c07e0da seq:0x0 flg:0xb1 consistancy value in tail 0x check value in block header: 0x10, check value not calculated spare1:0x0, spare2:0x0, spare2:0xd40 Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data Kevin
Corrupted data blocks in tablespace SYSTEM
I have just started a project and have encountered a database problem. The previous DBA did not create any backup scripts or backup and recovery plans. Is there a way to get around this problem without re-creating the database? When executing my script to get corrupted data block information , I get the following: ORA-01578: ORACLE data block corrupted (file # 1, block # 2660)ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf' Trace file information. Dump file /u01/app/oracle/admin/TIE/udump/tie_ora_11859.trcOracle8 Release 8.0.5.0.0 - ProductionPL/SQL Release 8.0.5.0.0 - ProductionORACLE_HOME = /u01/app/oracle/product/8.0.5System name: SunOSNode name: edi-01Release: 5.7Version: Generic_106541-17Machine: sun4uInstance name: TIERedo thread mounted by this instance: 1Oracle process number: 12Unix process pid: 11859, image: oracleTIE *** SESSION ID:(13.50289) 2001.11.30.15.00.00.000***Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.Bad header found during buffer readData in bad block - type:0. format:0. rdba:0x00090255last change scn:0x.3c07e0da seq:0x0 flg:0xb1consistancy value in tail 0xcheck value in block header: 0x10, check value not calculatedspare1:0x0, spare2:0x0, spare2:0xd40Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data*** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.Bad header found during buffer readData in bad block - type:0. format:0. rdba:0x00090255last change scn:0x.3c07e0da seq:0x0 flg:0xb1consistancy value in tail 0xcheck value in block header: 0x10, check value not calculatedspare1:0x0, spare2:0x0, spare2:0xd40Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data***Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.Bad header found during buffer readData in bad block - type:0. format:0. rdba:0x00090255last change scn:0x.3c07e0da seq:0x0 flg:0xb1consistancy value in tail 0xcheck value in block header: 0x10, check value not calculatedspare1:0x0, spare2:0x0, spare2:0xd40Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data***Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.Bad header found during buffer readData in bad block - type:0. format:0. rdba:0x00090255last change scn:0x.3c07e0da seq:0x0 flg:0xb1consistancy value in tail 0xcheck value in block header: 0x10, check value not calculatedspare1:0x0, spare2:0x0, spare2:0xd40Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data*** Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.Bad header found during buffer readData in bad block - type:0. format:0. rdba:0x00090255last change scn:0x.3c07e0da seq:0x0 flg:0xb1consistancy value in tail 0xcheck value in block header: 0x10, check value not calculatedspare1:0x0, spare2:0x0, spare2:0xd40Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data Kevin
