RMAN DUPLICATE command stopped working

DUPLICATE command which used to work now not working - missing SPFILE

We have a production database, on which a DBA who has left us has created a working setup of backups, and on-demand duplication to target databases on other DB servers.
Production Database & Server:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Linux <hostname> 2.6.18-308.1.1.0.1.el5 #1 SMP Wed Mar 7 11:39:17 EST 2012 x86_64 x86_64 x86_64 GNU/Linux



job defined on the OEM
==========================
the shell script run from the OEM executes this on the target Database server:

rman AUXILIARY SYS/${SYSTEM_PASSWD}@<target-DB-name> LOG /tmp/<target-DB-name>_refresh_rman.log @/home/oracle/admin/refresh_db/<target-DB-name>/<target-DB-name>_refresh.rman

<target-DB-name>_refresh.rman file contents:
==================================================
run {

Allocate auxiliary channel d1 DEVICE TYPE disk;
Allocate auxiliary channel d2 DEVICE TYPE disk;
Allocate auxiliary channel d3 DEVICE TYPE disk;
Allocate auxiliary channel d4 DEVICE TYPE disk;

DUPLICATE TARGET DATABASE TO <target-DB-name> BACKUP LOCATION '/oracle/BACKUP/<prod-DB-name>'
DB_FILE_NAME_CONVERT '/oracle/oradata/<prod-DB-SID>','/oracle/oradata/<target-DB-name>','/oracle/oradata/<prod-DB-name>','/oracle/oradata/<target-DB-name>','/oracle/archive/<prod-DB-SID>','/oracle/archive/<target-DB-name>','/oracle/archive/<prod-DB-name>','/oracle/archive/<target-DB-name>','/oracle/software/admin/<prod-DB-SID>','/oracle/software/admin/<target-DB-name>','/oracle/software/admin/<prod-DB-name>','/oracle/software/admin/<target-DB-name>','/oracle/software/diag/rdbms/swprod_p','/oracle/software/diag/rdbms/swdev','/oracle/software/diag/rdbms/swprod_p/<prod-DB-name>','/oracle/software/diag/rdbms/swdev/<target-DB-name>','/oracle/software/diag/rdbms/swprod','/oracle/software/diag/rdbms/swdev','/oracle/software/diag/rdbms/swprod/<prod-DB-name>','/oracle/software/diag/rdbms/swdev/<target-DB-name>'
SPFILE
    PARAMETER_VALUE_CONVERT '/oracle/oradata/<prod-DB-SID>','/oracle/oradata/<target-DB-name>','/oracle/oradata/<prod-DB-name>','/oracle/oradata/<target-DB-name>','/oracle/archive/<prod-DB-SID>','/oracle/archive/<target-DB-name>','/oracle/archive/<prod-DB-name>','/oracle/archive/<target-DB-name>','/oracle/software/admin/<prod-DB-SID>','/oracle/software/admin/<target-DB-name>','/oracle/software/admin/<prod-DB-name>','/oracle/software/admin/<target-DB-name>','/oracle/software/diag/rdbms/swprod_p','/oracle/software/diag/rdbms/swdev','/oracle/software/diag/rdbms/swprod_p/<prod-DB-name>','/oracle/software/diag/rdbms/swdev/<target-DB-name>','/oracle/software/diag/rdbms/swprod','/oracle/software/diag/rdbms/swdev','/oracle/software/diag/rdbms/swprod/<prod-DB-name>','/oracle/software/diag/rdbms/swdev/<target-DB-name>'
    SET LOG_FILE_NAME_CONVERT '/oracle/oradata/<prod-DB-SID>','/oracle/oradata/<target-DB-name>'
    SET SGA_MAX_SIZE '3000M'
    SET MEMORY_TARGET '3000M'
    SET memory_max_target '3000M'
    SET dg_broker_start 'false'
    SET dispatchers '(PROTOCOL=TCP)'
    SET open_cursors '300'
    SET log_archive_config ''
    SET log_archive_dest_1 ''
    SET log_archive_dest_2 ''
    SET log_archive_dest_3 ''
    SET service_names = '<target-DB-name>'
NOFILENAMECHECK;

}
exit;



log file showsd this:
===========================
allocated channel: d1
channel d1: SID=2271 device type=DISK

allocated channel: d2
channel d2: SID=2647 device type=DISK

allocated channel: d3
channel d3: SID=3 device type=DISK

allocated channel: d4
channel d4: SID=381 device type=DISK

Starting Duplicate Db at 03-OCT-13
released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/03/2013 20:09:44
RMAN-05501: aborting duplication of target database
RMAN-05569: SPFILE backup not found in /oracle/BACKUP/SWPROD

Recovery Manager complete.






backup settings on prod database
==============================
all done thru the OEM, attaching screen shots of setup and status, showing SPFILE is created and exists in the backup sets.

manage current backups - run at midnight, retain 3
contents of backup set for spfile
backup setting policy, include SPFILE
LVL 1
structuredwebAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
did you checked that the backup exists in the location i.e /oracle/BACKUP/SWPROD/ , as the error clearly states that the backup dosen't exist in the specified location.

http://rman-05569.db.quanti.cz/

also check the below document for cloning the DB from source to target using OEM.

http://oracleinout.blogspot.in/2010/04/oracle-enterprise-manager-11g-step-by.html

http://www.dba-oracle.com/t_rman_84_cloning_oem.htm
0
structuredwebAuthor Commented:
praveencpk,

thanks for tyou answer.

1) The backup exists - you can see in one of the screenshot which the OEM listing the current backups, all are listed in being good.  I verified that all the files referred to as the backup sets physically exist on the folder. I also checked the last 3 backup logs, all is good.

what worries me, if you look at then screen shot where is lists the content of the backup set with the controlfile and the SPFILE, it lists the spfile as having size in bytes of only 2 byes!. Is this normal? is there a possibility that the spfile is somewhat corrupted so the duplicate command cannot read it?

how can I verify if my current SPFILE is OK?

failing that, what is the most fool-proof way of generating an new SPFILE? is there any way to avoid a bounce of the Database?
0
hinpongCommented:
in sqlplus do a create spfile from pfile;
0
structuredwebAuthor Commented:
I found the issue -

the first time the refresh failed it was because of the network slow speed, the backup were on a remote file system.

for subsequent refresh i created a brand new pfile/spfile but even that did not help.

finally - i realized i had to tell RMAN not to reuse any partial backups, it was still trying to get the old backup set.
so, making brand new pfile and starting the DB from it, then the refresh with the NORESUME parameter - did the trick.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
structuredwebAuthor Commented:
the vital piece of information in making RMAN work after the failure was the NORESUME parameter,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.