Home » Infrastructure » Windows » ODP.NET unable to connect (11gR2)
ODP.NET unable to connect [message #543503] Wed, 15 February 2012 01:32 Go to next message
gustav
Messages: 6
Registered: February 2012
Junior Member
I am trying to connect to an 11gR2 DB from a Windows Server 2008 R2 64 bit client with a 64bit client application. The 64bit client is the only one installed in the machine and ORACLE_HOME and TNS_ADMIN points to the correct paths. I use ODP.net4 so I installed it to use the oracle home as the client.

I am able to connect to the server with SQLPlus, using TNS (afis/***@AFISL), but when I attempt to connect with the client application, I get this error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The connection string I use is: "Data Source=AFISL;user id=afis;password=***;"


Listener.ora:
# listener.ora Network Configuration File: G:\app\Administrator\product\11.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.22.18)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = AFISETX)
(ORACLE_HOME = G:\app\Administrator\product\11.2.0\dbhome_2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:G:\app\Administrator\product\11.2.0\dbhome_2\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = AFISL)
(GLOBAL_DBNAME = AFISl.PenCom.Local)
(ORACLE_HOME = G:\app\Administrator\product\11.2.0\dbhome_2)
)
)

ADR_BASE_LISTENER1 = G:\app\Administrator


# sqlnet.ora Network Configuration File: C:\app\Administrator\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN= PenCom.Local


# tnsnames.ora Network Configuration File: c:\app\Administrator\product\11.2.0\client_1\\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

AFISL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.22.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = AFISL.PenCom.Local)
)
)




Re: ODP.NET unable to connect [message #543510 is a reply to message #543503] Wed, 15 February 2012 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
 *Cause:  The listener received a request to establish a connection to a
 database or other service. The connect descriptor received by the listener
 specified a service name for a service (usually a database service)
 that either has not yet dynamically registered with the listener or has
 not been statically configured for the listener.  This may be a temporary
 condition such as after the listener has started, but before the database
 instance has registered with the listener.
 *Action:
  - Wait a moment and try to connect a second time.
  - Check which services are currently known by the listener by executing:
    lsnrctl services <listener name>
  - Check that the SERVICE_NAME parameter in the connect descriptor of the
    net service name used specifies a service known by the listener.
  - If an easy connect naming connect identifier was used, check that
    the service name specified is a service known by the listener.
  - Check for an event in the listener.log file.

Ask your DBA.

Regards
Michel
Re: ODP.NET unable to connect [message #543513 is a reply to message #543510] Wed, 15 February 2012 01:53 Go to previous messageGo to next message
gustav
Messages: 6
Registered: February 2012
Junior Member
Hi Michel,

Thank you for the input, I did check all the issues listed in the error description, I think that somehow ODP.net4 isn't using TNS correctly, as I am able to connect using SQLplus, using the same TNS name, so I don't think the problem is on the listener side.

Kind regards,
Gustav
Re: ODP.NET unable to connect [message #543519 is a reply to message #543513] Wed, 15 February 2012 02:07 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Gustav, there is inconsistency in your Oracle Net files. In listener.ora, you create a listener called LISTENER1, but then you define SID_LIST for a listener called LISTENER. This is possibly why you get the ora-12514.
Re: ODP.NET unable to connect [message #543521 is a reply to message #543519] Wed, 15 February 2012 02:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Another inconsistency: you have defined a default_domain in sqlnet.ora, but you have not appended this to the TNS name defined in tnsnames.ora. Personally, I do not use default_domain because I think it causes confusion for no benefit. I would get rid of it.
Re: ODP.NET unable to connect [message #543524 is a reply to message #543521] Wed, 15 February 2012 02:24 Go to previous messageGo to next message
gustav
Messages: 6
Registered: February 2012
Junior Member
Hi John,

Thank for for the advise, I renamed the SID listener to LISTENER and removed the default_domain, but the problem persists.

I also tried to connect via easyconnect Data Source=afis/***@//10.1.22.18/afisl.pencom.local and that also doesn't work. What I can't understand is that I can connect with SQLplus and sqldeveloper on both TNS and easyconnect
Re: ODP.NET unable to connect [message #543527 is a reply to message #543524] Wed, 15 February 2012 02:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I can connect with SQLplus
In that case, you are not using those files. The inconsistency with the default_domain makes it impossible.

Quote:
Data Source=afis/***@//10.1.22.18/afisl.pencom.local

There is no port specified here. Does ODP.net4 have a default for this? It would need to be :1521.

[edit: sorry, I missed that you had removed thhe default domain. So OK, SQL*Plus should work with those files now]

[Updated on: Wed, 15 February 2012 02:30]

Report message to a moderator

Re: ODP.NET unable to connect [message #543528 is a reply to message #543527] Wed, 15 February 2012 02:32 Go to previous messageGo to next message
gustav
Messages: 6
Registered: February 2012
Junior Member
I tried adding the port (set to default 1521), so Data Source=afis/***@//10.1.22.18:1521/afisl.pencom.local, but no change


Re: ODP.NET unable to connect [message #543529 is a reply to message #543528] Wed, 15 February 2012 02:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
but no change
this is not a very precise error message Confused
Re: ODP.NET unable to connect [message #543533 is a reply to message #543529] Wed, 15 February 2012 02:54 Go to previous messageGo to next message
gustav
Messages: 6
Registered: February 2012
Junior Member
Sorry John,

I keep getting:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Re: ODP.NET unable to connect [message #543534 is a reply to message #543533] Wed, 15 February 2012 02:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So does the listener know about the service?

lsnrctl services listener1


[edit:
also,

lsnrctl status listener1
]

[Updated on: Wed, 15 February 2012 02:59]

Report message to a moderator

Re: ODP.NET unable to connect [message #543541 is a reply to message #543534] Wed, 15 February 2012 03:08 Go to previous messageGo to next message
gustav
Messages: 6
Registered: February 2012
Junior Member
I renamed the listener from listener1 to listener to remove the inconsistency mentioned in the listeners.ora file

Output:
**********************************************************************
C:\Documents and Settings\Administrator>lsnrctl services listener

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-FEB-2012 10:00
:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.22.18)(PORT=1521)))

Services Summary...
Service "AFISETX" has 1 instance(s).
Instance "AFISETX", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "AFISl.PenCom.Local" has 1 instance(s).
Instance "AFISL", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
*****************************************************************

*****************************************************************
C:\Documents and Settings\Administrator>lsnrctl status listener

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-FEB-2012 10:03
:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.22.18)(PORT=1521)))

STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 15-FEB-2012 09:13:29
Uptime 0 days 0 hr. 49 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File G:\app\Administrator\product\11.2.0\dbhome_2\network\a
dmin\listener.ora
Listener Log File g:\app\administrator\diag\tnslsnr\oracle-discover\list
ener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.22.18)(PORT=1521)))
Services Summary...
Service "AFISETX" has 1 instance(s).
Instance "AFISETX", status UNKNOWN, has 1 handler(s) for this service...
Service "AFISl.PenCom.Local" has 1 instance(s).
Instance "AFISL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
************************************************************
Re: ODP.NET unable to connect [message #543547 is a reply to message #543541] Wed, 15 February 2012 03:20 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your database instance is not registering with the listener, probably because of the config file inconsistencies.

In your database istance:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.22.18)(PORT=1521))';
alter system register;


then re-run the lsnrctl commands, and try again.

But next time you post you MUST format the output correctly with [code] tags, as I have done above. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read



Previous Topic: Oracle Objects for OLE - Paranmert Query using OraParamArray
Next Topic: Error during Configuration
Goto Forum:
  


Current Time: Thu Mar 28 14:25:07 CDT 2024