Supporting CDC from a Oracle Multitenant Deployment with CData Sync

Oracle Multitenant enables an Oracle Database to function as a container database (CDB). A CDB consolidates multiple pluggable databases (PDB), a portable collection of schemas, schema objects, and non-schema objects. Whether deployed on-premises or in the cloud, with Oracle Multitenant, applications run unchanged in self-contained PDBs, improving resource utilization, management, and overall security.

Change data capture (CDC) refers to the process of identifying and capturing changes made to data in a database and then delivering those changes in real-time to a downstream process or system.

To capture change data from a pluggable database (PDB) in an Oracle container database (CDB), a user must verify that the PDB is open, verify that the CDB is running in ARCHIVELOG mode, and add a PDB entry that includes the PDB service name to the tnsnames.ora file, if this entry does not already exist.

Support for Oracle Change Data Capture in Oracle Multitenant instances enables CData Sync to reference schemas and tables at the PDB level while interacting with LogMiner at the CDB level. This capability ensures the high availability and scalability customers expect from Oracle Multitenant implementations is leveraged when replicating data from an operational or transactional Oracle database to an analytical database or data warehouse.

This article explains how to set up Change Data Capture (CDC) for an Oracle Database and connect both to an Oracle Multitenant Instance as a source and a Snowflake data warehouse as a destination in CData Sync.

Setting up Oracle as a Source

The first step is to set up an Oracle database as a source in CData Sync.

1. Log into CData Sync and navigate to the Connections tab. Click +Add Connection, then select Sources, and search for and select Oracle. (If you do not already have the Oracle Source connector installed, toggle "Installed Only" and download the connector and continue after CData Sync restarts.)
2. Enter the credentials, including Connection Type, Server, Service Name, User, Password, and Port for your Oracle database instance. Click Save and Test.

Setting up Snowflake as a Destination

After testing and saving our Oracle connection, we need to connect to the destination, in this case, Snowflake.

1. Click the Connections tab, select Destinations, and choose Snowflake.
2. Enter your Snowflake credentials, including Warehouse, URL, Auth Scheme, User, Password, Database, and Schema and click Save and Test.

Configuring Oracle for Change Data Capture (CDC)

Enabling Oracle Logminor

Oracle Logminor tracks changes in the redo logs allowing Sync to read those logs to determine the changes to a specific table. The main difference vs. Oracle Flashback is Sync does not interact with the source table. Instead, Sync reads directly from logs which has lower impact on the source database.

Now, we need to configure our Oracle instance for CDC by running specific commands in the hosting service where our instance lives. Log in to wherever your Oracle instance exists (AWS is shown here), and navigate to where you can write queries on it.

1. Enable the Archivelog on your database, if it has not been already. This will require restarting the database.

   --Check if ArchiveLog is enabled already
   SELECT LOG_MODE FROM V$DATABASE
   
   --If LOG_MODE does not equal ARCHIVELOG
   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;

2. Ensure the database retains backups and archivelogs for 24 hours (CData recommends 7 days).

   RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

3. Enable supplemental logging at the Database or Table level. Only 1 is required.

   --Database level
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
   
   --Table level
   ALTER TABLE <schema_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

4. Ensure the user has the correct permissions for viewing the Logminor.

Next, we create a Job in Sync, making sure to select the Use Change Data Capture option.

Creating a Job in Sync for Replication of Oracle Multitenant Data into Snowflake

With our source and destination configured, we are ready to create our replication job.

1. Click the Jobs tab and click +Add Job.
2. Name the job, select our Oracle connection as the source and our Snowflake connection as the destination, and choose Change Data Capture as the Type.
3. Finally, click Add Job.

With the replication job created, you can select Tasks to replicate your Oracle tables to your destination. Click +Add Tasks and walk through the Wizard to choose the Oracle tables to replicate to Snowflake.

Free Trial & More Information

At this point, you have configured CDC for an Oracle Multitenant Database Instance, and configured the Oracle Instance as a source in CData Sync. Try CData Sync free for 30 days and start using CDC to replicate all of your Oracle data to any popular database, datalake or data warehouse today.