RE: Oracle Instance Name and Listener Port information

        Here is the code I used to this exact task. Its not the prettiest sql, but does the job. Just run as sysman.

set pagesize 100000
set linesize 1000

select
host_short || '.' || sid || '=

    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = '|| hostname || ')(PORT = '|| port ||'))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      '|| case when instr(service_name,'XDB') > 0 then Null else '(SERVICE_NAME = '|| service_name  ||')' end  ||
      '(SID = '|| SID  ||')

from (SELECT                                                                                                                    --target_name,
          UPPER (host_name) hostname,
           UPPER (SUBSTR (t.host_name, 1, INSTR (t.host_name, '.') - 1)) HOST_SHORT,


           (SUBSTR (t.host_name, INSTR (t.host_name, '.') + 1, LENGTH (t.host_name))) DOMAIN,

           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'Port' AND p.target_guid = t.target_guid)
              port,
              'sys/anything_at_'
           || host_name
           || ':'
           || (SELECT p.property_value
                 FROM mgmt$target_properties p
                WHERE p.property_name = 'Port' AND p.target_guid = t.target_guid)
           || '/'
           || (SELECT p.property_value
                 FROM mgmt$target_properties p
                WHERE p.property_name = 'ServiceName' AND p.target_guid = t.target_guid)
           || ' as sysdba'
              Connection_string,


           (SELECT tp.property_value

              FROM mgmt$target_properties tp
             WHERE tp.target_type = 'host' AND tp.property_name = 'IP_address' AND tp.target_name = t.host_name)
              ip,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'DBVersion' AND p.target_guid = t.target_guid)
              DB_Version,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'OracleHome' AND p.target_guid = t.target_guid)
              oh,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'ServiceName' AND p.target_guid = t.target_guid)
              Service_name,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'log_archive_mode' AND p.target_guid = t.target_guid)
              logmode,
           UPPER ( (SELECT p.property_value
                      FROM mgmt$target_properties p
                     WHERE p.property_name = 'SID' AND p.target_guid = t.target_guid))
              sid,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'CPUCount' AND p.target_guid = t.target_guid)
              CPU,
           ROUND (  SYSDATE
                  - TO_DATE ( (SELECT p.property_value
                                 FROM mgmt$target_properties p
                                WHERE p.property_name = 'StartTime' AND p.target_guid = t.target_guid),
                             'YYYY-MM-DD HH24:MI:SS'),
                  0)
              Days_Uptime,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'StartTime' AND p.target_guid = t.target_guid)
              Uptime,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'VersionCategory' AND p.target_guid = t.target_guid)
              VersionCategory,


           (SELECT p.property_value

              FROM mgmt$target_properties p
             WHERE p.property_name = 'VersionBanner' AND p.target_guid = t.target_guid)
              VersionBanner,
           CASE
              WHEN (INSTR ( (SELECT UPPER (p.property_value)
                               FROM mgmt$target_properties p
                              WHERE p.property_name = 'VersionBanner' AND p.target_guid = t.target_guid),
                           'ENTERPRISE')) > 0
              THEN
                 'Enterprise'
              ELSE
                 'Standard/Standard One'
           END
              Edition,


           (SELECT b.VALUE

              FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
             WHERE     A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
                   AND a.TARGET_TYPE = 'oracle_database'
                   AND b.name = 'control_file_record_keep_time'
                   AND a.target_guid = t.target_guid)
              control_file_record_keep_time,


           (SELECT b.VALUE

              FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
             WHERE     A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
                   AND a.TARGET_TYPE = 'oracle_database'
                   AND b.name = 'optimizer_features_enable'
                   AND a.target_guid = t.target_guid)
              optimizer_features_enable,


           (SELECT ROUND (b.VALUE / 1024 / 1024 / 1024, 2)

              FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
             WHERE     A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
                   AND a.TARGET_TYPE = 'oracle_database'
                   AND b.name = 'memory_target'
                   AND a.target_guid = t.target_guid)
              memory_target,


           (SELECT sessions_highwater

              FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_license_ECM B
             WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND TARGET_TYPE = 'oracle_database' AND a.target_guid = t.target_guid)
              sessions_highwater,


           (SELECT sessions_current

              FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_license_ECM B
             WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND TARGET_TYPE = 'oracle_database' AND a.target_guid = t.target_guid)
              sessions_current
      FROM mgmt$target t
     WHERE t.target_type IN ('oracle_database')) Raw_data order by host_short, sid;

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra Sent: Friday, 25 October 2013 12:33 PM
To: oracle-l_at_freelists.org
Subject: Oracle Instance Name and Listener Port information

Hi
Does any one knows as how I can get Instance name and Listener Port configured on the database server from Oracle enterprise Manager Repository tables. I has 1200 database in one of the new client and want to create the tnsentry locally so that I can connect them. going to 100's of server with multiple database on multiple port is very time consuming process. As I had Grid control setup and so thought that if I can get the tablename which has the information then can can create tnsentry using script

TIA
Sanjay

--
http://www.freelists.org/webpage/oracle-l
This email contains confidential information. The contents must
not be disclosed to anyone else except with the authority of the sender.
Unauthorised recipients are requested to maintain this confidentiality and
immediately advise the sender of any error or misdirection in transmission.

--
http://www.freelists.org/webpage/oracle-l