Connecting to Oracle Database Using Service Names

note

Configurations for connecting to an Oracle database using a service name are also covered as part of setting up SSL encryption for Oracle database connections, described in Setting Up SSL Connection to Oracle Databases.

By default, Key Manager Servers connecting to an Oracle database do so using the SID of the database instance. In some situations however, such as when running the Key Manager Database on a clustered Oracle database, you may want to connect using the service name instead.

When you are configuring Key Manager Servers to connect using a service name, you can specify the target service in one of the following ways:

Separately in tnsnames.ora.
Directly in localsettings.py.

Option 1: Specify the service in `tnsnames.ora`

Unless otherwise specified, these steps are to be performed on each Key Manager Server.

Open or create the tnsnames.ora file at the path /etc/sshmgr/oracle_config/tnsnames.ora.

In the tnsnames.ora file, specify your Oracle service similarly to the following:
```
# tnsnames.ora Network Configuration File

net_service_name =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.example.com)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = service_name)
        )
    )
```
For more information about configuring the tnsnames.ora, please refer to the documentation provided by the database vendor.

Save the file to /etc/sshmgr/oracle_config/tnsnames.ora.

Set the appropriate access rights for the tnsnames.ora file:

# chown sshmgr:nginx /etc/sshmgr/oracle_config/tnsnames.ora
# chmod 740 /etc/sshmgr/oracle_config/tnsnames.ora

Access the Key Manager Server settings file /opt/sshmgr/app/localsettings.py using a text editor. From the file, find the DATABASES section, which should look similar to the following:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.oracle',
    'NAME': 'dbname',
    'USER': 'username',
    'PASSWORD': 'example_password',
    'HOST': 'oracle.example.com',
    'PORT': '1521',
    'OPTIONS': { },
    'ATOMIC_REQUESTS': True,
  }
}

Specify NAME to the value net_service_name (this was set earlier in tnsnames.ora). Also, remove the HOST and the PORT parameters. The DATABASES section should look similar to the following after the changes:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.oracle',
    'NAME': 'net_service_name',
    'USER': 'username',
    'PASSWORD': 'example_password',
    'OPTIONS': { },
    'ATOMIC_REQUESTS': True,
  }
}

Save the changes to the file.

Set up environment variables to allow the Key Manager service to find your Oracle configuration files. Do this by appending the following lines to /etc/sysconfig/sshmgr (if you have not done so already):
```
# Environment variables for Oracle connectivity
ORACLE_HOME=/usr/lib/oracle/12.2/client64
TNS_ADMIN=/etc/sshmgr/oracle_config
export ORACLE_HOME
export TNS_ADMIN
```
Restart the Key Manager processes to apply the changes.

On all the Key Manager back ends, restart the back-end services:
```
# supervisorctl restart backend:
```
On all the Key Manager front ends, restart the Nginx web server:
```
# service nginx restart
```

Option 2: Specify the service directly in `localsettings.py`

Unless otherwise specified, these steps are to be performed on each Key Manager Server.

Access the Key Manager Server settings file /opt/sshmgr/app/localsettings.py using a text editor. From the file, find the DATABASES section, which should look similar to the following:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.oracle',
    'NAME': 'dbname',
    'USER': 'username',
    'PASSWORD': 'example_password',
    'HOST': 'oracle.example.com',
    'PORT': '1521',
    'OPTIONS': { },
    'ATOMIC_REQUESTS': True,
  }
}

Specify the connection parameters directly under NAME (similar to tnsnames.ora content). Also, remove the HOST and the PORT parameters. The DATABASES section should look similar to the following after the changes:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.oracle',
    'NAME': '(DESCRIPTION = (ADDRESS_LIST = \
             (ADDRESS = (PROTOCOL = TCP)(HOST = \
             oracle.example.com)(PORT = 1521)) ) \
             (CONNECT_DATA = (SERVICE_NAME = service_name) ) )',
    'USER': 'username',
    'PASSWORD': 'example_password',
    'OPTIONS': { },
    'ATOMIC_REQUESTS': True,
  }
}

Save your changes to the file.

Restart the Key Manager processes to apply the changes.

On all the Key Manager back ends, restart the back-end services:
```
# supervisorctl restart backend:
```
On all the Key Manager front ends, restart the Nginx web server:
```
# service nginx restart
```

Option 1: Specify the service in tnsnames.ora​

Option 2: Specify the service directly in localsettings.py​

Option 1: Specify the service in `tnsnames.ora`

Option 2: Specify the service directly in `localsettings.py`