<

Go Premium for a chance to win a PS4. Enter to Win

x

Downloading Oracle Log Files in AWS RDS

Published on
3,928 Points
928 Views
Last Modified:
Doug Walton
DBA for the site that connects you with people and information to solve problems, inspire learning and influence the future of technology.
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Before we get started there are 2 things you'll need installed: Perl and the AWS Command Line Interface (https://aws.amazon.com/cli/).  They have an installer for the AWS CLI for Windows, but if you're using Mac or Linux you'll need python 2.6.5 or higher to install it.

Let's get right to it.  The core of the file is all built around this aws cli command:

/usr/bin/aws rds download-db-log-file-portion --region $region --db-instance-identifier $instanceName --log-file-name trace/alert_$sid.log > $logfile
That downloads the current alert log for the specified RDS instance.  That's all you really need to get started on a script of your own.  Fill in the $variables with whatever values apply to your instance.  

If you want to get actual alerting on the alert log, this is the script I setup to run every 5 minutes.  It determines where it left off in the log using readPos() and writePos().  If it finds a trace file, it also downloads that and emails it to $mailRecipient.  Downloading a trace file is the same as downloading the alert log.

Another thing to note are the .ignore and .trigger files, this is how I determine what lines are worth alerting on.  I'd recommend alerting on everything at first, and then adding stuff to the ignore file as it comes up.  Right now since this is for an RDS instance in AWS most of what we are ignoring we have little/no control over.

I don't recommend copying this directly, but rather using it to give you an idea on how you want to do something like this.  Downloading the alert log is simple, the alerting itself (if you want to do it) is where it gets more complicated.
 

analyze_log.pl


#!/usr/bin/perl
#
# Title:   analyze_log.pl
# Date:    Sep 17 2010
# Purpose: Check for new "Tracefile" entries in the alert.log file
# Hisory:  1.0  09/17/2010  * Initial version
#          2.0  08/08/2015  * Updated to pull from AWS


use Carp qw( confess );
$SIG{__DIE__} =  \&confess;
$SIG{__WARN__} = \&confess;

use strict;
use File::Basename;
use Sys::Syslog qw(:standard :macros);

# Configuration Vars
my $myName = basename($0);
my $myPath = dirname($0);
my $POS_DIR = $myPath . "/../var";
my $TRIGGER_FILE = $myPath . "/../conf/analyze_log.trigger";
my $IGNORE_FILE = $myPath . "/../conf/analyze_log.ignore";

my $mailRecipient = "to\@whoever.whatever";;
my $FROM_EMAIL = "from\@whoever.whatever";
my $alertRecipient = "to\@whoever.whatever"; #we send this to datadog for alerting
my $sendmail="/usr/sbin/sendmail -t -f$FROM_EMAIL";

my $extraLines = 5;     # Number of lines included around error.
my $traceMaxLen = 1000; # Max number of lines included in email
my $true = 1;
my $false = 0;


# Global Vars
my @triggerRules;
my @ignoreRules;
my @traceFiles;
my $lastErrCursor;
my $sid;

#AWS vars
my $instanceName; 
my $region; #us-west-2
my $downloadLocation = "/var/log/aws-rds/"; #location to download trace and alert files

########################################################################
## Main
{
        my $base;

        openlog($myName, "ndelay,pid", LOG_DAEMON);

        $sid = $ARGV[0];
        $instanceName = $ARGV[1];
        $region = $ARGV[2];
        die "Oracle SID not specified" unless ($sid);
        die "RDS instance name not specified" unless ($instanceName);
        die "RDS region not specified" unless ($region);

        $ENV{ORACLE_SID} = $sid;
        $ENV{ORACLE_BASE} = '/u01/app/oracle';
        $ENV{ORACLE_HOME} = '/u01/app/oracle/product/11.2.0/dbhome_1';

        $base = $ENV{'ORACLE_BASE'};
        die "Oracle Base not specified" unless ($base);

        @triggerRules = loadRules($TRIGGER_FILE);

        # Add Trigger for trace files
        push (@triggerRules, "/(Errors|More info) in file/");

        @ignoreRules = loadRules($IGNORE_FILE);

        analyzeLog($base);

        closelog();
        exit 0;
}

