regex on tnsnames.ora

Question

my tnsnames.ora file has 2 formats : db_cl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = a55)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cl) ) )

dbcd = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = a66 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cd) ) )

myx5= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = v55)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = x5) ) )

i want to get the hostname of a specific service_name or sid. in some cases it is sid and in some cases it is service_name. what should i search with grep in order to get the hostname? In this example i want to get the string "host_name".

****UPDATE**** I ALSO NEED THE db_name2, if someone can help

Answer 1

If perl is available try this command:

perl -nle 'BEGIN{$service = shift}
    $host = $1 if /HOST\s*=\s*([^\s\)]+)/i;
    print $host if /\((SID|SERVICE_NAME)\s*=\s*$service\)/;
' blabla tnsnames.ora

It stores the last host value found in HOST = ... and prints it when encounters (**SID/SERVICE_NAME** = blabla)

Answer 2

@drf: try:

 awk '/HOST/{sub(/).*/,"",$(NF-2));print $(NF-2)}'   Input_file

Simply looking for string HOST for each line then substituting the ).* from the 2nd last filed of the line where awk matches the string HOST in it, it should give you host_name then.

EDIT: For looking for a specific SID or service name try:

awk '/HOST/{sub(/).*/,"",$(NF-2));HOST=$(NF-2);next} /SID/{print HOST}'

Change SID with sid or service name which you want to search and it should work then.

EDIT2:

awk '/db_name/{sub(/=/,"",$1);DB=$1}/HOST/{sub(/).*/,"",$(NF-2));HOST=$(NF-2);next} /chumma/{print DB ORS HOST}'

EDIT3:

awk '{gsub(/\)|\(/,"");;for(i=1;i<=NF;i++){if($i=="HOST"){host=$(i+1)};if($NF=="cd"){val="DB_NAME= "$1", HOST_NAME= "host",SID/SERVICE_NAME= "$NF}};if(val){print val;val=""}}'  Input_file

OR(non-one liner form of solution too as follows):

awk '{gsub(/\)|\(/,"");
      for(i=1;i<=NF;i++){
                                if($i=="HOST"){
                                                host=$(i+1)
                                              };
                                if($NF=="cd") {
                                                val="DB_NAME= "$1", HOST_NAME= "host",SID/SERVICE_NAME= "$NF
                                              }
                        };
      if(val)           {
                                print val;
                                val=""
                        }
     }
    '   Input_file

You could put "cd"''s place another service or ssid which you want to search too in above code.