How to create a valid tnsnames.ora from Oracle Internet Directory (OID)

Posted by Dirk Nachbar on Friday, January 22, 2010 with 1 comment
Many of customers are using now an Oracle Internet Directory (OID) to store the tnsnames.ora entries in order to avoid a rollout to every client workstation, if a tnsnames.ora entry has been changed, deleted or added.
From time to time my customers asking me, how to spool out / create a tnsnames.ora file out of the entries of the Oracle Internet Directory.

Here two commands which you can use:

ldapsearch -b "" -s sub '((objectclass=orclNetService)(objectclass=orclService))' orclnetdescstring '((objectclass=orclNetService)(objectclass=orclNetServiceAlias))' orclnetdescstring aliasedobjectname > /tmp/temp_tnsnames.txt

sed -e 's/orclnetdescstring=/ /' -e 's/cn=//' -e 's/,dc=/./g' -e 's/,cn=OracleContext//' -e '/^$/d' -e 's/$/=/g' -e 's/)=/)/' -e '/)))/G' /tmp/temp_tnsnames.txt > /tmp/tnsnames.ora

Be aware both commands must be each in one line !