Script to check tnsnames.ora syntax
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Script to check tnsnames.ora syntax

  1. #1
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Question Script to check tnsnames.ora syntax

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    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
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Great stuff Gandolf! Thank you for sharing.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Talking Thanks!

    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?

    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
    Last edited by LKBrwn_DBA; 07-31-2008 at 08:31 AM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    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.
    this space intentionally left blank

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Thumbs down ONAMES full of c.r.a.p.

    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
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by 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

    Your welcome. Weren't you in Omaha or somewhere near there? Did you just move to Miami?
    this space intentionally left blank

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477
    Just moved from Atlanta.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by LKBrwn_DBA
    Just moved from Atlanta.
    I must be thinking of someone else.
    this space intentionally left blank

  10. #10
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Talking Fyi

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

    .
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width