Awk to format the files .ora (listener.ora, tnsnames.ora, etc) 

This post was originally published on this site

Tired of formatting the tnsnames.ora to make it more readable, I have taken the nice awk examples from Jeremy Schneider: https://ardentperf.com/2008/11/28/parsing-listenerora-with-awk-and-sed/ and created a function to format all files .ora (lisp-like config files).

Example, before:

$ cat tnsnames.ora
LUDOCDB1_SITE2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))
    (CONNECT_DATA =
      (SERVICE_NAME = LUDOCDB1_SITE2_DGMGRL )
    )
  )
LUDOCDB1_SITE1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_SITE1_DGMGRL )
    )
  )
LUDOCDB1 = (DESCRIPTION = (CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 ) (ADDRESS_LIST = (LOAD_BALANCE = OFF ) (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_RW )))

# read only:
LUDOCDB1_RO =
  (DESCRIPTION =
    (CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 )
    (ADDRESS_LIST = (LOAD_BALANCE = OFF )
      (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))
      (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 ))
    ) (CONNECT_DATA =
      (SERVICE_NAME = LUDOCDB1_RO )
    )
  )

and after:

# cat tnsnames.ora | tidy_dotora
LUDOCDB1_SITE2=
   (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=newbox02)
       (PORT=1522)
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_SITE2_DGMGRL)
     )
   )

LUDOCDB1_SITE1=
   (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=newbox01)
       (PORT=1522)
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_SITE1_DGMGRL)
     )
   )

LUDOCDB1=
   (DESCRIPTION=
     (CONNECT_TIMEOUT=5)
     (TRANSPORT_CONNECT_TIMEOUT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=OFF)
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox01)
         (PORT=1522)
       )
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox02)
         (PORT=1522)
       )
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_RW)
     )
   )
# read only:

LUDOCDB1_RO=
   (DESCRIPTION=
     (CONNECT_TIMEOUT=5)
     (TRANSPORT_CONNECT_TIMEOUT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=OFF)
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox02)
         (PORT=1522)
       )
       (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=newbox01)
         (PORT=1522)
       )
     )
     (CONNECT_DATA=
       (SERVICE_NAME=LUDOCDB1_RO)
     )
   )

The AWK script:

tidy_dotora () {
 
# Heavily based on Jeremy Scheider's work:
# https://ardentperf.com/2008/11/28/parsing-listenerora-with-awk-and-sed/
#
# you can source this function in bash and use it like:
# cat $TNS_ADMIN/listener.ora | tidy_dotora 
 
awk '
 
# function for padding
function pad (string, len, char) {
	ret = string;
	for ( padi = length(string); padi<len ; padi++) {
			ret = sprintf("%s%s",ret,char);
	}
	return ret;
}
	
BEGIN {
	level=1;
	first=1;
	lastcomment=0;
}
	
{
#MAIN
 
	# just skip any comments and print as is
	if ($0 ~ "^[[:space:]]*#") {
		if (lastcomment==0) {
			printf("n");
		}
		print;
		lastcomment=1;
		next;
	}
	lastcomment=0;
	
	# this puts every occurrence of =, ( and ) in different tokens
	gsub(/=/,"`=");
	gsub(/(/,"`(");
	gsub(/)/,"`)");
	split($0,tokens,"`");
 
	i=1; while(i in tokens) {
 
		# trim token and continue if empty
		gsub(/ /, "",tokens[i]);
		if(!tokens[i]) {i++; continue;}
 
		# got ( "open bracket": new level begins
		# increase the level, newline and pad
		if(tokens[i]~"^[(]") {
			level++;
			printf ("n");
			printf (pad("", 2*level-1, " "));
		}
	
		# got ) "close bracket" : level ends
		# decrease the level but newline only if another one was closed immediately before
		if(tokens[i]~"^[)]") {
			level--;
			if (wentdown==1) {
				printf("n");
				printf (pad("", 2*level+1, " "));
			}
			wentdown=1;
		} else {
			wentdown=0;
		}
	
		# if level==1 and is alphanumeric, it is a "TOP" entry (LISTENER, SID_LIST_LISTENER or property)
		# add extra line (and eventually track it for other usage)
		if(level==1 && i==1 && tokens[i]~"[A-Za-z]") {
			TOP=tokens[i];
			if (first==1) {
				first=0;
			} else {
				printf "nn";
			}
		}
 
		printf (tokens[i]);
		i++;
	}
}
END {
	# new line at the end of file
	printf("n");
}' 
}

I have included the function in the COE github repo. More functions to come (hopefully).

—

Ludo