Working with Amazon RDS for Oracle

Before you can land data from Amazon RDS for Oracle, make sure to fulfill the prerequisites described below.

Amazon RDS for Oracle considerations and limitations

When replicating from Amazon RDS for Oracle, TDE is supported only with encrypted tablespace and only when using Oracle LogMiner.

Required Amazon RDS for Oracle permissions

This topic describes the permissions required when replicating from Amazon RDS for Oracle.

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 (which will allow Qlik Replicate to retrieve the log information using Oracle LogMiner), 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.

Using Replicate Log Reader to access the redo logs

The following steps are relevant only when using Replicate Log Reader to access the redo logs.

Create directories for online and archived logs

Before you can use Replicate Log Reader, you need to create directories for the online and archived logs.

To create the directories, run the following commands:

exec rdsadmin.rdsadmin_master_util.create_archivelog_dir;

exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;

To remove the directories (if there are no ongoing replication tasks), run the following commands:

exec rdsadmin.rdsadmin_master_util.drop_archivelog_dir;

exec rdsadmin.rdsadmin_master_util.drop_onlinelog_dir;

Setting the replace path prefix parameter

To work with Replicate Log Reader, you must set the Replace path prefix option in the Advanced tab with the following values:

Replace path prefix:/rdsdbdata/db/<ORACLE-INSTANCE-NAME>_A/

With:/rdsdbdata/log/

Limitations and considerations

The following Oracle features are not supported when using Replicate Log Reader with Amazon RDS for Oracle:

• ASM
• TDE