>
Linux, Oracle, Technical

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

About these ads

About Jeremy

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider

Discussion

5 thoughts on “Parsing LISTENER.ORA with awk and sed

  1. I haven’t tried them out yet, but it looks useful to me. Personally, I’m a lot more friendly with perl, especially for heavy text parsing like this. Plus, these days, every Oracle install includes the basic perl install, so it’s guaranteed to be available anywhere. Maybe I’ll twist your code into perl someday and offer it up…it’d be a good reason to dust off my perl skilz too.

    Thanks for sharing these cool tools.

    Posted by Dan Norris | December 5, 2008, 2:38 pm
  2. Perl versions would be useful – I’m sure that a lot of people are using Perl in Oracle environments and it would be nice to have a native function. Might be more efficient ways to do it in perl too. You know me though, I’ve always been a bit shell-happy. :)

    Posted by Jeremy | December 5, 2008, 3:19 pm
  3. Hi,
    Do you have a script for parsing *.aud with awk and sed.

    I have set audit_trail=os and have a lot the file of audit, but i dont know how i can review all this files.

    Do you have any idea or script.

    Regards,

    Posted by Walter Rabanal | February 23, 2009, 2:54 pm
  4. Wow, these are amazing! I was looking for a script to parse listener.ora files, and you have done more than I hoped to find!

    Thanks

    Posted by Huffton | May 4, 2011, 5:27 am

Trackbacks/Pingbacks

  1. Pingback: Log Buffer #126: a Carnival of the Vanities for DBAs - December 5, 2008

Disclaimer

(a) This is my personal blog.

The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.

racattack-iconOakTableLogo-small

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 925 other followers

%d bloggers like this: