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.xx x.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=(C ONNECT_DATA=(SI D=*)(SERVICE_NA ME=xxxora2.xxx. xxx.com.au))(AD DRESS=(PROTOCOL =TCP)(HOST=xxxo ra2.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_MACH INE\SOFTWARE\OR ACLE\NLS_LANG value
which is AMERICAN_AMERIC A.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 @BHPBilliton.co m remove the xxx
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.xx x.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=(C ONNECT_DATA=(SI D=*)(SERVICE_NA ME=xxxora2.xxx. xxx.com.au))(AD DRESS=(PROTOCOL =TCP)(HOST=xxxo ra2.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_MACH INE\SOFTWARE\OR ACLE\NLS_LANG value
which is AMERICAN_AMERIC A.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 @BHPBilliton.co m remove the xxx
Comment