Creating tnsnames.ora file from Cloud Control

CloudCloud ComputingDatabasesOracleRemote DBAUncategorized

Hello everyone!

Today we wanted to share with you a script that we have used on several occasions to generate the tnsnames.ora connection file through a query to Cloud Control. 

To do this, we use the views  mgmt_target_properties and mgmt $target. From the first view we consult the ValuesMachineName , Port and SID to extract the data we need. The data of the query we export to file and with a small shell script program we generate our new tnsnames.ora.

The query we use for the first part is:

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

spool db_all.txt

select
distinct mgmt$target.host_name||'|'||sid.PROPERTY_VALUE||'|'||port.PROPERTY_VALUE
from
sysman.mgmt_target_properties machine,
sysman.mgmt_target_properties port,
sysman.mgmt_target_properties sid,
sysman.mgmt_target_properties domain,
sysman.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 sysman.mgmt_current_availability 
where sysman.EM_SEVERITY.get_avail_string(current_status)='UP')
AND machine.TARGET_GUID=mgmt$target.target_guid
order by 1;

spool off

In the query, only those instances that are with a “UP” status are searched, the condition can be removed to search for all. We also have the possibility to search for those of a specific machine, so we can consult by adding:

AND machine.PROPERTY_VALUE=’maquina1′

Once the file is generated, we look for the values and format the output in tnsnames.ora format:

cat db_all.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} ="                             >> 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

We hope that it will be as useful to you as it is to us.

Greetings.

DBA Team.

Comments are closed.