Pages

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
...
PINK=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PINK)(GLOBAL_NAME=PINK)))
FLOYD=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.100)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=FLOYD)(GLOBAL_NAME=FLOYD)))
...


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

$ tnsping PINK
...
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PINK)(GLOBAL_NAME=PINK)))
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/oidSchemaCreateBase.sbs | sed 's/attributetypes:/attributetype/g' > oidbase.schema
# grep "^objectclasses" /u01/11.2.0/db_1/ldap/schema/oid/oidSchemaCreateBase.sbs | sed 's/objectclasses:/objectClass/g' >> oidbase.schema
# grep "^attributetypes" /u01/11.2.0/db_1/ldap/schema/oid/oidSchemaCreateNet.sbs | sed 's/attributetypes:/attributetype/g' > oidnet.schema
# grep "^objectclasses" /u01/11.2.0/db_1/ldap/schema/oid/oidSchemaCreateNet.sbs | 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
...
# ORACLE
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://192.168.56.101:389


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
orclNetDescString: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PINK)(GLOBAL_NAME=PINK)))
orclOracleHome: /u01/app/ora11/product/11.2.0/db_1
orclSid: PINK
orclVersion: 11.2.0.3


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 192.168.56.101 -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"
DIRECTORY_SERVERS = (192.168.56.101:389)
DIRECTORY_SERVER_TYPE = OID


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
# NAMES.DIRECTORY_PATH = (TNSNAMES)
NAMES.DIRECTORY_PATH = (LDAP)
...


Run tnsping:

$  tnsping PINK
...
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PINK)(GLOBAL_NAME=PINK)))
OK (10 msec)


And note that tnsping is using the LDAP adapter.