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 PAGESIZE 999
SET LINESIZE 200
SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
COLUMN host FORMAT A50
COLUMN port FORMAT A10
COLUMN sid FORMAT A30
SPOOL Db_all.txt
SELECT DISTINCT
target.host_name || ‘|’ ||
sid.property_value || ‘|’ ||
port.property_value
FROM sysman.mgmt_target_properties machine
JOIN sysman.mgmt_target_properties sid
ON sid.target_guid = machine.target_guid
JOIN sysman.mgmt_target_properties port
ON port.target_guid = machine.target_guid
JOIN sysman.mgmt_target_properties domain
ON domain.target_guid = machine.target_guid
JOIN sysman.mgmt$target target
ON target.target_guid = machine.target_guid
WHERE 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’
)
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:
#!/bin/sh
INPUT_FILE=”Db_all.txt”
OUTPUT_FILE=”tnsnames.ora”
if [ ! -f “$INPUT_FILE” ]; then
echo “Error: input file ‘$INPUT_FILE’ does not exist.” >&2
exit 1
fi
rm -f “$OUTPUT_FILE”
while IFS=’|’ read -r HOST_NAME ORACLE_SID PORT; do
# Ignore blank or incomplete lines.
[ -z “$HOST_NAME” ] && continue
[ -z “$ORACLE_SID” ] && continue
[ -z “$PORT” ] && continue
cat >> “$OUTPUT_FILE” <<EOF
${ORACLE_SID} =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = ${HOST_NAME})
(PORT = ${PORT})
)
)
(CONNECT_DATA =
(SID = ${ORACLE_SID})
)
)
EOF
done < “$INPUT_FILE”
echo “File ‘$OUTPUT_FILE’ created successfully.
”
We hope you will be as useful as we are.
Greetings.
DBA team.
great query and shell commands.
But why have spaces been inserted in the statement and single quotes ?
here is the updated version
1. sql statement for the first part is :
set pages 999 lines 200 heading off trimspool off feedback 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’)
— WHERE current_status in (0,1) )
AND Machine.TARGET_GUID = mgmt$target.TARGET_GUID
ORDER BY 1;
spool off
2. shell script commands
rm tnsnames.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_LIST =” >> tnsnames.ora
echo ” (ADDRESS =” >> tnsnames.ora
echo ” (PROTOCOL = TCP)” >> tnsnames.ora
echo ” (HOST = ${HOST_NAME})” >> tnsnames.ora
echo ” (PORT = ${PORT})” >> tnsnames.ora
echo ” )” >> tnsnames.ora
echo ” )” >> tnsnames.ora
echo ” (CONNECT_DATA =” >> tnsnames.ora
echo ” (SERVICE_NAME = ${ORACLE_SID})” >> tnsnames.ora
echo ” )” >> tnsnames.ora
echo ” ) ” >> tnsnames.ora
done
Hello Tom,
Thank you very much for your comment and for taking the time to review the SQL query and shell script.
You were right to point out the unusual spaces. I have checked the original Spanish version of the article, and the code there does not contain spaces inside identifiers such as mgmt$target, filenames such as Db_all.txt, or the concatenation operator ||.
It appears that these spaces were introduced by the automatic translation or formatting of the English version of the post. For example, the English version may display expressions such as mgmt $ target, Db_all. txt, or | |, although they are written correctly in the original article.
Regarding the single quotes, those around the pipe character are intentional and required:
host_name || ‘|’ || sid.property_value || ‘|’ || port.property_value
The pipe character is a string literal used as a delimiter between the three values, so it must remain inside single quotes. Spaces around the || concatenation operator are optional and do not affect the SQL statement.
Thank you also for sharing your updated version. I will review the English formatting so that the code is displayed correctly.
Thanks again for reporting the issue and contributing to the article.
Best regards,