########################################################################
## analyzeLog - Read log file and look for problems.

sub analyzeLog($) {
        my ($base) = @_;
        my $lower_sid = lc($sid);
        my $logfile = "$downloadLocation/alert_$sid.log";
        `/usr/bin/aws rds download-db-log-file-portion --region $region --db-instance-identifier $instanceName --log-file-name trace/alert_$sid.log > $logfile`;
        
        my $maxPos;
        my $oldPos = readPos();
        my $cursor;
        my ($line, @lines);

        @lines = readFile($logfile);
        $maxPos = @lines;

        if ($maxPos < $oldPos) { # The file has shrunk, start over.
                $oldPos = 0;
        }

        $lastErrCursor = $oldPos;

        $cursor = $oldPos;

        while ($cursor < $maxPos) {
                $line = $lines[$cursor];

                if (checkLine($line)) {
                        reportLine(\@lines, $cursor, $maxPos);
                }

                $cursor++;
        }

        mailTraceFiles();

        writePos($maxPos);
}

########################################################################
## mailTraceFiles - Mail out trace files.

sub mailTraceFiles () {
        my %seen = ();
        my $file;

        # sometimes the log files aren't there right away, wait a few minutes
        sleep(300);

        # Mail a trace file only once.
        foreach $file (@traceFiles) {
                mailFile($file) unless $seen{$file}++;
        }
}

########################################################################
## mailFile - Mail file.

sub mailFile ($) {
        my ($file) = @_;
        my ($line, @lines);
        my $i = 0;
        my $trace_file;

        #download file here
        `/usr/bin/aws rds download-db-log-file-portion --region $region --db-instance-identifier $instanceName --log-file-name $file > $downloadLocation/$file 2>> /tmp/aws-trace-error.log`;

        $trace_file = "$downloadLocation/$file";


        open (SENDMAIL, "| $sendmail") || die "can't open $sendmail: $!\n";

        print SENDMAIL "To: $mailRecipient\n";
        print SENDMAIL "From: $FROM_EMAIL\n";
        print SENDMAIL "User-Agent: $myName\n";
        print SENDMAIL "Subject: Alert Trace: " . $sid . " - " . basename($file) . " - " . `date` . "\n";

        if ( -f $trace_file ) {
                open (TRACEFILE, "$trace_file") or die "Could not open '$trace_file' for reading: $!";

                foreach $line (<TRACEFILE>) {
                        chomp($line);
                        if ($i > $traceMaxLen) {
                                print SENDMAIL "-- File Truncated --\n";
                                last;
                        }

                        print SENDMAIL $line . "\n";
                        $i++
                }

                close (TRACEFILE);
        } else {
                print SENDMAIL "File '$trace_file' not found.\n";
        }

        close (SENDMAIL);
}

########################################################################
## checkLine - Check the line for problems.

sub checkLine ($) {
        my ($line) = @_;
        my ($rule, $result);
        my $ret = $false;


        foreach $rule (@ignoreRules) {
                $result = eval 'if ($line =~ ' . $rule . ') { return 1; } else { return 0; }';
                if ($@ ne "") {
                        die "error executing rule ($rule) on line ($line): $@";
                }
                if ($result) {
                        goto CONT;
                }
        }

        foreach $rule (@triggerRules) {
                $result = eval 'if ($line =~ ' . $rule . ') { return 1; } else { return 0; }';
                if ($@ ne "") {
                        die "error executing rule ($rule) on line ($line): $@";
                }
                if ($result) {
                        $ret = $true;
                }
        }

        # Check for trace files
        if ($line =~ /^(Errors|More info|ORA-00060: Deadlock detected\. More info) in file (.*)$/) {
                my @parts = split(/\s/, $2);
                my $file = $parts[0];
                $file =~ s/^.*\/trace/trace/;
                $file =~ s/\.$//;
                $file =~ s/:$//;
                push (@traceFiles, $file);
        }

        CONT:

        return $ret;
}

