Extract TNS alias from tnsnames.ora

Shell Programming and Scripting
1

Hi,

OS and bash version below:

$ cat /etc/system-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
$ bash --version
GNU bash, version 4.2.46(2)-release (x86_64-redhat-linux-gnu)
Copyright (C) 2011 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

I have found several post that deals about tnsnames.ora but I am getting lost understanding what the awk and sed do.

So I decided to write a script 'translating' the manual steps into a script form of some sort. It's a real crude way of doing it but it works as far as my testing goes.

I want to be able to extract the 'full' section of a given TNS entry/alias from an Oracle tnsnames.ora file and then get some information from this extracted section.

The script at that moment looks like below:

#!/bin/bash
#

echo "CHECKING tnsnames.ora FOR tns_alias=${1} .."
tns_alias=$( grep -i "^$1\.." tnsnames.ora )

if [[ ! -z "${tns_alias}" ]] ; then
  start=$( grep -in "^$1\.." tnsnames.ora | awk -F":" '{ print $1 }' )
  end=$(( start + 25 ))
  ##echo "start=$start | end=$end"
  sed -n "${start},${end}p" tnsnames.ora > /tmp/tnsnames.ora.tmp
  cat /dev/null > /tmp/tnsnames.ora.alias
  switch=0
  ##while read -r line
  while IFS= read -r line # retain indentation/white space
  do
    case $switch in
      0 )
        ## This is the TNS alias, the first line
        echo "$line" >> /tmp/tnsnames.ora.alias
        switch=1
        ;;
      1 )
        if [[ ! -z $( echo $line | grep "=$" ) ]]  && [[ ! -z $( echo $line | grep -i "(" ) ]] ; then
          echo "$line" >> /tmp/tnsnames.ora.alias
        else
          if [[ ! -z $( echo $line | grep "=$" ) ]] ; then
            break
          else
            echo "$line" >> /tmp/tnsnames.ora.alias
          fi
        fi
        ;;
    esac
  done < /tmp/tnsnames.ora.tmp
  echo
  ls /tmp/tnsnames*
  echo
  echo "## FOUND TNS alias entry ( \$1=$1 | tns_alias=[$tns_alias] ) = /tmp/tnsnames.ora.alias"
  cat /tmp/tnsnames.ora.alias
  echo
  echo "## Host BELOW:"
  echo "$( cat /tmp/tnsnames.ora.alias | tr [:upper:] [:lower:] | sed "s# *##g" | grep -i "(host" | awk -F"host=" '{ print $2 }' | awk -F")" '{ print $1 }' )"
  echo "## Port BELOW:"
  echo "$( cat /tmp/tnsnames.ora.alias | tr [:upper:] [:lower:] | sed "s# *##g" | grep -i "(port" | awk -F"port=" '{ print $2 }' | awk -F")" '{ print $1 }' )"
  echo "## SERVICE BELOW:"
  echo "$( cat /tmp/tnsnames.ora.alias | tr [:upper:] [:lower:] | sed "s# *##g" | grep -i "(service_name" | awk -F"service_name=" '{ print $2 }' | awk -F")" '{ print $1 }' )"
else
  echo "## tns_alias = [$1] NOT FOUND"
fi

Sample tnsnames.ora file is as below.

Most entries have similar lines of text but there are some odd entries like test1_DG_prim and testdb_oracle8. I have to make an assumption that the TNS alias is by itself on its own line and ends with a =.

test1.timbuktu.com =
  (DESCRIPTION=(CONNECT_TIMEOUT=10)(TRANSPORT_CONNECT_TIMEOUT=6)(RETRY_COUNT=2)
    (ADDRESS=(PROTOCOL=TCP)(Host=phse2aod123.timbuktu.com)(Port=1521))
    (ADDRESS=(PROTOCOL=TCP)(Host=phse2cod456.timbuktu.com)(Port=1521))
   (CONNECT_DATA=(SERVICE_NAME=test1.timbuktu.com)))
test1_app.timbuktu.com =
  (DESCRIPTION=(CONNECT_TIMEOUT=10)(TRANSPORT_CONNECT_TIMEOUT=6)(RETRY_COUNT=2)
    (ADDRESS=(PROTOCOL=TCP)(Host=phse2aod123.timbuktu.com)(Port=1521))
    (ADDRESS=(PROTOCOL=TCP)(Host=phse2cod456.timbuktu.com)(Port=1521))
   (CONNECT_DATA=(SERVICE_NAME=test1_app.timbuktu.com)))
