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:

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