Connect to Oracle from ArcGIS

To publish ArcGIS Server web services that reference the data in an Oracle database, complete the following:

Install an Oracle client on the ArcGIS Server machines.
Set environment variables that reference the Oracle client.
Set environment variables that reference the Oracle client.
Alter the init_user_param.sh script.
Create a database connection file.
Grant privileges to the login in the connection file.
Register the database connection file with the ArcGIS Server sites.

Install Oracle client software

Oracle client software must be installed on all machines in the ArcGIS Server site to which you will publish. If you do not have the privileges to install software on the machines where ArcGIS clients are running, you'll need to have your IT department install and configure the Oracle client for you.

Install a release of the Oracle client application that is compatible with the release of the database to which you need 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.

Use the information in the following table to determine the appropriate Oracle client configuration to use:


Installation combinations	Configuration to use
ArcGIS Server (single-machine site) and Oracle are installed on the same server; ArcGIS Pro is installed on different machines.	Install a 64-bit Oracle client on all ArcGIS Pro machines, and set the PATH variable to the Oracle client home directory. You do not need to install an Oracle client on the server because files to connect are installed with the Oracle database management system. Ensure the LD_LIBRARY_PATH (Linux) or PATH (Microsoft Windows) system variable is set to the Oracle home directory. If you set a tnsname for the Oracle client, you must use the same name on the ArcGIS Server and ArcGIS Pro machines.
ArcGIS Server is installed on one or more machines that are separate from the DBMS. ArcGIS Pro is installed on computers separate from both Oracle and ArcGIS Server.	Install a 64-bit Oracle client on all ArcGIS Pro machines, and set the PATH variable to the Oracle client home directory. Install the 64-bit Oracle client on all ArcGIS Server machines and ensure the PATH (Windows) or LD_LIBRARY_PATH (Linux) system variable is set to the Oracle client home directory. If you set a tnsname for the Oracle client, you must use the same name on the ArcGIS Server and ArcGIS Pro machines.
ArcGIS Server and ArcGIS Pro are installed on the same server; Oracle is installed on a different server.	Install the 64-bit Oracle client on all ArcGIS Server machines. Both ArcGIS Server and ArcGIS Pro will use this client. Set the PATH system variable to the Oracle client home directory.
ArcGIS Server, ArcGIS Pro, and Oracle are installed on one machine.	ArcGIS Server and ArcGIS Pro will use the 64-bit client files installed with the Oracle database management system to connect. Set the PATH variable to the Oracle client home directory. If your ArcGIS Server site includes additional servers, install the 64-bit Oracle client on each of the servers in the site, and set the PATH variable to the Oracle client home directory.

Set the LD_LIBRARY_PATH environment variable (Oracle Administrator, Developer, or Runtime client only)

If you installed the Oracle Administrator, Developer, or Runtime client, set the LD_LIBRARY_PATH system variable to the Oracle client home directory.

If you installed the Oracle Instant client, set the LD_LIBRARY_PATH variable in the init_user_param.sh script.

Alter the init_user_param.sh script

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

You must update the init_user_param.sh on every machine in the ArcGIS Server site.

Note:

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

Ensure that the ArcGIS Server installation owner on each machine has at least read and execute permissions on the database client libraries.
Open the init_user_param.sh script in a text editor.

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.

#
# For connection with Oracle Runtime or Administrator Client
#
export ORACLE_BASE=<Oracle_Installdir>/app
export ORACLE_HOME=$ORACLE_BASE/<Oracle_release>/product/<Oracle_version>/<client_version>
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:

#
# For connection with Oracle Instant Client
#
export LD_LIBRARY_PATH=<Location_to_instantclient_11_2>:$LD_LIBRARY_PATH

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>
    
    <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 may 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 Server and Oracle are installed on the same server, uncomment and set the following variables:
  - ORACLE_BASE=<Oracle_Installdir>
    
    <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 may 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.
Save and close the script.
For the changes you made in the init_user_param.sh file to take effect, you must restart ArcGIS Server.

To do this, run the stopserver.sh script, then run the startserver.sh script on each machine in the ArcGIS Server site.

./stopserver.sh

./startserver.sh

Set the PATH environment variable (Windows only)

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

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.

Create a database connection

You must have a database connection (.sde file) that authenticates the connection to the database.

You can call the ArcPy CreateDatabaseConnection function from one of the machines in the ArcGIS Server site that needs to connect to the database. See Create Database Connection in the ArcGIS Pro help for information about running this function.

The following information is specific to creating a database connection file for use with ArcGIS Server:

You must save the user information with the connection file.
If you choose to use operating system authentication, use a domain account for the ArcGIS Server account and add the domain account to the Oracle database.
If you use a TNS connection, ensure you use the same net service name in the tnsnames.ora file on all the machines in the ArcGIS Server site and the ArcGIS Pro clients from which you publish.

Grant privileges

The database user, role, or ArcGIS Server account (if you're using operating system authentication) with which you connect to the database must be granted privileges in the database to access the data to be published. The privileges and the circumstances under which you need to grant them are as follows:


Privilege	Reason
`CREATE SESSION`	Required to connect to the database.
`CREATE TABLE`	Selection sets cause a log file table to be created in the geodatabase. If a user does not have this privilege, log file tables will be created in the sde user's schema.
`SELECT` on the datasets to be published	Grant the user or ArcGIS account at least the `SELECT` privilege on the datasets to be published.
`INSERT`, `UPDATE`, or `DELETE` privileges on data published to editable feature services	To edit data through a feature service, privileges sufficient to edit must be granted on the datasets.

The database administrator must grant the first two privileges listed above. If the data is in a geodatabase, the data owner must grant the required privileges on the datasets. If the data is in a database, the data owner can grant privileges on datasets from ArcGIS or the database administrator can use database tools to grant the required privileges.

See Privileges for geodatabases in Oracle or Privileges for using ArcGIS with an Oracle database for more information.

Register the connection file

To allow ArcGIS Server sites to access the data, use the database connection file you created to add a registered data store in ArcGIS Pro or add a data store item in the portal.