test1_DG_prim.timbuktu.com =
  (DESCRIPTION=
   (ADDRESS_LIST=(LOAD_BALANCE=ON)
    (ADDRESS=(PROTOCOL=TCP)(HOST=test1prim.timbuktu.com)(PORT=8888)))
   (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test1.timbuktu.com)))
test1_DG_stdby.timbuktu.com =
  (DESCRIPTION=
   (ADDRESS_LIST=(LOAD_BALANCE=ON)
    (ADDRESS=(PROTOCOL=TCP)(HOST=test1stdby.timbuktu.com)(PORT=8888)))
   (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test1_stdby.timbuktu.com)))
testdb_oracle8.tahiti.com =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=2)
      (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=oracle8db.tahiti.com)(PORT=1521))
      )
      (CONNECT_DATA=
        (SERVER = DEDICATED)
        (SERVICE_NAME=oracle8_app.tahiti.com)
      )
    )
  )
test1_sol.timbuktu.com =
  (DESCRIPTION=(CONNECT_TIMEOUT=10)(TRANSPORT_CONNECT_TIMEOUT=6)(RETRY_COUNT=2)
    (ADDRESS=(PROTOCOL=TCP)(Host=test1prim.timbuktu.com)(Port=8888))
    (ADDRESS=(PROTOCOL=TCP)(Host=test1stdby.timbuktu.com)(Port=8888))
   (CONNECT_DATA=(SERVICE_NAME=test1_app.timbuktu.com)))

Sample runtime of my script below:

$ ./extract_tns_alias.bash hello
CHECKING tnsnames.ora FOR tns_alias=hello ..
## tns_alias = [hello] NOT FOUND
                                                                                                                                                                                                                                  $ ./extract_tns_alias.bash test1_dg_prim
CHECKING tnsnames.ora FOR tns_alias=test1_dg_prim ..

/tmp/tnsnames.ora.alias  /tmp/tnsnames.ora.tmp

## FOUND TNS alias entry ( $1=test1_dg_prim | tns_alias=[test1_DG_prim.timbuktu.com =] ) = /tmp/tnsnames.ora.alias
test1_DG_prim.timbuktu.com =
  (DESCRIPTION=
   (ADDRESS_LIST=(LOAD_BALANCE=ON)
    (ADDRESS=(PROTOCOL=TCP)(HOST=test1prim.timbuktu.com)(PORT=8888)))
   (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test1.timbuktu.com)))

## Host BELOW:
test1prim.timbuktu.com
## Port BELOW:
8888
## SERVICE BELOW:
test1.timbuktu.com

$ ./extract_tns_alias.bash testdb_oracle8
CHECKING tnsnames.ora FOR tns_alias=testdb_oracle8 ..

/tmp/tnsnames.ora.alias  /tmp/tnsnames.ora.tmp

## FOUND TNS alias entry ( $1=testdb_oracle8 | tns_alias=[testdb_oracle8.tahiti.com =] ) = /tmp/tnsnames.ora.alias
testdb_oracle8.tahiti.com =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=2)
      (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=oracle8db.tahiti.com)(PORT=1521))
      )
      (CONNECT_DATA=
        (SERVER = DEDICATED)
        (SERVICE_NAME=oracle8_app.tahiti.com)
      )
    )
  )

## Host BELOW:
oracle8db.tahiti.com
## Port BELOW:
1521
## SERVICE BELOW:
oracle8_app.tahiti.com

The use of start + 25 is because I don't know how to get the relevant section for the TNS alias that I provided in the command line. So, I am just getting the first 25 including the matching line.

  end=$(( start + 25 ))
  ##echo "start=$start | end=$end"
  sed -n "${start},${end}p" tnsnames.ora > /tmp/tnsnames.ora.tmp

On the output side of things. Not sure how I can change the following so that they just appear on one line.

## Host BELOW:
oracle8db.tahiti.com
## Port BELOW:
1521

## How to make the output like below for example:
oracle8db.tahiti.com | 1521

Ideally, would want to parse for other information for example, for connect_timeout, retry_count or server type. At the moment, I am retrieving information for host, port and service_name in some hard coded kind of way.

Any advice much appreciated. Thanks in advance.

extract_tns_alias.bash (1.8 KB)
tnsnames.ora (1.7 KB)

2
  1. Use some code validation, e.g. https://www.shellcheck.net

