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

select
distinct mgmt$target.host_name||'|'||sid.PROPERTY_VALUE||'|'||port.PROPERTY_VALUE
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 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
do
        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

done

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

  1. 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).

      Cheers
      Amit

Leave a Reply