Wednesday, September 19, 2012

How to Build tnsnames.ora File From Oracle Grid Control Repository

Sometimes it can be harder to keep tnsnames.ora file up to date, especially for the big database teams in huge companies which are creating new databases very often.
When we talk about Oracle Database it is critical for the DBA to add new databases to Grid Control.
While writing custom scripts, capacity reports or whatever, using Grid Control like an inventory and using the information in custom scripts can be easier and the most useful way to manage the databases.

In this post we share some select statements that build tnsnames.ora file for all the RAC Services and instances in the Grid Control Repository:
First Select statement is easy one it builds tns names for all the instances:
select
distinct sid.PROPERTY_VALUE||' =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)
     (HOST = '||mgmt$target.host_name||')(PORT ='|| port.PROPERTY_VALUE||'))
    (CONNECT_DATA =
     (SID = '||sid.PROPERTY_VALUE||')
    )
  )'|| CHR(13) || CHR(10)
from
mgmt_target_properties machine,
mgmt_target_properties port,
mgmt_target_properties sid,
mgmt_target_properties domain,
mgmt$target
where
machine.target_guid=sid.target_guid
AND sid.target_guid=port.target_guid
AND port.target_guid=domain.target_guid
AND machine.PROPERTY_NAME='MachineName'
AND port.PROPERTY_NAME='Port'
AND sid.PROPERTY_NAME='SID'
AND sid.PROPERTY_VALUE not like '%ASM%'
AND machine.TARGET_GUID=mgmt$target.target_guid
order by 1;

For RAC TNS it is a little complicated. With 11.2 new feature "scan-name" comes. And before 11.2, tns must include all the instances so we need to get the nodes for each RAC.

Below is the single select statement for building RAC TNS :


