SEO

I have a problem with a java application connecting to a test Oracle DB
and everything works fine. However, once we were ready to move to
production I changed the database URL and SID to connect to the new
database and I get an error. Below are the configurations that I
changed in my properties file and the error that was received from the
TNS listener.

OLD
driver=oracle.jdbc.driver.OracleDriver
url=jdbcracle:thin:@host:1521:h8sontst

NEW
driver=oracle.jdbc.driver.OracleDriver
url=jdbcracle:thin:@new_host:1521:h8sontst

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect
descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(HOST=new_host)(PROTOCOL=tcp )(PORT=1521))(CONNECT_DATA=(SID=h8sontst)))

I can connect using sqlplus with no issues from that server. Please
help if you have any ideas? Both the test server and production server
are running the same version of Oracle.

On 8 May 2006 13:02:57 -0700, brent.ryan@gmail.com wrote:

>I have a problem with a java application connecting to a test Oracle DB
>and everything works fine. However, once we were ready to move to
>production I changed the database URL and SID to connect to the new
>database and I get an error. Below are the configurations that I
>changed in my properties file and the error that was received from the
>TNS listener.
>
>OLD
>driver=oracle.jdbc.driver.OracleDriver
>url=jdbcracle:thin:@host:1521:h8sontst
>
>NEW
>driver=oracle.jdbc.driver.OracleDriver
>url=jdbcracle:thin:@new_host:1521:h8sontst
>
>Listener refused the connection with the following error:
>ORA-12505, TNS:listener does not currently know of SID given in connect
>descriptor
>The Connection descriptor used by the client was:
>(DESCRIPTION=(ADDRESS=(HOST=new_host)(PROTOCOL=tc p)(PORT=1521))(CONNECT_DATA=(SID=h8sontst)))
>
>I can connect using sqlplus with no issues from that server. Please
>help if you have any ideas? Both the test server and production server
>are running the same version of Oracle.


Check your listener.ora. It probably doesn't have a (correct)
sid_list_listener section, including the failing SID.

--
Sybrand Bakker, Senior Oracle DBA

<brent.ryan@gmail.com> wrote in message
news:1147118577.608410.90030@i40g2000cwc.googlegro ups.com...
>I have a problem with a java application connecting to a test Oracle DB
> and everything works fine. However, once we were ready to move to
> production I changed the database URL and SID to connect to the new
> database and I get an error. Below are the configurations that I
> changed in my properties file and the error that was received from the
> TNS listener.
>
> OLD
> driver=oracle.jdbc.driver.OracleDriver
> url=jdbcracle:thin:@host:1521:h8sontst
>
> NEW
> driver=oracle.jdbc.driver.OracleDriver
> url=jdbcracle:thin:@new_host:1521:h8sontst
>
> Listener refused the connection with the following error:
> ORA-12505, TNS:listener does not currently know of SID given in connect
> descriptor
> The Connection descriptor used by the client was:
> (DESCRIPTION=(ADDRESS=(HOST=new_host)(PROTOCOL=tcp )(PORT=1521))(CONNECT_DATA=(SID=h8sontst)))
>
> I can connect using sqlplus with no issues from that server. Please
> help if you have any ideas? Both the test server and production server
> are running the same version of Oracle.
>

You may have to do a few more diagnostic tests before you can resolve the
problem.

What does the result of 'lsnrctl status <LISTENER_NAME>' say? What you
would be looking for is an indication that the listener has the Oracle
instance registered.

The problem would appear to be on the server side. You have only given us
information on the client side. You did not indicate how you connected to
the Oracle instance on the server.

<esp>Without any other information, my guess is that you have a typo in
listener.ora.</esp>

Douglas Hawthorne

brent.ryan@gmail.com wrote:

> I have a problem with a java application connecting to a test Oracle DB
> and everything works fine. However, once we were ready to move to
> production I changed the database URL and SID to connect to the new
> database and I get an error. Below are the configurations that I
> changed in my properties file and the error that was received from the
> TNS listener.
>
> OLD
> driver=oracle.jdbc.driver.OracleDriver
> url=jdbcracle:thin:@host:1521:h8sontst
>
> NEW
> driver=oracle.jdbc.driver.OracleDriver
> url=jdbcracle:thin:@new_host:1521:h8sontst
>
> Listener refused the connection with the following error:
> ORA-12505, TNS:listener does not currently know of SID given in connect
> descriptor
> The Connection descriptor used by the client was:
> (DESCRIPTION=(ADDRESS=(HOST=new_host)(PROTOCOL=tcp )(PORT=1521))(CONNECT_DATA=(SID=h8sontst)))
>
> I can connect using sqlplus with no issues from that server. Please
> help if you have any ideas? Both the test server and production server
> are running the same version of Oracle.

Show the tnsnames.ora entry for the new DBMS.

Here's the tnsnames.ora entry.

H8SONTST =
(DESCRIPTION =
(ADDRESS_LIST =
(ENABLE = broken)
(FAILOVER = on)
(LOAD_BALANCE = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = host 1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host 2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = h8sontst)
(SID = h8sontst)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
(RETRIES = 180)
(DELAY = 5)
)
)
)

I don't have access to the database server. This was my thought as
well so I'm requesting this file from the DBA now.

brent wrote:
> Here's the tnsnames.ora entry.
>
> H8SONTST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ENABLE = broken)
> (FAILOVER = on)
> (LOAD_BALANCE = on)
> (ADDRESS = (PROTOCOL = TCP)(HOST = host 1)(PORT = 1521))
> (ADDRESS = (PROTOCOL = TCP)(HOST = host 2)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = h8sontst)
> (SID = h8sontst)
> (FAILOVER_MODE =
> (TYPE = select)
> (METHOD = basic)
> (RETRIES = 180)
> (DELAY = 5)
> )
> )
> )
>

Try removing the SERVICE_NAME from the TNS entries and see if you can
still connect via sqlplus. (You might also need to drop the failover
stuff). JDBC only uses the SID to connect, while sql*plus can use
either. Its possible that the SID is not the same as the service_name. I
am going to assume that "host 1" and "host 2" don't have spaces in the
name on the real file...

--Peter

Yes, the host 1 and host 2 are real host names like myhost1.edu and
myhost2.edu.

The JDBC connection is not even using the tnsnames.ora file. It's only
using the SID to connect. Once I get some information from the DBA on
the listener.ora,etc... I'll have more insight. I think the problem is
on the database setup side.

I don't have access to the database server and when I run this command
I get an error because I'm running it from the client side. Do you
know how to set the password?

What password is it looking for?

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.e du)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SE RVICE_NAME=h8sontst)(SID=h8sontst)(FAILOVER_MODE=( TYPE=select)(METHOD=basic)(RETRIES=180)(DELAY=5))) )
TNS-01169: The listener has not recognized the password

I actually just found out that they're running a Oracle RAC environment
so my JDBC url needs to look like this for it to work.

url=jdbcracle:thin:@(DESCRIPTION=(LOAD_BALANCE=o n)(ADDRESS=(PROTOCOL=TCP)(HOST=myhost1.edu)(PORT=1 521))
(ADDRESS=(PROTOCOL=TCP)(HOST=myhost2.edu)(PORT=152 1))(CONNECT_DATA=(SERVICE_NAME=h8sontst)))

Thanks for everyones help.