Underlying DBMS Error ORA-12154 When Connecting to Geodatabase

[PaulWilner]

We are running Oracle 11G and Desktop 10.3.1. and Server 10.1 Server and desktop is on the same machine which is running a 64 bit Windows 7 Professional operating system, Oracle 11G is on another machine.

.

We originally only had desktop  connecting to our 11G database using the 32 bit client. This worked perfectly for quite some time. We were able to connect and use the databases with no problem. Recently, we decided we'd like to register our database with Server. We installed the 64 Bit Client on the machine and set the path variable to read the 64 bit client first but we are continuously getting the ORA-12154. error. We tried to connect to our Oracle database using Desktop 10.1.1 on another machine and this worked fine.

We followed the instructions here:

Register an Oracle database with ArcGIS Server—Documentation (10.3 and 10.3.1) | ArcGIS for Server

for a deployment with Server and Desktop on the same machine. However we are now finding we have no connectivity through either desktop or the server.

We are at our wits end here and would like any insight or help anyone can provide, so we're fairly certain the problem centers around the 64 bit client on the client machine.

We've seen some documentation that centers around reinstalling Desktop because the client has trouble reading from the program 86 directory because of parenthesis, but this documentation looked limited to 9.3

I'm not an oracle database expert but we have some people in the building that are fairly well versed. If anyone can provide even some insight that would be great.

[JoshuaBixby]

ORA-12154 is a naming or connection identifier resolution error.  How is your client name resolution lookup configured?  Are you using TNSNAMES, LDAP, EZCONNECT, etc...?

[George_Thompson]

Hi Paul,

To add to what Josh mentioned, what type of Oracle 64-bit client was installed?

Did you restart the ArcGIS Server service after installation of the Oracle client? This MUST be done for the Server service to re-read the PATH variable and use the Oracle client.

-George

Geodatabase​ Managing Data​

[PaulWilner]

Joshua/George, we are using TNSNAMES, however the tnsnames.ora file is actually on a shared drive that the Desktop/Server machine does have access to.

We are using 64 bit client version 11.2.

Any thoughts? I appreciate your help so far.

[George_Thompson]

Paul,

The Oracle client would need to have access to the TNSNAMES.ora file. This is something that your IT and DBA team should be able to remedy.

Are you using the full Oracle client or Instant Client?

[PaulWilner]

George:

I had our database folks over this morning. We  are using the full client. We moved tnsnames.ora to the local machine  in the following directory

C:\oracle\client_64\network\admin

and that seems to have solved the desktop connectivity issue but the GIS Server is still throwing the 12154 error.

Our Path variable has the 64 bit client first.

The Path variable string looks like this:

C:\oracle\client_64\bin;C:\oracle\client_11.2.0\bin;%CommonProgramFiles%\Microsoft Shared\Windows Live;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft Host Integration Server 2006\system;C:\Program Files\Microsoft Host Integration Server 2006\SysWOW64;C:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files\Microsoft\Web Platform Installer\;C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\

We are again at a bit of a standstill

[George_Thompson]

Do you have the TNS_ADMIN variable set to the new location? Here is the link for Server 10.1:

ArcGIS Help 10.1

Can you open SQLPlus from the Oracle 64-bit client install (%ORACLE_HOME%\BIN folder) and then connect to the Oracle database as one of the users? This is an IMPORTANT testing step!!!!!!

If you are using the 64-bit SQLPlus it is also have a sqlplus.exe running in the process list.

If you see it with a *32 at the end then there is a client configuration issue. I would make sure that you can connect outside of the ArcGIS client first.

You may have to open a support case with Esri Support to investigate in more detail. but it points more towards the Oracle client configuration for the 64-bit client used by ArcGIS for Server.

Hope this helps!

-George

[PaulWilner · Accepted solution]

Hi George:

We did manage to figure this out. I'll share in case it can help others.

From my IT team:

"In the tnsnames file, the first part of the entry is called the alias.  The alias for gisdvl is gisdvl.world.  I would have to look at the sqlnet.ora file in the 64 bit client to see what it says, but I bet it is looking for .world.  I have seen other instances where using the alias name instead of the database name makes a difference."

Thank you for your help and direction. Your input really did help us troubleshoot. I am going to mark this post as the correct answer, but I will be marking all your posts as helpful!