4 Replies Latest reply on Apr 8, 2012 11:48 AM by EdStevens

    tnsnames.ora and listener.ora ORA-12514 error...where i am making mistake?

    OraDB
      Friends,

      OS: win7
      db: 10.2.0.5

      OS: RHEL 4.8
      db: 10.2.0.1

      i tried to connect the db of rhel os thru win7 db's sql* plus but i am not able to connect it.
      i tried with tnsping of primdb...its working..but try to connect thru gui sql*plus or command prompt sql*plus...i am getting the below error

      ========================================
      C:\Windows\system32>sqlplus system/password@primdb

      SQL*Plus: Release 10.2.0.5.0 - Production on Sun Apr 8 13:01:04 2012

      Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

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

      at the same time when i do the tnsping of that service name its pinging...

      C:\Windows\system32>tnsping primdb

      TNS Ping Utility for 64-bit Windows: Version 10.2.0.5.0 - Production on 08-APR-2
      012 13:01:22

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

      Used parameter files:
      D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.
      1.131)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb
      )))
      OK (0 msec)

      ==================================================

      listener.ora of win7

      # listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = ipc))
      (ADDRESS = (PROTOCOL = TCP)(HOST = mypc.thay.net)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      )


      tnsnames.ora of win7

      +# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora+
      +# Generated by Oracle configuration tools.+

      PRIMDB =
      +(DESCRIPTION =+
      +(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))+
      +(CONNECT_DATA =+
      +(SERVER = DEDICATED)+
      +(SERVICE_NAME = primdb)+
      +)+
      +)+

      CATDB =
      +(DESCRIPTION =+
      +(ADDRESS = (PROTOCOL = TCP)(HOST = mypc.thay.net)(PORT = 1521))+
      +(CONNECT_DATA =+
      +(SERVER = DEDICATED)+
      +(SERVICE_NAME = catdb)+
      +)+
      +)+

      ORCL =
      +(DESCRIPTION =+
      +(ADDRESS = (PROTOCOL = TCP)(HOST = mypc.thay.net)(PORT = 1521))+
      +(CONNECT_DATA =+
      +(SERVER = DEDICATED)+
      +(SERVICE_NAME = orcl)+
      +)+
      +)+

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

      ===================================================

      listener.ora of rhel 4.8

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

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = ipc))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
      )
      )

      tnsnames.ora of rhel

      # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.

      primdb =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = primdb)
      )
      )

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


      thanks
        • 2. Re: tnsnames.ora and listener.ora ORA-12514 error...where i am making mistake?
          OraDB
          windows7 listener status

          C:\Windows\system32>lsnrctl status

          LSNRCTL for 64-bit Windows: Version 10.2.0.5.0 - Production on 08-APR-2012 14:09
          :36

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

          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ipc)))
          STATUS of the LISTENER
          ------------------------
          Alias LISTENER
          Version TNSLSNR for 64-bit Windows: Version 10.2.0.5.0 - Produ
          ction
          Start Date 08-APR-2012 11:07:58
          Uptime 0 days 3 hr. 1 min. 42 sec
          Trace Level off
          Security ON: Local OS Authentication
          SNMP OFF
          Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listener.o
          ra
          Listener Log File D:\oracle\product\10.2.0\db_1\network\log\listener.log

          Listening Endpoints Summary...
          (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\ipcipc)))
          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mypc)(PORT=1521)))
          (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
          Services Summary...
          Service "PLSExtProc" has 1 instance(s).
          Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Service "catdb" has 1 instance(s).
          Instance "catdb", status READY, has 1 handler(s) for this service...
          Service "catdbXDB" has 1 instance(s).
          Instance "catdb", status READY, has 1 handler(s) for this service...
          Service "catdb_XPT" has 1 instance(s).
          Instance "catdb", status READY, has 1 handler(s) for this service...
          Service "orcl" has 1 instance(s).
          Instance "orcl", status READY, has 1 handler(s) for this service...
          Service "orclXDB" has 1 instance(s).
          Instance "orcl", status READY, has 1 handler(s) for this service...
          Service "orcl_XPT" has 1 instance(s).
          Instance "orcl", status READY, has 1 handler(s) for this service...
          The command completed successfully

          rhel listener status

          rac1-> lsnrctl status

          LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-APR-2012 14:20:46

          Copyright (c) 1991, 2005, Oracle. All rights reserved.

          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ipc)))
          STATUS of the LISTENER
          ------------------------
          Alias LISTENER
          Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
          Start Date 08-APR-2012 13:41:21
          Uptime 0 days 0 hr. 39 min. 24 sec
          Trace Level off
          Security ON: Local OS Authentication
          SNMP OFF
          Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
          Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
          Listening Endpoints Summary...
          (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ipc)))
          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.mycorpdomain.com)(PORT=1521)))
          Services Summary...
          Service "PLSExtProc" has 1 instance(s).
          Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Service "orcl" has 1 instance(s).
          Instance "orcl", status READY, has 1 handler(s) for this service...
          Service "orclXDB" has 1 instance(s).
          Instance "orcl", status READY, has 1 handler(s) for this service...
          Service "orcl_XPT" has 1 instance(s).
          Instance "orcl", status READY, has 1 handler(s) for this service...
          The command completed successfully


          Note: rac1.mycorpdomain.com 's ip is 192.168.1.131...i tried with both name and ip..but not succeeded.
          Also, i have created a new service name as primdb and also orcl1, that too failed...
          • 3. Re: tnsnames.ora and listener.ora ORA-12514 error...where i am making mistake?
            orafad
            906621 wrote:
            Services Summary...
            Service "catdb" has 1 instance(s).
            Instance "catdb", status READY, has 1 handler(s) for this service...
            Service "orcl" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            rhel listener status
            Services Summary...
            Service "orcl" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            As you can see, no Service "primdb" from either listener. Hence listener(s) saying "that service name is unknown to me" (ORA-12514).

            http://docs.oracle.com/cd/B19306_01/network.102/b14212/troublestng.htm#BCGEJFJJ
            • 4. Re: tnsnames.ora and listener.ora ORA-12514 error...where i am making mistake?
              EdStevens
              http://edstevensdba.wordpress.com/2011/03/19/ora-12514/