Connect to Oracle from ArcGIS

Install an Oracle client on the ArcGIS client machine, set environment variables that reference the Oracle client, and connect to the database from the ArcGIS client.

To use your Oracle data in services published to ArcGIS Server, you must also register the database with ArcGIS Server.

Connect from ArcGIS for Desktop

To connect to a database or geodatabase in Oracle from ArcGIS for Desktop, install an Oracle client on the ArcGIS for Desktop computer, set the PATH environment variable to the location of the Oracle client, and create a connection file in the Catalog tree.

Install the Oracle client

To make a connection from a client machine to an Oracle database, you must install the Oracle client application on the client machine. Be sure you install a release of the Oracle client application that is compatible with the release of the database to which you want to connect.

Note:

If you are connecting from a 32-bit ArcGIS client, you must install a 32-bit Oracle client. This is true even if you install the ArcGIS client application on a computer with a 64-bit operating system (OS) and the database and server OS are 64-bit. If you are connecting from a 64-bit ArcGIS client, you must install a 64-bit Oracle client.

You can obtain the Oracle Instant, Runtime, or Administrator Client from Oracle, and install it on the client computer, following the directions in your Oracle documentation.

Set the PATH variable

Set the PATH environment variable on ArcGIS for Desktop to the location of the Oracle Client installation. See Microsoft Windows documentation if you need instructions for setting the PATH environment variable.

Note:

If both ArcGIS for Desktop and ArcGIS for Server are installed on the same Windows computer, set the PATH variable to read the 64-bit client before the 32-bit client. For example, if your 32-bit Oracle Instant Client is installed to c:\Program Files (x86)\Oracle and your 64-bit Oracle Instant Client is installed to c:\Program Files\Oracle, add the following to the beginning of your PATH variable value on Windows: C:\Program Files\Oracle;C:\Program Files (x86)\Oracle;.

If ArcGIS for Desktop was running on the machine before you set the PATH variable, restart ArcGIS for Desktop to pick up the new setting.

Connect to the database

You can connect to a database or geodatabase in Oracle by adding a database connection under the Database Connections node in the Catalog tree in ArcGIS for Desktop.

Tip:

If you have connection files that were created prior to ArcGIS 10.1, they will still work. However, if you need to create a connection to a 10.2.2 or older release geodatabase using an ArcSDE service, use the Create ArcSDE Connection File geoprocessing tool.

1. Expand Database Connections in the Catalog tree in ArcMap or ArcCatalog and double-click Add Database Connection.
2. Choose Oracle from the Database Platform drop-down list.
3. When connecting to Oracle, either type the Oracle TNS name in the Instance text box, or provide one of the following Oracle Easy Connect strings:
   • The name of the Oracle server/Oracle service name or ID

     For example, if Oracle is installed on myserver and myosvc is the Oracle service name, type the following:

     myserver/myosvc

   • The name of the Oracle server:Oracle port number/Oracle service name or ID

     In this example, Oracle is installed on myserver, is listening on port 60000, and myoservice is the Oracle service name.

     myserver:60000/myoservice

   • The URL of the Oracle server

     The URL for the same instance shown in the last example would be:

     //myserver:60000/myoservice

   • The name of the Oracle server (You can use this if the Oracle listener on the server is configured to point to a default instance.)
   • The IP address of the Oracle server/Oracle service name or ID

     For example, if the address of the server is 10:10:10:10, and the Oracle service name is orasvc, type 10:10:10:10/orasvc.

     For IPV6 addresses, place brackets around the address, for example, [4000:ab5:0:0:f666:d191:77f5:e2bd]/orasvc.

   • The IP address of the Oracle server:Oracle port number/Oracle service name or ID

     In this example, the IP address is 10:20:30:40, port is 59999, and Oracle service is myomy1: 10:20:30:40:59999/myomy1.

     An IPV6 address for the same port and service would look like the following: [6543:ef4:0:1:f587:l249:12f9:a3cd]:59999/myomy110:20:30:40:59999/myomy1.

   Be sure your Oracle instance is configured to allow Easy Connect. If you have the full Oracle client installed but want to use Easy Connect syntax to connect, be sure the sqlnet.ora file on the client is configured to allow the use of Easy Connect and the Oracle server is configured to allow Easy Connect syntax. Also note that if your Oracle instance is not listening on the default Oracle port number, you must use connection syntax that includes the port number.

