asked on

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.

Praveen Kumar Chandrashekatr

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

structuredweb

ASKER

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?

SOLUTION

hinpong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

structuredweb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

structuredweb

ASKER

the vital piece of information in making RMAN work after the failure was the NORESUME parameter,