Oracle Source

The Oracle Source Connector is an advanced tool designed to synchronize Oracle databases with Popsink efficiently and in real-time. It enables the real-time transfer of data, ensuring that the latest database changes are mirrored to the target systems. This capability is crucial for maintaining up-to-date business intelligence, enabling real-time analytics, and enhancing operational workflows.

Compatible versions


Oracle Database	19c, 21c, 23ai, 26ai
Capture method	LogMiner, reading the online redo and archived redo logs
Deployments	Single instance and RAC; multitenant (CDB/PDB) and non-CDB
Managed services	Amazon RDS for Oracle, Oracle Base Database Service (OCI)
CDC engine	Debezium 3.5

Oracle 12c and 11g are not supported. On 23ai and 26ai, capture is available through LogMiner only.

Prerequisites

Before deploying the Oracle Source Connector, certain prerequisites must be met to ensure a smooth integration process:

Whitelisting Popsink’s IP Address: Ensure that Popsink’s IP address is whitelisted to allow uninterrupted communication between Oracle databases and Popsink.
Database Log Mode Configuration: The Oracle database Archive Mode should be set to ArchiveLog.
Supplemental Logging: Supplemental Logs should be enable on the source you wish to replicate. This can me done at Database or Table Level :
- At Database Level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
- At Table Level
ALTER TABLE **{db.name}** ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Note: If using AWS RDS, additional steps may be required. Please consult the relevant AWS RDS documentation and do feel free to reach out for help.

Required Permissions

Although you could use a superuser, we do recommend setting up dedicated users for security reasons. The user account that the Oracle Source Connector utilizes must have sufficient privileges. The following SQL statements outline the necessary permissions:

GRANT CREATE SESSION TO {username} CONTAINER=ALL;
GRANT SET CONTAINER TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to {username} CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO {username} CONTAINER=ALL;
GRANT SELECT ANY TABLE TO {username} CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO {username} CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO {username} CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO {username} CONTAINER=ALL;
GRANT LOGMINING TO {username} CONTAINER=ALL;
GRANT CREATE TABLE TO {username} CONTAINER=ALL;
GRANT LOCK ANY TABLE TO {username} CONTAINER=ALL;
GRANT CREATE SEQUENCE TO {username} CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO {username} CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOG TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOG_HISTORY TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$TRANSACTION TO {username} CONTAINER=ALL;

Replace {username} with the actual username being used for the connection.

Steps

Go to: Sources -> Create New then select the Oracle connector and Continue In the Credentials sections fill in the required information.

`host`

Description: The hostname or IP address of the Oracle database server.
Type: string
Example: oracle-db-server.example.com

`port`

Description: The port number on which the Oracle database server is listening.
Type: integer
Default: 1521 (default port for Oracle database)
Example: 1521

`user`

Description: The username used to connect to the Oracle database.
Type: string
Example: popsink_user

`password`

Description: The password associated with the specified user for authentication.
Type: string
Example: p@ssw0rd123

`service_name` or `sid`

Provide exactly one of the two:

service_name: The Oracle service name to connect to.
- Type: string
- Example: ORCLPDB1
sid: The Oracle System Identifier, for legacy databases that are not service-name addressable.
- Type: string
- Example: ORCL

Advanced Options

Field	Default	Description
Poll Interval (ms)	`2000`	How often the connector polls for new redo log activity
Initial Load	`true`	Snapshot existing rows before streaming changes

Connecting through an SSH Tunnel

If the database is only reachable through a bastion host, enable SSH Tunnel and provide:

Field	Required	Description
SSH Host	Yes	Hostname or IP of the bastion host
SSH Port	Yes	SSH port (default `22`)
SSH User	Yes	User to authenticate as on the bastion
SSH Private Key	Yes	Base64-encoded private key for the SSH user

Table Selection

After a successful credential check, pick the tables to replicate from the discovered list. The selection is stored as a comma-separated whitelist in SCHEMA.TABLE format. You can use Check Credentials at any moment to verify the validity of your inputs. Once the validity check has passed, the next step is the standard Informations page where you may give the connector a name and select which of your teams should own this connector. Create the connector and you are now done. The connector may take a few minutes to synchronize with the source database.

Conclusion

The Oracle Source Connector is an essential component for creating a reliable data pipeline between Oracle databases and Popsink. By following the guidelines provided in this document, you can ensure a successful setup and enjoy a robust, real-time data integration experience.

Welcome

Platform Overview

Deployment

Source Connectors

Target Connectors

On-Premises & Integrations

Transformation

Changelog

Compatible versions

Prerequisites

Required Permissions

Steps

`host`

`port`

`user`

`password`

`service_name` or `sid`

Advanced Options

Connecting through an SSH Tunnel

Table Selection

Conclusion

​Compatible versions

​Prerequisites

​Required Permissions

​Steps

​host

​port

​user

​password

​service_name or sid

​Advanced Options

​Connecting through an SSH Tunnel

​Table Selection

​Conclusion

Compatible versions

Prerequisites

Required Permissions

Steps

`host`

`port`

`user`

`password`

`service_name` or `sid`

Advanced Options

Connecting through an SSH Tunnel

Table Selection

Conclusion