Configure an Amazon RDS Oracle database

  1. Verify that your database is running in ARCHIVELOG mode. To do so, sign in to your Oracle database and run the following command at the SQL prompt:

    SELECT LOG_MODE FROM V$DATABASE;

    1. If the result is ARCHIVELOG, then move on to step c.
    2. If the result is NOARCHIVELOG, then you'll need to enable ARCHIVELOG mode for your database.
    3. Archived log files consume disk space, so you'll want to configure the DB_RECOVERY_FILE_DEST_SIZE parameter for your database. Use this parameter to specify (in bytes) the hard limit on the total space to be used by target database recovery files. By setting this parameter, you can manage the tradeoff between protecting the database from running out of disk space and the stream failing because of log position loss.
    4. Define a data retention policy for your database by running this command: 
      exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',96);
      We recommend that you retain backups and archive logs for a minimum of 4 days, and 7 days is recommended.
    5. Configure the Oracle log file rotation policy. We recommend setting a maximum log file size to a value lower than 1GB.

  2. Enable supplemental log data. To do so, first enable minimal database-level supplemental logging by running the following command: 

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

    Next, choose whether to turn on logging for specific tables or the entire database.

    To log changes only for specific tables, run the following command for each table that you want to replicate:

    ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

    Replace the following:

    • SCHEMA: the name of the schema that contains the table.
    • TABLE: the name of the table for which you want to log changes.

    To replicate most or all tables in your database, consider turning logging on for the entire database.

    At the SQL prompt, run the following command to enable supplemental log data for the entire database:

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

  3. Grant the appropriate privileges to the user account that will be used to connect to your database. To do so, run the following commands:

    GRANT EXECUTE_CATALOG_ROLE TO USER_NAME;
GRANT CONNECT TO USER_NAME;
GRANT CREATE SESSION TO USER_NAME;
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','USER_NAME','EXECUTE');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','USER_NAME','EXECUTE');
GRANT SELECT ANY TRANSACTION TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_NAME;

    If your organization doesn't permit granting the GRANT SELECT ANY TABLE permission, use the solution described in the Oracle change data capture (CDC) section of the Datastream FAQ page.

    If your source database is Oracle 12c or newer, then grant the following additional privilege:

    GRANT LOGMINING TO USER_NAME;