Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: tspitr and corruption followup

RE: tspitr and corruption followup

From: Herring Dave - dherri <>
Date: Sun, 12 Feb 2006 18:51:48 -0600
Message-ID: <>

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 Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762

> -----Original Message-----
> From:

> On Behalf Of Herring Dave - dherri
> Sent: Sunday, February 12, 2006 8:17 AM
> To:
> Subject: tspitr and corruption
> Okay, I need some serious help here. I've got a need to restore 1
> from a large tablespace (2.5TB) back on Friday. The database is
> with archiving on and backed up weekly level 0 with RMAN and
> 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
> 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
> 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
> process.
> Dave
> -------------------------------------
> Dave Herring, DBA
> Acxiom Corporation
> 3333 Finley
> Downers Grove, IL 60515
> wk: 630.944.4762
> <>
> -------------------------------------

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.

Received on Sun Feb 12 2006 - 18:51:48 CST

Original text of this message