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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
$ 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
# 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
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
The following two tabs change content below.
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Hi Ludovico – thanks for the great information. I’ve got it running (mostly), but I’m not an awk expert and occasionally run into issues if there are comments in the listener.ora.
Example lines:
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
Any suggestion on tweaking the awk code to handle ‘comments’ that aren’t at the beginning of the line?
Thanks again, best regards – Scott C.
maybe 🙂
The easiest would be to strip the comments before processing the .ora file.
I have to review this, but I really have no time. 🙁
How to run this?
Not clear where to create the awk script…
Thanks
Hi Pepe, it’s in the comments in the head of the code…
# you can source this function in bash and use it like: # cat $TNS_ADMIN/listener.ora | tidy_dotora