Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help/advice parsing Oracle tnsping output

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help/advice parsing Oracle tnsping output
# 1  
Old 10-16-2013
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:

Code:
$: 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:

Code:
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:

Code:
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:

Code:
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

Code:
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 Smilie-
# 2  
Old 10-16-2013
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:
Code:
$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.
# 3  
Old 10-16-2013
Hi Jim,

Thanks for your reply, very much appreciated.

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

Code:
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 Smilie

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.

Code:
$: 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:

Code:
$: ./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.
# 4  
Old 10-17-2013
Quote:
Originally Posted by newbie_01
I don't get the second host and port information for testp1.
That's because you're exitting from the loop after you match the first one.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Help on parsing Oracle RMAN output for string and print sections of a file

Hi, I need some advise on how to print 'sections' of the attached file. I am searching for some that says Marked Corrupt and print some lines after it. At the moment I am running the command below: sed -n -e '/Marked Corrupt/{N;N;p;}' rman_list_validate.txtThis gives me the following... (1 Reply)
Discussion started by: newbie_01
1 Replies

2. UNIX for Dummies Questions & Answers

How to get/print host string only from tnsping output?

Hello All, I am using Linux OS. My idea is get the host name when we do tnsping in oracle. output of : tnsping DOELO01 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 15-OCT-2015 20:20:05 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files:... (2 Replies)
Discussion started by: Ariean
2 Replies

3. Shell Programming and Scripting

Extract multiple values from a tnsping output

Hi all, Can anyone help with the following request? I need to extract HOST value, SERVICE_NAME and msec value from a tnsping output and append to a file. The tnsping output is typically as follows: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS =... (4 Replies)
Discussion started by: jonnyd
4 Replies

4. Shell Programming and Scripting

Parsing of TOP output

Hi , i am trying to set up an alert, when CPU usage (0.2%us in below output) is more than 40% top | head | grep '^Cpu' Cpu(s): 0.2%us, 0.2%sy, 0.0%ni, 99.1%id, 0.6%wa, 0.0%hi, 0.0%si, 0.0%st using CUT, i pulled the value 0.2 and assigned to CPU (variable) CPU=$(expr `top | head -10... (5 Replies)
Discussion started by: Prateek007
5 Replies

5. Shell Programming and Scripting

parsing output

Can somebody provide a solution to parse the following; cat /tmp/xxx Name: QUE_REQU (o.mtaseast-o.dmart) (MTPost queue) Number of messages: 66446 (Age 686 min; Size 214 mb) Backlog (messages): 0 (Age 0 min) Name: QUE_REQU... (6 Replies)
Discussion started by: BeefStu
6 Replies

6. Shell Programming and Scripting

Parsing the output from top

Guys can you help me fix this parse error. Here's my script. #!/bin/bash # Set up limit below NOTIFY="6.0% us 6.1% us 6.2% us 6.3% us 6.5% us 6.6% us 6.7% us 6.8% us 6.9% us 7.0% us" # CPU Usage every minute TOP="$(top -b -n2 -d 00.20 |grep Cpu|tail -1 | awk -F ":" '{ print $2 }' | cut... (3 Replies)
Discussion started by: redtred
3 Replies

7. Shell Programming and Scripting

parsing ifconfig output

I'm trying to gather information on the interfaces on a large number of servers. If I run ifconfig I will get: eth0 Link encap:Ethernet HWaddr 00:50:56:A2:27:C1 inet addr:10.145.xxx.xxx Bcast:10.152.45.255 Mask:255.255.254.0 ----- eth1 Link... (2 Replies)
Discussion started by: C0ppert0p
2 Replies

8. Shell Programming and Scripting

tnsping is not working

How to enable this command ? Thanks. ---------- Post updated at 03:36 AM ---------- Previous update was at 03:34 AM ---------- I forgot to tell it is Unix server (3 Replies)
Discussion started by: ajincoep
3 Replies

9. Shell Programming and Scripting

Parsing output

I need to parse the following out put and determine if the USB is a DISK and whether or not it's External. If an HBA line contains "USB" then does the next line contain "DISK" and "External". 0:0,31,0: HBA : (aacraid,1) AAC SCSI 0,0,0: DISK : Adaptec ASR4800SAS Volu0001 ... (6 Replies)
Discussion started by: lochraven
6 Replies

10. Shell Programming and Scripting

parsing output

I have a file that contains the output of the ls -iR command, something like this: ./results: 2504641011 result_1410 2500957642 result_525 2504641012 result_1425 2500957643 result_540 ./tests/1: 2500788755 1 2500788743 1000 ./tests/2: 2500788759 3 2500788758 999 ... (6 Replies)
Discussion started by: looza
6 Replies
Login or Register to Ask a Question