CDC Guide for Linux, UNIX, and Windows

10.5
- 10.5.6
- 10.5.5
- 10.5.4
- 10.5.3
- 10.5.2
- 10.5.1
- 10.4.1
- 10.4.0 HotFix 1
- 10.4.0

Back Next

Amazon RDS for Oracle Database Instances as Sources

PowerExchange Exchange Express CDC for Oracle can capture change data from online and archived redo logs for a database instance that is deployed in an Amazon Relational Database Service (RDS) for Oracle environment.

The redo logs must exist in ONLINELOG_DIR and ARCHIVELOG_DIR directories on the RDS file system. Express CDC runs on premises and reads change data from the redo logs.

PowerExchange Express CDC for Oracle cannot capture change data that was retrieved from TDE-encrypted tablespaces in an Amazon RDS Oracle database because the Oracle wallet is not available.

For Amazon RDS limitations and requirements related to Oracle versions and operating systems and other Oracle features, see the Amazon RDS documentation.

To configure change data capture for an Amazon RDS Oracle database, perform the following tasks:

Create the ONLINELOG_DIR and ARCHIVELOG_DIR directories that will hold the online and archive redo logs, respectively, on the RDS file system. Use the following exec statements:

exec rdsadmin.rdsadmin_master_util.create_archivelog_dir; 
exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;

Grant privileges to the Express CDC for Oracle user, ORACAPTL1.

You must log in to Amazon RDS under the master username to run the GRANT statements and procedures.

To grant the SELECT privilege, at minimum, on objects and system tables that are required for CDC processing, execute the following GRANT statements:

GRANT SELECT ON "PUBLIC"."V$ARCHIVED_LOG"           TO "ORACAPTL1";
GRANT SELECT ON "PUBLIC"."V$DATABASE"               TO "ORACAPTL1";
GRANT SELECT ON "PUBLIC"."V$LOG"                    TO "ORACAPTL1";
GRANT SELECT ON "PUBLIC"."V$LOGFILE"                TO "ORACAPTL1";
GRANT SELECT ON "PUBLIC"."V$TRANSPORTABLE_PLATFORM" TO "ORACAPTL1";
GRANT SELECT ON "PUBLIC"."V$THREAD"                 TO "ORACAPTL1";
GRANT SELECT ON "PUBLIC"."V$DATABASE_INCARNATION"   TO "ORACAPTL1";

GRANT SELECT ON "SYS"."DBA_LOG_GROUPS"        TO "ORACAPTL1";
GRANT SELECT ON "SYS"."DBA_LOG_GROUP_COLUMNS" TO "ORACAPTL1";
GRANT SELECT ON "SYS"."DBA_TABLESPACES"       TO "ORACAPTL1";

GRANT SELECT ON "SYS"."OBJ$"          TO "ORACAPTL1";
GRANT SELECT ON "SYS"."TAB$"          TO "ORACAPTL1";
GRANT SELECT ON "SYS"."IND$"          TO "ORACAPTL1";
GRANT SELECT ON "SYS"."COL$"          TO "ORACAPTL1";

GRANT SELECT ON "SYS"."PARTOBJ$"      TO "ORACAPTL1";
GRANT SELECT ON "SYS"."TABPART$"      TO "ORACAPTL1";
GRANT SELECT ON "SYS"."TABCOMPART$"   TO "ORACAPTL1";
GRANT SELECT ON "SYS"."TABSUBPART$"   TO "ORACAPTL1";
COMMIT;

To grant the SELECT privilege on some additional objects, run the following Amazon RDS procedures:

begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_USERS',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT',
p_grant_option => false);
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_TABLES',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT',
p_grant_option => false);
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_TAB_PARTITIONS',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT',
p_grant_option => false);
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$PARAMETER',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT');
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$SPPARAMETER',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT');
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$STANDBY_LOG',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT');
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$VERSION',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT');
end;
--
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'INDPART$',
p_grantee => 'ORACAPTL1',
p_privilege => 'SELECT');
end;
--
-- To register sources and perform other tasks in the PowerExchange Navigator:
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$PARAMETER',
p_grantee => 'registration_user',
p_privilege => 'SELECT');
end;

To provide read access to the Amazon RDS online and archived redo logs, execute the following GRANT statements:

GRANT READ ON DIRECTORY ONLINELOG_DIR to "ORACAPT1";
GRANT READ ON DIRECTORY ARCHIVELOG_DIR to "ORACAPT1";

To perform certain tasks, execute the following GRANT statements, as needed:

-- To run PowerExchange Logger tasks and extract change data continuously:
GRANT CREATE SESSION TO "ORACAPTL1";
--
-- To register sources and perform other tasks in the PowerExchange Navigator:
GRANT SELECT ON table TO "registration_user";       <-Repeat for each source table.
-- Or specify the following statement if your site security rules allow it: 
-- GRANT SELECT ANY TABLE to registration_user
--
-- To run the SQL for creating supplemental log groups at the end of registration:
GRANT ALTER ANY TABLE TO "navigator_user";
-- Or give the SQL file to your DBA. The DBA can run the SQL in the file.
--

Define an appropriate retention time for the archived redo logs. Use the following exec statement:

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention days',number_of_days);

In the Amazon RDS console, set the backup retention period for the source database to a value greater than zero to enable automated backups of the database instance.

This step enables ARCHIVELOG mode for the database.

Enable supplemental logging at the database level. Use the following exec statement:

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

Optionally, in the Amazon RDS console, you can create a parameter group and define the cache sizes of the default buffer pool. The default buffer pool holds buffers that use the primary database block size. Use the following DB_CACHE_SIZE parameter values:

DB_2K_CACHE_SIZE

DB_4K_CACHE_SIZE

DB_16K_CACHE_SIZE

DB_32K_CACHE_SIZE

Then select the parameter group for the source database.

Perform the usual PowerExchange configuration tasks, as described in Configuring PowerExchange for Express CDC.

When you configure the PowerExchange Express CDC for Oracle configuration file, specify RDS=Y
in the DATABASE statement to indicate that the source database is in an Amazon RDS for Oracle environment

Express CDC Considerations

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal