ORA 7.3 v 9.2 client conneection problems

From: Tig <Simon.Cullen_at_BHPBilliton.com>
Date: 7 Apr 2005 20:39:22 -0700
Message-ID: <1112931562.868228.185090_at_l41g2000cwc.googlegroups.com>

Hi all.

I have a need to connect to an Oracle 7.3.3.5 database. I have a user who successfully connects to it with her Oracle 7.3 client. I have an Oracle 9.2 client installed on my machine.

I had her send me her tnsnames.ora from her PC. The entry I wanted was as follows (some letters replaced with xxx for security reasons):

xxxora2_prod2 = (DESCRIPTION=

                 (ADDRESS=

(PROTOCOL=tcp)(PORT=1526)(HOST=xxxora2.xxx.xxx.com.au)
                 )
                 (CONNECT_DATA=(SID=prod2))
                 )

She has an ODBC connection using her Ora 73 client drivers which works fine.

I then add the same entry to my
C:\oracle\ora92\network\ADMIN\tnsnames.ora file.

Now if I ping this hostname, it works fine.

If I tnsping this hostname it works fine.

Used HOSTNAME adapter to resolve the alias Attempting to contact
(DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=xxxora2.xxx.xxx.com.au))(ADDRESS=(PROTOCOL=TCP)(HOST=xxxora2.xxx.xxx.com.au)(PORT=1521))) OK (70 msec)

If I try and tnsping the tnsname, however, I get an error:

TNS-03505: Failed to resolve name

And if I then try and create an ODBC connection using my client I get a short wait and then an "Unable to connect" "SQLState=08004" "ORA-12154: TNS:could not resolve service name" error.

The existing entries in my (9.2 client) tnsnames.ora don't have a SID, they have a SERVICE_NAME. So I change mine from SID to SERVICE_NAME. My file now looks like this:
 xxxora2_prod2 =
   (DESCRIPTION =

     (ADDRESS_LIST =


       (FAILOVER = ON)


       (ADDRESS = (PROTOCOL = TCP)(HOST = xxxora2.xxx.xxx.com.au)(PORT

= 1526))
     )
     (CONNECT_DATA=


       (SERVICE_NAME = prod2)


       (INSTANCE_NAME = prod2)


     )

   )

If I now try and create the connection, I get a "Unable to connect" "SQLState=S1000" "ORA-12705: invalid or unknown NLS parameter value specified" error.

I have checked my HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG value which is AMERICAN_AMERICA.WE8ISO8859P1 so that looks OK.

Does anyone have any ideas?? I am a developer not a DBA and haven't played with Oracle clients for years.

If I change the port number to 1521 and try again I get a "ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor" error, but I am just clutching at straws now.

Thanks!
Simon Cullen
Australia

Simon.Cullenxxx_at_BHPBilliton.com remove the xxx Received on Fri Apr 08 2005 - 05:39:22 CEST