Amazon RDS Oracle (Edge)

Prerequisites

• The Oracle database version is 12c or above.

• A database user is created with the necessary privileges.

• Redo Log-based replication is enabled.

• Hevo’s IP address(es) for your region is added to the Amazon RDS Oracle database IP Allowlist.

• 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

Connect to your Oracle server as a database administrator (DBA) using SQL Developer or any other SQL client tool and run the following script. This script creates a new database user in your Oracle database with access to the metadata tables and LogMiner. Keeping your privacy in mind, it grants only the necessary permissions required by Hevo to ingest data from your Oracle database.

# Create a Database User 
CREATE USER <username> IDENTIFIED BY <password>;

# Grant Privileges to the Database User
GRANT CREATE SESSION, ALTER SESSION, CONNECT TO <username>;
GRANT FLASHBACK ANY TABLE TO <username>;
GRANT SELECT ANY TABLE TO <username>;
GRANT SELECT ANY TRANSACTION TO <username>;

# Grant Roles to the Database User
GRANT SELECT_CATALOG_ROLE TO <username>;
GRANT EXECUTE_CATALOG_ROLE TO <username>;

# Grant Permission to run LogMiner
GRANT LOGMINING TO <username>;

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

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

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 archive log retention hours must be 3 days (72 hours). This avoids any data loss that may occur due to downtimes in the Source database.

2. Configure the Fast Recovery Area

The fast recovery area in Oracle serves as a centralized storage space for all database recovery-related files. Consolidating these files into a single location simplifies backup and recovery management.

To enable fast recovery area, follow these steps:

1. Access the Amazon RDS console.

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

3. In the Databases section on the right, click the DB instance that you want to connect to Hevo.

   

4. Click the Configuration tab, and then click the link text under DB instance parameter group.

   

5. Click Edit.

   

6. Set appropriate values for the following parameters:

   • DB_RECOVERY_FILE_DEST_SIZE: Sets the size of the fast recovery area.

   • DB_RECOVERY_FILE_DEST: Sets the location of the fast recovery area. This area contains the redo logs.

   • ARCHIVE_LAG_TARGET: Sets a non-zero target value in seconds for the maximum acceptable delay between log switches.

   

7. Click Save Changes.

8. Reboot the database instance that you are using to connect to Hevo, to apply the above changes.

   To do this:

   1. In the left navigation pane, under Dashboard, click Databases.

   2. In the Databases section on the right, select the DB instance that you want to connect to Hevo.

   3. In the Actions drop-down, click Reboot.

      

   4. On the Reboot DB Instance page, click Confirm to reboot your DB instance.

      

3. Enable supplemental logging

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

1. Check if supplemental logging is enabled:

   SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM "V$DATABASE";
   

   This query returns one of the following values:

   • YES: Indicates that supplemental logging is enabled.

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

   • NO: Indicates that supplemental logging is disabled.

2. If the result of the above query is NO, enable supplemental logging with one of the following commands:

   • Enable supplemental logging at the database level for all columns:

   begin
   rdsadmin.rdsadmin_util.alter_supplemental_logging(
       p_action => 'ADD',
       p_type   => 'ALL');
   end;
   

   Note: Enabling supplemental logging at the database level will increase redo log data.

   • Enable minimal supplemental logging at the database level:

   begin
   rdsadmin.rdsadmin_util.alter_supplemental_logging(
       p_action => 'ADD');
   end;
   

   1. Enter the following command to retrieve the status of table-level supplemental logging:

      SELECT COUNT(*) FROM ALL_LOG_GROUPS
          WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING'
          AND OWNER= '<group_name>'
          AND TABLE_NAME='<table_name>';
      

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

      This returns one of the following values:

      • <number>: The number of log groups for which supplemental logging is enabled.

      • zero: This represents that the supplemental logging is disabled.

   2. If the result of the above query is zero, enable supplemental logging for all columns of a table in your Source database which you want to replicate:

      ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
      

4. 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_STATUS', '<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 HEVO.

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

