Create file tnsnames.ora from Cloud Control

Hello everyone!

Today we wanted to share with you a script that we have used several times to generate the file tnsnames. ora connection by 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 values MachineName, PORT and SID to extract the data that we need. The data of the query we export to file and with a small program shell script 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 you search only those instances that are with status “UP”, you can remove the condition to find all. We also have the possibility to search for a specific machine, so we can consult adding:

AND machine. PROPERTY_VALUE = ‘ Maquina1 ‘

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

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 you will be as useful as we are.

Greetings.

DBA team.

Leave a Reply