Could someone review my RMAN Duplicate Command Syntax?

I've been tasked with re-creating a test instance using the RMAN duplicate command, and I plan to try this out on Monday.

I'm using Oracle 9iR2 and Solaris v9.2.0.7.0. I'm a neophyte DBA with little actual Oracle experience at this point. However, I've taken the Backup and Recovery course. I've also read the Recovery Manager Quick Reference - a96564.pdf, Backup and Recovery Concepts - a96519.pdf, and I'm in the process of reading the Recovery Manager Users Guide - a96566.pdf.

Would you guys mind reviewing the script below and let me know: (1) if the syntax looks OK; (2) if there's anything that I'm forgetting.

Thanks. I really appreciate your help.

#
# Author:
# Script: rest_226_from_t.sh
# Date: August 17, 2007
#
# Purpose: Use this script to restore the TEST226 database from what has been backed up
# for MACTIVET using the last RMAN backup. Note: (1) Auxillary instance, TEST226,
# must be started with the NOMOUNT option; (2) Either Move disk copies and backups
# from the target host to the duplicate host and re-catalog them OR
# make sure that all backups and copies on the target host are remotely accessible
# from the duplicate host.
#
# Commands to specify group1 and group2 for the logfiles have been removed as the
# syntax below should do the following - If neither logfile nor LOG_FILE_NAME_CONVERT is specified,
# RMAN uses the original target redo log filenames for the duplicate files. You must
# use the nofilenamecheck option in this case. This according to information found at
# http://download-west.oracle.com/docs...sy24.htm#49769
#
# syntax for set newname command found in PDF version of Recovery Manager Reference on
# page 139 or 292 in PDF (RMAN Commands 2-113)
#
# TEMP file location / name changed to use correct one in MACTIVET.
su - oracle
cd /u01/app/oracle/admin/MPRTEST/scripts/RMAN
rman target / catalog radmin/change_on_install@rcat
'connect catalog radmin/radmin@rcat;
connect auxiliary sys/change_on_install@TEST226;
run {
allocate auxiliary channel test1 type 'disk';
allocate auxiliary channel test2 type 'disk';
allocate auxiliary channel test3 type 'disk';
allocate auxiliary channel test4 type 'disk';
set newname for system01 1 TO '/u02/oradata/TEST226/system01.dbf';
set newname for undotbs01 2 TO '/u03/oradata/TEST226/undotbs01.dbf';
set newname for ADBASE_01 3 TO '/u04/oradata/TEST226/ADBASE_01.dbf';
set newname for ADBASE_BI_01 4 TO '/u07/oradata/TEST226/ADBASE_BI_01.dbf';
set newname for ADBASE_BI_IDX_01 5 TO '/u05/oradata/TEST226/ADBASE_BI_IDX_01.dbf';
set newname for CWMLITE_01 6 TO '/u04/oradata/TEST226/CWMLITE_01.dbf';
set newname for indx01 7 TO '/u05/oradata/TEST226/indx01.dbf';
set newname for ODM_01 8 TO '/u07/oradata/TEST226/ODM_01.dbf';
set newname for PGL_01 9 TO '/u04/oradata/TEST226/PGL_01.dbf';
set newname for tools01 10 TO '/u07/oradata/TEST226/tools01.dbf';
set newname for users01 11 TO '/u04/oradata/TEST226/users_01.dbf';
set newname for ADBASE_BLOB_01 12 TO '/u07/oradata/TEST226/ADBASE_BLOB_01.dbf';
set newname for TEMP01.dbf 13 TO '/u06/oradata/TEST226/temp01.dbf';

duplicate target database to TEST226 nofilenamecheck;
}

Hi

Hmm from your script it sounds like duplicating rman database is painful.

I would rather suggest that you have the same filesystem (i assume you are cloning it for development) as the production that would make things easier
rather then hard coding those path names

