Re: Oracle Instance Name and Listener Port information
Date: Fri, 25 Oct 2013 06:44:08 -0700 (PDT)
Message-ID: <1382708648.14941.YahooMailNeo_at_web122101.mail.ne1.yahoo.com>
Thanks Cameron. This is really great script and saved a lots of my time.
Regards
Sanjay
On Friday, October 25, 2013 12:51 AM, "Hodge, Cameron" <cameron.hodge_at_amec.com> wrote:
Hiya Sanjay,
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-lReceived on Fri Oct 25 2013 - 15:44:08 CEST