4 Replies Latest reply: Apr 8, 2012 6:48 AM by EdStevens RSS

    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
        • 1. Re: tnsnames.ora and listener.ora ORA-12514 error...where i am making mistake?
          orafad
          Please post lsnrctl stat output.
          • 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/