Lookup in the manuals esp for these two parameters
db_file_name_convert
log_file_name_convert

so you dont have to hard code so many paths in your script

regards
Hrishy

Thanks hrishy for the review. Unfortunately, the two test instances have already been installed with the paths detailed above so I'm going to have to work with what I have.

When I finally get the OK to do this, I'll post back and let you know how it goes.

P.S. Thanks for the hint about using db_filename_convert and logfile_name_convert. I'll looking them up now in the 'Recovery Manager Users Guide' - a96566.pdf

Since my last post I've broken up the script into two parts:

rest_226_from_t.sh

cd /u01/app/oracle/admin/MPRTEST/scripts/RMAN
pwd
export ORACLE_SID=MPRTEST
env
rman target / catalog radmin/change_on_install@rcat CMDFILE rest_226_from_t.res LOG duptest.log
echo "Done!"

rest_226_from_t.res

connect auxiliary sys/change_on_install@TEST226;
run {
set newname for datafile '/u02/oradata/MPRTEST/system01.dbf' TO '/u02/oradata/TEST226/system01.dbf';
set newname for datafile '/u03/oradata/MPRTEST/undotbs01.dbf' TO '/u03/oradata/TEST226/undotbs01.dbf';
set newname for datafile '/u04/oradata/MPRTEST/ADBASE_01.dbf' TO '/u04/oradata/TEST226/ADBASE_01.dbf';
set newname for datafile '/u07/oradata/MPRTEST/ADBASE_BI_01.dbf' TO '/u07/oradata/TEST226/ADBASE_BI_01.dbf';
set newname for datafile '/u05/oradata/MPRTEST/ADBASE_BI_IDX_01.dbf' TO '/u05/oradata/TEST226/ADBASE_BI_IDX_01.dbf';
set newname for datafile '/u04/oradata/MPRTEST/CWMLITE_01.dbf' TO '/u04/oradata/TEST226/CWMLITE_01.dbf';
set newname for datafile '/u05/oradata/MPRTEST/indx01.dbf' TO '/u05/oradata/TEST226/indx01.dbf';
set newname for datafile '/u07/oradata/MPRTEST/ODM_01.dbf' TO '/u07/oradata/TEST226/ODM_01.dbf';
set newname for datafile '/u04/oradata/MPRTEST/PGL_01.dbf' TO '/u04/oradata/TEST226/PGL_01.dbf';
set newname for datafile '/u07/oradata/MPRTEST/tools01.dbf' TO '/u07/oradata/TEST226/tools01.dbf';
set newname for datafile '/u04/oradata/MPRTEST/users_01.dbf' TO '/u04/oradata/TEST226/users_01.dbf';
set newname for datafile '/u07/oradata/MPRTEST/ADBASE_BLOB_01.dbf' TO '/u07/oradata/TEST226/ADBASE_BLOB_01.dbf';


duplicate target database to TEST226 nofilenamecheck;
}


TEST226 has been stated in NOMOUNT mode.

When I run it, I get the following error:

MACTIVET:/home/oracle>cat duptest.log

.
.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12523: TNS:listener could not fin
d instance appropriate for the client connection

Recovery Manager complete.

If I STARTUP the auxiliary database and it's mounted, then I get the error:

Starting Duplicate Db at 27-AUG-07
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/27/2007 15:18:30
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE
command

Recovery Manager complete.
MACTIVET:/home/oracle>


I don't get it because if the TEST226 instance is started up normally, then I can connect to it, query it, etc.

I'm stumped as to why I'm getting the listener error. Here are the contents of my listener.ora and tnsnames.ora on the server where both instances reside:

listener.ora
# LISTENER.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = MPRTEST)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = MPRTEST)
)

(SID_DESC =
(GLOBAL_DBNAME = TEST226)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = TEST226)
)
(SID_DESC =
(GLOBAL_DBNAME = RCAT)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = RCAT)
)
)
tnsnames.ora
# TNSNAMES.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVEP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

MPR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.204.89)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MPR)
)
)

MPRTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPRTEST)
)
)
RCAT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RCAT)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

TEST226 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST226)
)
)

MPRBI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MPRBI)
)
)

Hi

I had a look at this and in my opinion your initialization file should contain the db_file_name_convert parameter and you should reduce your coding

db_file_name_convert =
(
'/u02/oradata/MPRTEST' , '/u02/oradata/TEST226' ,
'/u03/oradata/MRPTEST' , '/u03/oradata/TEST226' ,
'/u04/oradata/MRPTEST' , '/u04/oradata/TEST226' ,
'/u05/oradata/MRPTEST' , '/u05/oradata/TEST226' ,
'/u07/oradata/MRPTEST' , '/u07/oradata/TEST226'
)


regarding the error
ORA-12523: TNS:listener could not find instance appropriate for the client connection

whats the output of

lsnrctl services

Are you able to connect to target and auxillary instances using sqlplus ?

sqlplus "sys/change_on_install@TEST226 as sysdba"

If you not then try changing the SID to service name in listener.ora and the reloading the listener.

The key thing is you should get the listener file working correctly and should be able to connect to both auxillary and source(Target database as oracle calls it) before you proceed with cloning

i.e both the following commands should work
sqlplus "sys/change_on_install@TEST226 as sysdba"
sqlplus "sys/change_on_install@MPRTEST as sysdba"

regards
Hrishy

Hi Hrishy,

The status displays as blocked (see below). When the instance is up and running it looks like the others. It's in a startup / nomount mode.

lsnMACTIVET:/home/oracle>lsnrctl status

LSNRCTL for Solaris: Version 9.2.0.7.0 - Production on 28-AUG-2007 10:47:43

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
Start Date 18-JUN-2006 09:21:31
Uptime 436 days 1 hr. 26 min. 11 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/oracle/product/9.2.0/network/admin/listener.o
ra
Listener Log File /u01/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACTIVET)(PORT=1521)))
Services Summary...
Service "MPRBI" has 1 instance(s).
Instance "MPRBI", status READY, has 1 handler(s) for this service...
Service "MPRTEST" has 2 instance(s).
Instance "MPRTEST", status UNKNOWN, has 1 handler(s) for this service...
Instance "MPRTEST", status READY, has 2 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 2 instance(s).
Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
Instance "RCAT", status READY, has 2 handler(s) for this service...
Service "TEST226" has 1 instance(s).
Instance "TEST226", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
MACTIVET:/home/oracle>

I can hit the target no problem with SQL Plus, but not the auxiliary:

SQL> connect sys/change_on_install@TEST226 as sysdba;
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection


SQL>

The entry in LISTENER.ORA is as follows:

(SID_DESC =
(GLOBAL_DBNAME = TEST226)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = TEST226)
)


The SID_NAME matches the SERVICE_NAME in TNSNAMES.ORA


TEST226 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST226)

I changed the entry in LISTENER.ORA to

SERVICE_NAME = TEST226

from

SID_NAME = TEST226

I'm still seeing a status of blocked. Thanks again for all of your help with this.

I logged a Metalink issue, followed the instructions reference in the article below, and this did the trick.

Article-ID: Note 313026.1
Title: Rman Offline Backup Fails with ORA-12519 or ORA-12523 Error


Basically, I changed it back to SERVICE_NAME back to SID_NAME = TEST226.
I then did a lsnctrl stop and a lsnctrl start

I can now connect to the nomount instance and I'll proceed on with the other part. Thanks.

Hi

If you look above in you listener.ora you already had static listener connection i am not sure what you changed now to make it work ?

regards
Hrishy

When it was all said and done, I really didn't change anything as I just changed SERVICE_NAME = TEST226 back to SID_NAME = TEST226. I think something had probably hosed up with the listener, and that's why the stop and start of the listener corrected the issue.