Back to overview

Online Magazine

How to easily manage names of (Oracle) databases

Every company with a larger number of databases benefits from a directory that stores the names of the databases. But how do you manage such a directory with as little effort as possible? For example, by using a 389 Directory Server. Let's install such a server together for an Oracle database.

 

by Stefan Oehrli

Both as a database administrator and as an end user, to access an Oracle database you need three things: its hostname, port and the database or service name. For smaller environments, Oracle users can easily access databases using a tnsnames.ora file. But in larger environments with multiple Oracle databases and hundreds of clients, managing and distributing the tnsnames.ora files becomes difficult – for example, if the administrator makes a change, they must notify everyone who wants to access the database's server individually. So what to do? I recommend an LDAP directory for easier administration.

This idea is not new and there are several approaches:

  • Using an Oracle Directory
  • Setting up Oracle Enterprise User Security (EUS)
  • Doing registration and administration in MS Active Directory
  • Using an LDAP server (389 Directory Server or OpenLDAP)

Especially the last method (although not officially documented and therefore not officially supported) has two decisive advantages:

  • With both the 389 Directory Server and OpenLDAP, it is comparatively easy to create a central directory for the Oracle Net Service names or tnsnames.ora. A stand-alone LDAP server is up and running in a matter of minutes. With a little more effort, secure SSL certificates, extended Directory Information Tree (DIT) with various suffixes, as well as replication, etc. can also be easily configured.
  • By using an open source LDAP server, costs remain manageable.


Let's look together step by step at how to install and configure a 389 Directory Server (Noob Hack) and how to create an Oracle context afterwards (Pro Hack).


Figure 1: LDAP based resolution of Oracle Net service names (sketch by Stefan Oehrli)

Noob Hack

389 Directory Server for name resolution.

Prerequisites and requirements

  • No specific hardware requirements
  • Recommendation: at least 2 CPU cores and 16 GB memory


Preparation

The software for the 389 Directory Server is available with Oracle Enterprise Linux 8 in the additional repository Oracle Linux 8 Application Stream as module 389-ds.


First we check and activate the module:

sudo yum module --available list 389-ds
sudo yum -y module enable 389-ds


Then we open the local firewall ports for LDAP and LSAPS:

sudo firewall-cmd --list-all --permanent --zone=public
sudo firewall-cmd --permanent --add-service=ldap --zone=public
sudo firewall-cmd --permanent --add-service=ldaps --zone=public
sudo firewall-cmd --permanent --add-port=9090/tcp --zone=public
sudo firewall-cmd -reload
sudo firewall-cmd --list-all --permanent --zone=public


With a specific GID, we create the group dirsrv and assign oracle as the operating system user to this group:

sudo groupadd --gid 520 dirsrv
cat /etc/group
sudo usermod -a -G dirsrv oracle

(This step is optional. Alternatively, you can run the administration of the LDAP server either as user root or always via LDAPS).


Installation

We have already activated the module 389-ds. With the command yum we install the packages and dependencies for the 389 Directory Server:

sudo yum install 389-ds-base sscg


Configuration

Now we start the configuration of the 389 Directory Server for the Oracle Net service. First, we create a directory server instance using a template:

sudo dscreate create-template /tmp/oraNet.inf.


Next, we customize the template, specifically the following values:

  • full_machine_name – fully qualified hostname of the server
  • instance_name – name of the LDAP instance
  • root_password – password for the root user of the LDAP server
  • suffix – base DN for the Directory Information Tree (DIT) of the LDAP server



Below, I will show you an example of setting up an instance named oraNet on the host ldap1.trivadislabs.com with the base DN dc=trivadislabs,dc=com:

[general]
full_machine_name = ldap1.trivadislabs.com
start = true
[slapd]
instance_name = oraNet
port = 389
root_password = Welcome1
secure_port = 636
self_sign_cert = true
self_sign_cert_valid_months = 24
[backend-userroot]
create_suffix_entry = true
suffix = dc=trivadislabs,dc=com


We create the instance as OS user root with the dscreate command:

sudo dscreate from-file /tmp/oraNet.inf


The dscreate command not only creates the instance, but also the corresponding start/stop scripts or a service. Whether the instance is running can be checked with the command dsctl. Alternatively you can check the status of the service directly with the command systemctl:

sudo dsctl -list
sudo systemctl status dirsrv@oraNet.service


Now we load the Oracle specific schema definitions. To do this, we copy the file 90orclNet.ldif into the appropriate instance directory and then restart the instance. This file contains the corresponding LDAP object classes and attributes which are required for Oracle Net Service Name objects:

curl -Lf https://url.oradba.ch/90orclNet -o /tmp/90orclNet.ldif
sudo cp /tmp/90orclNet.ldif /etc/dirsrv/slapd-oraNet/schema/90orclNet.ldif
sudo systemctl restart dirsrv@oraNet.service


We use the dsctl command to check the status of the LDAP instance again:

