ORA-12504 When Using (HOSTNAME) Method For 11G Client/Database [ID 556996.1]

ORA-12504 When Using (HOSTNAME) Method For 11G Client/Database [ID 556996.1]

--------------------------------------------------------------------------------

Modified 03-DEC-2010 Type PROBLEM Status PUBLISHED

In this Document
Symptoms
Cause
Solution
References



--------------------------------------------------------------------------------



Applies to:
Oracle Net Services - Version: 11.1.0.6.0 and later [Release: 11.1 and later ]
Information in this document applies to any platform.
Checked for relevance on 14-MAY-2010.

The issue documented here has been resolved in an enhancement request since the original writing. Please refer to the notes below.
Symptoms
Connecting from an 11.1.0.x client using the HOSTNAME adapter fails with the error:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

However, the same client connection works fine using the LDAP, TNSNAMES, or EZCONNECT methods.



Cause
From 11g onwards, the feature of using the hostname provided in the /etc/hosts file, as the alias / service name has been changed. As a result, the user will have to provide the service name while connecting OR configure the target listener with a default service name:

This was justified by Bug.6374523 "CAN NOT USE HOSTNAME NAMING METHOD FROM 11G DATABASE" (which was closed as not a bug).

IMPORTANT NOTE: This issue was resolved by way of an enhancement request. The issue was handled in unpublished BUG 9271246 Abstract: REINTRODUCE DEPRECATED 10G EZCONNECT FUNCTIONALITY TO 11G.
There are interim patches available for multiple platforms on My Oracle Support. The patch will restore the functionality to pre-11g behavior.




Further clarification from the bug as to why there is a difference between 10g and 11g:

While connecting when you issue the following :
CONNECT username@alias

We get the following results depending on the version:

10g working case (when hostname would be used as the default service) -->
- The 'alias' gets resolved using the hostname adapter to the respective host.
- HOWEVER, you have not specified the service name, so the alias is itself used as the service name and the connect string generated has the service name filled up with the alias you specified.
- This string goes to the listener and the connection is fine.

11g non-working case (when the hostname cannot be used as the default service and the listener's default service is expected to be used) -->
- The 'alias' gets resolved using the hostname adapter to the respective host.
- HOWEVER, you have not specified the service name, so the alias is not used as the service name, and the service name is left blank in the connect string generated (the reason being is that we expect the listener to use the default service configured at its end, such as older versions used to do).
- This string with the "blank" service name goes to the listener and the ORA-12504 error is thrown, correctly indicating that the listener is not configured with the default service name.



Solution
With 11g, the way the HOSTNAME adapter is used has changed. You now have to use the hostname adapter as per the Net Services Administrator's guide.

**See IMPORTANT NOTE above. An enhancement request has been worked and a patch is available that will return the HOSTNAME adapter to pre-11g behavior.

Reference
The following is taken from the Oracle� Database Net Services Reference, 11g Release 1 (11.1), Part Number B28317-02 -- Chapter 7, Oracle Net Listener Parameters (listener.ora) :

"7.2.4.3 DEFAULT_SERVICE_listener_name

Purpose
Use the DEFAULT_SERVICE_listener_name parameter to enable users to connect to the database without having to specify a service name from client side.

In Oracle Database 11g, when a client tries to connect to the database the connection request passes through the listener. The listener may be servicing several different databases. If a service name is configured in this parameter, users may not necessarily need to specify a service name in the connect syntax. If a user specifies a service name, the listener will connect the user to that specific database, otherwise the listener will connect to the service name specified by the DEFAULT_SERVICE_listener_name parameter.

See Also:
Oracle Database Net Services Administrator's Guide for more information about the easy connect naming method

Default
There is no default value for the DEFAULT_SERVICE_listener_name parameter. If this parameter is not configured and a user does not specify a fully qualified service name in the connect syntax, the connection attempt fails. This parameter only accepts one value.

Example
DEFAULT_SERVICE_LISTENER=sales.us.acme.com "


1. Obtain and apply the interim patch for unpublished Bug 9271246 which will restore the functionality to pre-11g. If a patch does not exist for your platform, please request a backport from Oracle Support.

2. As one example, use the host / service_name in the connect string:
CONNECT username/password@host/service_name

OR

3. Configure the listener with a default service name as follows:
You need to add the following parameter to the listener.ora file for the 11g tns listener
DEFAULT_SERVICE_listener_name= ( service_name )
where service_name is the target database name.
After these changes you need to restart the listener for the changes to take place.



References
http://st-doc.us.oracle.com/11/111/network.111/b28317/toc.htm
BUG:6374523 - CAN NOT USE HOSTNAME NAMING METHOD FROM 11G DATABASE

Related



--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------

•Oracle Database Products > Oracle Database > Net Services > Oracle Net Services
Keywords
--------------------------------------------------------------------------------
EASY CONNECT; TNSNAMES; EZCONNECT; HOSTNAME; ADAPTER
Errors
--------------------------------------------------------------------------------
ORA-12504; 12504 ERROR




Back to top

Copyright (c) 2007, 2010, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement Rate this document
Article Rating Rate this document
Excellent
Good
Poor

Did this document help you?
Yes
No
Just browsing

How easy was it to find this document?
Very easy
Somewhat easy
Not easy
CommentsProvide feedback for this article. Please use 'Contact Us' for other feedback. Important Note: this feedback may be anonymously visible to other customers until processed by Oracle Support.


Cancel