Oracle Names Server can be configured to store TNS connect data in database tables. The TNS connect string data is found in NAMES.ONRS_REGION. This can be used to extract an equivalent tnsnames.ora .
Note: The easiest way to put the names server's data in a tnsnames.ora file is to enter namesctl and use the dump_tnsnames
command. However, if something's wrong with the names server, you may not be able to start it to issue this command. In this
situation, it's handy to be able to extract the data from the region database.
set pages 0 set lines 1000 spool tnsnames.ora.new select substr(astr,1,length(astr)-2) tnsname from (select name_p || '= ' || substr(zd_value1_p, instr(zd_value1_p,'(DESCRIP'), 100000000000) astr from onrs_region where instr(upper(zd_value1_p),'SID',1)!=0 ); spool off exit
Sample output:
laesdev.harvard.edu= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=laes.harvard.edu)(PORT=5121)) (CONNECT_DATA=(SID=LAESDEV)(Server=Dedicated))) dwfinrev.harvard.edu= (DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=youngs.cadm.harvard.edu)(PORT=5141)))(CONNECT_DATA=(SID=dwfinrev)(SRVR=))) dwhrnew.harvard.edu= (DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=starb.cadm.harvard.edu)(PORT=5543)))(CONNECT_DATA=(SID=dwhrnew)(SRVR=))) ....