3 Replies Latest reply: Nov 8, 2012 11:41 PM by Niket Kumar RSS

    ORA-12514: How to configure entry with Static listener

    787588
      11gR2 on windows.
      i have configured listener with below entries
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
        )
      
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = prod)
            (ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
            (PROGRAM = extproc)
            (ENVS = "EXTPROC_DLLS=ONLY:D:\app\admin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
          )
        )
      When am trying to connect as below facing error with ORA-12514. I want to connect in "NOMOUNT" instance. Any suggestions please?
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
      ction
      Start Date                09-NOV-2012 10:25:29
      Uptime                    0 days 0 hr. 0 min. 2 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   D:\app\admin\product\11.2.0\dbhome_1\network\admin\lis
      tener.ora
      Listener Log File         d:\app\admin\diag\tnslsnr\admin-PC\listener\alert\log.
      xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xx)(PORT=1521)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
      Services Summary...
      Service "prod" has 1 instance(s).
        Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      C:\Windows\system32>sqlplus sys/manager@prod as sysdba
      SQLPlus: Release 11.2.0.1.0 Production on Fri Nov 9 10:09:40 2012*

      Copyright (c) 1982, 2010, Oracle.  All rights reserved.

      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor

      What the changes i have to do in my listener.ora file?
        • 1. Re: ORA-12514: How to configure entry with Static listener
          Niket Kumar
          cmd>tnsping prod
          post the results.

          listener is just started so its take time to register database:
          Uptime                    0 days 0 hr. 0 min. 2 sec
          post the results.

          Edited by: Niket Kumar on Nov 9, 2012 11:05 AM
          • 2. Re: ORA-12514: How to configure entry with Static listener
            787588
            Niket Kumar wrote:
            cmd>tnsping prod
            post the results.

            listener is just started so its take time to register database:
            Uptime                    0 days 0 hr. 0 min. 2 sec
            post the results.

            Edited by: Niket Kumar on Nov 9, 2012 11:05 AM
            Tnsping results are fine and i tried to connect even now. Still same issue.
            C:\Windows\system32>tnsping prod
            TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 09-NOV-2
            012 11:07:04
            Copyright (c) 1997, 2010, Oracle. All rights reserved.
            Used parameter files:
            D:\app\admin\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod
            _un) (UR = A)))
            OK (0 msec)
            C:\Windows\system32>
            issue resolved by replacing SID with SERVICE_NAME.
            Edited by: 784585 on Nov 8, 2012 10:10 PM
            • 3. Re: ORA-12514: How to configure entry with Static listener
              Niket Kumar
              try to connect system/manager@prod

              or

              sqlplus '/ as sysdba'