Reading Alert Logs of RDS in AWS

Reading Alert Logs of RDS in AWS

1. Access through AWS console --------------------------------------- The easiest way to access your alert.log is to use the AWS console: - Go to the RDS section of your AWS console - Select your database - Choose the "Logs & events" tab below the summary - Scroll down to the bottom of the page, there you should see the "Logs" section - Type "alert" in the search box to filter out the trace and audit files - Select the alert.log and click View/Watch/Download to access the content


"View" and "Watch" display the contents of the file within your browser. "View" shows a snapshot of the file whereas "Watch" keeps updating the contents in real time.


2. Access through SQL statements --------------------------------------- Another way to access the contents of the alert log is to query the ALERTLOG table:

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; SELECT * FROM ALERTLOG; The information in this table is limited to 10 MB, so depending on the amount of data your instance writes into the alert.log you might see more or less information in the table than in the log file. Accessing the log files through these tables is also described here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.Oracle.html#USER_LogAccess.Concepts.Oracle.AlertLogAndListenerLog

check for errors using

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select ORIGINATING_TIMESTAMP,message_text from alertlog 

where message_text like '%error%' OR message_text like '%ERROR%' 

order by 1 desc;

select ORIGINATING_TIMESTAMP,message_text from alertlog where message_text like 'ORA-%';

3. Access via AWS CLI --------------------------------------- You can also list and download the log files from your instance using AWS CLI. Listing the logs:

export DB=ORCL aws rds describe-db-log-files --db-instance-identifier $DB --filename-contains alert --no-verify-ssl --region ap-southeast-2

or

aws rds describe-db-log-files --db-instance-identifier $DB --filename-contains alert --region ap-southeast-2

Downloading the logs: aws rds download-db-log-file-portion --db-instance-identifier $DB --starting-token 0 --output text --log-file-name alert_$DB.log > alertlog.txt

in some environments

aws rds download-db-log-file-portion --db-instance-identifier $DB --starting-token 0 --output text --log-file-name trace/alert_$DB.log > alertlog.txt

The listenerlog view contains entries for Oracle Database version 12.1.0.2 and earlier. To access the listener log for these database versions, use the following query.

SELECT message_text FROM listenerlog;

For Oracle Database versions 12.2.0.1 and later, access the listener log using Amazon CloudWatch Logs.