Home » RDBMS Server » Networking and Gateways » ORA-12514: TNS:listener does not currently know of service requested in connect
ORA-12514: TNS:listener does not currently know of service requested in connect [message #306484] Fri, 14 March 2008 08:39 Go to next message
kirdha1
Messages: 18
Registered: July 2005
Junior Member
Hi

I had one instance running on the sever. I built new database cogcond1 and tried to configure the new listener by making the copy of old listener and changed the name and port.

I was able to start the listner but I not able to connect the datbase using the service descriptor.

I am getting the error
ORA-12514: TNS:listener does not currently know of service requested in connect

Here is the list of the listener.ora and tnsname.ora file.
listener.ora
******************************************
SID_LIST_LISTENER_PFSCOND1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/product/10.1.0)
(PROGRAM = extproc)
)

(SID_DESC =
(GLOBAL_DBNAME = COGCOND1.XX.XXX.COM)
(SID_NAME = COGCOND1)
(ORACLE_HOME = /u01/oracle/product/10.1.0)
)
)

LISTENER_PFSCOND1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cpcorad1.xx.xxx.com)(PORT = 1521))
)
)
)

LISTENER_COGCOND1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cpcorad1.xx.xxx.com)(PORT = 1672))
)
)
)


*****************************************************************

Tnsnames.ora


COGCOND1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cpcorad1.na.jnj.com)(PORT = 1672))
)
(CONNECT_DATA =
(SERVICE_NAME = COGCOND1.XX.XXX.COM)
)
)

PFSCOND1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cpcorad1.xx.xxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PFSCOND1.XX.XXX.COM)
)
)


*************************************************************************

$ sqlplus system/xxx@cogcond1

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Mar 14 09:10:28 2008

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

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


**************************************************************************

HP-UX cpcorad1 B.11.23 U ia64

$ ps -eaf | grep tns
conora 19303 14975 1 09:17:39 pts/1 0:00 grep tns
conora 18866 1 0 09:09:21 ? 0:00 /u01/oracle/product/10.1.0/bin/tnslsnr LISTENER_COGCOND1 -inherit
conora 2757 1 0 18:31:39 ? 0:01 /u01/oracle/product/10.1.0/bin/tnslsnr LISTENER -inherit
*****************************************************************


I tried to change the diffrent port and tired this but getting same error. I am able to connect database psfcond1 but unable to connect the database cogcond1.

Kindly help on this as this needs to go in prodution by today.

Sad

[Updated on: Fri, 14 March 2008 08:45]

Report message to a moderator

Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306493 is a reply to message #306484] Fri, 14 March 2008 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 23039
Registered: January 2009
Senior Member
Please use CUT & PASTE to show complete session for while doing the following commands.

lsnrctl status
lsnrctl service
lsnrctl status LISTENER_COGCOND1
lsnrctl service LISTENER_COGCOND1

What is contents of sqlnet.ora?
Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306518 is a reply to message #306493] Fri, 14 March 2008 09:25 Go to previous messageGo to next message
kirdha1
Messages: 18
Registered: July 2005
Junior Member
$ lsnrctl status

LSNRCTL for HPUX: Version 10.1.0.5.0 - Production on 14-MAR-2008 10:23:27

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 10.1.0.5.0 - Production
Start Date 13-MAR-2008 18:31:40
Uptime 0 days 15 hr. 51 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/product/10.1.0/network/admin/listener.ora
Listener Log File /u01/oracle/product/10.1.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cpcorad1)(PORT=1521)))
Services Summary...
Service "COGCOND1.NA.JNJ.COM" has 1 instance(s).
Instance "COGCOND1", status READY, has 1 handler(s) for this service...
Service "COGCOND1XDB.NA.JNJ.COM" has 1 instance(s).
Instance "COGCOND1", status READY, has 1 handler(s) for this service...
Service "PFSCOND1.NA.JNJ.COM" has 1 instance(s).
Instance "PFSCOND1", status READY, has 1 handler(s) for this service...
Service "PFSCOND1XDB.NA.JNJ.COM" has 1 instance(s).
Instance "PFSCOND1", status READY, has 1 handler(s) for this service...
The command completed successfully
$








$ lsnrctl service

