Thursday 28 May 2015

Grid/CloudControl: HowTo generate tnsnames.ora file from GridControl/CloudControl repository

Do you have more than, say, 100 databases? Do you wonder how to generate in simple way tnsnames.ora file? If you have access to Grid- or CloudControl repository, you are lucky!
You can use following scripts to generate your tnsnames(tested with OMS 11g and 12c):
SELECT s.target_name||' = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '||g.host_name||')(PORT = '||p2.property_value||'))) (CONNECT_DATA = (service_name = '||i.instance_name||')))' tnsnames
FROM
    sysman.mgmt_targets g,
    sysman.mgmt_db_dbninstanceinfo_ecm i,
    sysman.mgmt_ecm_gen_snapshot s,
    sysman.mgmt_target_properties p2
WHERE
  s.snapshot_guid = i.ecm_snapshot_id
  AND g.target_guid = p2.target_guid
  AND s.target_guid = g.target_guid
  AND s.is_current  = 'Y'
  AND p2.property_name='Port';

Output:
DWHQWHT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxqsa32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = DWHQWHT)))
PORTALT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxqsa32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = PORTALT)))
PUTFF8T = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxqsa32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = PUTFF8T)))
DBTESTT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxint32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = DBTESTT)))
RMANN2T = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod62.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = RMANN2T)))
DWHPWHT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod62.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = DWHPWHT)))
OEMPEMT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = OEMPEMT)))
RMANN7T = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = RMANN7T)))
oemrreT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = oemrreT)))
RMANN1T = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxint32.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = RMANN1T)))
PORTALT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod52.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = PORTALT)))
RMANN3T = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod72.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = RMANN3T)))
HRDWWHT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxprod72.lab.com)(PORT = 1601))) (CONNECT_DATA = (service_name = HRDWWHT)))
FINENET = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pruxent35.lab.com)(PORT = 1428))) (CONNECT_DATA = (service_name = FINENET)))