Friday, June 14, 2013

EM12c Dynamically Generate TNS Entry

Follow me on Twitter

Do you want to dynamically create TNS entries for all your databases in your EM12c? Well using the SQL statement below you can.

-----------------SQL-----------------------

SELECT DISTINCT
            lower(tbl_sid.sid) as db_name,LOWER (tbl_sid.sid)
         || ' = '
         || '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='
         || tbl_tar.host_name
         || ')(PORT='
         || tbl_port.port
         || '))(CONNECT_DATA=(sid='
         || tbl_sid.sid
         || ')))'
            AS TNS_ENTRY
       FROM (SELECT p.target_guid, p.property_value AS port
               FROM mgmt_target_properties p
              WHERE p.property_name = 'Port') tbl_port,
            (SELECT s.target_guid, UPPER (s.property_value) AS sid
               FROM mgmt_target_properties s
              WHERE s.property_name = 'SID') tbl_sid,
            (SELECT s.target_guid, s.property_value AS version
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('Version')) tbl_ver,
            (SELECT s.target_guid, s.property_value AS PATH
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('OracleHome')) tbl_home,
            (SELECT s.target_guid, s.property_value AS data_guard_status
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('DataGuardStatus')) tbl_dg,
            (SELECT s.target_guid, s.VALUE AS PGA
               FROM mgmt$db_init_params s
              WHERE s.name = 'pga_aggregate_target') tbl_pga,
            (SELECT s.target_guid, s.VALUE AS SGA
               FROM mgmt$db_init_params s
              WHERE s.name = 'sga_max_size') tbl_sga,
            (SELECT s.target_guid, s.VALUE AS mem_max
               FROM mgmt$db_init_params s
              WHERE s.name = 'memory_target') tbl_mem,
            mgmt_target_properties tbl_main,
            mgmt_targets tbl_tar
      WHERE     tbl_main.target_guid = tbl_port.target_guid(+)
            AND tbl_main.target_guid = tbl_sid.target_guid(+)
            AND tbl_main.target_guid = tbl_tar.target_guid(+)
            AND tbl_main.target_guid = tbl_ver.target_guid(+)
            AND tbl_main.target_guid = tbl_home.target_guid(+)
            AND tbl_main.target_guid = tbl_dg.target_guid(+)
            AND tbl_main.target_guid = tbl_pga.target_guid(+)
            AND tbl_main.target_guid = tbl_sga.target_guid(+)
            AND tbl_main.target_guid = tbl_mem.target_guid(+)
            AND tbl_tar.target_type = 'oracle_database'
   GROUP BY tbl_port.port,
            tbl_sid.sid,
            tbl_tar.host_name
            ORDER BY 1

Note: If you are going to use another account other than SYSMAN to select you will need to have the following privilege.

grant select on mgmt$storage_report_data to <username>;
grant select on mgmt_target_properties to <username>;
grant select on mgmt_targets to <username>;
grant exempt access policy to <username>;

2 comments:

  1. What is "etc_repository"? I don't have anything like that in my EM12c system.

    Thanks,
    Mike

    ReplyDelete
  2. Mike,
    The etc_repository was a custom view that is not needed. I update the SQL statement above to not include that custom view. The query is now only using the two tables own my SYSMAN mgmt_target_properties and mgmt_targe. Thanks for pointing this out.

    ReplyDelete

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).