DBA Tips Archive for Oracle
No Title[an error occurred while processing this directive]
by Michael New, MichaelNew@earthlink.net, Gradation LLC
Anyone who has ever worked with the Oracle Database is most likely familiar with the tnsnames.ora file. The tnsnames.ora file is a text file that contains client side definitions for net service names, sometimes call aliases, needed to log in to an Oracle instance. This provides local name resolution by mapping net service names to connect description information for Oracle instances; similar to how the local hosts file for an operating system maps machine names to IP addresses. The tnsnames.ora file is commonly found in its default location under the ORACLE_HOME/network/admin directory.
One of the advantages of using a local tnsnames.ora file is that it is easy to create and edit entries. This is especially true when there are only a few entries to maintain and the file only needs to be distributed to a small number of Oracle client machines. The primary disadvantage of local naming is when there are a large number of client machines on the network, say 100 or even a 1000 clients, each required to have their own tnsnames.ora file. This problem gets compounded when frequent changes to the tnsnames.ora are required as a result of adding or moving database and the file has to be re-distributed to those clients.
A more efficient solution is to centralize the list of database names in a repository that every Oracle client can access. In this article, I will demonstrate how to configure Oracle database naming in an LDAP directory. LDAP is for tnsnames.ora what DNS is for a local hosts file like /etc/hosts. Similar to how the local tnsnames.ora file works, when a client performs a lookup for a net server name in LDAP, it is given the appropriate connect descriptor information for the Oracle instance or service name.
Support for net service name resolution through LDAP started in Net8 with Oracle database release 8.1.6. Note that the name Net8 was changed to Oracle Net in Oracle9i. Oracle Net runs on both the client machine and the database server and allows transparent database communication over a network. Before LDAP, Oracle Names was the service used to support a centralized naming service and a way to eliminate the local tnsnames.ora file on client machines. Although this technology worked well, it was a proprietary solution that didn't offer the integrated solution with other standards based naming services like directory naming found in LDAP. Oracle9i was the terminal release of Oracle Names and Oracle strongly recommends users to migrate to directory naming using LDAP.
The LDAP implementation used in this guide is OpenLDAP Software running on the CentOS 5 platform. This guide assumes the LDAP directory is already configured and running on a machine named ldapsrv.idevelopment.info. The LDAP directory used in this guide has been initialized with a base DN of dc=idevelopment,dc=info. Obviously, the name of your LDAP server and the base DN will differ and the examples presented in this guide will need to be modified accordingly for you environment. Refer to the following two tutorials on how to install OpenLDAP Software and initialize the LDAP directory on the server.
Although Oracle provides an officially supported LDAP implementation named Oracle Internet Directory (OID), it is part of a much larger and complex Identity Management software product that is integrated into their Oracle Fusion Middleware and Oracle Applications. Since I am using the LDAP directory for nothing more than database name lookups, this option would be extreme overkill. Not to mention that OpenLDAP is open source and comes at a total cost of $0.00 while OID will set you back a good chunk of change.
The process starts with configuring the OpenLDAP directory with similar capabilities of Oracle Internet Directory (OID). This involves importing four Oracle specific schema specification files that implement the structure of a net service name entry and the connect descriptor information that should be returned to the client that asks for the name to be resolved.
First, create the following directory on the LDAP server:
Next, download the four schema specification files to the newly created directory. These files were taken from the OID schema objects and slightly modified to work with OpenLDAP.
Open the main configuration file for the OpenLDAP server (/etc/openldap/slapd.conf) and add links to the OID schema files.
After making modifications to the configuration file, restart the LDAP server daemon.
Finally, create the following LDAP record that defines the orclContext object class. To do this, create a new LDIF file with the following contents. Modify the example entry below to match your base DN.
Use the ldapadd command to load the new object class to the directory.
The next step is to setup the Oracle Net configuration files on the client for net service name resolution through LDAP. The client in this case is the machine being used to connect to the Oracle instance. The client machine will need to have the Oracle Client or Oracle Database software installed which includes the Oracle Net software.
Start by specifying the naming methods through the NAMES.DIRECTORY_PATH parameter in the ORACLE_HOME/network/admin/sqlnet.ora file on the client machine. The keyword for directory naming is LDAP. The following parameter setting will configure a client to attempt name resolution through the local tnsnames.ora file first, and then through the LDAP directory.
If your client configuration will not include a tnsnames.ora file for local name resolution, remove the TNSNAMES keyword and only include LDAP.
Next, create a new file in the same directory named ORACLE_HOME/network/admin/ldap.ora. This file will contain information necessary to connect to the LDAP server and includes three entries similar to the following. Modify the contents described below to the name of your LDAP server, LDAP listening port, and the default administrative context within your LDAP directory.
Even though the LDAP implementation used in this guide is OpenLDAP, we still need to set the DIRECTORY_SERVER_TYPE parameter to OID for Oracle Internet Directory.
The default administrative context specified in the ldap.ora file (DEFAULT_ADMIN_CONTEXT) is the LDAP equivalent of the default Oracle Net domain name (NAMES.DEFAULT_DOMAIN) specified in the sqlnet.ora file. Since there is no strict format for the naming structure in the target LDAP domain, the DEFAULT_ADMIN_CONTEXT provides a mechanism to specify the context in which unqualified net server names are resolved For example, consider the default administrative context used in this guide of "dc=idevelopment,dc=info". Oracle Net will create a fully distinguished name (dn) from an unqualified net server name which will then be passed to the LDAP directory as shown in the following example:
In the above example, Oracle Net will translate the unqualified net server name (testdb1) to a fully qualified distinguished name (dn: cn=testdb1,cn=OracleContext,dc=idevelopment,dc=info) which is then passed to the LDAP directory. If a match is found, the LDAP server then returns the connect descriptor information to the client which will be used to initiate a connection to the specified Oracle instance or service name.
At this point, the LDAP directory knows what the structure of an Oracle net service name record should look like and the Oracle client knows how to access the LDAP server. The next step is to add a new Oracle net service name record to the LDAP directory. The method used in this section is to create a template record in the LDAP Data Interchange Format, or LDIF, file format and load it using the LDAP client tool ldapadd.
Create an LDIF file named new-netservicename.ldif with information similar to the following example:
The last line of the example above should look familiar if you have ever managed entries in the tnsnames.ora file. The new record will be stored within the OracleContext schema that was created earlier in this guide.
After modifying the example record for your environment, add it to the directory using ldapadd.
Test that the Oracle client is able to resolve the net service name through LDAP using an unqualified and fully qualified net service name.
In addition to adding new entries, any existing LDAP record can be modified by creating the appropriate LDIF file and using the ldapmodify command. For example, if the testdb1 database moved to another host, say newtestnode1, create an LDIF record similar to the following:
Update the directory record using ldapmodify.
Test the new record from the Oracle client.
Use the ldapdelete command to delete a record (an Oracle net service name entry) from the LDAP directory.
After adding or modifying a few of these net service name records using LDIF, it shouldn't be long before you start asking yourself whether or not there is a more efficient method for managing directory entries. Your first thought may be to fire up the Oracle client tool Net Manager. Unfortunately, Net Manager and many other Oracle client tools only work with Oracle Internet Directory; not OpenLDAP or any other generic LDAP server. Although I was able to successfully authenticate to the OpenLDAP directory using Net Manager, I was not able to add, view, or modify any of the directory entries in the OracleContext schema.
There are several free LDAP admin products that you might find useful; some of which are listed below.
My personal favorite is phpLDAPadmin (also known as PLA). phpLDAPadmin is a free and popular web-based LDAP client that is easy to install and customize. Its hierarchical tree-viewer and advanced search functionality make it intuitive to browse and administer an LDAP directory. Since it is web-based, the LDAP browser works on many platforms, making your LDAP server easy to manage from any location.
Use the following instructions to install the phpLDAPadmin software along with PHP and the Apache HTTP Server. For the purpose of this example, I installed the phpLDAPadmin web application on the same server hosting the OpenLDAP directory (ldapsrv.idevelopment.info).
If the application server already has the Apache HTTP Server installed, this section may be skipped. Make certain that the Apache HTTP server is started and configured to launch on boot.
The Apache HTTP Server can be installed on CentOS, RedHat Enterprise Linux, or Oracle Linux using the following:
Install Apache HTTP Server.
Set the Apache service to start on boot.
Manually start the Apache HTTP service.
If the php and php-ldap packages are already installed on the application server, this section may be skipped.
Install php package.
Install php-ldap package.
The php-ldap package is a dynamic shared object (DSO) for the Apache Web server that adds Lightweight Directory Access Protocol (LDAP) support to PHP and is required by phpLDAPadmin.
After verifying that the Apache HTTP Server is running and accessible, download and install phpLDAPadmin.
Verify that your LDAP server (ldapsrv in this example) provides access to the schema using an anonymous bind. Ensure that you can see your schema without having to bind to the LDAP server. For example:
Verify any additional pre-requisites.
Download the current version of phpLDAPadmin (phpldapadmin-1.2.2.zip at the time of this writing) to the application server.
Unpack the phpLDAPadmin archive.
Put the resulting 'phpldapadmin' directory anywhere in your DocumentRoot (i.e. /var/www/html).
In the 'phpldapadmin' config directory (i.e. DocumentRoot/phpldapadmin/config) copy 'config.php.example' to 'config.php' and modify any settings in config.php to customize the phpLDAPadmin environment.
The only modification I made to the config.php file was to $servers->setValue:
Restart the Apache HTTP service.
Managing LDAP records using the phpLDAPadmin web interface is fairly self-explanatory. In this final section, I'll provide the methods I use to manage net service names in OpenLDAP using phpLDAPadmin. I will provide two custom templates that I wrote which makes creating and modifying net service names in phpLDAPadmin simple and straightforward.
Download the following Create Template that will provide a custom interface used to create new Oracle net service names in phpLDAPadmin.
Copy the orclNetService.xml file to the DocumentRoot/phpldapadmin/templates/creation directory on the application server.
Download the following Modification Template that will provide a custom interface used to modify existing Oracle net service names in phpLDAPadmin.
Copy the orclNetService.xml file to the DocumentRoot/phpldapadmin/templates/modification directory on the application server.
Open your web browser and navigate to the phpLDAPadmin web application.
Log in to your LDAP directory from the phpLDAPadmin home page.
From the base DN (left pane), expand your directory tree and navigate to the OracleContext schema. If you have any databases registered, click on the net service name (cn=<tns_alias>) to view or modify that entry.
If you installed the two templates described in the previous steps, this will bring up a page (right pane) that allows you to choose which template to edit the entry with. In the example below, the choices are:
Oracle: Net Service Name
(our custom Modification Template)
(phpLDAPadmin default modification screen)
Select the 'Oracle: Net Service Name' template.
Use this template to modify the Connect Descriptor attribute for the selected net service name. Notice in this form that the Net Service Name field is read-only.
It should be noted that it is not required to use the custom modification template (Oracle: Net Service Name) to update the net service name entry. If you selected the Default template, the entry form provides all updatable fields and the actual name of the attributes (as opposed to the titles I used in place of the attribute names).
To create a new net service name, click the Create new entry here option under the OracleContext schema.
Just as we saw when modifying an entry, a list of available templates will be listed which can be used to create a new entry. Since we will be creating a net service name entry, select the custom template Oracle: Net Service Name. Remember, you can always select the Default template which provides a form with attributes that are a candidate for being updated.
The Oracle: Net Service Name template makes it easy to enter a Net Service Name and the Connect Descriptor.
After adding the new net service name, jump on to the Oracle client machine and try to log on to the database through LDAP.
One of the first Oracle client tools I attempted to configure to use LDAP for name resolution was SQL Developer. Unfortunately, just like with the Oracle Net Manager client, SQL Developer only works with Oracle Internet Directory (OID).
Selecting the LDAP Connection Type in the New / Select Database Connection wizard, SQL Developer will return an error after choosing the LDAP Server from the pull-down selection.
Looking at the log file for the LDAP server shows that SQL Developer sent an LDAP request that didn't match the OID LDAP structure that we improvised in OpenLDAP. The request sent by SQL Developer was expecting a standard OID LDAP structure that didn't match our implementation.
The workaround is to create an Advanced Connection Type with a Custom JDBC URL similar to the following:
where ldapsrv:389 is the name of the LDAP server:port, testdb1 is the net service name stored in LDAP, and cn=OracleContext,dc=idevelopment,dc=info is the entry location in the directory.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Wednesday, 03-Jul-2013 10:49:12 EDT
Page Count: 560