ORA-01157 strangeness after RMAN duplicate with ASM

From: Don Granaman <DonGranaman_at_solutionary.com>
Date: Tue, 24 Jan 2012 12:06:14 -0600
Message-ID: <FD98CB0EE75EEA438CAF4DA2E6071C42107F9FE048_at_MAIL.solutionary.com>

Twice in the last six weeks I have seen this strangeness after doing an RMAN duplicate with a skip tablespace clause (e.g. ... skip tablespace QRTZ_DAT,SUMM_DAT,LOGA_IDX,ROLL_DAT,MAUD_DAT,MAUD_IDX,HOST_DAT,HOST_IDX;). Sometimes it works and sometimes it does this:

[...]
# drop offline and skipped tablespaces
sql clone "drop tablespace SUMM_DAT including contents cascade constraints"; # drop offline and skipped tablespaces
sql clone "drop tablespace ROLL_DAT including contents cascade constraints"; }
executing Memory Script

sql statement: drop tablespace SUMM_DAT including contents cascade constraints

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/24/2012 03:25:45
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of sql command on clone_default channel at 01/24/2012 03:25:45 RMAN-11003: failure during parse/execution of SQL statement: drop tablespace SUMM_DAT including contents cascade constraints

ORA-00604: error occurred at recursive SQL level 2
ORA-01157: cannot identify/lock data file 501 - see DBWR trace file
ORA-01110: data file 501: '+TESTDG'

'TESTDG' is the ASM disk group name on the "cloned to" server. If I manually try to drop the tablespacs, I see that they all fail on this same "datafile" with file#=501, even though no file with this name or number exists - in v$datafile or DBA_DATA_FILES.

SQL> drop tablespace SUMM_DAT including contents and datafiles; drop tablespace SUMM_DAT including contents and datafiles
*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2
ORA-01157: cannot identify/lock data file 501 - see DBWR trace file
ORA-01110: data file 501: '+TESTDG'
ORA-06512: at line 34

SQL> drop tablespace ROLL_DAT including contents and datafiles; drop tablespace ROLL_DAT including contents and datafiles
*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2
ORA-01157: cannot identify/lock data file 501 - see DBWR trace file
ORA-01110: data file 501: '+TESTDG'
ORA-06512: at line 34

This same rman script (with a shorter list of skipped tablespaces) has run without problems for years. DB_FILE_NAME_CONVERT and other such parameters haven't changed in years and are set correctly. All the non-skipped tablespace datafiles are OK. This occurred first about a monmths ago. At that time, I just chahnged the until time, wiped out the clone dataabase and rtan it again - and it worked.

I am wondering if (a) anyone knows what might cause this and (b) is there another way to "fix" it other than just roll the dice again?

Thanks for any insights!

Don Granaman - OraSaurus (and admitting it ...again)

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

Received on Tue Jan 24 2012 - 12:06:14 CST