Help/advice parsing Oracle tnsping output

Hi,

tnsping is an Oracle tool that is sort of like a ping command. Unfortunately it does not come with a tool that can be used to parse its output which is very frustrating.

Example output of tnsping are as below:

$: tnsping testp1

TNS Ping Utility for Solaris: Version 11.2.0.2.0 - Production on 16-OCT-2013 16:18:06

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/db/testp1/dba/sqlnet/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = testp1prim.mnl.ph.com) (Port = 10666))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = testp1stdby.mnl.ph.com) (Port = 10666))) (CONNECT_DATA = (SERVICE_NAME=testp1_app.mnl.ph.com)))
OK (10 msec)

$: tnsping testd1

TNS Ping Utility for Solaris: Version 11.2.0.2.0 - Production on 16-OCT-2013 16:33:09

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/db/testp1/dba/sqlnet/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (COMMUNITY = tcpip.world) (PROTOCOL = TCP) (Host = testd1.mnl.ph.com) (Port = 25666)) (CONNECT_DATA = (SID = testd1) (GLOBAL_NAME = testd1.mnl.ph.com)))
OK (20 msec)

At the moment, I re-direct the output of tnsping to a file and then grep -i for the the "Attempting to contact" line. which should give me the following output:

For tnsping testp1

Attempting to contact (DESCRIPTION =(LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = testp1prim.mnl.ph.com) (Port = 10666))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = testp1stdby.mnl.ph.com) (Port = 10666))) (CONNECT_DATA = (SERVICE_NAME=testp1_app.mnl.ph.com)))

For tnsping testd1

Attempting to contact (DESCRIPTION = (ADDRESS = (COMMUNITY = tcpip.world) (PROTOCOL = TCP) (Host = testd1.mnl.ph.com) (Port = 25666)) (CONNECT_DATA = (SID = testd1) (GLOBAL_NAME = testd1.mnl.ph.com)))

For clarity, I manually indented and posted these output to the forum as below:

For tnsping testp1

(DESCRIPTION =
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (CONNECT_TIMEOUT=5)
   (TRANSPORT_CONNECT_TIMEOUT=3)
   (RETRY_COUNT=3)
   (ADDRESS_LIST = 
      (ADDRESS = 
         (PROTOCOL = TCP) 
         (Host = testp1prim.mnl.ph.com)
         (Port = 10666) 
      )
   ) 
   (ADDRESS_LIST = 
      (ADDRESS = 
         (PROTOCOL = TCP)
         (Host = testp1stdby.mnl.ph.com)
         (Port = 10666)
      )
   )
   (CONNECT_DATA = 
      (SERVICE_NAME=testp1_app.mnl.ph.com)
   )
)


For tnsping testd1

(DESCRIPTION = 
   (ADDRESS = 
      (COMMUNITY = tcpip.world) 
      (PROTOCOL = TCP) 
      (Host = testd1.mnl.ph.com) 
      (Port = 25666)
   ) 
   (CONNECT_DATA = 
      (SID = testd1) 
      (GLOBAL_NAME = testd1.mnl.ph.com)
   )
)

Now come the big questions. Can anyone help me how to re-direct the output to an array and access it like an associative array?

For example, for the tnsping testd1 output, I want to be able to access address[host] and get testd1.mnl.ph.com or connect_data[sid] and get testd1.

For the tnsping testp1, it has two (2) address_list, so I will need to differentiate between the two (2) so that one is address_list_1 and the other is address_list_2. I want to be able to access address_list_1[host] and should get testp1prim.mnl.ph.com and address_list_2[host] and should give me testp1stdby.mnl.ph.com. Some tnsping output gives me as much as four (4) values.

I honestly don't know where to start except for doing the tnsping and redirect the output to a file and grep -i for the "Attempting to contact" string.

For a start, how do I count the number of times a string appears in a line? For example, I want to be able to check how many address_list there is from the tnsping output?

Not sure how to proceed from there. If any awk expert can help me parse the output into several parts that can be re-directed to a file, I will be more than satisfied with that instead of using an array.

For example, if using awk/nawk/gawk, how do I parse the string so that I get the following result:

For tnsping testp1

(DESCRIPTION =
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (CONNECT_TIMEOUT=5)
   (TRANSPORT_CONNECT_TIMEOUT=3)
   (RETRY_COUNT=3)
   (ADDRESS_LIST = 
      (ADDRESS = 
         (PROTOCOL = TCP) 
         (Host = testp1prim.mnl.ph.com)
         (Port = 10666) 
      )
   ) 
   (ADDRESS_LIST = 
      (ADDRESS = 
         (PROTOCOL = TCP)
         (Host = testp1stdby.mnl.ph.com)
         (Port = 10666)
      )
   )
   (CONNECT_DATA = 
      (SERVICE_NAME=testp1_app.mnl.ph.com)
   )
)