4. Choose the type of authentication to use when connecting to the database: Database authentication or Operating system authentication.
   • If you choose Operating system authentication, you do not need to type a user name and password—the connection is made using the login name and password used to log in to the operating system. If the login used for the operating system is not a valid database login, the connection fails. Be aware that you cannot use operating system authentication if you are using the Oracle instant client.

     Note:

     When you use operating system authentication in Oracle, the operating system login is prefixed with an os_authent_prefix string (by default, OPS$) and stored in the USERNAME table. The maximum number of characters allowed for the user name when connecting to the geodatabase is 30. In these specific cases, ArcGIS will place quotes around your user name to pass it to the database. Those quotes count toward the 30-character total.

   • If you choose Database authentication, you must provide a valid database user name and password in the User name and Password text boxes, respectively. User names can be a maximum of 30 characters.

     Uncheck Save user name and password if you prefer not to save your login information as part of the connection; doing this can help maintain the security of the database. However, if you do this, you will be prompted to provide a user name and password every time you connect. Also note that Save user name and password must be checked for connection files that provide ArcGIS services with access to the database or geodatabase, or if you want to use the Catalog search to locate data accessed through this connection file.

   In the following example, a connection is made to the oracl/dbdata Oracle instance using database authentication.

   

5. Click OK to connect.

A file is created in \\<computer_name>\Users\<user_name>\AppData\Roaming\ESRI\Desktop<release#>\ArcCatalog. If you encounter any problems with the connection, check the sdedc_Oracle.log file in the %TEMP% directory for extended error messages.

You can move the connection to another location, but be sure users and applications that need to make a connection have read access to the directory where you place the connection file.

If you use the data from this connection as the source for a service, such as a geoprocessing or geocoding service, you may need to place the connection file in a shared location on your network. See Preparing resources for publishing as services for more information about sharing a connection file.

Connect to a user-schema geodatabase or different version

You are connected to the DEFAULT version of the master sde geodatabase when you initially connect using the Database Connection dialog box. If you want to connect to a user-schema geodatabase, a transactional version other than DEFAULT, or an historical version, use the Geodatabase Connection Properties dialog box. Note that user-schema geodatabases are listed as transactional versions in this dialog box. For more information, see Connect to a specific geodatabase version.

Connect from ArcGIS for Server

If Oracle and ArcGIS for Server are running on separate servers, install a 64-bit Oracle client on the ArcGIS for Server computer.

If ArcGIS for Server is installed on a Linux server, edit the init_user_param.sh script to include information about the Oracle client and instance and restart ArcGIS for Server.

If you want the services you publish to ArcGIS Server to use the data in your database or geodatabase in Oracle, you must register the database with ArcGIS Server.

Install the Oracle client

If ArcGIS for Server and Oracle are installed on different servers, you must install the Oracle client application on the ArcGIS for Server machine. Be sure you install a release of the Oracle client application that is compatible with the release of the database to which you want to connect.

You can obtain the Oracle Instant, Runtime, or Administrator Client from Oracle, and install it on the client computer, following the directions in your Oracle documentation.

Set the PATH environment variable (Windows only)

Set the PATH environment variable on the ArcGIS for Server Windows server to the location of the Oracle Client installation. See Microsoft Windows documentation if you need instructions for setting the PATH environment variable.

Note:

If both ArcGIS for Server and ArcGIS for Desktop are installed on the same Windows computer, set the PATH variable to read the 64-bit client before the 32-bit client. For example, if your 32-bit Oracle Instant Client is installed to c:\Program Files (x86)\Oracle and your 64-bit Oracle Instant Client is installed to c:\Program Files\Oracle, add the following to the beginning of your PATH variable value on Windows: C:\Program Files\Oracle;C:\Program Files (x86)\Oracle;.

