Script to check tnsnames.ora syntax

[LKBrwn_DBA]

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) .

Thanks,
Mike.

PS: Or maybe a script to just "pretty print" the file so I can visually check the matching parenthesis.

[gandolf989]

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.

#!/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

[PAVB]

Great stuff Gandolf! Thank you for sharing.

[LKBrwn_DBA]

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?

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:

#!/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

[gandolf989]

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.

[LKBrwn_DBA]

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

[gandolf989]

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

Your welcome. Weren't you in Omaha or somewhere near there? Did you just move to Miami?

[LKBrwn_DBA]

Just moved from Atlanta.

[gandolf989]

Just moved from Atlanta.

I must be thinking of someone else.

[LKBrwn_DBA]

Just to let all know that those "partial address" entries in the tnsnames.ora are used by the Oracle Names Server...FYI

.