Menu
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 default retention period for alert logs is 30 days. Amazon RDS may delete alert logs older than 30 days. Oracle rotates alert logs when they exceed 10MB, at which point they will be unavailable from the Amazon RDS views.

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.

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

Retaining archived redo logs is supported for Oracle version 11.2.0.2.v7 and later. 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:

Copy
exec rdsadmin.manage_tracefiles.refresh_tracefile_listing;

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

Copy
rdsadmin.tracefile_listing

An alternative to the previous process (available beginning with version 11.2.0.3.v1) is to use "from table" to stream non-table data in a table-like format to list DB directory contents:

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

The following query shows text of a log file:

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

Generating Trace Files

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;

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:

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

Copy
# 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.

Copy
# 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.

Copy
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".

Copy
exec rdsadmin.manage_tracefiles.purge_tracefiles('SCHPOC1_ora_5935');

Previous Methods for Accessing Alert Logs and Listener Logs

You can view the alert and listener logs using the Amazon RDS console. You can also use the following methods to access these logs:

To access the alert log, use the following command:

Copy
select message_text from alertlog;

To access the listener log, use the following command:

Copy
select message_text from listenerlog;

Note

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

Related Topics