RE: tspitr and corruption followup
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Feb 2006 10:15:07 -0600
Thanks Mark - great explanation! My statement was just a shot in the
dark, made after not really sleeping for a few days during the restore
attempts (not that sleep would have made understand the problem any
better :0 ).
Yup, got the ORA-26040 error as well. I used DBMS_REPAIR to identify,
then skip all corrupted blocks, so that I could retrieve whatever
possible data from the table. I'm not sure if 98% of the table would
help, but I thought I'd at least get that before another 15hr attempt.
My problem with all this is the entire database env was "designed" from
the point of view of the developers. They wanted everything as easy as
possible for them to pound out code, then run for the hills. Almost all
tables are refreshed using CTAS, followed by a rename, with no attempt
to save off any privileges or indexes or any other supporting object on
the table. All 2.5TB of data is saved to one tablespace, the default
tablespace for the Oracle account they use. I'll save you from any
further ranting.
Given your explanation, I'll try to review their daily and weekend
refresh processes to see if I should adjust when the level 0, level 1,
and level a (archive only) backups occur.
Thanks again.
Dave
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
> -----Original Message-----
> From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
> Sent: Monday, February 13, 2006 9:54 AM
> To: Herring Dave - dherri; oracle-l@xxxxxxxxxxxxx
> Subject: 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@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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
