Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Oracle Database Log Files

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

The Oracle audit files provided are the standard Oracle auditing files. While Fine Grained Auditing (FGA) is a supported feature, log access does not provide access to FGA events stored in the SYS.FGA_LOG$ table and that are accessible through the DBA_FGA_AUDIT_TRAIL view.

The DescribeDBLogFiles API action that lists the Oracle log files that are available for a DB instance ignores the MaxRecords parameter and returns up to 1000 records.

Retention Schedule

The Oracle database engine may rotate logs files if they get very large. If you want to retain audit or trace files, you should download them. Storing the files locally reduces your Amazon RDS storage costs and makes more space available for your data.

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

Log Type Retention Schedule

Alert Logs

The text alert log is rotated daily with 30 day retention managed by Amazon RDS. The XML alert log is retained for at least 7 days and can be accessed using the ALERTLOG view.

Audit Files

The default retention period for audit files is 7 days. Amazon RDS may delete audit files older than 7 days.

Trace files

The default retention period for trace files is 7 days. Amazon RDS may delete trace files older than 7 days.


Audit files and trace files share the same retention configuration.

Switching Online Log files

You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile switch online log files. For more information, see Switching Online Log Files.

Retrieving Archived Redo Logs

You can retain archived redo logs. For more information, see Retaining Archived Redo Logs.

Working with Oracle Trace Files

This section describes Amazon RDS-specific procedures to create, refresh, access, and delete trace files.

Listing Files

Two procedures are available to allow access to any file within the background_dump_dest. The first method refreshes a view containing a listing of all files currently in the background_dump_dest:

exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;

Once the view is refreshed, use the following view to access the results.


An alternative to the previous process is to use "from table" to stream non-table data in a table-like format to list DB directory contents:

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

The following query shows text of a log file:

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','alert_xxx.log'));

Generating Trace Files and Tracing a Session

Since 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.

Oracle Method Amazon RDS Method

oradebug hanganalyze 3

exec rdsadmin.manage_tracefiles.hanganalyze;

oradebug dump systemstate 266

exec rdsadmin.manage_tracefiles.dump_systemstate;

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 the DBMS_SESSION and DBMS_MONITOR packages. 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 of 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 above to list the 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. The contents can be retrieved into a local file using a SQL query.

# eg: send the contents of the tracefile to a local file: sqlplus user/password@TNS alias << EOF > /tmp/tracefile.txt select * from tracefile_table; EOF

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. Note that 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); #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 (do not 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');

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;

To access the listener log, use the following SQL statement:

select message_text from listenerlog;


Oracle rotates the alert and listener logs when they exceed 10 MB, at which point they will be unavailable from the Amazon RDS views.

Related Topics