tspitr and corruption

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sun, 12 Feb 2006 08:16:34 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36016D98C1@CWYMSX04.Corp.Acxiom.net>

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 - 08:16:34 CST