- Parse the output to create four (4) or more files below:

description.txt
address_list_01.txt, address_list_02.txt ... or up to how many address_list sections there is
connect_data.txt

description.txt should contain all lines but the text from address_list and connect_data
address_list TXT files should have information from the address section
connect_data should have information from the connect_data section

For tnsping testd1

(DESCRIPTION = 
   (ADDRESS = 
      (COMMUNITY = tcpip.world) 
      (PROTOCOL = TCP) 
      (Host = testd1.mnl.ph.com) 
      (Port = 25666)
   ) 
   (CONNECT_DATA = 
      (SID = testd1) 
      (GLOBAL_NAME = testd1.mnl.ph.com)
   )
)

- Parse the output to create three (30 or more files

description.txt
address_list_01.txt, address_list_02.txt ... or up to how many address_list sections there is
connect_data.txt

description.txt should contain all lines but the text from address_list and connect_data
address_list TXT files should have information from the address section
connect_data should have information from the connect_data section

I hope I've explained myself well. Sorry for a long post.

Any feedback/guidance will be much appreciated. Thanks in advance.

---------- Post updated at 12:50 AM ---------- Previous update was at 12:39 AM ----------

Hi

BTW, need to do this in KSH as the boxes only have KSH, no bash :(-

Those come from the tnsnames.ora file. Rather than pinging, simply get a local copy and work with it. In order for tnsping to "ping" the entry has to exists in tnsnames.ora, and the db has to be up. tnsnames.ora files are usually not updated very often.

The location is:

$ORACLE_HOME/network/admin/tnsnames.ora

You may need to get your DBA to copy it for you.... if you do not have permissions.

Using tnsping and reverse engineering it into a tnsnames.ora file entry is a little like trying to throw a diesel truck over your shoulder using the exhaust cloud as a handle. If you get the file, we'll show you how to parse out a given entry.

Hi Jim,

Thanks for your reply, very much appreciated.

Below is how the entries look like in the tnsnames.ora file for these two.

testp1.mnl.ph.com =
 (DESCRIPTION =(LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP) (Host = testp1prim.mnl.ph.com) (Port = 10666))
    )
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP) (Host = testp1stdby.mnl.ph.com) (Port = 10666))
    )
    (CONNECT_DATA =
           (SERVICE_NAME=testp1_app.mnl.ph.com)
    )
  )

testd1.mnl.ph.com =
  (DESCRIPTION =
        (ADDRESS =
          (COMMUNITY = tcpip.world)
          (PROTOCOL = TCP)
          (Host = testd1.mnl.ph.com)
          (Port = 25666)
        )
      (CONNECT_DATA =
        (SID = testd1)
           (GLOBAL_NAME = testd1.mnl.ph.com)
      )
  )

tnsping saves me having to do an "extract" of the information from the tnsnames.ora file, I think, albeit the result is a long line of strings and without the line endings as they look like from the tnsnames.ora file

If I don't use tnsping, that means I have to search for the TNS alias in the tnsnames.ora file and then extract the number of lines that I am interested in but with the proper line endings, isn't it?

Using tnsping, I do tnsping | grep "Attempting to contact" and that gives me the TNS or strings that I want to get the information. At the moment, I am interesting in getting the HOST and PORT information.

At the moment, I have something line below using tnsping. It works fine with testd1 but I can't get it work for testp1 that has two address information. If I search for address_list, I only get address.

$: cat x.ksh
#!/bin/ksh

TNS=${1}

tnsping ${TNS} | nawk '/Attempting/ {
gsub("[()=]"," ")
$0=toupper($0)
for (i=1;i<=NF;i++)
   if ($i=="HOST")
       {print $(i+1)
        exit}
}' | read host

tnsping ${TNS} | nawk '/Attempting/ {
gsub("[()=]"," ")
$0=toupper($0)
for (i=1;i<=NF;i++)
   if ($i=="PORT")
       {print $(i+1)
        exit}
}' | read port

echo "host=$host"
echo "port=$port"

exit 0

Sample run output below:

$: ./x.ksh testp1
host=TESTP1PRIM.MNL.PH.COM
port=10666
$: ./x.ksh testd1
host=TESTD1.MNL.PH.COM
port=25666

I don't get the second host and port information for testp1. If I search for address_list, I only get ADDRESS, not the whole address section.

Thanks again for your reply.

That's because you're exitting from the loop after you match the first one.