sudo dsctl $(dsctl --list) status
sudo dsctl $(dsctl --list) healthcheck


When executing the LDAP commands, you have to enter the password. If you don't want to do this interactively every time, you can save the password temporarily in a file and pass it to the LDAP commands with the parameter -y. The password file should be deleted afterwards:

echo "Welcome1" | tr -d '\n' >.oraNetDirectoryManager.pwd
chmod 600 .oraNetDirectoryManager.pwd


So far so good: The directory server is ready to receive information. If this already helps you, you can sit back now. But if you want to register the corresponding Oracle net service names, stay tuned: because for this you need an Oracle context, which we will establish in the "Pro Hack".

More tips and tricks from the world of data:

How to use the modulo operator for data analysis? Find out here.

How to achieve more privacy on your smartphone in 4 steps? Find the answer here.

Adding up orders from customers – how to do that when you need to take into account more orders than are visible in the selected period? Here's a concrete guide.

Pro Hack

389 Directory Server with Oracle context

To set up the Oracle context, we first create an object cn=OracleContext with class orclContext in base DN dc=trivadislabs,dc=com:

ldapadd -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: cn=OracleContext,dc=trivadislabs,dc=com
Object class: orclContext
cn: OracleContext
EOI


The Oracle clients usually perform the requests for Oracle Net service name resolution with an anonymous LDAP query or with an anonymous bind. In the default configuration, we have to enable this with appropriate ACI rules:

ldapadd -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: dc=trivadislabs,dc=com
changetype: modify
add: aci
aci: (targetattr!="userPassword||authPassword")(version 3.0; acl "Anonymous read access"; allow (read,search,compare) userdn="ldap:///anyone";)
EOI


Now that the empty 389 Directory Server is ready to capture Oracle Net service names, we can capture the entries, e.g. using the ldapadd command, LDAP Browser, etc. In the following example, we use the ldapadd command to add an entry with the name TDB02 and the corresponding Oracle Net description string:

ldapadd -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: cn=TDB02,cn=OracleContext,dc=trivadislabs,dc=com
Object class: top
Object class: orclNetService
cn: TDB02
orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19)(PORT=1521)) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TDB02.trivadislabs.com))(UR=A))
EOI


Modifying entries can be done with the Idapmodify command, ...

ldapmodify -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: cn=TDB02,cn=OracleContext,dc=trivadislabs,dc=com
changetype: modify
replace: orclNetDescString
orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TDB02.trivadislabs.com))(UR=A))
EOI


... searching with ldapsearch, ...

ldapsearch -h $(hostname -f) -p 389 -x -LLL -b "dc=trivadislabs,dc=com" -s sub "(&(objectclass=orclNetService)(cn=TDB0*))"


... and deleting with ldapdelete:

ldapdelete -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd \
cn=TDB03,cn=OracleContext,dc=trivadislabs,dc=com


Before we can use the LDAP-based Oracle Net service names, we still need to adjust the Oracle Net configuration. To do this, we need to adjust the name resolution order in $TNS_ADMIN/sqlnet.ora with the NAMES.DIRECTORY_PATH parameter and set LDAP to the first position:

NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, EZCONNECT )


In addition, we need to create the $TNS_ADMIN/ldap.ora file. In it, the LDAP server configuration is specified as follows:

DIRECTORY_SERVERS=(ldap1.trivadislabs.com:389:636)
DEFAULT_ADMIN_CONTEXT="dc=trivadislabs,dc=com"
DIRECTORY_SERVER_TYPE=OID


And tadaa: Oracle Net configuration is complete! With the command tnsping you can now check if the Oracle Net service name is resolved via LDAP or tnsnames.ora.

References

Oracle® Database, Database Net Services Reference 21c – Appendix B LDAP Schema for Oracle Net Services https://docs.oracle.com/en/database/oracle/oracle-database/21/netrf/index.html

Red Hat Directory Server 11, Product Documentation https://access.redhat.com/documentation/en-us/red_hat_directory_server/11

Oehrli Stefan (2022), GitHub Gist mit LDAP Schema für Oracle Net Services
https://url.oradba.ch/90orclNet

Oehrli Stefan (2022), GitHub Projekt oehrlis/389ds 389 Directory Server in Docker

Oracle® Database, Database Administrator's Reference 21c for Microsoft Windows - Using Oracle Database with Microsoft Active Directory, https://docs.oracle.com/en/database/oracle/oracle-database/21/ntqrf/index.html

Fedora 389 Directory Server, https://directory.fedoraproject.org

Your contact

HERE YOU CAN FIND MORE ARTICLES BY OUR DATA & AI EXPERTS:

TechTalk
Cloud Data Platform

TechTalk Audio: Data Engineer
TechTalk
Key Visual Tech Talk
Cloud Health Care

Privacy by Design: Protecting sensitive data
TechTalk
Key Visual Tech Talk
AI Health Care

How AlphaFold helps drug discovery