6 Replies Latest reply: Aug 12, 2012 5:24 AM by LazyOraDBA RSS

    ORA-12514: TNS:listener does not currently know of service requested in con

    LazyOraDBA
      Hi Experts,

      I have installed Oracle RAC in the below environment.But unable to connect ASM instance using tns string

      This is production DB and up and running.

      RAC 2 nodes
      Oracle RAC 11.2.0.1.0
      ASM

      Enterprise Linux Server release 5.5 (Carthage)
      Processor Type x64
      OS 64 bit

      Getting below error while connecting with asm instance directly:
      [oracle@ admin]$ sqlplus sys@asm as sysasm
      
      SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 5 11:16:11 2012
      
      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
      
      Enter password:
      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor
      
      
      Enter user-name:
      Listener entries on node 1
      [oracle@ admin]$ cat listener.ora
      LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
      LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
      LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
      LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
      
      SID_LIST_LISTENER =
          (SID_LIST =
            (SID_DESC =
               (SID_NAME = +ASM1)
               (GLOBAL_DBNAME=ASM)
               (ORACLE_HOME = /u01/app/11.2.0/grid)
            )
            (SID_DESC =
               (SID_NAME = PLSExtProc)
               (ORACLE_HOME = /u01/app/11.2.0/grid)
               (PROGRAM = extproc)
            )
         )
      [oracle@ admin]$
      $ lsnrctl services
      
      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2012 00:31:04
      
      Copyright (c) 1991, 2009, Oracle.  All rights reserved.
      
      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
      Services Summary...
      Service "+ASM" has 1 instance(s).
        Instance "+ASM1", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0 state:ready
               LOCAL SERVER
      Service "ASM" has 1 instance(s).
        Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
       
      Tnsnames.ora entries are as below:
      [oracle@ admin]$ cat tnsnames.ora
      # tnsnames.ora Network Configuration File: 
      
      /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.
      
      XXX =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = xxx-scan)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = xxx)
          )
        )
      
      ASM =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = xxxdata-scan)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ASM)
            (SID_NAME = +ASM1)
          )
        )
      Any help ?

      Regards,

      Edited by: LazyDBA11g on Aug 11, 2012 9:58 PM

      Edited by: LazyDBA11g on Aug 12, 2012 3:27 AM
        • 1. Re: ORA-12514: TNS:listener does not currently know of service requested in con
          mseberg
          Hello;

          Is your REMOTE_LISTENER set to SCAN:PORT?

          http://docs.oracle.com/cd/E11882_01/install.112/e24660/undrstnd.htm

          Best Regards

          mseberg
          • 2. Re: ORA-12514: TNS:listener does not currently know of service requested in con
            Levi Pereira
            Hi,
            I recommend you dont use SCAN to connect on ASM.
            ASM dont failover...so dont need SCAN. (ASM is local resource)

            I always connect on ASM using EASY CONNECT METHOD (EZCONNECT) using public IP of host and port of local listerner.

            E.g
            sqlplus sys/password@hostname:1521/+ASM as sysasm
            Regards,
            Levi Pereira

            Edited by: Levi Pereira on Aug 11, 2012 7:32 PM
            • 3. Re: ORA-12514: TNS:listener does not currently know of service requested in con
              585179
              LazyDBA11g wrote:
              Hi Experts,

              ASM =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = ucbdata-scan)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = ASM)
              (SID_NAME = +ASM1)
              )
              )

              Any help ?
              Hi,

              First check your asm is listening on scan listener. If this is default installation then I would suggest change "(HOST = ucbdata-scan)" to local host where asm instance reside

              second thing is there was typo in your tnsnames. change this part "(SERVICE_NAME = ASM)" to "(SERVICE_NAME = +ASM)"


              Hope it helps

              Cheers
              Fiedi
              • 4. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                LazyOraDBA
                Thanks you all guys for quike response,

                mseberg > REMOTE_LISTENER settings in DB and ASM instances respectively are as below
                SQL>  select INST_ID, NAME, VALUE from gv$parameter where name like '%_listener%';
                 
                   INST_ID    NAME           VALUE
                ---------- -------------------------------------------------------------------------------- 
                 1   local_listener  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip)ORT=1521))))
                 
                 1   remote_listener         xxx-scan:1521
                
                 2   local_listener  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip)ORT=1521))))
                 2   remote_listener         xxx-scan:1521
                
                SQL> select instance_name from v$instance;
                 
                INSTANCE_NAME
                --------------
                +ASM1
                 
                SQL> select INST_ID, NAME, VALUE from gv$parameter where name like '%_listener%';
                 
                   INST_ID  NAME         VALUE
                ---------- -------------------------------------------------------------------------------- 
                 2  local_listener  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip)PORT=1521))))
                 2  remote_listener
                 1  local_listener  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip)PORT=1521))))
                 1  remote_listener
                 
                SQL> 
                Levi Pereira > I am able to connect to ASM instance using EASY CONNECT METHOD.
                 sqlplus sys/password@hostname:1521/+ASM as sysasm 
                Fied > ASM is not listening on SCAN LISTENER. see as below:
                [oracle@ admin]$ ps -ef|grep tns
                oracle   12496     1  0  2011 ?        00:05:05 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
                oracle   14423  9610  0 13:15 pts/1    00:00:00 grep tns
                oracle   16606     1  0 Jan27 ?        00:01:28 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -
                
                inherit
                oracle   16607     1  0 Jan27 ?        00:01:26 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -
                
                inherit
                [oracle@ admin]$ lsnrctl status LISTENER_SCAN3
                
                LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2012 13:15:33
                
                Copyright (c) 1991, 2009, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER_SCAN3
                Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                Start Date                27-JAN-2012 10:25:31
                Uptime                    198 days 2 hr. 50 min. 2 sec
                Trace Level               off
                Security                  ON: Local OS Authentication
                SNMP                      OFF
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.xxx)(PORT=1521)))
                Services Summary...
                Service "xxx" has 2 instance(s).
                  Instance "xxx1", status READY, has 1 handler(s) for this service...
                  Instance "xxx2", status READY, has 1 handler(s) for this service...
                Service "xxxXDB" has 2 instance(s).
                  Instance "xxx1", status READY, has 1 handler(s) for this service...
                  Instance "xxx2", status READY, has 1 handler(s) for this service...
                The command completed successfully
                [oracle@ admin]$ lsnrctl status  LISTENER_SCAN2
                
                LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2012 13:15:54
                
                Copyright (c) 1991, 2009, Oracle.  All rights reserved.
                
                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
                STATUS of the LISTENER
                ------------------------
                Alias                     LISTENER_SCAN2
                Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                Start Date                27-JAN-2012 10:25:31
                Uptime                    198 days 2 hr. 50 min. 23 sec
                Trace Level               off
                Security                  ON: Local OS Authentication
                SNMP                      OFF
                Listening Endpoints Summary...
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.x.xxx)(PORT=1521)))
                Services Summary...
                Service "xxx" has 2 instance(s).
                  Instance "xxx1", status READY, has 1 handler(s) for this service...
                  Instance "xxx2", status READY, has 1 handler(s) for this service...
                Service "xxxXDB" has 2 instance(s).
                  Instance "xxx1", status READY, has 1 handler(s) for this service...
                  Instance "xxx2", status READY, has 1 handler(s) for this service...
                The command completed successfully
                However, I am able to connect to ASM after changing "(HOST = xxx-scan)" to local host where asm

                instance reside.

                Is it necessary to listen ASM on SACN_LISTENER ? coz I am going to implement Golden Gate using ASM.

                Best Regards,

                LazyDBA

                Edited by: LazyDBA11g on Aug 12, 2012 3:33 AM
                • 5. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                  585179
                  Hi,

                  No, as suggested asm would be better to run on local listener.

                  Cheers
                  Fiedi
                  • 6. Re: ORA-12514: TNS:listener does not currently know of service requested in con
                    LazyOraDBA
                    Thanks Fiedi ,

                    My problem got resolved now.

                    Best regards,

                    LazyDBA