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 !
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 !
Categories: Oracle Internet Directory
call me old fashioned but I prefer to run sql for this:
ReplyDeleteselect substr(rdn,4,15) || '=' || ATTRVAL
FROM ods.CT_DN C, ods.ds_attrStore D
WHERE C.ENTRYID = D.ENTRYID
and attrname='orclnetdescstring'
order by rdn
/
works in our shop, may or may not in yours and is a bit simpler that the older versions query to do the same. This is on 11G oid.