Configure your source Oracle database

Overview

This section covers configuring your source Oracle database so that Datastream can pull data from it.

Configure your source Oracle database

Before you can use Datastream to pull data from your source Oracle database, you must configure your database by:

  • Setting up archive logging to track changes in your database, such as the insert, update, delete, and rename operations.
  • Granting the appropriate privileges to the user account that will be used to connect to your database.
  • Defining a data retention policy for your database to determine which data will be archived, how long it will be kept, should the data at the end of the retention period be archived or destroyed, and so on.

Information about how to configure each of these Oracle database types is covered in the sections that follow.

Datastream currently works with the following types of Oracle databases:

Configure an Amazon RDS for Oracle database

  1. Verify that your database is running in ARCHIVELOG mode. To do so, run the following command:

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

    4. Configure the Oracle log file rotation policy. We recommend setting a maximum log file size of no more than 512MB.

  2. Enable supplemental log data. To do so, first enable supplemental logging on the database 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
    

    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];
    GRANT CONNECT TO [user];
    GRANT CREATE SESSION TO [user];
    exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','[USER]','SELECT');
    exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','[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');
    exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','[USER]','EXECUTE');
    GRANT SELECT ANY TRANSACTION TO [user];
    GRANT SELECT ANY TABLE TO [user];
    

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

    GRANT LOGMINING TO [user];

Configure a self-hosted Oracle database

  1. Verify that your database is running in ARCHIVELOG mode. To do so, run the following command:

    SELECT LOG_MODE FROM V$DATABASE;

    1. If the result is ARCHIVELOG, then move on to step 2.

    2. If the result is NOARCHIVELOG, then you'll need to enable ARCHIVELOG mode for your database.

    3. Run the following commands when logged in as SYSDBA:

      SHUTDOWN IMMEDIATE;
      STARTUP MOUNT;
      ALTER DATABASE ARCHIVELOG;
      ALTER DATABASE OPEN;
      
  2. Define a data retention policy for your database by running these Oracle Recovery Manager (RMAN) commands:

        RMAN > TARGET /
        RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
      

    We recommend that you retain backups and archive logs for a minimum of 4 days, and 7 days is recommended.

  3. Return to the SQL prompt of the database tool that you're using to configure the Oracle log file rotation policy. We recommend setting a maximum log file size of no more than 512MB.

  4. Enable supplemental log data. To do so, first enable supplemental logging on the database by running the following command:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    

    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
    

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

    Run the following command to enable supplemental log data for the entire database:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (all) COLUMNS;
  5. 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];
    GRANT CONNECT TO [user];
    GRANT CREATE SESSION TO [user];
    GRANT SELECT ON SYS.V_$DATABASE TO [user];
    GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO [user];
    GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO [user];
    GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO [user];
    GRANT EXECUTE ON DBMS_LOGMNR TO [user];
    GRANT EXECUTE ON DBMS_LOGMNR_D TO [user];
    GRANT SELECT ANY TRANSACTION TO [user];
    GRANT SELECT ANY TABLE TO [user];
    

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

    GRANT LOGMINING TO [user];