Amazon RDS - Oracle DBA Tasks

---

As an industry leading database technology, oracle has many in-built features which makes it easy to manage the DBA activities, even in the cloud. The Amazon RDS oracle DB provides access to many stored procedures and functions which can be accessed using the SQL developer client tool. This procedure can be executed using the user ID and password created during the Amazon RDS instance creation. Below are the examples of some of the most frequently used DBA activities.

Killing a Session

Sometimes a long running query or any other DB activity needs to be killed by killing the session. We use the Amazon RDS procedure rdsadmin.rdsadmin_util.kill to kill a session. The following code does that.

# First get the session identifier and the session serial number,
select SID, SERIAL#, STATUS from V$SESSION where USERNAME = 'AWSUSER';

# Next use the procedure 
begin
    rdsadmin.rdsadmin_util.kill(
        sid    => sid, 
        serial => serial_number);
end;
/

Setting the Default Tablespace

The Amazon RDS procedure rdsadmin.rdsadmin_util.alter_default_tablespace can be used to set to the default tablespace for a DB using the following command.

exec rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => 'AWSuser');

Setting the Database Time Zone

We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_db_time_zone to changes the time zone for the DB.

# Change the time zone of the DB to UTC + 5.30 
exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => '+5:30');
# Change the time zone to a specific region
exec rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => 'Asia/Kolkata');

Adding Online Redo Logs

We can use the Amazon RDS procedure rdsadmin.rdsadmin_util.add_logfile to add additional redo logs. The following command adds a log file of size 128MB.

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