Working with Oracle on Amazon RDS

Putting databases and everything else into the cloud has become quite popular over the last few years and it looks like there is nothing that can stop this movement. The biggest player in the field is Amazon so if you are looking at using their managed service RDS then here’s some of the common tasks that DBA’s will need to adjust to.

Firstly, RDS is a full managed service by Amazon and takes away some of the basic administration and makes it easy to setup, configure, backup and scale a database. You can read all the gory details here

What this means to DBA’s is there are a number of restrictions in what you can and can’t do. On thing is you can’t invoke the “alter system” command. You have to use the utilities provided by the RDS. So here’s a quick view of some of the common dba tasks in the RDS world and how you would invoke them.

Killing a Session

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

For use with version 11.2.0.3.v1 or higher:

exec rdsadmin.rdsadmin_util.kill(sid number, serial number, method varchar default null);

Flushing the Shared Pool

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

Modifying DBMS_SCHEDULER Jobs

Append the sys schema.

Oracle Method Amazon RDS Method
execute dbms_scheduler.
set_attribute(‘MONDAY_WINDOW’,’RESOURCE_PLAN’,”);
execute dbms_scheduler.
set_attribute(‘SYS.MONDAY_WINDOW’,’RESOURCE_PLAN’,”);

Adding, Dropping and Resizing Online Redo Logs

Oracle Method Amazon RDS Method
ALTER DATABASE ADD LOGFILE GROUP group_number
(‘log_member_path_and_name’) SIZE integer K|M;
exec rdsadmin.rdsadmin_util.add_logfile(size bytes);

exec rdsadmin.rdsadmin_util.drop_logfile(group#);

If you are using version 11.2.0.3.v1 or later, you can specify the size modifier as well. For example, the following command would add a 100 Mb log file:

exec rdsadmin.rdsadmin_util.add_logfile(‘100M’);

Setting Default Tablespace

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

Granting SELECT or EXECUTE privileges to SYS Objects (for version 11.2.0.3.v1 and later)

Oracle Method Amazon RDS Method
grant select on V_$SESSION to myuser; exec rdsadmin.rdsadmin_util.grant_sys_object(‘V_$SESSION’,’MYUSER’);

The above examples are just a few of the nuances when working with RDS. You can read all about the common dba tasks in the Oracle RDS user guide appendix.

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.