If ArcGIS Server was running before you configured the Oracle client and set the PATH variable, you must restart ArcGIS Server. You can restart ArcGIS Server from the Windows Services interface.

Alter the init_user_param.sh script (Linux only)

Once you have installed the database client files, alter the init_user_param.sh script to reference them. You can access this script by browsing to the <ArcGIS Server installation directory>/arcgis/server/usr directory.

Note:

If your user profile references the Oracle client, ArcGIS Server should still connect to the database. However, if the client information differs between the user profile and init_user_param.sh, you may experience problems when attempting to connect to the database. To alleviate connection issues, remove the reference information from your user profile and re-reference your database client libraries using the following steps.

1. Ensure that the ArcGIS Server installation owner has at least read and execute permissions on the database client libraries.
2. Open the init_user_param.sh script in a text editor.
3. Remove the comment marks (#) from the lines beginning with export.

   If you installed the Oracle Administrator, Developer, or Runtime client, remove comment marks from all the Oracle lines beginning with export.

   #
   # To connect to Oracle
   #
   export ORACLE_BASE=<Oracle_Installdir>/app
   export ORACLE_HOME=$ORACLE_BASE/<Oracle_Release>/product/<Oracle_Version>/client_1
   export ORACLE_SID=<set when applicable>
   export TNS_ADMIN=<set when applicable. e.g.$ORACLE_HOME/network/admin>
   export PATH=$ORACLE_HOME/bin:$PATH
   export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
   

   If you are using the Oracle database Instant client libraries, you only need to uncomment and set the $LD_LIBRARY_PATH, for example

   #
   # To connect with Oracle Instant Client
   #
   export LD_LIBRARY_PATH=<Location_to_instantclient>:$LD_LIBRARY_PATH
   

4. Set values for the export variables.
   • For Oracle Administrator, Developer, or Runtime clients, uncomment and set the following variables using information specific to your implementation:

     • ORACLE_BASE=<Oracle_Installdir>

       The <Oracle_Installdir> is the path to and name of the top-level directory where the Oracle client is installed.

     • ORACLE_HOME=$ORACLE_BASE/<Oracle_Release>/product/<Oracle_Version>/client_1

       This is the path to the Oracle client library files. Set ORACLE_HOME to the directory where these reside. By default, the directory name is client_1, but your installation my have a different directory name.

     • If you will be using an Oracle system ID (SID) to connect, set ORACLE_SID= to the Oracle system ID of your database. If you will be using an Easy Connect string to connect, comment out this line.
     • Set TNS_ADMIN= to the location of your Oracle client TNS administration files. If you will be using an Easy Connect string to connect, you do not need to set this value and must comment out this line.
   • For Oracle Instant clients, uncomment and set only the LD_LIBRARY_PATH variable, replacing <Location_to_instantclient> with the path to and directory name where the Oracle Instant client files are located.
   • If ArcGIS for Server and Oracle are installed on the same server, uncomment and set the following variables:

     • ORACLE_BASE=<Oracle_Installdir>

       The <Oracle_Installdir> is the path to and name of the top-level directory where Oracle is installed.

     • ORACLE_HOME=$ORACLE_BASE/<Oracle_Release>/product/<Oracle_Version>/client_1

       This is the path to the Oracle library files. Set ORACLE_HOME to the directory where these reside. By default, the directory name is db_<n>, but your installation my have a different directory name.

     • If you will be using an Oracle system ID (SID) to connect, set ORACLE_SID= to the Oracle system ID of your database. If you will be using an Easy Connect string to connect, comment out this line.
     • Set TNS_ADMIN= to the location of your Oracle client TNS administration files. If you will be using an Easy Connect string to connect, you do not need to set this value and must comment out this line.
5. Save and close the script.
6. Restart each GIS server in your ArcGIS Server deployment by running the startserver.sh script.

   ./startserver.sh

Register the database

If you want your web services to use the data in your Oracle database or a geodatabase in Oracle, register it with ArcGIS Server. See About registering your data with the server for information on registration options and links to topics with instructions for registering your database.