########################################################################
## reportLine - Report problems.

sub reportLine ($$$) {
        my ($lines_ref, $cursor, $maxPos) = @_;
        my ($beg, $end);
        my $i;
        my ($subject, $message);

        $beg = $cursor - $extraLines;
        $beg = 0 if ($beg < 0);

        #Try not to include the last error.
        $beg = $lastErrCursor if ($beg < $lastErrCursor);
        $lastErrCursor = $cursor + 1;

        $end = $cursor + $extraLines;
        $end = $maxPos - 1 if ($end > $maxPos - 1);

        #Try not to include the next error.
        foreach $i (1 .. $extraLines) {
                if (checkLine($lines_ref -> [$cursor + $i])) {
                        $end = $cursor + $i - 1;
                        last;
                }
        }

        foreach $i ($beg .. $end) {
                if ($i == $cursor) {
                        $subject = $sid . " - " . $lines_ref -> [$i];
                        $message .= "==> " . $lines_ref -> [$i] . "\n";
                        #log_warning("==> " . $lines_ref -> [$i]);
                } else {
                        $message .= "    " . $lines_ref -> [$i] . "\n";
                        #log_warning("    " . $lines_ref -> [$i]);
                }
        }
        log_warning($subject, $message);
}

########################################################################
## setEnv - Read in variables and set environment.

sub setEnv ($) {
        my ($file) = @_;
        my ($line, @lines);
        my ($parm, $value);

        @lines = readFile($file);

        foreach $line (@lines) {
                $line =~ s/\t/ /g;   # Convert tabs to spaces
                $line = trim($line);

                if ($line eq "") {
                 next;
                }

                if ($line =~ /^#/) {
                 next;
                }

                next unless ($line =~ /=/);

                $line =~ s/^export//;
                $line = trim($line);

                ($parm, $value) = split (/=/, $line);

                if ( $value =~ /^"(.*)"$/ ) {
                        $value = $1;
                }
                if ( $value =~ /^'(.*)'$/ ) {
                        $value = $1;
                }

                $ENV{$parm} = $value;
        }
}

########################################################################
## writePos - Write Position File

sub writePos ($) {
        my ($pos) = @_;
        my $file = $POS_DIR . "/" . $myName . "-" . $sid . ".loc";

        open (FILE, ">$file") or die "Could not open '$file' for writing: $!";
        print FILE $pos . "\n";
        close (FILE);
}

########################################################################
## readPos - Read Position File

sub readPos () {
        my @lines;
        my $posFile = $POS_DIR . "/" . $myName . "-" . $sid . ".loc";
        my $loc = 0;

        if ( -f $posFile ) {
                @lines = readFile($posFile);
                $loc = $lines[0] * 1;   # Make sure it's a number
                $loc = 0 if ($loc < 0);
        }

        return $loc;
}

########################################################################
## readFile - Read file into an array

sub readFile ($) {
        my ($file) = @_;
        my @array;
        my @parts;

        @parts = split(/\s/, $file);
        $file =  $parts[0];

        open (FILE, "$file") or die "Could not open '$file' for reading: $!";
        @array = <FILE>;
        close (FILE);

        chomp(@array);
        return @array;
}

########################################################################
## trim - Trim leading and trailing whitespace

sub trim($)
{
        my $string = shift;
        $string =~ s/^\s+//;
        $string =~ s/\s+$//;
        return $string;
}

########################################################################
## loadRules - Load the ignore and trigger rules.

