64/32 bit problem with external data connections

5185
9
02-26-2016 05:58 AM
scottandres
New Contributor

Have you has solved this problem?  I would appreciate hearing from you.  Specifics follow…..

I have a Win7 x64 machine with functional 64 and 32 bit Oracle 12.1.0.2 run-time clients complete with functioning ODBC and OLEDB drivers.  Did everything recommended by Microsoft, Oracle, and “BIG TIP for ArcGIS/ODBC/OLEDB users on 64 bit Windows” post on GeoNet.  ArcCatalog will not create a connection to Oracle. 

The 32 and 64 bit clients support SQL*Plus, SQL*Developer, and ODBC.     The 64 bit ODBC runs connections for 64 bit MS Office. Path and TNS_Admin environment variables are pointing to the appropriate home, bin and network/admin folders.  The 32-bit oracle client and ODBC connection have been named to clearly indicate that they are 32 bit.  This eliminates any confusion about what client is being invoked when configuring services.  The ODBCad32.exe in the syswow64 folder (see “BIG TIP…” post) creates a working connection to my oracle server. 

My oracle db is using TCP and TNSnames and default port 1521:

  # tnsnames.ora Network Configuration File: \app\client\asa32\product\12.1.0\client_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

db32 =

(DESCRIPTION =

    (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain1.edu)(PORT = 1521))

    )

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = db1.domain1.edu)

    )

  )

The add database connection only allows a choice of “Oracle” for the database platform box.  It does not display specific information about the ODBC connection or Oracle driver in the instance box. Database authentication is used for all.

The ArcCatalog add database connection dialog will not connect to the Oracle server with any variation tried so far:

When instance is defined as db1:  Failed to connect to the specified server.  Do you want to continue? Underlying DBMS error (unknown errorNo extended error.]

When instance is defined as db1.domain1.edu:  ORA-12154 (TNS:could not resolve the connect identifier…]

When instance is defined as the alias “db32”: ORA-12154 (TNS:could not resolve the connect identifier…]

Following the naming advice in the on line help instance set to “server1/db32”:  Failed to connect to the specified server.  Do you want to continue? Failure to access the DBMS server

I would appreciate hearing how you would fill in the entries of the Database Connection dialog given the tnsnames.ora shown above or if you think this post should be tagged differently. thanks!

0 Kudos
9 Replies
MichaelVolz
Esteemed Contributor

Is this setup being done to have 32 bit ArcGIS Desktop connections to Oracle and 64 bit ArcGIS Pro connections to Oracle on the same machine?

0 Kudos
scottandres
New Contributor

That was quick, thank you

I don't have ArcGIS Pro. This is for 32 bit ArcGIS connection to Oracle.

Scott Andres

Delaware Geological Survey

University of Delaware

Newark, DE 19716

302-831-0599

www.dgs.udel.edu

0 Kudos
George_Thompson
Esri Frequent Contributor

You only need the 32 bit client installed for ArcMap. Is the 32 bit client 1st or 2nd in the PATH variable (behind the 64 bit client).

Are the Oracle clients the full Admin clients? I am not sure about the run-time clients.

-George

Geodatabase

--- George T.
0 Kudos
scottandres
New Contributor

thanks

the 32 bit client is referenced first in the path and tns_admin

variables. that fix was in the first group of tricks that was tried.

i have a run time client. Do you have experience or seen interweb

resources that reference success with the admin client?

Scott Andres

Delaware Geological Survey

University of Delaware

Newark, DE 19716

302-831-0599

www.dgs.udel.edu

0 Kudos
George_Thompson
Esri Frequent Contributor

The Oracle Full Client (Admin) should have everything installed.

As an alternate for testing, you can install the 32 bit Oracle Instant client and add the folder to the PATH variable and test in ArcCatalog (after closing it down and re-opening it): ServerName/SID

--- George T.
0 Kudos
scottandres
New Contributor

Thanks,

I have also tried the 32-bit Oracle instant client with the ODBC, OLEDB,

and ODAC components.Installation and configuration logs generated by the

installer show everything was successful..net-based apps with an

explicit connection defined in ODAC calls did function as did tests

with *.udl and *.odc methods.But OBDCad32 did not show that the ODBC

driver was present so not a solution for Arc.Posts in OTN forum and

Oracle docs indicate that this is the way the instant client is supposed

to work.The uninstaller was used to remove this client. Checks of the

registry don't find any litter that would interfere later.

Scott Andres

Delaware Geological Survey

University of Delaware

Newark, DE 19716

302-831-0599

www.dgs.udel.edu

0 Kudos
MichaelVolz
Esteemed Contributor

Can you try removing the 64 bit Oracle client and just working with the 32 bit Oracle client?

0 Kudos
scottandres
New Contributor

Yes a 32 bit client without a co-existing 64 bit client has worked

everytime for years. The 64 bit office apps are more critical to my

workflow so I have done without the ArcMap connection.

Scott Andres

Delaware Geological Survey

University of Delaware

Newark, DE 19716

302-831-0599

www.dgs.udel.edu

0 Kudos
JamesGough1
Esri Contributor

ArcMap is a 32-bit application and you need to install the 32-bit version of Oracle client drivers like Oracle Instant client.

Then you need to add both the 64-bit and 32-bit oracle clients in your system path.  64-bit path first and followed by the 32-bit Oracle bin in the path.

You need to re-start ArcMap before attempting to connect so it can pick up the updated path variable.

The error you are getting says it can not find your netservice name in the tnsnames.ora file which should be located in the path you specified in the system TNS_ADMIN variable.

The name you specify for the for the Instance in the Oracle Database Connection dialog (should be db32) needs to match a Net Service Name in your tnsnames.ora file found in the TNS_ADMIN path.

db32 =

(DESCRIPTION =

    (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain1.edu)(PORT = 1521))

I can work with you on this in a meeting.

You can contact me at jgough@esri.com.

Thank You,
Jim Gough | Technical Lead Enterprise - Educational Services
Esri
0 Kudos