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

Common DBA Log Tasks for Oracle DB Instances

This section describes how you can perform 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 Oracle Database Log Files.

Setting Force Logging

Setting force logging is supported for Oracle version 11.2.0.4.v1 and later.

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.

You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.force_logging to set force logging. The force_logging procedure has the following parameters.

Parameter Name Data Type Default Required Description

p_enable

boolean

true

optional

Set to true to put the database in force logging mode, false to remove the database from force logging mode.

The following example puts the database in force logging mode.

Copy
exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);

Setting Supplemental Logging

Setting supplemental logging is supported for Oracle version 11.2.0.4.v1 and later.

Supplemental logging ensures that LogMiner and products that use LogMiner technology have sufficient information to support chained rows and storage arrangements such as cluster tables. For more information, see Supplemental Logging in the Oracle documentation.

Oracle Database doesn't enable supplemental logging by default. You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_supplemental_logging to enable and disable 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.

Parameter Name Data Type Default Required Description

p_action

varchar2

required

'ADD' to add supplemental logging, 'DROP' to drop supplemental logging.

p_type

varchar2

null

optional

The type of supplemental logging. Valid values are 'ALL', 'FOREIGN KEY', 'PRIMARY KEY', or 'UNIQUE'.

The following example enables supplemental logging:

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

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

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

The following example enables supplemental logging for primary key columns:

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

Switching Online Log Files

You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile to switch log files. The switch_logfile procedure has no parameters.

The following example switches log files.

Copy
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. You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_logfile to add additional redo logs.

For any version of Oracle, the add_logfile procedure has the following parameters.

Parameter Name Data Type Default Required Description

bytes

positive

null

optional

The size of the log file in bytes.

If you are using version 11.2.0.4.v1 or later, the add_logfile procedure has the following parameters.

Parameter Name Data Type Default Required Description

p_size

varchar2

required

The size of the log file. You can specify the size in kilobytes (K), megabytes (M), or gigabytes (G).

The following command adds a 100 MB log file:

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

Dropping Online Redo Logs

You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.drop_logfile to drop redo logs. The drop_logfile procedure has the following parameters.

Parameter Name Data Type Default Required Description

grp

positive

requried

The group number of the log.

The following example drops the log with group number 3:

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

Copy
select GROUP#, STATUS from V$LOG; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVE 4 UNUSED

Resizing Online Redo Logs

An Amazon RDS DB instance running Oracle starts with four online redo logs, 128 MB each. The following example shows how you can use Amazon RDS procedures to resize your logs from 128 MB each to 512 MB each.

Copy
/* Query V$LOG to see the logs. */ /* You start with 4 logs of 128 MB each. */ select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- ---------------- 1 134217728 INACTIVE 2 134217728 CURRENT 3 134217728 INACTIVE 4 134217728 INACTIVE /* Add four new logs that are each 512 MB */ exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); exec rdsadmin.rdsadmin_util.add_logfile(bytes => 536870912); /* Query V$LOG to see the logs. */ /* Now there are 8 logs. */ select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- ---------------- 1 134217728 INACTIVE 2 134217728 CURRENT 3 134217728 INACTIVE 4 134217728 INACTIVE 5 536870912 UNUSED 6 536870912 UNUSED 7 536870912 UNUSED 8 536870912 UNUSED /* Drop each inactive log using the group number. */ exec rdsadmin.rdsadmin_util.drop_logfile(grp => 1); exec rdsadmin.rdsadmin_util.drop_logfile(grp => 3); exec rdsadmin.rdsadmin_util.drop_logfile(grp => 4); /* Query V$LOG to see the logs. */ /* Now there are 5 logs. */ select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- ---------------- 2 134217728 CURRENT 5 536870912 UNUSED 6 536870912 UNUSED 7 536870912 UNUSED 8 536870912 UNUSED /* Switch logs so that group 2 is no longer current. */ exec rdsadmin.rdsadmin_util.switch_logfile; /* Query V$LOG to see the logs. */ /* Now one of the new logs is current. */ SQL>select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- ---------------- 2 134217728 ACTIVE 5 536870912 CURRENT 6 536870912 UNUSED 7 536870912 UNUSED 8 536870912 UNUSED /* Issue a checkpoint to clear log 2. */ exec rdsadmin.rdsadmin_util.checkpoint; /* Query V$LOG to see the logs. */ /* Now the final original log is inactive. */ select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- ---------------- 2 134217728 INACTIVE 5 536870912 CURRENT 6 536870912 UNUSED 7 536870912 UNUSED 8 536870912 UNUSED # Drop the final inactive log. exec rdsadmin.rdsadmin_util.drop_logfile(grp => 2); /* Query V$LOG to see the logs. */ /* Now there are four 512 MB logs. */ select GROUP#, BYTES, STATUS from V$LOG; GROUP# BYTES STATUS ---------- ---------- ---------------- 5 536870912 CURRENT 6 536870912 UNUSED 7 536870912 UNUSED 8 536870912 UNUSED