LSNRCTL for HPUX: Version 10.1.0.5.0 - Production on 14-MAR-2008 10:24:29

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
Services Summary...
Service "COGCOND1.NA.JNJ.COM" has 1 instance(s).
Instance "COGCOND1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "COGCOND1XDB.NA.JNJ.COM" has 1 instance(s).
Instance "COGCOND1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:2046 state:ready
DISPATCHER <machine: cpcorad1, pid: 16989>
(ADDRESS=(PROTOCOL=tcp)(HOST=cpcorad1)(PORT=61917))
Service "PFSCOND1.NA.JNJ.COM" has 1 instance(s).
Instance "PFSCOND1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "PFSCOND1XDB.NA.JNJ.COM" has 1 instance(s).
Instance "PFSCOND1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:2046 state:ready
DISPATCHER <machine: cpcorad1, pid: 25076>
(ADDRESS=(PROTOCOL=tcp)(HOST=cpcorad1)(PORT=57033))
The command completed successfully





$ lsnrctl status LISTENER_COGCOND1

LSNRCTL for HPUX: Version 10.1.0.5.0 - Production on 14-MAR-2008 10:24:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cpcorad1.na.jnj.com)(PORT=1672)))
STATUS of the LISTENER
------------------------
Alias LISTENER_COGCOND1
Version TNSLSNR for HPUX: Version 10.1.0.5.0 - Production
Start Date 14-MAR-2008 09:59:47
Uptime 0 days 0 hr. 24 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/product/10.1.0/network/admin/listener.ora
Listener Log File /u01/oracle/product/10.1.0/network/log/listener_cogcond1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cpcorad1)(PORT=1672)))
The listener supports no services
The command completed successfully


$ lsnrctl service LISTENER_COGCOND1

LSNRCTL for HPUX: Version 10.1.0.5.0 - Production on 14-MAR-2008 10:24:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cpcorad1.na.jnj.com)(PORT=1672)))
The listener supports no services
The command completed successfully




$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/oracle/product/10.1.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[Updated on: Fri, 14 March 2008 09:26]

Report message to a moderator

Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306519 is a reply to message #306484] Fri, 14 March 2008 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 23039
Registered: January 2009
Senior Member
As you can plainly see LISTENER_COGCOND1 does not know about any databases or services which is why you can not connect to anything on port 1672.

Why do you want or think you need two listeners on different ports?
Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306521 is a reply to message #306519] Fri, 14 March 2008 09:44 Go to previous messageGo to next message
kirdha1
Messages: 18
Registered: July 2005
Junior Member
That is the requirement of the company standards.
Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306526 is a reply to message #306484] Fri, 14 March 2008 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 23039
Registered: January 2009
Senior Member
You need to have the COGCOND1 instance register its local_listener as LISTENER_COGCOND1.
Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306533 is a reply to message #306526] Fri, 14 March 2008 10:31 Go to previous messageGo to next message
kirdha1
Messages: 18
Registered: July 2005
Junior Member
SQL> alter system set local_listener='LISTENER_COGCOND1';
alter system set local_listener='LISTENER_COGCOND1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_COGCOND1'


If I try to set this with init ora parameter instance fails to start
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_COGCOND1'
Shocked

[Updated on: Fri, 14 March 2008 10:32]

Report message to a moderator

Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306539 is a reply to message #306533] Fri, 14 March 2008 11:00 Go to previous messageGo to next message
kirdha1
Messages: 18
Registered: July 2005
Junior Member
ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST = cpcorad1.na.jnj.com)(PORT = 1672))"
SCOPE=SPFILE;


system altered
this was done
Re: ORA-12514: TNS:listener does not currently know of service requested in connect [message #306541 is a reply to message #306484] Fri, 14 March 2008 11:09 Go to previous message
kirdha1
Messages: 18
Registered: July 2005
Junior Member
This worked,
Thanks Everyone.

[Updated on: Fri, 14 March 2008 11:11]

Report message to a moderator

Previous Topic: conectivity of oracle 9i and forms 6i
Next Topic: How to Create Multisession on single connection(dedicated server)
Goto Forum:
  


Current Time: Mon Nov 24 14:41:07 CST 2014

Total time taken to generate the page: 0.08966 seconds