TNS:could not resolve the connect identifier specified at oci call ociserveratta

[nQsError:17014] Could not connect to Oracle database.
[nQsError:17001] Oracle Error code: 12154, message: ORA-12154:
TNS:could not resolve the connect identifier specified
at OCI call OCIServerAttach.

Obiee repository creation error

Have you tried writing the full connection string, as it appears in the tnsnames.ora file, instead of just the name??

Instead of writing pdborcl write:

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = pdborcl))
  )

Solution:

set path environment variable:

1. C:\Oracle\product\11203_32bit\CLIENT_1\NETWORK\ADMIN
2. C:\Oracle\product\11203_64bit\CLIENT_1\NETWORK\ADMIN

According Oracle these locations are searched for tnsnames.ora, resp. sqlnet.ora and ldap.ora:

  1. current path (associated with the running client application)
  2. Environment variable TNS_ADMIN defined for the session
  3. Environment variable TNS_ADMIN defined for the system
  4. Windows Registry Key HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN (for x64) or HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN (for x86)
  5. %ORACLE_HOME%\network\admin

However, I am not sure whether each application/driver follows this list. I got this list from Oracle Document 111942.1 referring to Oracle 9i.

So, I also would recommend to define an environment variable for TNS_ADMIN and use only one tnsnames.ora file. In order to be on the safe side, check also your registry values.

If your files are not located in %ORACLE_HOME%\network\admin, I recommend to create a symbolic link for it – just to be on the very safe side, e.g. mklink /d %ORACLE_HOME%\network\admin c:\Oracle\common\settings\admin

Another note, you don’t have to “play” with your tnsnames.ora file. With Process Monitor from Microsoft Sysinternals you can monitor each file access, i.e. the filter would be Path contains tnsnames

Update

When I run a test on my machine I get following order:

  1. Environment variable TNS_ADMIN
  2. HKLM\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN, resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN -> Only if TNS_ADMINEnvironment variable is not set.
  3. %ORACLE_HOME%\network\admin
  4. Current directory (which can be different to directory where your application is located)
  5. Folder where your application is located

Update 2

Obviously there is no fix search, it varies for different providers/drivers. Maybe it also depends on the Oracle version.

For example, for ODP.NET Managed Driver (Oracle.ManagedDataAccess) I found this order at Oracle Managed and TNS Names :

  1. data source alias in the ‘dataSources’ section under <oracle.manageddataaccess.client>section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.
  4. data source alias in the tnsnames.ora file present at %TNS_ADMIN%
    (where %TNS_ADMIN% is an environment variable setting).
  5. data source alias in the tnsnames.ora file present at %ORACLE_HOME%\network\admin
    (where %ORACLE_HOME% is an environment variable setting).

whereas official documentation says:

  1. data source alias in the dataSources section under <oracle.manageddataaccess.client>section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.
 

 

Leave a comment