SELECT     DECODE
              (dbversion,
               (SELECT DISTINCT SUBSTR (p.property_value, 1, 4)
                           FROM mgmt$target_properties p
                          WHERE p.property_name = 'DBVersion'
                            AND p.property_value LIKE '11.2%'), UPPER
                                                                  (rac_db_name)
                || '='
                || '(DESCRIPTION ='
                || '(ADDRESS = (PROTOCOL = TCP)(HOST ='
                || MachineName
                || ')(PORT ='
                || port
                || '))'
                || '(CONNECT_DATA ='
                || '(SERVER = DEDICATED)'
                || '(SERVICE_NAME ='
                || UPPER (rac_db_name)
                || ')'
                || ')'
                || ')'|| CHR (13) || CHR (10),
                  UPPER (rac_db_name)
               || ' =
  (DESCRIPTION=
    (FAILOVER=on)
    (LOAD_BALANCE=yes)
    (ADDRESS_LIST='
               || UPPER (REPLACE (SYS_CONNECT_BY_PATH (nodevip_name||')'
                                         || CHR (13)
                                         || CHR (10)
                                         || '        (PORT='||port||')'
                                         || CHR (13)
                                         || CHR (10)
                                         || '      )'
                                         || CHR (13)
                                         || CHR (10),'#' ),
                                         '#',
                                          '      (ADDRESS='
                                         || CHR (13)
                                         || CHR (10)
                                         || '        (PROTOCOL=TCP)'
                                         || CHR (13)
                                         || CHR (10)
                                         || '        (HOST='
                                        )
                               ) || '    )
    (CONNECT_DATA=
      (FAILOVER_MODE=
        (TYPE=session)
        (METHOD=basic)
        (RETRIES=180)
        (DELAY=5)
      )
      (SERVER=dedicated)
      (SERVICE_NAME='
               || UPPER (rac_db_name)
               || ')
    )
  )'|| CHR (13) || CHR (10)
              ) tns
      FROM (SELECT rac_db_name, rac_node_name, MachineName, dbversion, port,nodevip_name,
                   COUNT (*) OVER (PARTITION BY rac_db_name) cnt,
                   ROW_NUMBER () OVER (PARTITION BY rac_db_name ORDER BY rac_node_name)
                                                                          seq
              FROM (
SELECT p.property_value AS cluster_name, sn.scanName AS MachineName, nn.PROPERTY_VALUE as nodevip_name,
                           rac.target_name AS rac_db_name,
                           db.host_name AS rac_node_name,
                           db.target_name AS db_instance_name,
                           DECODE(sn.scanName, NULL, '10.2',SUBSTR (dbv.property_value, 1, 4)) AS dbversion, port.PROPERTY_VALUE port
                      FROM mgmt_targets rac,
                           mgmt_target_properties p,
                           mgmt_target_properties nn,
                           mgmt_target_properties port,
                           sysman.mgmt_target_assoc_defs d,
                           sysman.mgmt_target_assocs a,
                           mgmt_targets db,
                           (SELECT p.target_guid, p.property_value
                              FROM mgmt$target_properties p
                             WHERE p.property_name = 'DBVersion') dbv,
                           (SELECT mn.target_guid target_guid, mn.property_value cluster_name,sn.property_value scanName
                                 FROM mgmt$target_properties mn, mgmt$target_properties sn
                                WHERE  mn.property_name = 'ClusterName'
                                and sn.target_guid =mn.target_guid
                                and sn.property_name = 'scanName_dynamic') sn
                     WHERE rac.target_type = 'rac_database'
                       AND p.target_guid = rac.target_guid
                       AND nn.target_guid = db.target_guid
                       AND nn.property_name= 'MachineName'
                       AND port.target_guid=p.target_guid
                       AND p.property_name = 'ClusterName'
                       AND p.property_value  = sn.cluster_name (+)
                       AND p.property_type = 'INSTANCE'
                       AND d.assoc_def_name = 'contains'
                       AND d.source_target_type = '*'
                       AND port.PROPERTY_NAME='Port'
                       AND d.scope_target_type = ' '
                       AND a.assoc_guid = d.assoc_guid
                       AND a.source_target_guid = rac.target_guid
                       AND a.scope_target_guid = '0000000000000000'
                       AND db.target_guid = a.assoc_target_guid
                       AND dbv.target_guid = db.target_guid
                       AND db.target_type = 'oracle_database'
                           WITH READ ONLY))
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR rac_db_name = rac_db_name;



And Below Select statement is building TNS Names for all the RAC Services:



SELECT     DECODE
              (dbversion,
               (SELECT DISTINCT SUBSTR (p.property_value, 1, 4)
                           FROM mgmt$target_properties p
                          WHERE p.property_name = 'DBVersion'
                            AND p.property_value LIKE '11.2%'), UPPER
                                                                  (service_name)
                || '='
                || '(DESCRIPTION ='
                || '(ADDRESS = (PROTOCOL = TCP)(HOST = '
                || MachineName
                || ')(PORT = '
                || port
                || '))'
                || '(CONNECT_DATA ='
                || '(SERVER = DEDICATED)'
                || '(SERVICE_NAME = '
                || UPPER (service_name)
                || ')'
                || ')'
                || ')'|| CHR (13) || CHR (10),
                  UPPER (service_name)
               || ' =
  (DESCRIPTION=
    (FAILOVER=on)
    (LOAD_BALANCE=yes)
    (ADDRESS_LIST='
               || UPPER (REPLACE (SYS_CONNECT_BY_PATH (nodevip_name||')'
                                         || CHR (13)
                                         || CHR (10)
                                         || '        (PORT='||port||')'
                                         || CHR (13)
                                         || CHR (10)
                                         || '      )'
                                         || CHR (13)
                                         || CHR (10),'#' ),
                                         '#',
                                          '      (ADDRESS='
                                         || CHR (13)
                                         || CHR (10)
                                         || '        (PROTOCOL=TCP)'
                                         || CHR (13)
                                         || CHR (10)
                                         || '        (HOST='
                                        )
                               ) || '    )
    (CONNECT_DATA=
      (FAILOVER_MODE=
        (TYPE=session)
        (METHOD=basic)
        (RETRIES=180)
        (DELAY=5)
      )
      (SERVER=dedicated)
      (SERVICE_NAME='
               || UPPER (service_name)
               || ')
    )
  )'|| CHR (13) || CHR (10)
              ) tns
      FROM  (SELECT service_name, rac_node_name,MachineName, cluster_name, dbversion, port,nodevip_name,
                   COUNT (*) OVER (PARTITION BY service_name) cnt,
                   ROW_NUMBER () OVER (PARTITION BY service_name ORDER BY rac_node_name)
                                                                          seq
              FROM (
SELECT distinct p.property_value AS cluster_name, sn.scanName AS MachineName, nn.PROPERTY_VALUE as nodevip_name,
                           rs.service_name as service_name,
                           db.host_name AS rac_node_name,
                           db.target_name AS db_instance_name,
                           DECODE(sn.scanName, NULL, '10.2',SUBSTR (dbv.property_value, 1, 4)) AS dbversion, port.PROPERTY_VALUE port
                      FROM mgmt_targets rac,
                           mgmt_target_properties p,
      mgmt_target_properties nn,
                           mgmt_target_properties port,
                           sysman.mgmt_target_assoc_defs d,
                           sysman.mgmt_target_assocs a,
                           mgmt_targets db,
                           sysman.MGMT_RAC_SERVICES rs,
                           (SELECT p.target_guid, p.property_value
                              FROM mgmt$target_properties p
                             WHERE p.property_name = 'DBVersion') dbv,
   (SELECT mn.target_guid target_guid, mn.property_value cluster_name,sn.property_value scanName
                                 FROM mgmt$target_properties mn, mgmt$target_properties sn
                                WHERE  mn.property_name = 'ClusterName'
                                and sn.target_guid =mn.target_guid
                                and sn.property_name = 'scanName_dynamic') sn
                     WHERE rac.target_type = 'rac_database'
                       AND p.target_guid = rac.target_guid
                       AND nn.target_guid = db.target_guid
                       AND nn.property_name= 'MachineName'
   AND p.property_value  = sn.cluster_name (+)
                       and rs.DATABASE_UNIQUE_NAME=rac.target_name
                       AND port.target_guid=p.target_guid
                       AND p.property_name = 'ClusterName'
                       AND p.property_type = 'INSTANCE'
                       AND d.assoc_def_name = 'contains'
                       AND d.source_target_type = '*'
                       AND port.PROPERTY_NAME='Port'
                       AND d.scope_target_type = ' '
                       AND a.assoc_guid = d.assoc_guid
                       AND a.source_target_guid = rac.target_guid
                       AND a.scope_target_guid = '0000000000000000'
                       AND db.target_guid = a.assoc_target_guid
                       AND dbv.target_guid = db.target_guid
                       AND db.target_type = 'oracle_database'
                           WITH READ ONLY))
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR service_name = service_name;

No comments:

Post a Comment