Printable View
Hi guys,
I just inherited a tnsnames.ora file with 119 entries (one for each db).
Anyone have a ksh, awk or other script to check "basic" syntax of the tnsnames.ora file?
Actually I just need to make sure all parenthesis match (it's not loading in Oracle Net Manager) . :p
Thanks,
Mike.
PS: Or maybe a script to just "pretty print" the file so I can visually check the matching parenthesis.
Here is a brute force way to test the file. I sort the file with a unique flag, then grep out the stuff that isn't a connect string. I then loop through the connect strings and do a tnsping on each one.
You can also run the sort grep thing by itself and just get a list of connect strings. You can also chop up the file to find the server name and check to see if each server it in your /etc/hosts file or if you can ping each server.
Code:#!/usr/bin/bash
export CONNSTRINGS=`sort -u $TNS_ADMIN/tnsnames.ora | grep -v "^ " | grep -v "^#" | grep -v "^(" | grep -v "^)" | sed "s/ =//g" | grep -v "^$"`
echo "${CONNSTRINGS}" | while read line
do
tnsping $line
done
exit 0
Great stuff Gandolf! Thank you for sharing.
Thanks gandolf, it did help alot.
Also I found these "partial address" lines which I had never seen before in any other installation; obviously they produced error, but I'm not sure if they are required (or used) by some obscure application out there -- any clues?
:confused:Code:on_unix1 = (ADDRESS=(PROTOCOL=TCP)(HOST=UNIX1)(PORT=1575))
on_hpux3 = (address=(protocol=tcp)(host=hpux3)(port=1575))
PS: I tweeked a bit gandolf's script:
Code:#!/usr/bin/bash
export CONNSTRINGS=`sort -u $TNS_ADMIN/tnsnames.ora | grep -v "^ " | grep -v "^#" | grep -v "^(" | grep -v "^)" | sed "s/ =//g" | grep -v "^$"`
echo "${CONNSTRINGS}" | while read line
do
dbsv=`echo $line|cut -d' ' -f1`
addr=`echo $line|cut -d' ' -f2-`
echo "#####+--- Checking: $dbsv ---+#####"
tnsping $addr
done
exit 0
Glad to help. I suppose someone who is better with regular expressions could have done it with one grep and a giant glob of expression. But writing something with multiple greps made it easier to write in a incremental fashion. I have written a lot of scripts that read a file listing server names, or the oratab file and loops through doing something for each record.
I have public/private authentication setup accross all my database servers. So I wrote a script that will copy one or more files to every database server. So if I change by hot backup script I can copy it to every server with one command.
Adding tools just makes the job easier, and in my humble opinion less boring since there is less mindless work.
LKBrwn_DBA have you thought about using either open LDAP or OID for resolving your database names? We have always just used the tns names file, but will probably start using Open LDAP soon.
Were still on version 9.2 using Oracle Names Server (ONAMES) and unfortunately it's full of c.r.a.p.; the tnsnames.ora was genereted using the DUMP_TNSNAMES command and has a lot of invalid entries.
Thanks for your help!
PS: It's my third day on this job.
Cheers :D
Quote:
Originally Posted by LKBrwn_DBA
Your welcome. Weren't you in Omaha or somewhere near there? Did you just move to Miami?
Just moved from Atlanta.
I must be thinking of someone else.Quote:
Originally Posted by LKBrwn_DBA
Just to let all know that those "partial address" entries in the tnsnames.ora are used by the Oracle Names Server...FYI :p
.
EXCELLENT - Script is still very useful over 10 years later (Oracle 11g and 12c). THANK YOU!
I took the supplied script and modified it to check the results for a "TNS-" response (which typically only occurs when unsuccessful). This was useful in my case as some tnsnames.ora files had many entries that made the results too tedious to visually scan.