Friday, August 9, 2013

TNS lookup over LDAP

This article describes how to migrate tnsnames.ora into LDAP. My current tnsnames.ora looks like this:

$ cat tnsnames.ora

When I run a tnsping for PINK then I get the following answer:

$ tnsping PINK
Used TNSNAMES adapter to resolve the alias
OK (0 msec)

It clearly indicates that tnsping uses the TNSNAMES adapter. To start with migrating the tnsnames.ora into LDAP you need to prepare your LDAP first. As usual I am using OpenLDAP as LDAP server running on a Solaris 10 X86 VM. The first thing you need are two new schema files. I am using the sbs files from Oracle which are available when Oracle DB is installed. Create a folder for storing the new schema files:

# mkdir -p /opt/openldap/latest/etc/openldap/schema/oracle
# cd /opt/openldap/latest/etc/openldap/schema/oracle

Then create the two new schema files. The sbs files can not be used directly why I am using the following grep and sed commands:

# grep "^attributetypes" /u01/11.2.0/db_1/ldap/schema/oid/ | sed 's/attributetypes:/attributetype/g' > oidbase.schema
# grep "^objectclasses" /u01/11.2.0/db_1/ldap/schema/oid/ | sed 's/objectclasses:/objectClass/g' >> oidbase.schema
# grep "^attributetypes" /u01/11.2.0/db_1/ldap/schema/oid/ | sed 's/attributetypes:/attributetype/g' > oidnet.schema
# grep "^objectclasses" /u01/11.2.0/db_1/ldap/schema/oid/ | sed 's/objectclasses:/objectClass/g' >> oidnet.schema

The above lines will create the two schema files oidbase.schema and oidnet.schema with the extracted attributetype and objectClass information from the two sbs files. Add the two schema files to your slapd.conf:

# vi /opt/openldap/latest/etc/openldap/slapd.conf
include         /opt/openldap/latest/etc/openldap/schema/oracle/oidbase.schema
include         /opt/openldap/latest/etc/openldap/schema/oracle/oidnet.schema

And restart the slapd daemon eg.:

# kill -15 `pgrep slapd`
# /opt/openldap/latest/libexec/slapd -h ldap://

Check that the slapd is running again. Then create the following ldif file with a container for your Oracle TNS:

# vi tns.ldif
dn: ou=tns,dc=karellen,dc=local
objectClass: organizationalUnit
objectClass: top
ou: tns

dn: cn=OracleContext,ou=tns,dc=karellen,dc=local
cn: OracleContext
objectClass: orclContainer
objectClass: top

dn: cn=PINK,cn=OracleContext,ou=tns,dc=karellen,dc=local
cn: PINK
objectClass: orclService
objectClass: top
orclOracleHome: /u01/app/ora11/product/11.2.0/db_1
orclSid: PINK

The orclNetDescString entry is nearly the same from the tnsnames.ora (see above) except for the leading SID PINK=. Finally add the ldif file to your LDAP:

# ldapadd -x -W -D 'cn=ldapadmin,dc=karellen,dc=local' -h -f tns.ldif

For the next steps you need to configure Oracle itself. Start with creating the ldap.ora file like this:

$ vi /u01/11.2.0/db_1/network/admin/ldap.ora
DEFAULT_ADMIN_CONTEXT = "ou=tns,dc=karellen,dc=local"

Then edit the sqlnet.ora file and switch from TNSNAMES to LDAP:

$ vi /u01/11.2.0/db_1/network/admin/sqlnet.ora
# Name resolution

Run tnsping:

$  tnsping PINK
Used LDAP adapter to resolve the alias
OK (10 msec)

And note that tnsping is using the LDAP adapter.