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

Posted by Dirk Nachbar on Friday, January 22, 2010
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 !