AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Working with Oracle on Amazon RDS » Appendix: Common DBA Tasks for Oracle	« Previous Next »
	Did this page help you? Yes \| No \| Tell us about it...

Appendix: Common DBA Tasks for Oracle

In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB Instances, and restricts access to certain system procedures and tables that require advanced privileges. This section describes the Amazon RDS-specific implementations of some common DBA tasks for DB Instances running the Oracle database engine.

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_buffer_cache;`

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;`

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.

Setting Default Tablespace

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

Setting the Database Time Zone

You can alter the time zone of a database by running the rdsadmin_util procedure as shown in the following table. You can use any time zone name or GMT offset that is accepted by Oracle.

Oracle Method	Amazon RDS Method
`alter database set time_zone = '+3:00';`	`exec rdsadmin.rdsadmin_util.alter_db_time_zone('+3:00');`

After you alter the time zone, you must reboot the DB Instance for the change to take effect.

There are additional restrictions on setting time zones listed in the Oracle documentation.

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;

Document Conventions	« Previous Next »
Terms of Use	Did this page help you? Yes \| No \| Tell us about it...