Parsing LISTENER.ORA with awk and sed

Happy Thanksgiving to everyone in the US! And happy belated thanksgiving to everyone in Canada since you celebrated back at the beginning of October. :)

I’ve been doing a lot of scripting work lately. Although I can’t write about everything I’m doing, I would like to post a pattern that I thought could be useful to other folks. I needed to update some properties in the LISTENER.ORA file from a shell script. The problem is that basically you have to clobber it or properly parse it! Even though I initialized this listener file myself, there is always the possibility that a DBA could manually edit it – so I didn’t want to clobber the contents. That means that I had to parse it.

So how do you parse a listener file from a shell script? I came up with a nifty way to tokenize and parse the file through a simple awk script combined with sed – and it seemed rather useful so I refactored it a few times to apply to more general use cases. I think someone else could use something like this too so I’m posting it – do me a favor and keep the reference to ardentperf in there if you copy the code.

There’s two functions that are basically the same pattern: the first will read an attribute and the second will set one. I’ve included some examples at the bottom of the post. I’ve tried to make the awk code portable but so far I’ve only tested it with gawk and mawk on a few flavors of Linux.

Reading an Attribute

# get a property from stdin (listener.ora format) for a specific listener and 
# print it on stdout. this awk script will tokenize the listener file so that 
# it can find the property no matter what format the file is in. it will only 
# print the first match (for example an address).
#   param 1: top-level property filter (e.g. listener name)
#   param 2: leaf property to get (e.g. host, port, protocol)
# by Jeremy Schneider - ardentperf.com
getlistenerproperty() {
  sed -e 's/=/`=/g' -e 's/(/`(/g' -e 's/)/`)/g'|awk 'BEGIN{level=1} {
    wrote=0
    split($0,tokens,"`")
    i=1; while(i in tokens) {
      if(tokens[i]~"^[(]") level++
      if(tokens[i]~"^[)]") level--
      if(level==1&&i==1&&tokens[i]~"[A-Za-z]") TOP=tokens[i]
      if(toupper(TOP)~toupper("^[ t]*'"$1"'[ t]*$")) {
        if(propertylvl) {
          if(level>=propertylvl) {
            if(tokens[i]~"^="&&level==propertylvl) printf substr(tokens[i],2)
              else printf tokens[i]
            wrote=1
          } else propertylvl=0
          found=1
        }
        if(!found&&toupper(tokens[i])~toupper("^[(]?[ t]*'"$2"'[ t]*$")) propertylvl=level
      }
      i++
    }
    if(wrote) printf "n"
  }'
}

Setting an Attribute

This uses essentially the same pattern but it sets an attribute instead of reading one. It outputs the new LISTENER.ORA file on stdout.

# read stdin (listener.ora format) and change a property for a specific listener,
# writing the updated file on stdout. the function will tokenize the listener file
# on stdin so that it can find the property no matter what format the file is in. 
# it will only replace the first match (for example an address).
#   param 1: top-level property filter (e.g. listener name)
#   param 2: leaf property to set (e.g. host, port, protocol, address, etc)
#   param 3: new value of the property
# by Jeremy Schneider - ardentperf.com
setlistenerproperty() {
  sed -e 's/=/`=/g' -e 's/(/`(/g' -e 's/)/`)/g'|awk 'BEGIN{level=1} {
    wrote=0
    split($0,tokens,"`")
    i=1; while(i in tokens) {
      if(tokens[i]~"^[(]") level++
      if(tokens[i]~"^[)]") level--
      if(level==1&&i==1&&tokens[i]~"[A-Za-z]") TOP=tokens[i]
      if(toupper(TOP)~toupper("^[ t]*'"$1"'[ t]*$")) {
        if(propertylvl) {
          if(level>=propertylvl) {
            if(!found) {
              if(tokens[i]~"^="&&level==propertylvl) printf "= '$3'"
                else printf "'$3'"
              wrote=1
            }
          } else { printf tokens[i]; wrote=1; propertylvl=0 }
          found=1
        } else { printf tokens[i]; wrote=1 }
        if(!found&&toupper(tokens[i])~toupper("^[(]?[ t]*'"$2"'[ t]*$")) propertylvl=level
      } else { printf tokens[i]; wrote=1 }
      i++
    }
    if(wrote||i==1) printf "n"
  }'
}

