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.
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|
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.
The default retention period for audit files is 7 days. Amazon RDS may delete audit files older than 7 days.
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
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 184.108.40.206.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.
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
Once the view is refreshed, use the following view to access the results.
An alternative to the previous process (available beginning with version 220.127.116.11.v1) 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
Since there are no restrictions on
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
|Oracle Method||Amazon RDS Method|
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
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:
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
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.
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".
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:
select message_text from alertlog;
To access the listener log, use the following command:
select message_text from listenerlog;
Oracle rotates the alert and listener logs when they exceed 10MB, at which point they will be unavailable from the Amazon RDS views.