1

I have a file tnsnames.ora and its contents are as below.

NEWDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1550))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = newdb)
    )
  )

LISTENER_DG11G =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1550))
  )

LISTENER_SABDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1550))
  )

STEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STEST)
    )
  )

RBSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxerp.de.mph.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RBSDB)
     )
    )

In the above file NEWDB = LISTENER_DG11G = LISTENER_SABDB = STEST = RBSDB = are the database names and the respective service names are included in SERVICE_NAME =

So, From the above file I am trying to extract the Database name and respective service names and put it into a file or .xls in linux.

The output file should be like

NEWDB   newdb
STEST   STEST
RBSDB   RBSDB

And what all databases that don't have service name should not be added into the output file.

I tried using CSPLIT and move the first set of lines to a file "X" and select the first line and SERVICE_NAME using cat X | grep -i "SERVICE_NAME" | cut -d "=" -f2 | rev | cut -d ")" -f2 | rev | awk "NF" and move it to a file and append the same way to rest of database names.

But it seems so complicated. Any other idea how it can be done will be appreciated.

sabarish jackson
  • 588
  • 3
  • 14
  • 30

2 Answers2

2

I'm sure there are better ways of handling this, but this pair of awk scripts will do the job. You can use either the enlarged version with comments or the one-liner below; they're functionally identical.

awk '
    BEGIN { RS="" }                             # Slurp paragraphs
    { print gensub("\n", " ", "g") }            # Replace NL with SPACE
' /tmp/tnsnames.ora |                           # ...in this file

awk '
    /SERVICE_NAME/ {                            # Only process matching lines
        listener=$1;                            # Listener is the first field
        si=NF-2;                                # Count fields back from end of string
        service=gensub(")", "", 1, $si);        # Strip trailing ")"
        printf "%s\t%s\n", listener, service    # Output result
    }
'

Sample run

awk 'BEGIN { RS="" } { print gensub("\n", " ", "g") }' /tmp/tnsnames.ora | awk '/SERVICE_NAME/ { listener=$1; si=NF-2; service=gensub(")", "", 1, $si);  printf "%s\t%s\n", listener, service }'
NEWDB   newdb
STEST   STEST
RBSDB   RBSDB
roaima
  • 107,089
  • 14
  • 139
  • 261
2
perl -l -00ne '
my ($blk) = /^\w+/g;

$np = qr/
   \(              # match an opening paren
     (?:
      (?> [^)(]+ ) # one or more non paren, non backtracking
           |
      (??{ $np })  # recurse for more
     )*
   \)              # match a closing paren
/x;

1 while
   /$np
     (?{
        m{ \( CONNECT_DATA \s+ = (?:\s*$np\s*)+ \) }x and
        m{ \( SERVICE_NAME \s+ = \s+ (\w+)      \) }x and
           print join $", $blk, $1 for $&;
     })
   /gx;
' input_file

Output

NEWDB newdb
STEST STEST
RBSDB RBSDB
  • +1 I do like `perl`'s powerful REs. In particular that it's so easy to extract a value from a variable-position string like `SERVICE_NAME = {servicename}`. – roaima Mar 21 '17 at 14:41