2

I have a BizTalk 2013r2 app with a WCF-Oracle send port running under a 32bit host.

When run in my test environment it works fine. However, on my dev box I get the following error:

"ORA-12154: TNS:could not resolve the connect identifier specified"

The address set on the send port is "oracledb://test_godw_lincoln/" . My understanding is that the ip address and port should be resolved from the local tnsnames.ora file. I have a system environment variable for TNS_ADMIN set correctly:

enter image description here

From a command prompt, if I enter "set tns_admin" then the following is displayed:

TNS_ADMIN=C:\app\biztalk.admin\product\12.1.0\client_1\Network\Admin

If I open the file "C:\app\biztalk.admin\product\12.1.0\client_1\Network\Admin\tnsnames.ora" then I see the following entry:

test_godw_lincoln =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gotest) 
    )
  )

(I've substituted the real IP address)

If I open Sql Developer on the same dev vm then I am able to open a connection to the required database using the tnsname entry "test_godw_lincoln":

enter image description here

I realise that Oracle can be addressed from the adaptor without using tnsnames.ora but I understand that if ambient transactions are to be used (the app does make use of them) then the address must be via tnsnames.ora

Any suggestions as to how I can track down the cause of the problem with this particular VM?

Re question from Dmitir - the registry is empty:

registry

Re suggestion from Felix - I am able to create the connection on the same dev server from Visual Studio server explorer:

enter image description here

However, if I try to connect from the "Consume Adapter Service" wizard (add generated items) then I get the ORA-12154 error:

enter image description here

Rob Bowman
  • 7,632
  • 22
  • 93
  • 200
  • Check that is your System Environment variable TNS_ADMIN with the correct value and not a User Environment variable. – felixmondelo Jan 21 '18 at 10:19
  • Thanks for your comment Felix but yes, it is an System environment variable – Rob Bowman Jan 21 '18 at 11:38
  • this might help you https://stackoverflow.com/questions/30861061/ora-12505-tns-listener-does-not-currently-know-of-sid-given-in-connect-descript/30862252#30862252 – Ravi Jan 21 '18 at 11:40
  • Hi Ravi, I ran the Sql from this and got SID=329 and SYS_CONTEXT=gotest. Not sure how that helps though. I suspect the root cause of the problem may be some 64 / 32 bit library mismatch - but that's really just a guess – Rob Bowman Jan 21 '18 at 11:54
  • Try to specify in the connection string a full description of DB from tnsnames.ora `"oracledb:// (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xx.xx)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = gotest)))"` – Dmitry Demin Jan 21 '18 at 13:40
  • How many oracle clients are installed on the server? Check the value of the key ”ORACLE_HOME” in the registry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ ? – Dmitry Demin Jan 21 '18 at 14:19
  • I suppose that no, but have you a Visual Studio installed on that machine? Because with the VS Servers Explorer is easy to resolve. – felixmondelo Jan 21 '18 at 14:28
  • @DmitryDemin the send port won't accept the full description for the address - it errors with "is not a well-formed absolute uri" – Rob Bowman Jan 21 '18 at 15:13
  • @DmitryDemin the registry node is empty of value, just has a couple of empty sub nodes – Rob Bowman Jan 21 '18 at 15:17
  • Try using the sqlplus utility for diagnostics. `sqlplus user/password@test_godw_lincoln` – Dmitry Demin Jan 21 '18 at 15:25
  • Does the send port host account have the ORACLE_HOME env variable configured? – Ralph Jan 21 '18 at 22:37
  • Hi @Ralph, on this dev vm there is only one account. It's a member of the local admin group, the account I login with and the account the send port is using. It does have ORACLE_HOME=C:\app\biztalk.admin\product\12.1.0\client_1 – Rob Bowman Jan 22 '18 at 08:03
  • Upvoted for a very good description! Exactly my situation a few years later for BizTalk 2020. I resaved my tnsnames.ora as ANSI and restared hosts. – JERKER Nov 16 '20 at 10:18

3 Answers3

1

After all this changes, have you restarted your host instances?

Hichamveo
  • 475
  • 3
  • 16
  • Yes, I've restarted the host instance for the send port - no change :( – Rob Bowman Jan 22 '18 at 08:08
  • Important not to forget! This forces BizTalk to reload assemblies and their settings, potentially reloading oracle connection settings. Thanks for the input! – JERKER Nov 16 '20 at 10:17
0

Try also to connect without a tnsname, with connection string in this format : host:port/database_name

Hichamveo
  • 475
  • 3
  • 16
  • Yes I am able to connect using the alternative format but I need to know why I can't connect through tnsnames on this VM - whereas I can connect to the same database through tnsnames from a different VM. – Rob Bowman Jan 22 '18 at 08:05
  • This is because connection has to be made through tnsnames where ambient transactions are required. – Rob Bowman Jan 22 '18 at 08:17
  • 1
    Good. How about this env variable : TNS_ADMIN = /home/oracle/network/admin , it is already added? Try also to test your tns entry in an other environment to be sure is well formed(and change connection string data) – Hichamveo Jan 22 '18 at 08:21
0

This came down to a mysterious problem with the tnsnames.ora file. The suggestion from @Hichamveo helped but rather that try my bad tnsnames.ora file on a working environment, I copied the tnsnames file from an environment that worked. I found that the dev VM then started working - so I knew the problem was with the tnsnames.ora file rather than ODAC / ODP.Net assemblies.

I then spent quite some time trying to figure out exactly what was broken with the bad tnsnames file. I used file compare tools and tried a linter at: http://brantwills.github.io/tns-check/ but these showed no problems.

Rob Bowman
  • 7,632
  • 22
  • 93
  • 200