Oracle database log files

You can access Oracle alert logs, audit files, and trace files by using the Amazon RDS console or API. For more information about viewing, downloading, and watching file-based database logs, see Monitoring Amazon RDS log files.

The Oracle audit files provided are the standard Oracle auditing files. Amazon RDS supports the Oracle fine-grained auditing (FGA) feature. However, log access doesn't provide access to FGA events that are stored in the SYS.FGA_LOG$ table and that are accessible through the DBA_FGA_AUDIT_TRAIL view.

The DescribeDBLogFiles API operation that lists the Oracle log files that are available for a DB instance ignores the MaxRecords parameter and returns up to 1,000 records. The call returns LastWritten as a POSIX date in milliseconds.

Retention schedule

The Oracle database engine might rotate log files if they get very large. To retain audit or trace files, download them. If you store the files locally, you reduce your Amazon RDS storage costs and make more space available for your data.

The following table shows the retention schedule for Oracle alert logs, audit files, and trace files on Amazon RDS.

Working with Oracle trace files

Following, you can find descriptions of Amazon RDS procedures to create, refresh, access, and delete trace files.

Listing files

You can use either of two procedures to allow access to any file in the background_dump_dest path. The first procedure refreshes a view containing a listing of all files currently in background_dump_dest.

EXEC rdsadmin.manage_tracefiles.refresh_tracefile_listing;

After the view is refreshed, query the following view to access the results.

SELECT * FROM rdsadmin.tracefile_listing;

An alternative to the previous process is to use FROM table to stream nonrelational data in a table-like format to list database directory contents.

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP'));

The following query shows the text of a log file.

SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP','alert_dbname.log.date'));

On a read replica, get the name of the BDUMP directory by querying V$DATABASE.DB_UNIQUE_NAME. If the unique name is DATABASE_B, then the BDUMP directory is BDUMP_B. The following example queries the BDUMP name on a replica and then uses this name to query the contents of alert_DATABASE.log.2020-06-23.

SELECT 'BDUMP' || (SELECT regexp_replace(DB_UNIQUE_NAME,'.*(_[A-Z])', '\1') FROM V$DATABASE) AS BDUMP_VARIABLE FROM DUAL;

BDUMP_VARIABLE
--------------
BDUMP_B

SELECT TEXT FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP_B','alert_DATABASE.log.2020-06-23'));

Generating trace files and tracing a session

Because there are no restrictions on ALTER SESSION, many standard methods to generate trace files in Oracle remain available to an Amazon RDS DB instance. The following procedures are provided for trace files that require greater access.

You can use many standard methods to trace individual sessions connected to an Oracle DB instance in Amazon RDS. To enable tracing for a session, you can run subprograms in PL/SQL packages supplied by Oracle, such as DBMS_SESSION and DBMS_MONITOR. For more information, see Enabling tracing for a session in the Oracle documentation.

Retrieving trace files

You can retrieve any trace file in background_dump_dest using a standard SQL query on an Amazon RDS–managed external table. To use this method, you must execute the procedure to set the location for this table to the specific trace file.

For example, you can use the rdsadmin.tracefile_listing view mentioned preceding to list all of the trace files on the system. You can then set the tracefile_table view to point to the intended trace file using the following procedure.

EXEC rdsadmin.manage_tracefiles.set_tracefile_table_location('CUST01_ora_3260_SYSTEMSTATE.trc');

The following example creates an external table in the current schema with the location set to the file provided. You can retrieve the contents into a local file using a SQL query.

SPOOL /tmp/tracefile.txt
SELECT * FROM tracefile_table;
SPOOL OFF;

Purging trace files

Trace files can accumulate and consume disk space. Amazon RDS purges trace files by default and log files that are older than seven days. You can view and set the trace file retention period using the show_configuration procedure. You should run the command SET SERVEROUTPUT ON so that you can view the configuration results.

The following example shows the current trace file retention period, and then sets a new trace file retention period.

