RE: tspitr and corruption followup

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave -
dherri
Sent: Sunday, February 12, 2006 7:52 PM
To: oracle-l_at_freelists.org
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_at_acxiom.com>
-------------------------------------
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Herring Dave - dherri
> Sent: Sunday, February 12, 2006 8:17 AM
> To: oracle-l_at_freelists.org
> 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_at_acxiom.com>
> -------------------------------------
************************************************************************
***
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.
************************************************************************
****
--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l