Retrieving Database SID,Port information from Grid Control repository

This is short posting on sql which can be used to get Hotsname,SID,Port information for databases registered in Grid Control repository. This information can be  used to create a tns entries and we can further use it to run a sql on all these databases.

set pages 999 lines 200
col host for a50
col port for a10
col sid for a10

distinct mgmt$target.host_name||'|'||sid.PROPERTY_VALUE||'|'||port.PROPERTY_VALUE
mgmt_target_properties machine,
mgmt_target_properties port,
mgmt_target_properties sid,
mgmt_target_properties domain,
AND sid.target_guid=port.target_guid
AND port.target_guid=domain.target_guid
AND machine.PROPERTY_NAME='MachineName'
AND sid.PROPERTY_VALUE not like '%ASM%'
AND machine.TARGET_GUID in (select TARGET_GUID from mgmt_current_availability where EM_SEVERITY.get_avail_string(current_status)='UP')
AND machine.TARGET_GUID=mgmt$target.target_guid
order by 1;

Pasting a small shell script, which can be used to create tnsnames.ora

cat db_list.txt |grep -v "^$"| while read each_line
        HOST_NAME=`echo $each_line |cut -d"|" -f1`
        ORACLE_SID=`echo $each_line |cut -d"|" -f2`
        PORT=`echo $each_line |cut -d"|" -f3`

echo "${ORACLE_SID}.world ="                    >> tnsnames.ora
echo "  (DESCRIPTION ="                         >> tnsnames.ora
echo "    (ADDRESS = (PROTOCOL = TCP)"          >> tnsnames.ora
echo "     (HOST = ${HOST_NAME})(PORT = ${PORT}))" >> tnsnames.ora
echo "    (CONNECT_DATA = "                     >> tnsnames.ora
echo "     (SID = ${ORACLE_SID})"               >> tnsnames.ora
echo "    )"                                    >> tnsnames.ora
echo "  )"                                      >> tnsnames.ora
echo " "                                        >> tnsnames.ora

Tags: ,

3 thoughts on “Retrieving Database SID,Port information from Grid Control repository”

  • We do soemthing similar but create EZconnect strings rtaher than a TNS. You are not filtering so you will get each instance of a RAC cluster and also any standby instances, probably not what you want if you are going to run a sql against each database.

    • Thanks John . True this would give all RAC hosts and standby instances.
      We actually wanted to collect all this information i.e both standby and all RAC instances.

      Yes Ezconnect is also option with 10g databases (I am not sure if it works with 10g client and 9i database).


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.