# Show the current tracefile retention
SQL> EXEC rdsadmin.rdsadmin_util.show_configuration;
NAME:tracefile retention
VALUE:10080
DESCRIPTION:tracefile expiration specifies the duration in minutes before tracefiles in bdump are automatically deleted.
		
# Set the tracefile retention to 24 hours:
SQL> EXEC rdsadmin.rdsadmin_util.set_configuration('tracefile retention',1440);
SQL> commit;

#show the new tracefile retention
SQL> EXEC rdsadmin.rdsadmin_util.show_configuration;
NAME:tracefile retention
VALUE:1440
DESCRIPTION:tracefile expiration specifies the duration in minutes before tracefiles in bdump are automatically deleted. 		

In addition to the periodic purge process, you can manually remove files from the background_dump_dest. The following example shows how to purge all files older than five minutes.

EXEC rdsadmin.manage_tracefiles.purge_tracefiles(5);

You can also purge all files that match a specific pattern (if you do, don't include the file extension, such as .trc). The following example shows how to purge all files that start with SCHPOC1_ora_5935.

EXEC rdsadmin.manage_tracefiles.purge_tracefiles('SCHPOC1_ora_5935');

Publishing Oracle logs to Amazon CloudWatch Logs

You can configure your RDS for Oracle DB instance to publish log data to a log group in Amazon CloudWatch Logs. With CloudWatch Logs, you can analyze the log data, and use CloudWatch to create alarms and view metrics. You can use CloudWatch Logs to store your log records in highly durable storage.

Amazon RDS publishes each Oracle database log as a separate database stream in the log group. For example, if you configure the export function to include the audit log, audit data is stored in an audit log stream in the /aws/rds/instance/my_instance/audit log group. The following table summarizes the requirements for RDS for Oracle to publish logs to Amazon CloudWatch Logs.

{ none | os | db [, extended] | xml [, extended] }

This Oracle Management Agent log consists of the log groups shown in the following table.

For more information, see Locating Management Agent Log and Trace Files in the Oracle documentation.

aws rds create-db-instance \
    --db-instance-identifier mydbinstance \
    --cloudwatch-logs-export-configuration '["trace","audit","alert","listener","oemagent"]' \
    --db-instance-class db.m5.large \
    --allocated-storage 20 \
    --engine oracle-ee \
    --engine-version 12.1.0.2.v18 \
    --license-model bring-your-own-license \
    --master-username myadmin \
    --manage-master-user-password 

aws rds create-db-instance ^
    --db-instance-identifier mydbinstance ^
    --cloudwatch-logs-export-configuration trace alert audit listener oemagent ^
    --db-instance-class db.m5.large ^
    --allocated-storage 20 ^
    --engine oracle-ee ^
    --engine-version 12.1.0.2.v18 ^
    --license-model bring-your-own-license ^
    --master-username myadmin ^
    --manage-master-user-password 

aws rds modify-db-instance \
    --db-instance-identifier mydbinstance \
    --cloudwatch-logs-export-configuration '{"EnableLogTypes":["trace","alert","audit","listener","oemagent"]}'

aws rds modify-db-instance ^
    --db-instance-identifier mydbinstance ^
    --cloudwatch-logs-export-configuration EnableLogTypes=\"trace\",\"alert\",\"audit\",\"listener\",\"oemagent\"

aws rds modify-db-instance \
    --db-instance-identifier mydbinstance \
    --cloudwatch-logs-export-configuration '{"DisableLogTypes":["audit","listener"]}'

aws rds modify-db-instance ^
    --db-instance-identifier mydbinstance ^
    --cloudwatch-logs-export-configuration DisableLogTypes=\"audit\",\"listener\"

Previous methods for accessing alert logs and listener logs

You can view the alert log using the Amazon RDS console. You can also use the following SQL statement to access the alert log.

SELECT message_text FROM alertlog;

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.