ERROR OGG-00868 Attaching to ASM server +ASM1: (12514) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
When we are connecting to the Golden gate's Source database as RAC and with ASM then you nedd to create the ASM services in the Listener .. the asm Services will be blocked always science it is not allowed to connect remotely.. follow the steps to create the entry in the listener.ora file and tnsnames.ora files for the asm instance and create the service's and the remote connection
Step 1
Add the entries for the ASM instance in the listener.ora file check the listener has the services for the ASM
oracle@db01[PROD]:$ lsnrctl services LISTENER_db01 |grep ASM
oracle@db02[PROD]:$ lsnrctl services LISTENER_db02 |grep ASM
IF there is no services for asm then it will not return any rows we need to create the new services for the ASM .. to create the new services for ASM edit the listener.ora and tnsnames.ora file
Here we have assumed the paractice for two node cluster if you have n number of nodes then you shoud create the respective ASM Instance's entry in each Node's
In Node 1 Listener make an entry for the ASM 1 Instance
SID_LIST_LISTENER_db01 =
(SID_LIST =
(SID_DESC = (ORACLE_HOME = /prod01/app/oracle/db/10.2.0)(SID_NAME = PROD1))
(SID_DESC =(GLOBAL_DBNAME = +ASM)(SID_NAME = +ASM1)(ORACLE_HOME =/prod01/app/oracle/asm/10.2.0 ))
(SID_DESC = (SID_NAME = PLSExtProc)(ORACLE_HOME = /prod01/app/oracle/db/10.2.0)(PROGRAM = extproc))
)
IN Node two Listener Make the entry of ASM2 instance
SID_LIST_LISTENER_db02 =
(SID_LIST =
(SID_DESC = (ORACLE_HOME = /prod01/app/oracle/db/10.2.0)(SID_NAME = PROD2))
(SID_DESC =(GLOBAL_DBNAME = +ASM) (SID_NAME = +ASM2)(ORACLE_HOME =/prod01/app/oracle/asm/10.2.0 ))
(SID_DESC = (SID_NAME = PLSExtProc)(ORACLE_HOME = /prod01/app/oracle/db/10.2.0)(PROGRAM = extproc))
)
Once the listener files had been edited then edit the tnsnames.ora file for adding the entries for the
In Node 1 add the entries to the tnsnames.ora file for asm instance
ASM
+ASM1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <host name>)(PORT = <port number>))
)
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
(UR=A) ))
In Node 2 add the entries to the tnsnames.ora file for asm instance
+ASM2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <host name>)(PORT = <port number>))
)
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
(UR=A)
Step 2
Check the Local_Listener parameter in the ASM instnace's
IN ASM Instance 1
SQL> show parameter LOCAL
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string
IN ASM Instance 2
SQL> show parameter LOCAL
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string
Add the listener value for the parameter in the respective instance's
IN ASM 1 instance
SQL>alter system set local_listener=LISTENER_DB01
SQL> show parameter LOCAL
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string LISTENER_DB01
IN ASM2 Instance
SQL>alter system set local_listener=LISTENER_DB02
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string LISTENER_DB02
The Listener LISTENER_db01 and LISTENER_db02 are two listener which we have in the rac environment but according to your environment you can set the listener names
Step 3
Reload the Listener for the changes to take effect
IN Node 1
oracle@db01[PROD]:$ lsnrctl reload LISTENER_DB01
LSNRCTL for HPUX: Version 10.2.0.3.0 - Production on 08-JUL-2013 16:34:44
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<portnumber>)(IP=FIRST)))
The command completed successfully
In Node 2
oracle@db02[PROD]:$ lsnrctl reload LISTENER_DB02
LSNRCTL for HPUX: Version 10.2.0.3.0 - Production on 08-JUL-2013 16:36:35
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<portnumber>)(IP=FIRST)))
The command completed successfully
Step 4
check the listener for the new services in both the nodes you will get the services it will be blocked by default and if you want to connect remotely then you need to add the (UR=A) parameter in the tnsnames .ora file
oracle@db01[+ASM1]:$ lsnrctl services LISTENER_DB01|grep ASM
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
oracle@db02[+ASM2]:$ lsnrctl services LISTENER_DB02|grep ASM
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Step 5
Connect the ASM instance remotely and check whether it is able to connect the instance
oracle @db01[+ASM1]:$ sqlplus "sys<password>@+asm1 as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 8 16:40:29 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Step 6
Check the parameter to use the TRANLOGOPTIONS for the extract
Add the TRANLOGOPTIONS parameter with the below syntax
Syntax for the parameter
TRANLOGOPTIONS ASMUSER <username>@<instnace_name> , ASMPASSWORD<password>
Now start the extract to read the data from the ASM
