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

Common DBA Tasks for Oracle DB Instances

This section describes the Amazon RDS-specific implementations of some common DBA tasks for DB instances running the Oracle database engine. 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.

For information about working with Oracle log files on Amazon RDS, see Oracle Database Log Files.

Tasks

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;

Disconnecting a Session (for version 11.2.0.3.v1 and later)

The following Amazon RDS method disconnects the current session by ending the dedicated server process. Note that the database must be open to use this method. For more information about disconnecting a session, see the Oracle documentation.

You must specify both the SID and serial number of the session. To obtain these values, query the V$SESSION view. For example, the following query shows all sessions for the user AWSUSER:

SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'AWSUSER'; 
Oracle Method Amazon RDS Method

alter system disconnect session;

exec rdsadmin.rdsadmin_util.disconnect(sid number, serial number, method varchar default 'IMMEDIATE');

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

If you are using version 11.2.0.3.v1 or higher, you can specify either IMMEDIATE or PROCESS as a value for the method parameter. Specifying PROCESS as the method value enables you to kill processes associated with a session. You should only do this if killing the session using IMMEDIATE as the method value was unsuccessful.

Renaming the Global Name (for version 11.2.0.3.v1 and later)

The following Amazon RDS method changes the global name of the database. Note that the database must be open for the name change to take effect. For more information about changing the global name of a database, see the Oracle documentation.

Oracle Method Amazon RDS Method

alter database rename global_name;

exec rdsadmin.rdsadmin_util.rename_global_name(p_new_global_name in varchar2);

Granting Privileges to Non-Master Users

The following example creates a non-master user named user1 and grants the CREATE SESSION privilege and the SELECT privilege for a database named sh.sales:

CREATE USER user1 IDENTIFIED BY password;
GRANT CREATE SESSION TO user1;
GRANT SELECT ON sh.sales TO user1; 

You can grant explicit object privileges for objects in the SYS schema using the SELECT_CATALOG_ROLE and the EXECUTE_CATALOG_ROLE roles. The SELECT_CATALOG_ROLE role allows users SELECT privileges on data dictionary views and the EXECUTE_CATALOG_ROLE role allows users EXECUTE privileges for packages and procedures in the data dictionary.

The following example grants the SELECT_CATALOG_ROLE role to a user named user1:

GRANT SELECT_CATALOG_ROLE TO user1;  

The following example grants the EXECUTE_CATALOG_ROLE role to a user named user1:

GRANT EXECUTE_CATALOG_ROLE TO user1; 

To view the permissions that the SELECT_CATALOG_ROLE and the EXECUTE_CATALOG_ROLE roles allow, use the following query:

SELECT * FROM ROLE_TAB_PRIVS 
    WHERE ROLE IN ('SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE') 
    ORDER BY ROLE, TABLE_NAME ASC; 

Modifying DBMS_SCHEDULER Jobs

You can modify the default DBMS_SCHEDULER jobs and windows by following the Oracle documentation, but you need to prepend the SYS schema name to the WINDOW_NAME. For example, with a local Oracle database you could do the following:

execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN',''); 

For an Amazon RDS DB instance, you would include the SYS schema name:

execute dbms_scheduler.set_attribute('SYS.MONDAY_WINDOW','RESOURCE_PLAN',''); 

Switching Online Log files

You can use the following Amazon RDS method to switch 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. Note that in cases where you want to add more logs, the same restrictions apply to naming physical files as they do for naming 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#);

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');

Example

The following 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 v$log 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 Force Logging (for version 11.2.0.3.v1 and later)

The following Amazon RDS method puts the database in or removes the database from FORCE LOGGING mode. In FORCE LOGGING mode, Oracle logs all changes to the database except changes in temporary tablespaces and temporary segments. For more information about forcing logging, see the Oracle documentation.

Oracle Method Amazon RDS Method

alter database [no] force logging;

exec rdsadmin.rdsadmin_util.force_logging(p_enable in boolean := true);

Retaining Archived Redo Logs (for version 11.2.0.2.v7 and later)

You can retain archived redo logs on your DB instance for use with products like Oracle LogMiner (DBMS_LOGMNR). Once you have retained the redo logs, you can use LogMiner to analyze the logs as explained in the Oracle documentation. Note that you need to ensure that the DB instance has enough allocated storage to store the retained logs.

Use the Amazon RDS method rdsadmin.rdsadmin_util.set_configuration to retain archived redo logs. The following example shows how to retain 24 hours of redo logs:

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24); 

If you need 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:

select sum(blocks * block_size) bytes from v$archived_log 
   where first_time >=sysdate-X/24 and dest_id=1; 

Setting Supplemental Logging (for version 11.2.0.3.v1 and later)

The following Amazon RDS method enables supplemental logging, including minimal supplemental logging. Oracle Database does not enable supplemental logging by default. Supplemental logging ensures that LogMiner and products that use LogMiner technology will have sufficient information to support chained rows and various storage arrangements such as cluster tables. For more information on supplemental logging, see the Oracle documentation.

Oracle Method Amazon RDS Method

alter database [add|drop] supplemental log;

exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action in varchar2, p_type in varchar2 default NULL);

alter database add supplemental log data (PRIMARY KEY) columns;

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');

alter database add supplemental log data (ALL) columns;

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');

alter database add supplemental log data (UNIQUE) columns;

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','UNIQUE');

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 file names.

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 bigfile tablespace named users2 to 200 MB:

alter tablespace users2 resize 200M;

For smallfile tablespaces, you need to add an additional datafile, like in the following example:

