Hello experts I need your expert help to solve the following errors.
RMAN Duplicate Database Errors:
RMAN-04006: error from auxiliary database:
ORA-12514: TNS: listener does not currently know of service requested in connect descriptor
PRIMARY SERVER CONFIGURATION
Server = testnode1.idevelopment.info (192.168.1.106) RHEL 5 64 bit Oracle 11g r2 Enterprise
Database = temp
Password File = /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtemp
TNSNAMES.ora from primary server.
TEMP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.idevelopment.info)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = temp.idevelopment.info)
)
)
TEMPAUX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=testnode2.idevelopment.info)(PORT=1521))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = tempaux.idevelopment.info)
)
)
AUXILIARY SERVER CONFIGURATION
Server =testnode2.idevelopment.info (192.168.1.107) RHEL 5 64 Bit Oracle 11g r2 Enterprise
Auxiliary Instance = tempaux
Password File = /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtempaux
Listener.ora on Auxiliary Server
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = testnode2.idevelopment.info)(PORT = 1521))
)
)
SID_LIST_LISTENER
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME = tempaux.idevelopment.info)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = tempaux)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
TEMPAUX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=testnode2.idevelopment.info)(PORT = 1521))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = tempaux.idevelopment.info)
)
)
TEMP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.idevelopment.info)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = temp.idevelopment.info)
)
)
Created a pfile and a password file for auxiliary instance. Created required directories on Auxiliary Server.
I am able to do tnsping from both the sides
From primary side
[oracle@testnode1 ~]$ tnsping tempaux
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-JUN-2017 19:09:00
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/grid/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=testnode2.idevelopment.info)(PORT=1521)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME = tempaux.idevelopment.info)))
OK (0 msec)
From Auxiliary side
[oracle@testnode2 ~]$ tnsping temp
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-JUN-2017 19:10:41
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/grid/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = temp.idevelopment.info)))
OK (0 msec)
I am able to login to auxiliary instance as well.
[oracle@testnode2 ~]$export ORACLE_SID=tempaux
[oracle@testnode2 ~]$ rlwrap sqlplus /@tempaux as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 1 19:12:02 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
LSNRCTL STATUS shows BLOCKED for auxiliary instance.
[oracle@testnode2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JUN-2017 17:52:58
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 01-JUN-2017 16:24:15
Uptime 0 days 1 hr. 28 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/testnode2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testnode2.idevelopment.info)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "tempaux.idevelopment.info" has 1 instance(s).
Instance "tempaux", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JUN-2017 17:53:08
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Thus when I try to issue the Duplicate command I get following errors.
[oracle@testnode2 ~]$ rlwrap rman
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 1 17:53:25 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/sys@temp
connected to target database: TEMP (DBID=2909104792)
RMAN> connect auxiliary sys/sys@tempaux
connected to auxiliary database: TEMPAUX (not mounted)
RMAN> duplicate target database to tempaux from active database;
Starting Duplicate Db at 01-JUN-2017 17:54:00
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''TEMP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TEMPAUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/tempaux/control01.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/tempaux/control02.ctl' from
'/u01/app/oracle/oradata/tempaux/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''TEMP'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TEMPAUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/01/2017 17:54:07
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS: listener does not currently know of service requested in connect descriptor
RMAN>
From the blogs I read over the internet and communicity.oracle.com, I realise that when RMAN shuts the auxiliary instance and tries to reopen it, it is not able to. It is so because Listener unregisters the auxiliary instance when it is shut down. Thus when RMAN tries to reopen it, the auxiliary instance is not registered with the listener. Hence the error. The solution I found on internet is to register it dynamically.
Restart the instance and try again. Not helpful.
Restart the listener and try again. Not helpful.
I tried ALTER SYSTEM REGISTER. didn’t help.
I added an SID_LIST entry for the auxiliary instance. didn’t help.
I added local_listener to the spfile for auxiliary instance. didn’t help.
SQL> create spfile from pfile=’/home/oracle/inittempaux.ora’
File Created.
SQL> alter system set local_listener = ‘listener’ scope=spfile;
System altered.
I have been after this from two days. Please help.
Thanking you.