Oracle

Etleap supports both query-based extractions and Change Data Capture (CDC) for Oracle. When CDC is explicitly enabled on the source connection, Etleap captures changes from Oracle’s replication logs.

Source Setup

Step 1. Create a Database User

To create a user that will allow Etleap to connect to your database, follow the steps below:

1. Create a new user for the Etleap connection. The <user_name> and <password> values will be needed when creating the connection in Etleap.

   CREATE USER <user_name> IDENTIFIED BY <password>;

2. Grant Read Permission to the database or tables you would like to extract data from.

   Choose one of the statements below to assign select permissions at a certain level

   GRANT SELECT, SHOW VIEW ON <database_name>.* TO <user_name>;
   GRANT SELECT ON <table_name> TO <username>;

You can now use this user to set up a new connection in Etleap.

Step 2. Whitelist Etleap’s IP Addresses

Etleap’s IP addresses must be whitelisted in Oracle. The IP addresses to whitelist are specified when creating the connection in Etleap. Review the IP Whitelisting article for more information on which to use.

Change Data Capture (CDC) Setup

To configure an Oracle database for CDC-enabled pipelines with Etleap, the database must be configured in ARCHIVELOG mode, Supplemental logging must be enabled for the database and all columns in each table that will be extracted with CDC. The user configured in Etleap will also require several additional privileges.

Step 1. Enable ARCHIVELOG Mode

ARCHIVELOG mode tells Oracle to copy old log files to an archive directory before reusing and overwriting the online-log file. This is a required setting for CDC.

To confirm whether ARCHIVELOG mode is already enabled, run the following SQL query:

SELECT LOG_MODE FROM V$DATABASE;

If the response is ARCHIVELOG, please continue to the next section. Otherwise, if the returned value is NOARCHIVELOG, please complete the following steps to switch the database mode:

1. Login to the host running the Oracle DB.

   $ export ORACLE_SID=<database_name>

2. Connect to the database as sysdba.

   $ sqlplus / as sysdba

3. Run the following commands in the SQL console.

   SQL> shutdown immediate
    
   SQL> startup mount
    
   SQL> alter database archivelog;
    
   SQL> alter database open;

4. Confirm that ARCHIVELOG mode is enabled in the SQL console.

   SQL> archive log list;
    
   Database log mode ... Archive Mode

Step 2. Enable Supplemental Logging in the Database

Supplemental logging must be enabled for the Oracle CDB as well as the Oracle PDB that the CDC pipeline is being set up for.

To confirm whether Supplemental logging is already enabled, run the following SQL query:

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If it is not already enabled, follow these steps:

1. Switch the session container to the CDB.

   ALTER SESSION SET CONTAINER = CDB$ROOT;

2. Enable supplemental logging for the CDB.

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3. Switch the session container back to the target PDB. You can get the pdb_name by querying SELECT pdb FROM v$services;.

   ALTER SESSION SET CONTAINER = <pdb_name>;

4. Enable supplemental logging for the PDB.

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 3. Enable Supplemental Logging for All Columns

Supplemental logging is required to be enabled for all columns on your source tables. If supplemental logging is not enabled for all columns, some columns may be captured as NULL from the source.

To check whether supplemental logging has been enabled for all columns in a table, run the following query:

SELECT TABLE_NAME, LOG_GROUP_TYPE FROM ALL_LOG_GROUPS;

Review the results of that query to ensure that each table that will be ingested by Etleap has ALL COLUMN LOGGING enabled. If any do not, enable it with the following statement:

ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Step 4. Set User Permissions

The user configured for the Oracle connection must have the following privileges. Replace the name cdc_user with your own user name.

GRANT SELECT ANY TRANSACTION to cdc_user
GRANT SELECT on V_$ARCHIVED_LOG to cdc_user
GRANT SELECT on V_$LOG to cdc_user
GRANT SELECT on V_$LOGFILE to cdc_user
GRANT SELECT on V_$DATABASE to cdc_user
GRANT SELECT ON V_$CONTAINERS to cdc_user
GRANT SELECT on V_$THREAD to cdc_user
GRANT SELECT on V_$PARAMETER to cdc_user
GRANT SELECT on V_$NLS_PARAMETERS to cdc_user
GRANT SELECT on V_$TIMEZONE_NAMES to cdc_user
GRANT SELECT on V_$TRANSACTION to cdc_user
GRANT SELECT on ALL_INDEXES to cdc_user
GRANT SELECT on ALL_OBJECTS to cdc_user
GRANT SELECT on DBA_OBJECTS to cdc_user (for Oracle v < 11.2.0.3)
GRANT SELECT on ALL_TABLES to cdc_user
GRANT SELECT on ALL_USERS to cdc_user
GRANT SELECT on ALL_CATALOG to cdc_user
GRANT SELECT on ALL_CONSTRAINTS to cdc_user
GRANT SELECT on ALL_CONS_COLUMNS to cdc_user
GRANT SELECT on ALL_TAB_COLS to cdc_user
GRANT SELECT on ALL_IND_COLUMNS to cdc_user
GRANT SELECT on ALL_LOG_GROUPS to cdc_user
GRANT SELECT on SYS.DBA_REGISTRY to cdc_user
GRANT SELECT on SYS.OBJ$ to cdc_user
GRANT SELECT on DBA_TABLESPACES to cdc_user
GRANT SELECT on ALL_TAB_PARTITIONS to cdc_user
GRANT SELECT on ALL_ENCRYPTED_COLUMNS to cdc_user
GRANT SELECT on V_$LOGMNR_LOGS to cdc_user
GRANT SELECT on V_$LOGMNR_CONTENTS to cdc_user
GRANT SELECT on V_$STANDBY_LOG to cdc_user

Step 5. Add Archived Log Entry

For CDC-enabled pipelines to progress in Etleap, there must be at least one archived log file. Run the following SQL query:

SELECT * FROM V$ARCHIVED_LOG;

If at least one row is returned, then the database configuration is complete. If no results are returned, follow these steps:

1. Login to the host running the Oracle DB.

   $ export ORACLE_SID=<database name>

2. Connect to the database as sysdba.

   $ sqlplus / as sysdba

3. Run the following command in the SQL console.

   SQL> alter system switch logfile;

4. Confirm that there is at least one archived log file in the SQL console.

   SQL> SELECT * FROM V$ARCHIVED_LOG;

Step 6. Create Log Directories

In order to read from archived logs with Binary Reader, Etleap needs the following permissions to create directories.

GRANT CREATE ANY DIRECTORY to cdc_user;
GRANT EXECUTE on DBMS_FILE_TRANSFER to cdc_user;
GRANT EXECUTE on DBMS_FILE_GROUP to cdc_user;

exec rdsadmin.rdsadmin_master_util.create_archivelog_dir;
exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;
 
GRANT READ ON DIRECTORY ONLINELOG_DIR TO cdc_user;
GRANT READ ON DIRECTORY ARCHIVELOG_DIR TO cdc_user;

Key Considerations

Data types

Oracle’s NUMBER type (without precision or scale) will be converted to a floating-point number in Etleap. This is because its range  exceeds the maximum range for a fixed-point number in destinations that Etleap supports, such as Amazon Redshift and Snowflake, where the maximum fixed-point precision and scale are 38 and 20, respectively. This conversion may result in a loss of precision.

An exception is if the column is part of a primary key, in which case the type is converted to NUMBER(38,20) to avoid data integrity issues. Any values outside the range of NUMBER(38,20) will result in parsing errors.

CDC Limitations

You can find a detailed list of all CDC limitations for Oracle imposed by AWS here . Additionally, the database password must not contain the following invalid character : ,.