Oracle Buffers

In Oracle, buffers refer to memory storage for caching data to enhance database performance. When LogMiner reads data from redo log files, it utilizes the native in-memory Oracle buffer to cache ongoing transactions (ones that have not been committed or rolled back).

Data Manipulation Language (DML) operations within a transaction are buffered until a commit or rollback is detected. A long-running transaction can have a negative impact on the database performance. It can lead to increased lag in processing change events and memory usage on the database server.

This accumulation of data in the Oracle LogMiner buffers can lead to increased Program Global Area (PGA) memory consumption in your database. Therefore, it is essential to appropriately set the PGA memory size based on your database workload. Read Check PGA/SGA Memory Settings to review the memory settings and configure the PGA_AGGREGATE_LIMIT to prevent server OutOfMemory (OOM) errors.

Only your DBA can determine if your database has long-running transactions with a large number of changes. If so, set the PGA_AGGREGATE_LIMIT to an appropriate value to process these transactions.

5. Check PGA/SGA Memory Settings (Recommended)

In Oracle, the PGA memory settings allow you to manage and optimize memory usage for individual user processes involved in SQL execution.

1. Enter the command below to retrieve information about the initialization parameters ‘pga_aggregate_limit’ and ‘pga_aggregate_target’ from the V$PARAMETER view:

   SELECT NAME, VALUE/1024/1024 as VALUE_MB
       FROM V$PARAMETER
       WHERE NAME IN ('pga_aggregate_limit', 'pga_aggregate_target');
   

   The following is an explanation of the command above:

   • SELECT NAME, VALUE/1024/1024 as VALUE_MB: This part retrieves data for the columns NAME and VALUE from the dynamic performance view V$PARAMETER. It retrieves the NAME column as is and calculates the VALUE column divided by 1024 twice to convert the value from bytes to megabytes. It aliases the result as VALUE_MB.

   • WHERE NAME IN ('pga_aggregate_limit', 'pga_aggregate_target'): This part filters the results to include only the rows where the NAME column is either pga_aggregate_limit or pga_aggregate_target.

2. Enter the command below to monitor the current PGA memory usage in your Oracle database:

   SELECT NAME, VALUE, UNIT
       FROM V$PGASTAT
       WHERE NAME IN ('total PGA inuse','total PGA allocated');
   

   The following is an explanation of the command above:

   • SELECT NAME, VALUE, UNIT: This part retrieves data for the columns NAME, VALUE, and UNIT from the dynamic performance view V$PGASTAT. It retrieves the PGA statistic name, its value, and the unit for the value.

   • WHERE NAME IN ('total PGA inuse', 'total PGA allocated'): This part filters the results to include only the rows where the NAME column is either total PGA inuse or total PGA allocated.

6. Configure the PGA Aggregate Limit (Recommended)

It is important to set up the PGA aggregate limit for managing and controlling memory usage across individual user sessions and queries. Depending on your database workload, you can set the pga_aggregate_limit parameter to prevent out-of-memory errors.

To do this, follow these steps:

1. Complete steps 1 to 5 in Configure the Fast Recovery Area.

2. Set the appropriate value for the pga_aggregate_limit parameter, and then click Save Changes.

   

3. Reboot the database instance that you are using to connect to Hevo, to apply the above changes.

   To do this:

   1. In the left navigation pane, under Dashboard, click Databases.

   2. In the Databases section on the right, select the DB instance that you want to connect to Hevo.

   3. In the Actions drop-down, click Reboot.

      

   4. On the Reboot DB Instance page, click Confirm to reboot your DB instance.

      

Allowlist Hevo IP addresses for your region

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, click Databases (or Instances if you are using an older version).

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

   

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

   

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

   You can also select an existing group, which you have used for other database instances, and modify it.

   

6. On 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 specify Hevo’s IP addresses for your region.

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

3. Click Create security group.

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-1.xxxxxxxxx.rds.amazonaws.com
Database name: ORCL
Port: 1521

To retrieve these credentials:

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.