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

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.