{"id":1958,"date":"2020-06-23T14:12:24","date_gmt":"2020-06-23T12:12:24","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1958"},"modified":"2020-08-18T15:55:44","modified_gmt":"2020-08-18T13:55:44","slug":"tidy_dotora","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/tidy_dotora\/","title":{"rendered":"Awk to format the files .ora (listener.ora, tnsnames.ora, etc)"},"content":{"rendered":"<p>Tired of formatting the tnsnames.ora to make it more readable, I have taken the nice awk examples from Jeremy Schneider: <a href=\"https:\/\/ardentperf.com\/2008\/11\/28\/parsing-listenerora-with-awk-and-sed\/\">https:\/\/ardentperf.com\/2008\/11\/28\/parsing-listenerora-with-awk-and-sed\/ <\/a>and created a function to format all files .ora (lisp-like config files).<\/p>\n<p>Example, before:<\/p>\n<pre class=\"lang:lisp decode:true\">$ cat tnsnames.ora\r\nLUDOCDB1_SITE2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))\r\n    (CONNECT_DATA =\r\n      (SERVICE_NAME = LUDOCDB1_SITE2_DGMGRL )\r\n    )\r\n  )\r\nLUDOCDB1_SITE1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 )) (CONNECT_DATA = (SERVICE_NAME = LUDOCDB1_SITE1_DGMGRL )\r\n    )\r\n  )\r\nLUDOCDB1 = (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 )))\r\n\r\n# read only:\r\nLUDOCDB1_RO =\r\n  (DESCRIPTION =\r\n    (CONNECT_TIMEOUT = 5 )(TRANSPORT_CONNECT_TIMEOUT = 3 )\r\n    (ADDRESS_LIST = (LOAD_BALANCE = OFF )\r\n      (ADDRESS = (PROTOCOL = TCP )(HOST = newbox02 )(PORT = 1522 ))\r\n      (ADDRESS = (PROTOCOL = TCP )(HOST = newbox01 )(PORT = 1522 ))\r\n    ) (CONNECT_DATA =\r\n      (SERVICE_NAME = LUDOCDB1_RO )\r\n    )\r\n  )\r\n<\/pre>\n<p>and after:<\/p>\n<pre class=\"lang:lisp decode:true\"># cat tnsnames.ora | tidy_dotora\r\nLUDOCDB1_SITE2=\r\n   (DESCRIPTION=\r\n     (ADDRESS=\r\n       (PROTOCOL=TCP)\r\n       (HOST=newbox02)\r\n       (PORT=1522)\r\n     )\r\n     (CONNECT_DATA=\r\n       (SERVICE_NAME=LUDOCDB1_SITE2_DGMGRL)\r\n     )\r\n   )\r\n\r\nLUDOCDB1_SITE1=\r\n   (DESCRIPTION=\r\n     (ADDRESS=\r\n       (PROTOCOL=TCP)\r\n       (HOST=newbox01)\r\n       (PORT=1522)\r\n     )\r\n     (CONNECT_DATA=\r\n       (SERVICE_NAME=LUDOCDB1_SITE1_DGMGRL)\r\n     )\r\n   )\r\n\r\nLUDOCDB1=\r\n   (DESCRIPTION=\r\n     (CONNECT_TIMEOUT=5)\r\n     (TRANSPORT_CONNECT_TIMEOUT=3)\r\n     (ADDRESS_LIST=\r\n       (LOAD_BALANCE=OFF)\r\n       (ADDRESS=\r\n         (PROTOCOL=TCP)\r\n         (HOST=newbox01)\r\n         (PORT=1522)\r\n       )\r\n       (ADDRESS=\r\n         (PROTOCOL=TCP)\r\n         (HOST=newbox02)\r\n         (PORT=1522)\r\n       )\r\n     )\r\n     (CONNECT_DATA=\r\n       (SERVICE_NAME=LUDOCDB1_RW)\r\n     )\r\n   )\r\n# read only:\r\n\r\nLUDOCDB1_RO=\r\n   (DESCRIPTION=\r\n     (CONNECT_TIMEOUT=5)\r\n     (TRANSPORT_CONNECT_TIMEOUT=3)\r\n     (ADDRESS_LIST=\r\n       (LOAD_BALANCE=OFF)\r\n       (ADDRESS=\r\n         (PROTOCOL=TCP)\r\n         (HOST=newbox02)\r\n         (PORT=1522)\r\n       )\r\n       (ADDRESS=\r\n         (PROTOCOL=TCP)\r\n         (HOST=newbox01)\r\n         (PORT=1522)\r\n       )\r\n     )\r\n     (CONNECT_DATA=\r\n       (SERVICE_NAME=LUDOCDB1_RO)\r\n     )\r\n   )\r\n<\/pre>\n<p>The AWK script:<\/p>\n<pre class=\"lang:sh decode:true \">tidy_dotora () {\r\n \r\n# Heavily based on Jeremy Scheider's work:\r\n# https:\/\/ardentperf.com\/2008\/11\/28\/parsing-listenerora-with-awk-and-sed\/\r\n#\r\n# you can source this function in bash and use it like:\r\n# cat $TNS_ADMIN\/listener.ora | tidy_dotora \r\n \r\nawk '\r\n \r\n# function for padding\r\nfunction pad (string, len, char) {\r\n\tret = string;\r\n\tfor ( padi = length(string); padi&lt;len ; padi++) {\r\n\t\t\tret = sprintf(\"%s%s\",ret,char);\r\n\t}\r\n\treturn ret;\r\n}\r\n\t\r\nBEGIN {\r\n\tlevel=1;\r\n\tfirst=1;\r\n\tlastcomment=0;\r\n}\r\n\t\r\n{\r\n#MAIN\r\n \r\n\t# just skip any comments and print as is\r\n\tif ($0 ~ \"^[[:space:]]*#\") {\r\n\t\tif (lastcomment==0) {\r\n\t\t\tprintf(\"\\n\");\r\n\t\t}\r\n\t\tprint;\r\n\t\tlastcomment=1;\r\n\t\tnext;\r\n\t}\r\n\tlastcomment=0;\r\n\t\r\n\t# this puts every occurrence of =, ( and ) in different tokens\r\n\tgsub(\/=\/,\"`=\");\r\n\tgsub(\/\\(\/,\"`(\");\r\n\tgsub(\/\\)\/,\"`)\");\r\n\tsplit($0,tokens,\"`\");\r\n \r\n\ti=1; while(i in tokens) {\r\n \r\n\t\t# trim token and continue if empty\r\n\t\tgsub(\/ \/, \"\",tokens[i]);\r\n\t\tif(!tokens[i]) {i++; continue;}\r\n \r\n\t\t# got ( \"open bracket\": new level begins\r\n\t\t# increase the level, newline and pad\r\n\t\tif(tokens[i]~\"^[(]\") {\r\n\t\t\tlevel++;\r\n\t\t\tprintf (\"\\n\");\r\n\t\t\tprintf (pad(\"\", 2*level-1, \" \"));\r\n\t\t}\r\n\t\r\n\t\t# got ) \"close bracket\" : level ends\r\n\t\t# decrease the level but newline only if another one was closed immediately before\r\n\t\tif(tokens[i]~\"^[)]\") {\r\n\t\t\tlevel--;\r\n\t\t\tif (wentdown==1) {\r\n\t\t\t\tprintf(\"\\n\");\r\n\t\t\t\tprintf (pad(\"\", 2*level+1, \" \"));\r\n\t\t\t}\r\n\t\t\twentdown=1;\r\n\t\t} else {\r\n\t\t\twentdown=0;\r\n\t\t}\r\n\t\r\n\t\t# if level==1 and is alphanumeric, it is a \"TOP\" entry (LISTENER, SID_LIST_LISTENER or property)\r\n\t\t# add extra line (and eventually track it for other usage)\r\n\t\tif(level==1 &amp;&amp; i==1 &amp;&amp; tokens[i]~\"[A-Za-z]\") {\r\n\t\t\tTOP=tokens[i];\r\n\t\t\tif (first==1) {\r\n\t\t\t\tfirst=0;\r\n\t\t\t} else {\r\n\t\t\t\tprintf \"\\n\\n\";\r\n\t\t\t}\r\n\t\t}\r\n \r\n\t\tprintf (tokens[i]);\r\n\t\ti++;\r\n\t}\r\n}\r\nEND {\r\n\t# new line at the end of file\r\n\tprintf(\"\\n\");\r\n}' \r\n}<\/pre>\n<p>I have included the function in the <a href=\"https:\/\/github.com\/ludovicocaldara\/COE\">COE github repo<\/a>. More functions to come (hopefully).<\/p>\n<p>&#8212;<\/p>\n<p>Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 = &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/tidy_dotora\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[321,326,3,330,132],"tags":[],"class_list":["post-1958","post","type-post","status-publish","format-standard","hentry","category-aced","category-oracle","category-oracledb","category-oracle-inst-upg","category-triblog"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1958","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1958"}],"version-history":[{"count":6,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1958\/revisions"}],"predecessor-version":[{"id":1962,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1958\/revisions\/1962"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1958"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}