This discussion is archived
4 Replies Latest reply: Apr 8, 2012 4:48 AM by EdStevens RSS

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

OraDB Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    Please post lsnrctl stat output.
  • 2. Re: tnsnames.ora and listener.ora ORA-12514 error...where i am making mistake?
    OraDB Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points