You can e.g. assign the output of a command substitution to a variable (instead of echoing it)
Btw: there's so much unneeded code, no need for cat, no need for tr, no need for sed nor grep - as I'm sure awk is pretty capable of doing all of it on its own. And yes, I'm aware that it involves some more elaborate logic, like additional "if-else" statements to get the numbers of correct fields to display - but beware, that you're working on a "loosely structured" data anyway, so numbers of properties, parentheses and lines of text may differ for every section.

  host="$( cat /tmp/tnsnames.ora.alias | tr [:upper:] [:lower:] | sed "s# *##g" | grep -i "(host" | awk -F"host=" '{ print $2 }' | awk -F")" '{ print $1 }' )"
  port="$( cat /tmp/tnsnames.ora.alias | tr [:upper:] [:lower:] | sed "s# *##g" | grep -i "(port" | awk -F"port=" '{ print $2 }' | awk -F")" '{ print $1 }' )"
  service="$( cat /tmp/tnsnames.ora.alias | tr [:upper:] [:lower:] | sed "s# *##g" | grep -i "(service_name" | awk -F"service_name=" '{ print $2 }' | awk -F")" '{ print $1 }' )"
  echo -e "## Host  | Port | Service BELOW:\n${host} | ${port} | ${service}";

However, this will most likely fail for any alias containing more than one single address (I've seen some in your sample file, see the previous remark about "data structure")

Well, apparently the relevant section is anything that follows the line matching a provided host name and is not a host name itself, try e.g. this:

#!/usr/bin/env bash

[[ -z "${1}" ]] && exit;
echo "CHECKING tnsnames.ora FOR tns_alias=${1} ..."
[[ -f "/tmp/tnsnames.ora.alias" ]] && rm "/tmp/tnsnames.ora.alias";
[[ -f "/tmp/tnsnames.ora.tmp" ]] && rm "/tmp/tnsnames.ora.tmp";
tns_lno_alias=$(grep -ino "^$1\.[^= ]*" "./tnsnames.ora");

if [[ -n "${tns_lno_alias}" ]]; then
{
  grep -i "^$1\.[^= ]*" "./tnsnames.ora" | tee "/tmp/tnsnames.ora.alias";
  while IFS= read -r line; do
    [[ "$line" =~ ^[[:alnum:]]+ ]] && break;
    echo "$line" | tee -a "/tmp/tnsnames.ora.alias";
  done < <(tail -n +$((${tns_lno_alias%%:*}+1)) "./tnsnames.ora")
} | sed -e 's#.*#\L&#g' -e 's#[[:blank:]]##g' > "/tmp/tnsnames.ora.tmp";
else
  echo "Not found.";
  exit;
fi;
echo
ls /tmp/tnsnames*
echo
echo "## FOUND TNS alias entry ( \$1=$1 | tns_alias=[${tns_lno_alias#*:}] ) => /tmp/tnsnames.ora.alias"
cat "/tmp/tnsnames.ora.alias" - "/tmp/tnsnames.ora.tmp" <<<$'\n## /tmp/tnsnames.ora.tmp ##\n';
3

Here is one in pure bash:

#!/bin/bash
#
tnsnamesfile="tnsnames.ora"
lb="\<" rb="\>" # helpers for [[ =~ ]]
typeset -l lowerarg lowerline
for lowerarg
do
  echo "CHECKING $tnsnamesfile FOR tns_alias=${lowerarg} .."
  found=0
  while IFS= read -r lowerline # retain indentation/white space
  do 
    if (( ! found ))
    then
      [[ $lowerline =~ ^"$lowerarg"${rb} ]] && found=1
    elif [[ $lowerline == [a-z]* ]]
    then
      found=0
    else
      # echo "$lowerline"
      if [[ $lowerline =~ ${lb}address\ ?= ]]
      then
        [[ $lowerline =~ ${lb}protocol\ ?=\ ?([[:alnum:]]+) ]]; protocol=${BASH_REMATCH[1]:-tcp}
        [[ $lowerline =~ ${lb}port\ ?=\ ?([[:alnum:]]+) ]]; port=${BASH_REMATCH[1]}
        [[ $lowerline =~ ${lb}host\ ?=\ ?([[:alnum:]][-_.[:alnum:]]*) ]]; host=${BASH_REMATCH[1]}
        echo "${host}:${port}/${protocol}"
      fi
    fi
  done < "$tnsnamesfile"
done

EDIT: allow "=" with spaces, allow service name without a dot.