sub loadRules($)
{
        my ($file) = @_;
        my ($line, @lines);
        my ($test, $result);
        my @rules;

        @lines = readFile($file);

        foreach $line (@lines) {
                next if ($line =~ /^#/);
                $line =~ s/#.*//g;
                $line = trim($line);

                next unless ($line);

                # test that the rule is a valid regular expression
                $result = eval 'if ($test =~ ' . $line . ') { return 1; } else { return 0; }';
                if ($@ ne "") {
                        die "error testing rule ($line): $@";
                }

                push (@rules, $line);
        }

        return @rules;
}

########################################################################
## log_warning - Send warning logger message

sub log_warning($$)
{
        my ($subject, $message) = @_;

        #print "EMERGENCY: " . $message . "\n";

        open (SENDMAIL2, "| $sendmail") || die "can't open $sendmail: $!\n";

        print SENDMAIL2 "To: $alertRecipient\n";
        print SENDMAIL2 "From: $FROM_EMAIL\n";
        print SENDMAIL2 "User-Agent: $myName\n";
        print SENDMAIL2 "Subject: Alert Trace: " . $subject . "\n";

        print SENDMAIL2 $message . "\n";

        if($message =~ /\.trc/)
        {
                print SENDMAIL2 "Trace file emailed to '$mailRecipient'";
        }

        close (SENDMAIL2);
}

Open in new window


analyze_log.ignore


## analyze_log.ignore - Ignore items

###
# Entries in this file should be perl regular expression that are evaluated and
# compared against incoming Oracle alert log entries.  When a match is found, the
# corresponding entry is ignored.  Regular expressions should be as strict as
# possible to avoid skipping of entries that deserve to be checked.
#
# Entries in this file take presedence over analyze_log.trigger.
#
# Comments are encouraged.
#
# example:
# /message repeated \d+ times/
#
# below is a list of characters that should be escaped and there corresponding
# escaped values and an example
#
# /   \/
# (   \(
# )   \)
# [   \[
# ]   \]
# *   \*
# +   \+
# .   \.
###

# Ignore "continue" errors.
/Archival Error\. Archiver continuing\./

# Ignore 16166 log writer errors
/ORA-16166/
/Errors in file .+_lgwr_\d+\.trc/
/LGWR: I\/O error 16166 archiving log/

# Ignore ORA-16401 log archiver errors
/ORA-16401/
/Errors in file .+_arc0_\d+\.trc/

# Ignore ORA-19583/19914/28365 backup errors
/ORA-19583/
/ORA-19914/
/ORA-28365/
/Errors in file .+_m000_\d+\.trc/

# Ignore ORA-03113 log archiver errors
/ORA-03113/
/LGWR: Error 3113 creating archivelog file/
/LGWR: Error 3113 disconnecting from destination/

# Ignore RFS errors during Standby resync
/ORA-16040/
/FAL archive, error 16040 creating remote archivelog/
/ORA-16055/

# Ignore ORA-16400: standby destination archive log file is locked
/ORA-16400/

# Ignore ORA-48913 non-critical error, tracefile reached max size
/ORA-48913/

# Ignore TNS errors
/Fatal NI connect error \d+\./
/Tns error struct:/

# Ignore TNS errors
/Fatal NI connect error \d+,/
/opiodr aborting process unknown ospid/

# Ignore when Oracle has limited trace files
/Suppressing further error logging/

# Ignore DataGuard log failures
/ORA-16145/
/Errors in file .+_pr00_\d+\.trc/

# Ignore missing files (AWS RDS should handle this)
/ORA-00308/
/ORA-27037: unable to obtain file status/
/Linux-x86_64 Error: 2: No such file or directory/

Open in new window


analyze_log.trigger


## analyze_log.trigger - Error Condition regular expressions.

###
# Entries in this file should be perl regular expression that are evaluated and
# compared against incoming Oracle alert log entries.  When a match is found, the
# corresponding entry is escalated and sent to syslog.  Regular expressions
# should be as strict as possible to avoid false positives.
#
# Entries in this file are overridden by analyze_log.ignore.
#
# Comments are encouraged.
#
# example:
# /message repeated \d+ times/
#
# below is a list of characters that should be escaped and there corresponding
# escaped values and an example
#
# /   \/
# (   \(
# )   \)
# [   \[
# ]   \]
# *   \*
# +   \+
# .   \.
###


/ORA-/
/[Ee]rror/

Open in new window


Feel free to comment if there's something you don't understand and I'll try to reply and update the article to better explain.
0
Comment
Author:Doug Walton
0 Comments

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month