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
Received on Sun Feb 12 2006 - 18:51:48 CST