Examples

First I’ll setup a sample LISTENER.ORA file that I can use to demonstrate a few different uses:

jeremy@debianvm:~/lsnr$ cat list-ardent.ora
TRACE_LEVEL_ARDENTL1 = ADMIN
TRACE_FILE_ARDENTL1 = listener
TRACE_DIRECTORY_ARDENTL1 = /u01/app/oracle/product/10.2.0/db_1/network/trace
LOG_FILE_ARDENTL1 = listener
LOG_DIRECTORY_ARDENTL1 = /u01/app/oracle/product/10.2.0/db_1/network/log
LOGGING_ARDENTL1 = ON

#SSL_CLIENT_AUTHENTICATION = FALSE
ARDENTL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.8.33)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.9.8.33)(PORT = 2485))
  )


SID_LIST_ARDENTL1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = JTEST1)
      (SID_NAME = JTEST1)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    )
  )

WALLET_LOCATION = (
  SOURCE=
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY=/home/oracle/Wallet_server))
  )

Now lets look at a few examples of reading properties. Note that – by design – it only reads the first match.

jeremy@debianvm:~/lsnr$ cat list-ardent.ora|getlistenerproperty ardentl1 host
 10.9.8.33
jeremy@debianvm:~/lsnr$ cat list-ardent.ora|getlistenerproperty ardentl1 port
 1522
jeremy@debianvm:~/lsnr$ cat list-ardent.ora|getlistenerproperty ardentl1 address
 (PROTOCOL = TCP)(HOST = 10.9.8.33)(PORT = 1522)
jeremy@debianvm:~/lsnr$ cat list-ardent.ora|getlistenerproperty ardentl1 description

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.8.33)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.9.8.33)(PORT = 2485))

jeremy@debianvm:~/lsnr$ cat list-ardent.ora|getlistenerproperty ardentl1 ardentl1

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.8.33)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.9.8.33)(PORT = 2485))
  )
jeremy@debianvm:~/lsnr$ cat list-ardent.ora|getlistenerproperty log_directory_ardentl1 log_directory_ardentl1
 /u01/app/oracle/product/10.2.0/db_1/network/log
jeremy@debianvm:~/lsnr$ 

And finally, a few examples of changing properties. Note that the entire file is always displayed but I’ve only shown the relevant excerpts.

jeremy@debianvm:~/lsnr$ cat list-ardent.ora|setlistenerproperty ardentl1 host 1.2.3.4
...
#SSL_CLIENT_AUTHENTICATION = FALSE
ARDENTL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.9.8.33)(PORT = 2485))
  )
...

jeremy@debianvm:~/lsnr$ cat list-ardent.ora|setlistenerproperty ardentl1 address "(HOST=1.2.3.4)(PORT=999)"
...
#SSL_CLIENT_AUTHENTICATION = FALSE
ARDENTL1 =
  (DESCRIPTION =
    (ADDRESS = (HOST=1.2.3.4)(PORT=999))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.9.8.33)(PORT = 2485))
  )
...

jeremy@debianvm:~/lsnr$ cat list-ardent.ora|setlistenerproperty logging_ardentl1 logging_ardentl1 OFF
TRACE_LEVEL_ARDENTL1 = ADMIN
TRACE_FILE_ARDENTL1 = listener
TRACE_DIRECTORY_ARDENTL1 = /u01/app/oracle/product/10.2.0/db_1/network/trace
LOG_FILE_ARDENTL1 = listener
LOG_DIRECTORY_ARDENTL1 = /u01/app/oracle/product/10.2.0/db_1/network/log
LOGGING_ARDENTL1 = OFF

#SSL_CLIENT_AUTHENTICATION = FALSE
ARDENTL1 =
...

jeremy@debianvm:~/lsnr$ cat list-ardent.ora|
> setlistenerproperty ardentl1 host 1.2.3.4|
> setlistenerproperty ardentl1 port 9999
...
#SSL_CLIENT_AUTHENTICATION = FALSE
ARDENTL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 9999))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.9.8.33)(PORT = 2485))
  )
...

And that’s about it. Leave me a comment and let me know if you find this useful. :)