June, 2020 - DBA survival BLOG

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 )
    )
  )

$ 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)
     )
   )

# 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 "\n\n";
			}
		}
 
		printf (tokens[i]);
		i++;
	}
}
END {
	# new line at the end of file
	printf("\n");
}' 
}

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 "\n\n";

}

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

DBA survival BLOG

DBA stuff and Oracle Data Guard

Monthly Archives: June 2020

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