<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Downloading Oracle Log Files in AWS RDS

Published on
3,776 Points
776 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Join & Write a Comment

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month