Dave, Glad to hear you got (most of) your data back! Not sure what you meant by "It was listed as NOLOGGING when backed up, so my assumption is RMAN tried to restore it that way, and when an error occurred it caused corruption." Tables which have data blocks loaded w/ NOLOGGING are subject to corruption in the event that the table is restored from a backup taken *before* the NOLOGGING load. This is because, if you start at a point in time before the data load, then when you roll forward through the NOLOGGING operation, there is no data in the redo log to write into the datablocks. Instead, there are extent invalidation records, which will mark those datablocks as corrupt. Any subsequent attempt to access data in those blocks will result in: SQL> !oerr ora 1578 01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)" // *Cause: The data block indicated was corrupted, mostly due to software // errors. // *Action: Try to restore the segment containing the block indicated. This // may involve dropping the segment and recreating it. If there // is a trace file, report the errors in it to your ORACLE // representative. Which is Oracle's generic data block corruption error, as well as the more specific: SQL> !oerr ora 26040 26040, 00000, "Data block was loaded using the NOLOGGING option\n" //* Cause: Trying to access data in block that was loaded without //* redo generation using the NOLOGGING/UNRECOVERABLE option //* Action: Drop the object containing the block. Which tells you that the datablocks are corrupt due to recovery through a NOLOGGING operation. This is why it's recommended, after a NOLOGGING dataload, to take a backup of at least the tablespace the object resides in. Did you see the ORA-26040 error? If the corruption was due to NOLOGGING, the ORA-26040 should have been raised. Also, if it was due to NOLOGGING, the data would simply not be available. In that case, I don't see how DBMS_REPAIR could possibly help you. It can't repair, if there is no data to repair.... -Mark -- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning "There are 10 types of people in the world: Those who understand binary, and those who don't." -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Herring Dave - dherri Sent: Sunday, February 12, 2006 7:52 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: tspitr and corruption followup FYI, I used DBMS_REPAIR to identify all corrupt blocks in the segment, then attempt to fix or skip them entirely. From this I was able to retrieve 77K+ blocks of data, skipping 110. This is good enough for now. One thing I will look into is all tables marked as NOLOGGING. It appears at some point an error occurred during the restore of this table. It was listed as NOLOGGING when backed up, so my assumption is RMAN tried to restore it that way, and when an error occurred it caused corruption. That's my uneducated guess at least. I'm not sure what that buys me, but I'm trying to gain someone from this experience, possibly adjusting the production env. just a bit to limit the impact when restoring. Dave ------------------------------------- Dave Herring, DBA Acxiom Corporation 3333 Finley Downers Grove, IL 60515 wk: 630.944.4762 <mailto:dherri@xxxxxxxxxx> ------------------------------------- > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Herring Dave - dherri > Sent: Sunday, February 12, 2006 8:17 AM > To: oracle-l@xxxxxxxxxxxxx > Subject: tspitr and corruption > > Okay, I need some serious help here. I've got a need to restore 1 table > from a large tablespace (2.5TB) back on Friday. The database is Oracle > 9.2.0.6 with archiving on and backed up weekly level 0 with RMAN and the > 6 other days level 1. I just tried to create a duplicate database > restoring just a subset of tablespaces - the one in question, SYSTEM, > USERS, UNDOTBS1, TOOLS, skipping all others. This died at the very end > with: > > RMAN-00571: =========================================================== > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== > RMAN-00571: =========================================================== > RMAN-03002: failure of Duplicate Db command at 02/12/2006 07:50:31 > RMAN-03015: error occurred in stored script Memory Script > RMAN-03009: failure of sql command on clone_default channel at > 02/12/2006 07:50:31 > RMAN-11003: failure during parse/execution of SQL statement: drop > tablespace PROD_MASTER_ATOMIC_TBS including contents > ORA-00604: error occurred at recursive SQL level 1 > ORA-12083: must use DROP MATERIALIZED VIEW to drop > "PROD_MASTER"."MODEL_SCORE_VW" > > I really don't care about this tablespace, as I asked RMAN to skip it, > hence the drop attempt by RMAN. > > The table I want is in tablespace PROD_ATOMIC_TBS. When querying it I > get: > > select count(*) from prod.in_data_tb > * > ERROR at line 1: > ORA-12801: error signaled in parallel query server P001 > ORA-01578: ORACLE data block corrupted (file # 64, block # 811191) > ORA-26040: Data block was loaded using the NOLOGGING option > ORA-01110: data file 64: > '/appdata/oradata/p_tspitr/prod_atomic_tbs19.dbf' > > I assume this is due to RMAN not completing. If so, are there any > options to skip around RMAN's failure dropping PROD_MASTER_ATOMIC_TBS > and get it to recover PROD_ATOMIC_TBS? > > Thanks in advance for your help. I'd really like to avoid another 15+hr > process. > > Dave > ------------------------------------- > Dave Herring, DBA > Acxiom Corporation > 3333 Finley > Downers Grove, IL 60515 > wk: 630.944.4762 > <mailto:dherri@xxxxxxxxxx> > ------------------------------------- ************************************************************************ *** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ************************************************************************ **** -- //www.freelists.org/webpage/oracle-l
BEGIN:VCARD VERSION:2.1 N:Bobak;Mark FN:Bobak, Mark ORG:ProQuest Information and Learning;Database Group TITLE:Software Architect, Sr-TPD TEL;WORK;VOICE:734.975.6086 ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx REV:20051213T223654Z END:VCARD