RE: ORA-01157 strangeness after RMAN duplicate with ASM

Don,

My guess is you have db_files=500.  Therefore, 501 is the first tempfile.

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Don Granaman
Sent: Tuesday, January 24, 2012 1:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA-01157 strangeness after RMAN duplicate with ASM

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




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


Other related posts: