Prerequisites

• Oracle database version is 12c and above.

• A database user is created with the necessary privileges.

• Redo Log replication is enabled.

• Hevo’s IP addresses are allowlisted.

• The database hostname and port number of the Source instance are available.

Perform the following steps to configure your Amazon RDS Oracle Source:

Create a Database User and Grant Privileges

1. Create a database user (optional)

CREATE USER <username> IDENTIFIED BY <password>;

2. Grant privileges to the user

The database user that you specify in the Hevo Pipeline must have SELECT privileges. To assign this privilege, connect to your Oracle server in SQL Developer or any SQL client tool as a root user and enter the following commands:

1. (Optional) Check current privileges assigned to the database user:

   SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '<username>';
   

   The above command returns a table with the PRIVILEGE column specifying the privileges and the tables on which they are granted.

2. Grant access to query data dictionary views and create and alter session settings:

   GRANT SELECT ANY DICTIONARY to <username>;
   GRANT CONNECT, CREATE, ALTER SESSION TO <username>;
   

   This allows the database user to retrieve information about the database structure and metadata.

3. Grant access to the user to select data from all views accessible to them in the database:

   BEGIN
   rdsadmin.rdsadmin_util.grant_sys_object('ALL_VIEWS','<USERNAME>','SELECT');
   END;
   

   Note: In the command above, the <USERNAME> must be in capital letters.

4. Do one of the following:

   • Grant access to the database user to select data from a specific table within a specific schema:

     GRANT SELECT ON <schema_name>.<table_name> TO <username>;
     

   • Grant access to the database user to select data from any table:

     GRANT SELECT ANY TABLE TO <username>;
     

Note: Replace the placeholder values in the commands above with your own. For example, <schema_name> with sales.

Set up Redo Logs for Replication

A redo log is a collection of log files that record information about modifications made to data objects on an Oracle server instance. Oracle LogMiner uses redo logs to track these modifications and determine the rows requiring updates in the Destination system.

Connect to your Oracle server as a user with SYSDBA privileges using SQL Developer or any other SQL client tool and perform the following steps:

1. Enable Archive log

Archive logs are essential for the backup and recovery processes of a database management system, especially in systems that use the transactional or logging approach to maintain data integrity. Before enabling this, verify if the current archiving mode of the database is ARCHIVELOG:

SELECT LOG_MODE FROM V$DATABASE;

This query returns either of the following values:

• ARCHIVELOG: Indicates that archive log mode is enabled.

• NOARCHIVELOG: Indicates that archive log mode is disabled.

If the result of the above query is NOARCHIVELOG, enable archive log using the following commands:

BEGIN
rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 72);
END;

Note: The minimum value for archivelog retention hours is 72. The archive log retention must be 72 hours at a minimum. This avoids any data loss that may occur due to downtimes in the Source database.

2. Enable supplemental logging

Supplemental logging ensures that columns are logged in Redo log files, which is essential for LogMiner to access the activity history of a database.

Before enabling supplemental logging, check if it is already enabled:

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM "V$DATABASE";

This returns either of the following values:

• YES: This represents that supplemental logging is enabled.

• IMPLICIT: Indicates that supplemental logging is automatically enabled by the database based on certain conditions or configurations.

• NO: This represents that supplemental logging is disabled.

If the result of the above query is NO, enable supplemental logging at the database level:

BEGIN
rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');
END;

3. Grant permissions to run LogMiner

The database user you created above must have SELECT privileges on LogMiner views and EXECUTE privileges on LogMiner packages. This ensures that the user can effectively analyze, mine redo log files, gaining valuable insights into database changes.

To grant these privileges, enter the following commands:

BEGIN
    rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','<USERNAME>','EXECUTE');
    rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','<USERNAME>','EXECUTE');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG_HISTORY', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_PARAMETERS', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVE_DEST', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$MYSTAT', '<USERNAME>','SELECT');
    rdsadmin.rdsadmin_util.grant_sys_object('V_$STATNAME', '<USERNAME>','SELECT');
END;

Note: Replace the placeholder values in the commands above with your own. For example, <USERNAME> with JACOBS.

Refer to the table below for more information about these commands:

Allowlist Hevo’s IP Addresses

You need to allowlist the Hevo IP address for your region to enable Hevo to connect to your Amazon RDS Oracle database. You can do this by creating a VPC security group and adding inbound and outbound access rules for the Hevo IP addresses. A VPC group controls access to the database instances and virtual server instances inside a VPC. To do this:

1. Create a VPC security group

1. Access the Amazon RDS console.

2. In the left navigation pane, under Dashboard, select Databases (or Instances if you are using an older version).

3. In the Databases section on the right, select the read replica or master database instance that you want to connect.

   

4. In the Connectivity & Security tab, click the hyperlink under Security, VPC security groups.

   

5. In the Security Groups page, click Create security group.

   You can also click on an existing group, which you have used for other database instances, and modify it (or use the Security group selected by Default).

   

6. In the Create security group page, specify the following:

   • Security group name: An appropriate name for the security group.

   • Description: A brief description of the security group.

   • VPC ID: A unique identifier for the VPC.

2. Add inbound rules

In the Inbound Rules section:

1. Click Add Rule and specify the following:

   • Port range: The port of your Amazon RDS Oracle instance. For example, 1521.

   • Source: Select Custom from the drop-down and enter Hevo’s IP addresses for your region.

2. Click Save rule.

3. Add more rules for all the Hevo IPs you want to allow.

Retrieve the Hostname, Service ID, and Port Number

For configuring the Amazon RDS Oracle Source in Hevo, you must specify the database name, hostname, and port. The hostnames start with your database name and end with rds.amazonaws.com.

For example: Host: oracle-database-kb.xxxxxxxxx.rds.amazonaws.com
Database name: ORCL Port: 1521

1. In the left navigation pane of the Amazon RDS console, click Databases (or Instances if you are using an older version).

2. In the Databases section on the right, click the DB identifier of the Amazon RDS Oracle instance.

   

3. Click the Connectivity & Security tab, and copy the values under Endpoint and Port. Specify these values as the Database Host and Database Port, respectively, while creating your Hevo Pipeline.

   

4. Click the Configuration tab, and copy the value under DB name. Use this value as the Database Name while creating your Hevo Pipeline.