11i and Listener Passwords

A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.

There are some pre-reqs, specifically:

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher

The note has detailed steps on how to set the password at the application and database tiers.

In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:

TNS-01169: The listener has not recognized the password

Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.

#!/usr/bin/perl -w

if ($#ARGV !=1) {
  die "Usage:  check_alert.pl   ex.  hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;

$interval=60;        # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5;  # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";


for (;;) {
  @errors=("Subject: Listener Password  Errors for $hostname\n");
  $currTime = localtime(time);
  push(@errors,"Here are some errors found at $currTime for $hostname.\n");

  while () {
   chop $_;
   if (/TNS-01169/) {
       push(@errors, "$_\n"); 
   }
  }

  if ($#errors > $email_threshold)  {
   $rndExt = time;
   $rndFile = "alert_errors_$rndExt";
   open (TMPFILE, ">/tmp/$rndFile");

   foreach $error (@errors) {
        print TMPFILE $error;
   }
   close(TMPFILE);
   system ("mail username\@mycompany.com < /tmp/$rndFile");
   system ("rm /tmp/$rndFile");
  }

  sleep $interval;
  seek filePtr, 0, 1;
}

The script takes two parameters: <hostname> <path_to_listener_log>

A couple of key things you may want to customize in the above script are the $interval and $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.

Now, I am by no means a perl expert but the above works for me. I'm sure there are some more efficient ways to monitor the log file. Feel free to post a comment with your preferred method. If you cycle your logfiles keep in mind you'll need to restart this script. I've monitored resource consumption and its pretty minimal but I know some DBA's will prefer not having a process run constantly.

Note: I've only tested this script on a 9i listener log. Its on my todo list to test it on 8.0.6.

UPDATE: Thanks to Howard Rogers for informing me of the 10g changes. Security has been enhanced such that the TNS listener administrator has to be a member of the local dba group. Unless you plan on administering your listener remotely then you don't need a password. Please see the comments and Metalink note: 340009.1

Most of our databases are still 9i but i'll be sure to remove the listener passwords as part of the upgrades.