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

Tags: GRIDoracle