Oracle SID and Service name; connection problems

Question

I'm trying to connect to oracle db on remote server through sql developer. I copied the connection details to tnsnames and I'm able to connect to the db.

However i have another db application which is same as sqldeveloper and when I try to make a connection, I keep getting this error. This application uses oracle jdbc thin client which requires hostname and SID.

I got the below error:

java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

While troubleshooting, I changed the tns option in sqldeveloper and I have selected basic option to find out what went wrong and I realized that I'm able to connect to it because of the correct service name and not able to connect to the db via SID name even through sqldeveloper.

I have used some db queries to find out SID name but still i keep getting the same error. Please help me troubleshoot.

Thanks for your time..

Can you give us the connection string you're using in SQLDeveloper and in your application? Blank out the IP, if necessary! — KeyNone
CONNECTION = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) — user1751356
You say you only can connect using service name yet in the above connection string the service name is not specified. How did you come out with this connection string? Did you try to use this connection string in the app with the service name specified with the same value you specified in sqldeveloper? — Piotr Dobrogost

A B A B · Accepted Answer · 2012-12-03T21:10:04

ORA-12505 means your client passed a SID that the listener on the server end didn't recognize at all.

In 10G and above You can use EZ connect without configuring the server side like this:

sqlplus hr@liverpool:1521/DEMO

hr is the user name
liverpool is the server name
1521 is the port the listener for the DB is listening on
DEMO is the database SID

(OR)

If you still want to use tnsnames.ora, try running tnsping SID from your client.

On LINUX, You can also have ORACLE read a tnsnames.ora file from a local path - just set TNS_ADMIN to the directory where your tnsnames.ora file is.

Otherwise, you need to configure tnsnames.ora in $ORACLE_HOME/network/admin on the client

If you need to know the database SID, use this:

select sys_context('userenv','db_name') from dual;

See this URL:

Checking oracle sid and database name

Oracle SID and Service name; connection problems

2 Answers