ALTER TABLESPACE users2 ADD DATAFILE SIZE 100000M AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Setting Default Tablespace

Oracle Method Amazon RDS Method

alter database default tablespace users2;

exec rdsadmin.rdsadmin_util.alter_default_tablespace('users2');

Setting Default Temporary Tablespace

Oracle Method Amazon RDS Method

alter database default temporary tablespace temp2;

exec rdsadmin.rdsadmin_util.alter_default_temp_tablespace('temp2');

Checkpointing the Database

Oracle Method Amazon RDS Method

alter system checkpoint;

exec rdsadmin.rdsadmin_util.checkpoint;

Setting Distributed Recovery (for version 11.2.0.3.v1 and later)

Oracle Method Amazon RDS Method

alter system enable/disable distributed recovery;

exec rdsadmin.rdsadmin_util.enable_distr_recovery and rdsadmin_util.disable_distr_recovery (mydatabase);

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

Generally, you can use grant select_catalog_role or grant execute_catalog_role to grant privileges. If you need to grant privileges to a single object instead of using a role that may contain many objects, you can use the grant_sys_object Amazon RDS method. You can set the third parameter (p_grant_option) to TRUE to grant such privileges with the grant option.

The following procedure transfers existing privileges such as SELECT and EXECUTE via a role to another account. Note that it only grants privileges that the master account already has via a role or direct grant.

Oracle Method Amazon RDS Method

grant select on V_$SESSION to myuser;

exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION','MYUSER');

grant select on V_$SESSION to myuser with grant option;

exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION','MYUSER','TRUE');

In order to be able to grant privileges on an object, your account must have those privileges granted to it directly with the grant option or via a role granted using with admin option. In the most common case, you may want to grant SELECT on a DBA view that has been granted to the SELECT_CATALOG_ROLE role. If that role isn't already directly granted to your user using with admin option, then you won't be able to transfer the privilege. If you have the DBA privilege, then you can grant the role directly to another user.

For example, an initial grant for SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE could be:

GRANT SELECT_CATALOG_ROLE TO user1 WITH ADMIN OPTION; 
GRANT EXECUTE_CATALOG_ROLE TO user1 WITH ADMIN OPTION; 

In the previous example, since "WITH ADMIN OPTION," was used when granting "user1" access, "user1" will be able to grant access to SYS objects that have been granted to SELECT_CATALOG_ROLE.

Note that objects already granted to PUBLIC do not need to be re-granted, but if you use the grant_sys_object procedure to re-grant access the procedure will not fail. Note too that object names must be spelled exactly as they appear in DBA_OBJECTS (Most SYS objects are defined in UPPERCASE, so we recommend you try that first).

Setting the Database Time Zone

You can alter the time zone of a database in two ways, by either using the rdsadmin_util.alter_db_time_zone command or by setting the Oracle Time Zone option. The rdsadmin_util.alter_db_time_zone command changes the time zone for only certain data types and does not change SYSDATE, and must be used with versions 11.2.0.2.v4 or later. The Timezone option changes the time zone at the host level and impacts all date columns and values such as SYSDATE.

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.

Working with Automatic Workload Repository (AWR)

If you use Oracle Enterprise Edition and want to use Automatic Workload Repository (AWR), you can enable AWR by changing the CONTROL_MANAGEMENT_PACK_ACCESS parameter.

Oracle AWR includes several report generation scripts, such as awrrpt.sql, that are installed on the host server. Since you do not have access to host directories, you can download the scripts.

To use Oracle database links with DB instances inside a VPC, the two instances must be either in the same VPC or you must create an EC2 or VPC security group that both DB instances are a member of. For example, when using Oracle Data Pump and Oracle DBLinks to move data between DB instances, the instances must be members of the same VPC or EC2 security group or they must be in the same VPC. For more information about using database links with Oracle Data Pump, see Oracle Data Pump

Creating New Directories in the Main Data Storage Space (for version 11.2.0.4.v1 and later)

A DB instance come with a set of directories; you can create additional directories using the following Amazon RDS method. The create_directory() method lets you create up to 10,000 directories, all located in your main data storage space. The following example uses the method to create a directory named "MY_DIR".

Oracle Method Amazon RDS Method

create directory MY_DIR as '/my/os/pathname';

exec rdsadmin.rdsadmin_util.create_directory('MY_DIR');

You can list the directories by querying the DBA_DIRECTORIES view. Note that the system chose the actual host pathname automatically:

select * from DBA_DIRECTORIES where directory_name='MY_DIR';
        
select directory_path from DBA_DIRECTORIES where directory_name='MY_DIR';
        
DIRECTORY_PATH
----------------------------------------
/rdsdbdata/userdirs/01 

The master user name for the DB instance has read and write privileges in the new directory, and can grant access to other users. Note that "execute" privileges are not available for directories on a DB instance. Directories are created in your main data storage space and will consume space and I/O bandwidth.

You can drop a directory that you created by using the Oracle drop directory command. Dropping a directory does not remove its contents; because the create_directory() method can reuse pathnames, files in dropped directories could appear in a newly created directory. Before you drop a directory, you should use UTL_FILE.FREMOVE to remove files from the directory.

Listing and Reading Files in a DB Instance Directory (for version 11.2.0.3.v1 and later)

You can use the RDSADMIN.RDS_FILE_UTIL.LISTDIR() Amazon RDS method to list the files in any DB instance directory (from DBA_DIRECTORIES) that you have access to:

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')); 

If you find a text file that you want to read, you can use the RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE() Amazon RDS method. The following example reads the filename.log file in the DATA_PUMP_DIR directory:

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','filename.log'));