Working with Amazon RDS for Oracle

When using Amazon RDS for Oracle as a source, make sure to fulfill the prerequisites described below.

Required Amazon RDS for Oracle permissions

Grant the following privileges:

• GRANT SELECT ANY TABLE
• GRANT SELECT on ALL_VIEWS to user;
• GRANT SELECT ANY TRANSACTION to user;
• GRANT SELECT on DBA_TABLESPACES to user;
• GRANT SELECT on ALL_TAB_PARTITIONS to user;
• GRANT SELECT on ALL_INDEXES to user;
• GRANT SELECT on ALL_OBJECTS to user;
• GRANT SELECT on ALL_TABLES to user;
• GRANT SELECT on ALL_USERS to user;
• GRANT SELECT on ALL_CATALOG to user;
• GRANT SELECT on ALL_CONSTRAINTS to user;
• GRANT SELECT on ALL_CONS_COLUMNS to user;
• GRANT SELECT on ALL_TAB_COLS to user;
• GRANT SELECT on ALL_IND_COLUMNS to user;
• GRANT SELECT on ALL_LOG_GROUPS to user;
• GRANT LOGMINING TO user;

In addition, execute the following statements:

exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$THREAD','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$NLS_PARAMETERS','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TIMEZONE_NAMES','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$CONTAINERS','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_REGISTRY','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('OBJ$','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('ALL_ENCRYPTED_COLUMNS','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','user','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','user','EXECUTE');

Preparing replication from Amazon RDS for Oracle

This section details the requirements for working with Oracle on Amazon RDS.

Set up supplemental logging

Qlik Replicate requires database-level supplemental logging to be enabled. To enable database-level supplemental logging, execute the following command:

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

Although not required, examples of additional commands that you can execute to change the supplemental logging attributes include:

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

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

Enabling automatic backups

In Step 5: Management Options of setting up your Oracle database instance, set the Enabled Automatic Backups option to Yes.

Setting up archiving

To retain archived redo logs of your Oracle database instance, execute the following command (example 24 hours):

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

Make sure that your storage has sufficient space for the archived redo logs during the specified period.