ORA-12504 TNS-12504 – TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

This error occurs when there was no default service configured for the listener and SERVICE_NAME was missing from the request received by the listener.

When a user tries to connect to the listener, it may be servicing several different databases. If user specifies a service name in the connect request then the listener will connect the user to that specific database but if there is no service name in the listener it will connect the user to the service name specified by the DEFAULT_SERVICE parameter.

To fix this issue there are two solutions:-

Sol 1: Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service name:-

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.example.com)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl.example.com)
(SID_NAME=orcl))
DEFAULT_SERVICE_LISTENER=oracle.example.com

Once the DEFAULT_SERVICE parameter is configured, reload the listener to bring this change into effect:-

lsnrctl reload

Sol 2. If using a service name, check that the connect descriptor corresponding to the service name in TNSNAMES.ORA of the client has a SERVICE_NAME or SID component in the CONNECT_DATA.

#When using SERVICE_NAME in CONNECT_DATA component
orcl.example.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

#When using SID in CONNECT_DATA component
orcl.example.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)

Advertisements

One thought on “ORA-12504 TNS-12504 – TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

  1. Hi,

    We have a oracle database server, with two oracle client servers. We are trying to add another client server. Installed the Oracle client , and copied listener.ora, tnsnames.ora, sqlnet.ora files from the running client and saved in the oracle folder for this new client. ( we were told by the OEM who set up this system , that we just have to copy these files to make the client start communicating with the database)
    When we are testing the connections using ODBC configuration utility we are getting this ORA-12504 error.

    Can you suggest what is missing from our side, to make the new oracle client communicate with the database.
    We are the end users are not very familiar with the oracle system.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.