TNS:listener could not resolve SID

sjordery · Post by **sjordery** » Wed Mar 12, 2014 3:07 am

Hi,

I have configured a DSN for DataIntDev project on dstaged1 server to connect to Oracle 11g server . While connecting to db using this DSN I am getting error on DataStage Designer, the following are the steps I have followed. Please let me know is there anything missing or needs to be added.

1) I have configured .odbc.ini , uvodbc.config file under the below path

/opt/DataStage/Ascential/DataStage/DSEngine>

2) uvodbc.config under the project path
/opt/DataStage/Ascential/DataStage/Projects/DataIntDev>

I am able to connect from UNIX server using SQLPLUS (Oracle 9i client) to the Oracle database 11g.

On UNIX box Oracle client is 9i connecting to Oracle 11 g database. the following is the error while connecting to Oracle 11g (RAC)

BCI Error:SQLSTATE=08001,CODE=-1,[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]TNS-12505: TNS:listener could not resolve SID given in connect descriptor

But from the same server when I connect to Oracle 10g (RAC) database I don't have any issues.

Thanks,
Chakrapani Dadam

[Note - Title modified to be more descriptive - Admin]

chulett · Post by **chulett** » Wed Mar 12, 2014 7:10 am

Check with your DBA, that error just indicates a problem with your tnsnames.ora entry.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Wed Mar 12, 2014 12:29 pm

Also - entries in .odbc.ini and uvodbc.config are only used by ODBC stages, and are not needed if you are using Oracle stages.

And it is always recommended you use native (in this case Oracle) stages instead of ODBC if possible for better performance.

priyadarshikunal · Post by **priyadarshikunal** » Thu Mar 13, 2014 5:14 am

I don't think ODBC uses tnsnames.ora file. When you define the ODBC connection in .odbc.ini which you use to put in the odbc server details, you mention SID, double check SID/service name, IP, port etc in .odbc.ini, Also are you able to connect to that server using any other client?

chulett · Post by **chulett** » Thu Mar 13, 2014 7:24 am

It needs a client and the client leverages the tnsnames.ora file. And 'SID' in your case really means the TNS alias entry. From the thin client docs:

B. Configuring an Oracle DSN

The Oracle ODBC driver configuration has changed slightly with each newer version of the ODBC driver, but there are only 2 pieces of information that are absolutely required to create a working Oracle DSN:
1. A DSN Name (whatever you like)
2. A valid SQL*Net connect string (TNS Alias or Oracle 8 service Name)

In Oracle 8, this connect string is sometimes referred to as the Service name, and in some versions of the 7.3 driver (and in some 3rd party drivers) this is sometimes called SERVER NAME. This does not mean to use the network name of the server or even the SID name, but rather the actual TNS Alias that you configured when you configured the SQL*Net layer. You can look in the Oracle_Home\network\Admin (or Net80\admin) directory for the TNSNAMES.ORA file (a text file) to see what TNS Aliases is configured. You could also use the SQL*Net Easy Config (or Net8 Easy Config depending on your version of SQL*Net and the driver you are configuring) program to view this information. In the case of a local RDBMS you would normally use the Beq-Local alias, normally created for you when the RDBMS is installed. If you are unsure (or if you are using Names Server) you may need to consult with your Oracle DBA or whoever configured your SQL*Net connection.