Performing common log-related tasks for Oracle DB instances

Following, you can find how to perform certain common DBA tasks related to logging on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.

For more information, see Amazon RDS for Oracle database log files.

Setting force logging

In force logging mode, Oracle logs all changes to the database except changes in temporary tablespaces and temporary segments (NOLOGGING clauses are ignored). For more information, see Specifying FORCE LOGGING mode in the Oracle documentation.

To set force logging, use the Amazon RDS procedure rdsadmin.rdsadmin_util.force_logging. The force_logging procedure has the following parameters.

The following example puts the database in force logging mode.

EXEC rdsadmin.rdsadmin_util.force_logging(p_enable => true);

Setting supplemental logging

If you enable supplemental logging, LogMiner has the necessary information to support chained rows and clustered tables. For more information, see Supplemental logging in the Oracle documentation.

Oracle Database doesn't enable supplemental logging by default. To enable and disable supplemental logging, use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_supplemental_logging. For more information about how Amazon RDS manages the retention of archived redo logs for Oracle DB instances, see Retaining archived redo logs.

The alter_supplemental_logging procedure has the following parameters.

The following example enables supplemental logging.

begin
    rdsadmin.rdsadmin_util.alter_supplemental_logging(
        p_action => 'ADD');
end;
/

The following example enables supplemental logging for all fixed-length maximum size columns.

begin
    rdsadmin.rdsadmin_util.alter_supplemental_logging(
        p_action => 'ADD',
        p_type   => 'ALL');
end;
/

The following example enables supplemental logging for primary key columns.

begin
    rdsadmin.rdsadmin_util.alter_supplemental_logging(
        p_action => 'ADD',
        p_type   => 'PRIMARY KEY');
end;
/

Switching online log files

To switch log files, use the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile. The switch_logfile procedure has no parameters.

The following example switches log files.

EXEC rdsadmin.rdsadmin_util.switch_logfile;

Adding online redo logs

An Amazon RDS DB instance running Oracle starts with four online redo logs, 128 MB each. To add additional redo logs, use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_logfile.

The add_logfile procedure has the following parameters.

The following command adds a 100 MB log file.

EXEC rdsadmin.rdsadmin_util.add_logfile(p_size => '100M');

Dropping online redo logs

To drop redo logs, use the Amazon RDS procedure rdsadmin.rdsadmin_util.drop_logfile. The drop_logfile procedure has the following parameters.

The following example drops the log with group number 3.

EXEC rdsadmin.rdsadmin_util.drop_logfile(grp => 3);

You can only drop logs that have a status of unused or inactive. The following example gets the statuses of the logs.

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#     STATUS
---------- ----------------
1          CURRENT
2          INACTIVE
3          INACTIVE
4          UNUSED