Oracle Names Resolution with ApacheDS

Introduction

Oracle supports the use of LDAP technology to resolve service names. Using a centralized LDAP directory greatly simplifies the configuration and maintenance required by applications to connect to Oracle databases. The directory contains entries that map net service names to connection strings. The benefits of using LDAP for Oracle names resolution are:

• Oracle connection data is stored and maintained in a centralized directory. Any changes to connection parameters DO NOT necessitate a change in application configuration files on client machines. Furthermore if an application uses a pooling technology that supports validation queries, the application will not need to be changed or restarted.

• It eliminates the need for need for the tnsnames.ora file. Oracle client apps will instead use the LDAP directory.

• LDAP alias entries offer an additional degree of flexibility that enable you to define an entry to point to another existing entry. 

Why ApacheDS?

Certainly Oracle Internet Directory can be used for names resolution.  But it's part of a full blown identity management system and might be overkill if all you're looking for is a centralized repository for tnsnames. (and you'll have to deal with Oracle licensing) How about OpenLDAP? If you're a Linux shop, OpenLDAP would be a good solution (see articles at http://www.dizwell.com/ and DBA Tips ).  However it only targets NIX systems. If you're an exclusive Windows shop, you could still use OpenLDAP but you'll have to use a version ported from the official NIX-based product.  Examples of these are OpenLDAP for Windows on SourceForge and another from UserBooster.  However using these products puts you at the mercy of the folks who maintain these ports (unless you want to do it yourself). Another solution would be to use an open LDAP solution that's supported on Windows out of the box.  One such solution is Apache Directory Server (ApacheDS).  It's a Java based product which needless to say runs on Windows. It can be easily managed with its ubiquitous sister product, Apache Directory Studio, and can run standalone or as an Eclipse Plugin.  And if your shop ever decides to support another OS, the solution is theoretically portable (disclaimer - I've only tested it on Windows).  It has a small footprint and performance is good.  The only drawback I've found is that the replication is little shaky but I'm anticipating that will improve in upcoming releases.

The following diagram depicts an ApacheDS LDAP server that resolves service names for a typical JEE application server and an Oracle client machine.

Installing ApacheDS

1. Install a Java JDK. The latest JDK can be downloaded from http://www.oracle.com/technetwork/java/javase/downloads/index.html. 
• DO NOT install the Public JRE.
• Set the JAVA_HOME system environment variable to the directory where the JDK was installed. For example, JAVA_HOME=C:\PROGRA~2\Java\jdk1.7.0_45 
2. Download the latest version of ApacheDS for Windows from http://directory.apache.org/apacheds/download/download-windows.html. 
3. The installation directions are included in that page. When you launch the installer, a wizard guides you through the installation process. Take the defaults except for the following: 
• Set the Server Home Directory to C:\ApacheDS 
• If the default JRE home directory is displayed, change it to the JDK installation directory from your Java installation. 

Installing Apache Directory Studio 

You'll use Apache Directory Studio to manage the Oracle names directory. Follow these instructions to install it: 

1. Download the latest version of Apache Directory Studio from http://directory.apache.org/studio/downloads.html 
2. Run the installer. Take the defaults except for the following: 
• If the default JRE home directory is displayed, change it to the JDK installation directory from your Java installation. 
3. Tip: After Apache Directory Studio is installed, you might want to make a shortcut to it on your desktop. 

Setting Up a Connection With Apache Directory Studio

1. Open up Apache Directory Studio.
2. In '''Connections''' view located on lower left hand of the perspective, right click and select '''New Connection...'''.
3. Enter a descriptive name for the connection, and then specify the server host name and port. Note that typically ApacheDS LDAP servers use port 10389.
   
   New Connection Screen:
   
   
   
   
   Click the ''Check Network Parameter'' button to insure that the connection was configured correctly. If everything is okay, click the ''Next >'' button.
4. The Authentication screen will display. Populate the text boxes as follows:
   • Bind DN or user:  uid=admin,ou=system
   • Bind password: secret
   Note that the above credential comes out the out-of-the-box from ApacheDS. Of course you'll want to change it.
   
   Authentication Screen:
   
   
   
5. Click "Next >" through the next couple of screens taking the defaults. Then click the ''Finished" button.

Adding Your Organization's Partition

In ApacheDS, partitions are used to organize directories into domains. Follow these steps to create a partition for your organization's Oracle names entries.

1. In Apache Directory Studio, right click on the connection your created in the previous steps. Then select Open Configuration.
2. Follow the directions outlined in this URL: [http://directory.apache.org/apacheds/basic-ug/1.4.3-adding-partition.html http://directory.apache.org/apacheds/basic-ug/1.4.3-adding-partition.html] In the Partition Details set the ID to your organization and the suffix to dc=[your organization],dc=com.
   Example - Adding a Partition:
   
3. Click File --> Save or click the ''Save'' icon in the menu bar.
4. IMPORTANT - You'll have to bounce the ApacheDS-default service on the server for the partition to go into effect.

Importing The Oracle LDAP Schema

To implement any open solution for Oracle LDAP, the Schema for Oracle Net Services must be imported into it. With the help of the schema files from this article on http://www.idevelopment.info, I was able to tweak them for use in ApacheDS.  For your convenience, these schemas definitions have been stored an LDIF file and can be downloaded from here.

The following instructions outline how to import these schemas into ApacheDS.

For more information on Orache LDAP schemas, see LDAP Schema for Oracle Net Services

1. Download the oraldapschema.ldif file from here.
2. In Apache Directory Studio, open the desired connection and expand and right-click on ou=schema. Then select Import --> LDIF Import....
3. For the LDIF File, select the oraldapschema.ldif file you downloaded in step 1. 
4. Click the "Finish" button. Your schema list should now contain oidbase, oidnet and oidrdbms.

Oracle LDAP Schemas in Apache Directory Studio Schema List:

Creating The Oracle Context

For the Oracle names resolution LDAP lookup to work, an OracleContext entry must be created. It will be the parent context for all DB entries.

1. There are a few ways to create a context entry in Apache Directory Studio. You can manually create one or import one from a LDIF file. For your convenience, an Oracle context LDIF file can be found here. Simply download it and change the domain components (dc) accordingly.
   Example Oracle Context Entry:
2. In Apache Directory Studio, Open the desired connection and right click on your organization's partition. For example, dc=foo,dc=com. Then click on Import --> LDIF Import....
3. For the LDIF File, select the LDIF file from step 1. (Note that if you have to redo this operation, you'll need to select the Overwrite existing logfile checkbox.) Your screen should now look something like this:
   
   
   
4. Click the "Finish" button.

Adding Entries

1. Right click on the OracleContext entry under your partition and select "New --> New Entry...".  The "New Entry" dialog box appears.
2. Select "Create entry from scratch" and click "Next >".
3. In the "Available object classes" box, select "orclNetService" (note that you might have to click the refresh button next to the text box for the "orcl" object classes to appear).  Click the "Add" button". You'll now see "OrcleNetService" and "top" appear in the "Selected object classes" box.  Then click the "Next >" button.  The "Distinguished Name" dialog box appears.
4. From the "RDN" dropdown (RDN=Relative Distinguished Name), select "cn".  In the text box next to the "=" sign, enter the net service name.  Then click the "Next >" button.  The "Attributes" dialog box will appear.
5. Add a new attribute by clicking the "New Attribute..." icon above the attributes grid. From the "Attribute type" dropdown list, select "orclNetDescString".  Then click the "Finish" button.
6. Double click in the value cell and enter the connection string.

After you're done, your entry should look something like this:

Adding Alias Entries

LDAP alias entries are useful in a lot of instances.  For example, they can be used in the development environment to easily change test databases without the need to modify the datasource configuration files. Here are the steps to add an alias in Apache Directory Studio:

1. Right click on the OracleContext entry under your partition and select "New --> New Entry...".  The "New Entry" dialog box appears.
2. Select "Create entry from scratch" and click "Next >".
3. In the "Available object classes" box, select "orclNetServiceAlias".  Click the "Add" button". You'll now see "alias", "OrclNetServiceAlias" and "top" appear in the "Selected object classes" box.  Then click the "Next >" button.  The "Distinguished Name" dialog box appears.
4. From the "RDN" dropdown, select "cn".  In the text box next to the "=" sign, enter the alias name.  Then click the "Next >" button.  The "DN Editor" dialog box will appear.
5. Click the "Browse..." button and drill down to the entry of the database you want to alias. Select it and click the "OK" button.  The selected DN will now appear in the attributes dialog as the value of the "aliasedObjectName" attribute. Your alias should now look something like this:
   
   
   
6. Click the "Finish" button.   If you don't see the alias under the cn=OracleContext tree, right-click on cn=OracleContext.  Then click on Fetch --> Fetch Aliases.

Importing Tnsnames.ora

The Apache Directory Project provides a Groovy LDAP library that provides an API to the LDAP directory.  Using this API, a tnsnames.ora file can be easily imported into the directory using a Groovy script.

Installing Groovy and ApacheDS Groovy Module

1. Download and install Groovy from http://docs.codehaus.org/display/GROOVY/Download and set a GROOVY_HOME environment variable. 
2. Download the ApacheDS Groovy Module from http://directory.apache.org/api/groovy-api/1-groovy-ldap-download.html and copy the dist/groovy-ldap.jar to your ${GROOVY_HOME}/lib folder.

Example Tnsnames.ora Import Script

Note that the credentials for accessing the directory are stored in a jndi.properties file and should be installed in the classpath.

jndi.properties file:

Using Oracle LDAP

Oracle Client Applications

The files needed for LDAP for Oracle names resolution are located in the ${ORACLE_HOME}/NETWORK/ADMIN directory. Namely, ldap.ora and sqlnet.ora. 

ldap.ora

sqlnet.ora

For troubleshooting, use:

JDBC Applications

The JDBC URL contains the distinguished name of the LDAP entry record. The following example shows the JDBC URL for mydb:

Replication 

To implement replication between multiple servers, one server will assume the role of the provider or master. The other servers will be consumers of the master data.

Warning: Replication in ApacheDS is not fully documented and doesn't appear ready for prime time. Use with caution.

Provider Server

1. In Apache Directory Studio, open the connection for the server that will be the provider (master) server.
2. Navigate to Root --> ou=config --> ou=servers --> ads-serverId=ldapServer and select that entry by clicking on it.
3. In the attributes screen, right-click and select New Attribute...
4. In the Attribute type dropdown, select ads-replReqHandler. Then click the "Finish" button.
5. In the value column next to the newly created ads-replReqHandler attribute enter: org.apache.directory.server.ldap.replication.provider.SyncReplRequestHandler
   

After you're done, your screen should look like this:

Consumer Servers

1. In Apache Directory Studio, right click on the connection for the server that will a consumer server and click on Open Configuration.
2. Select the Replication tab located towards the bottom right hand corner of the Overview screen.
3. The Replication screen will appear. Click on the "Add" button.
4. Enter the consumer information as follows:

   Replication Consumer Details 

   • Select Enabled
   • ID: The short name to identify this consumer.
   • Description: (optional) enter the description for this consumer

   Connection 

   • Replication Mode: accept defaults
   • Remote Host: The host name of the provider (master) server.
   • Remote Port: The port of the provider (master) server.
   • Bind DN: The distinguished name (user id) used to connect to the provider server.
   • Bind Password: The password for the Bind DN user.
   • Size Limit: 0
   • Time Limit: 0

   Replication Consumer Details 

   • Base DN: Use the "Browse..." to select cn=OracleContext,dc=foo,dc=com.
   • Filter: (objectClass=*)
   • Scope: Subtree
   • Attributes: Check All Attributes
   • Aliases: leave unchecked
   • Dereferencing: leave unchecked
5. Save the configuration by clicking File --> Save.

Your configuration should look something like this:

Integration Testing Using The Maven Tomcat Plugin And JaCoCo Code Coverage

One of the frustrating things about using Maven and its many plugins is often they lack some type of key functionality that you would think would be a no-brainer to include.  Take for instance the Tomcat Maven Plugin - One would think that its fork option would spin up a truly forked instance of Tomcat and accept JVM arguments such as -Xmx, -XX:MaxPermSize, etc.  Disappointingly, the Tomcat plugin merely runs Tomcat in the same process as the Maven command and is incapable of accepting JVM arguments directly (BTW, the maven-jetty-plugin does this very well with its run-forked option).  The only way to provide them is through the MAVEN_OPTS environment variable before the Maven command is executed.  This might be no big deal while working in your local environment but what if you want your continuous integration system to run Tomcat integration tests as part of a build pipeline?  In Jenkins or Hudson, you can include a shell command to set MAVEN_OPTS.  That might work well if all you want to do is set a max memory parameter.  But what if you want to report code coverage statistics using the JaCoCo Maven plugin?  Because the Maven Tomcat plugin doesn't have a way to accept JVM arguments, you'll have to find a way to capture the JaCoCo Java Agent JVM argument and set it in the MAVEN_OPTS variable.  Here's the steps I took in order to accomplish that.  Understand that it will take multiple commands to accomplish this.  Admittedly it's a bit clunky - here goes...

1. First, you'll need to generate a command that sets the MAVEN_OPTS environment variable.  For this I use a special Maven profile that employs the jacoco-maven-plugin and the exec-maven-plugin to generate a command file to be run before the verify goal of the build process.  I'm binding the goals to the clean phase.  I chose that phase because I wanted to insure that this command will be successfully generated before any of the subsequent Maven commands.  Shown below is an example of this profile. Here's an example of the Maven command using this profile:
   mvn clean -Pjacoco-maven-opts
   Note that this generates a simple Windows DOS command named mvnopts.bat (you could easily modify this to run a command suited for Linux). Shown below is an example of the generated mvnopts.bat command:
2. The preceding mvn command generates the mvnopts.bat command file in the project's ${basedir} directory which where the pom.xml is located. Simply run the command from that directory to update MAVEN_OPTS. For example:
   C:\myproject\mvnopts.bat
3. After you execute the mvnopts.bat command, your environment will be ready for running a JaCoCo aware Maven verify phase. You'll want to use the maven-failsafe-plugin and bind the tomcat7-maven-plugin run and shutdown goals to the pre-integration-test and post-integration-test phases respectively. It might be helpful to encapsulate the build plugins for Tomcat integration into a profile too.  The following is an example of such a profile:
4. Note that the Tomcat plugin should be defined in the <build> section as follows: Notice the fork=true option. As stated above, the fork option does not fork a new JVM. Rather it is run in a thread created by the Maven Launcher class (org.codehaus.plexus.classworlds.launcher.Launcher) that is invoked by the mvn command.
5. To run your Tomcat integration tests, you would run the following command:
   mvn verify -Ptomcat-it-coverage
   The following JVisualVM screen shots show the JaCoCo JVM argument provided by MAVEN_OPTS and the Tomcat thread spawned by the Maven Launcher:
   
   
   
   

Sample Jenkins Build Job Script:

Now that you have these Maven commands worked out, you can use them a Jenkins job. I like to run my Maven test stuff in a "free-style software project" rather than a "maven2/3" type of project. With a free style project, you have greater flexibility mixing Maven and other types of commands and metrics gathering is more consistent.  Simply enter the commands you wish to execute in a "Windows batch command" text box.  Here's an example using the commands described above: