Appendix: Common DBA Tasks for Oracle - Amazon Relational Database Service

Enabling and disabling Restricted Session

Oracle Method	Amazon RDS Method
`alter system enable restricted session;`	`exec rdsadmin.rdsadmin_util.restricted_session(true);`
`alter system disable restricted session;`	`exec rdsadmin.rdsadmin_util.restricted_session(false);`

The following example shows how to enable and disable restricted sessions.

select logins from v$instance;

LOGINS
-------
ALLOWED

exec rdsadmin.rdsadmin_util.restricted_session(true);

select logins from v$instance;

LOGINS
----------
RESTRICTED

exec rdsadmin.rdsadmin_util.restricted_session(false);

select logins from v$instance;

LOGINS
-------
ALLOWED

Flushing the Shared Pool

Oracle Method	Amazon RDS Method
`alter system flush shared_pool;`	`exec rdsadmin.rdsadmin_util.flush_shared_pool;`

Flushing the Buffer Cache

Oracle Method	Amazon RDS Method
`alter system flush buffer_cache;`	`exec rdsadmin.rdsadmin_util.flush_shared_pool;`

Checkpointing the Database

Oracle Method	Amazon RDS Method
`alter system checkpoint;`	`exec rdsadmin.rdsadmin_util.checkpoint;`

Killing a Session

Oracle Method	Amazon RDS Method
`alter system kill session ' sid, serial#' IMMEDIATE;`	`exec rdsadmin.rdsadmin_util.kill(sid, serial#);`

Switching Online Log files

Oracle Method	Amazon RDS Method
`alter system switch logfile;`	`exec rdsadmin.rdsadmin_util.switch_logfile;`

Setting Default Tablespace

Oracle Method	Amazon RDS Method
`alter database default tablespace users2;`	`exec rdsadmin.rdsadmin_util.alter_default_tablespace('users2');`

Creating and Resizing Tablespaces and Data Files

Amazon RDS only supports Oracle Managed Files (OMF) for data files, log files and control files. When creating data files and log files you cannot specify physical filenames.

The following example creates a tablespace:

create tablespace users2;

The following example creates temporary tablespace:

create temporary tablespace temp01;

Because the Oracle ALTER DATABASE system privilege is not available on Amazon RDS, you must use ALTER TABLESPACE to resize a tablespace. The following example resizes a tablespace named users2 to 200 MB:

alter tablespace users2 resize 200M;

Adding, dropping and resizing online redo logs

A newly created Amazon RDS instance using the Oracle database engine will have four 128MB online redo logs. In cases where you want to resize their logs or add more logs, the same restrictions apply to naming physical files for online redo logs.

Use the following procedures to add or drop redo logs:

exec rdsadmin.rdsadmin_util.add_logfile(size bytes);

exec rdsadmin.rdsadmin_util.drop_logfile(group#);

Example

This example shows how you can use the Amazon RDS-provided procedures to resize your online redo logs from their default size to 512M.

# Start with four 128m logs. 
SQL>select GROUP#, BYTES, STATUS from v$log;

GROUP#      BYTES STATUS
---------- ---------- ----------------
1  134217728 INACTIVE
2  134217728 CURRENT
3  134217728 INACTIVE
4  134217728 INACTIVE

4 rows selected.

# Add four new logs with that are each 512m.

SQL>exec rdsadmin.rdsadmin_util.add_logfile(536870912);

PL/SQL procedure successfully completed.

SQL>exec rdsadmin.rdsadmin_util.add_logfile(536870912);

PL/SQL procedure successfully completed.

SQL>exec rdsadmin.rdsadmin_util.add_logfile(536870912);

PL/SQL procedure successfully completed.

SQL>exec rdsadmin.rdsadmin_util.add_logfile(536870912);

PL/SQL procedure successfully completed.

# Now query v4log to show that there are 8 logs:

SQL>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

8 rows selected.

# Now, drop each INACTIVE log using the group#.

SQL>exec rdsadmin.rdsadmin_util.drop_logfile(1);

PL/SQL procedure successfully completed.

SQL>exec rdsadmin.rdsadmin_util.drop_logfile(3);

PL/SQL procedure successfully completed.

SQL>exec rdsadmin.rdsadmin_util.drop_logfile(4);

PL/SQL procedure successfully completed.

# 

SQL>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

8 rows selected.

# Switch logs so that group 2 is no longer current:

SQL>exec rdsadmin.rdsadmin_util.switch_logfile;

PL/SQL procedure successfully completed.

# 
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

5 rows selected.

# Issue a checkpoint to clear log 2

SQL>exec rdsadmin.rdsadmin_util.checkpoint;

PL/SQL procedure successfully completed.

#

SQL>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

5 rows selected.

# Checkpointing clears log group 2 so that its status is now INACTIVE allowing us to drop the final log group 2:

SQL>exec rdsadmin.rdsadmin_util.drop_logfile(2);

PL/SQL procedure successfully completed.

# Now, there are four 512m logs. Oracle using Oracle Managed Files (OMF) will automatically remove the old logfiles from the file system.

SQL>select GROUP#, BYTES, STATUS from v$log;

GROUP#      BYTES STATUS
---------- ---------- ----------------
5  536870912 CURRENT
6  536870912 UNUSED
7  536870912 UNUSED
8  536870912 UNUSED

4 rows selected.

Accessing alertlogs and listenerlogs

The standard method for accessing alert logs, trace files and listener logs requires host access. Since Amazon RDS does not provide host access, the following methods to access these files are included with every DB Instance using the Oracle database engine:

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;

	Note
	Oracle rotates the alert and listener logs when they exceed 10MB, at which point they will be unavailable from the Amazon RDS views. See Workig with Trace Files to access the older alertlogs and other files.

Working with Tracefiles

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.

rdsadmin.tracefile_listing

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.

Hanganalyze

Oracle Method	Amazon RDS Method
`oradebug hanganalyze 3`	`exec rdsadmin.manage_tracefiles.hanganalyze;`

System State Dump

Oracle Method	Amazon RDS Method
`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 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:
sql customer_dba/password@cust01 << EOF > /tmp/systemstatedump.txt
select * from tracefile_table;
EOF

Purging Trace Files

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

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(minutes number);

The following example shows how to purge all files that match a specific pattern:

exec rdsadmin.manage_tracefiles.purge_tracefiles('MYTRACE*');