Not able to create database connection in Oracle connect

vinu1103sharma · Post by **vinu1103sharma** » Thu Jul 23, 2015 5:04 am

Hi friends,

Issue - Not able to connect oracle DB in ODBC stage but same database i can connect in ODBC stage.

More description about issue ->
Our DBA has changed the host name like abc@xyz.com to abc@pqr.org in oracle database.
So we also need to update in our infoSpare server in tnsnames.ora file.
I made the all changes in tnsnames.ora file and then update in .odbc.ini and uvodbc.config file.
I have restarted dsengin. Now i have check connection in ODBC stage i am able to connect "abc@pqr.org" database.
But in oracle connector stage i am getting below error-

below error when server name old one "abc@xyz.com"

" The connection could not establish connection Oracle server abc@pqr.org
Method: OCIServerAttach,Error code:12545, Error message ORA-12545:Connection Failed
because target host or object host dones not exist"

below error when server name new "abc@pqr.org"

" The connection could not establish connection Oracle server abc@pqr.org
Method: OCIServerAttach,Error code:12504, Error message ORA-12504:TNS:listener
was not given the SERVICE_NAME in CONNECT_DATA"

Please expert advice me where we need to change the HOST name or if i missed to update any file like tnsnames.ora.

chulett · Post by **chulett** » Thu Jul 23, 2015 10:47 am

Why not use the IP address?

priyadarshikunal · Post by **priyadarshikunal** » Thu Jul 23, 2015 11:34 am

validate the entry in tnsnames.ora? there may be some issue with the syntax. are you able to connect via sqlplus?

qt_ky · Post by **qt_ky** » Thu Jul 23, 2015 11:46 am

Verify that the host name resolves correctly to the IP address, or just use the IP address instead, as Craig mentioned. Test using ping.

Then try the Oracle command line utility:

tnsping database_name

Most likely, the tnsnames.ora entry could be incorrect.

You may try having the Oracle DBA get on your client to troubleshoot it.

Also make sure you're not blocked by a firewall.

vinu1103sharma · Post by **vinu1103sharma** » Thu Jul 23, 2015 11:50 pm

Hi All,

I don't know, you are able to understand the connector issue or not.

Same IP address/HOST name, i am able to connect in ODBC stage but not able to connect in ORACLE stage.

If my tnsnames.ora file is not correct the i can't connect in ODBC stage also.

I think we need to modify some file in datastage server side for oracle setting or may be we need to add environment parameter.

DS Admin can help on this, how can add new database in datastage and what are need to setting for oracle stage.

qt_ky · Post by **qt_ky** » Fri Jul 24, 2015 5:31 am

vinu1103sharma wrote:If my tnsnames.ora file is not correct the i can't connect in ODBC stage also.

Not true... ODBC uses the .odbc.ini config file. Fix your tnsnames.ora file accordingly.

chulett · Post by **chulett** » Fri Jul 24, 2015 7:15 am

Oh, we understand.

It's all about the tnsnames.ora file as the errors note, there's no "server side Oracle setting" other than that. And ODBC may be working because you are using the thin client. [shrug]

I think you have a DNS or /etc/hosts issue since "all you did" was rename the host server. For grins, edit the tns entry and replace the host name with the IP address. See what happens. You can always change it back.

chulett · Post by **chulett** » Fri Jul 24, 2015 7:18 am

qt_ky wrote:
vinu1103sharma wrote:If my tnsnames.ora file is not correct the i can't connect in ODBC stage also.
Not true... ODBC uses the .odbc.ini config file. Fix your tnsnames.ora file accordingly.

From what I recall it uses the config file to point to the tns entry to use, if one is using the thick drivers. Thin... not so much. At least that's what I'm remembering here at home at the crack of dawn.

qt_ky · Post by **qt_ky** » Sat Jul 25, 2015 6:44 am

OK, so I guess it depends on which ODBC driver--the Oracle driver vs. the Oracle Wire Protocol driver. I've only used the latter (thin) and never had a need to use the thick one because if you've already got the Oracle Client, then what's the point of dropping an ODBC driver on top of it (unless one works in the Redundancy Department of Redundancy)?

chulett · Post by **chulett** » Sat Jul 25, 2015 7:19 am

Or the Department of Redundancy Department.