Question:
I am getting the ORA-12504 error when I I am not able to connect
to target via SQL*Plus. It fails with the error:
ORA-12504: TNS:listener was not given the SERVICE_NAME in
CONNECT_DATA.
How do I prevent this ORA-12504 error?
Answer: The ORA-12504 says that your service_name in your tnsnames.ora
has no SID.
Also see problem 556996.1 in
MOSC for known issues causing a false ORA-12504.
The
oerr utility gives details on the ORA-12504 error:
ORA-12504: TNS:listener was not given the SID
in CONNECT_DATA
Cause: The SID was missing from the CONNECT_DATA.
Action: Check that the connect descriptor corresponding to the
service name in TNSNAMES.ORA has an SID component in the
CONNECT_DATA.
See
HERE for important method for debugging and resolving the
ORA-12504 error.
If you are experiencing a connection issue from the server side, a change to tnsnames.ora can help solve the connection
issue. For example, the following tnsnames.ora was changed
from this:
ORA11gR23 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = orasrv-alanb-lx06)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA11gR23)
)
)
)
To this:
ORA11gR23 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = orasrv-alanb-lx06)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA11gR23)
)
)
This change resolved the connection issue.
Thank you to Alan B. for the contributing the above tnsnames.ora
suggestion.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