Retaining Archived Redo Logs

Retaining archived redo logs locally on your DB instance is supported for Oracle version 11.2.0.2.v7 and later.

You can retain archived redo logs locally on your DB instance for use with products like Oracle LogMiner (DBMS_LOGMNR). After you have retained the redo logs, you can use LogMiner to analyze the logs. For more information, see Using LogMiner to Analyze Redo Log Files in the Oracle documentation.

You can use the Amazon RDS procedure rdsadmin.rdsadmin_util.set_configuration to retain archived redo logs. The set_configuration procedure has the following parameters.

Parameter Name Data Type Default Required Description

name

varchar

required

The name of the configuration to update.

value

varchar

required

The value for the configuration.

The following example retains 24 hours of redo logs:

Copy
begin rdsadmin.rdsadmin_util.set_configuration( name => 'archivelog retention hours', value => '24'); end; /

Because the archived redo logs are retained on your DB instance, ensure that your DB instance has enough allocated storage for the retained logs. To determine how much space your DB instance has used in the last X hours, you can run the following query, replacing X with the number of hours:

Copy
select sum(BLOCKS * BLOCK_SIZE) bytes from V$ARCHIVED_LOG where FIRST_TIME >= SYSDATE-(X/24) and DEST_ID=1;

Archived redo logs are only generated if the backup retention period of your DB instance is greater than zero. By default the backup retention period is greater than zero, so unless you explicitly set yours to zero, archived redo logs are generated for your DB instance. To modify the backup retention period for your DB instance, see Modifying a DB Instance Running the Oracle Database Engine.

After the archived redo logs are removed from your DB instance, you can't download them again to your DB instance. Amazon RDS retains the archived redo logs outside of your DB instance to support restoring your DB instance to a point in time. Amazon RDS retains the archived redo logs outside of your DB instance based on the backup retention period configured for your DB instance. To modify the backup retention period for your DB instance, see Modifying a DB Instance Running the Oracle Database Engine.

Accessing Transaction Logs

Accessing transaction logs is supported for Oracle version 11.2.0.4.v11 and later, and 12.1.0.2.v7 and later.

You might want to access your online and archived redo log files for mining with external tools such as GoldenGate, Attunity, Informatica, and others. If you want to access your online and archived redo log files, you must first create directory objects that provide read-only access to the physical file paths.

The following code creates directories that provide read-only access to your online and archived redo log files:

Important

This code also revokes the DROP ANY DIRECTORY privilege.

Copy
exec rdsadmin.rdsadmin_master_util.create_archivelog_dir; exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;

After you create directory objects for your online and archived redo log files, you can read the files by using PL/SQL. For more information about reading files from directory objects, see Listing Files in a DB Instance Directory and Reading Files in a DB Instance Directory.

The following code drops the directories for your online and archived redo log files:

Copy
exec rdsadmin.rdsadmin_master_util.drop_archivelog_dir; exec rdsadmin.rdsadmin_master_util.drop_onlinelog_dir;

The following code grants and revokes the DROP ANY DIRECTORY privilege:

Copy
exec rdsadmin.rdsadmin_master_util.revoke_drop_any_directory; exec rdsadmin.rdsadmin_master_util.grant_drop_any_directory;

Related Topics