Oracle Source Connector: Setup and Configuration

Prerequisites

Host and port of the primary, or of an Active Data Guard physical standby (see Can I replicate from a standby?)
Single-tenant architecture: Database name
Multi-tenant architecture: Container database name (CDB) and pluggable database name (PDB)
Service account credentials

Oracle setup

1. Enable archive logs

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2. Enable LogMiner

-- Enable database supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- For each table, enable column level supplemental logging:
ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

3. Creating the database user

ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER C##ARTIE_USER IDENTIFIED BY YOUR_PASSWORD;

ALTER SESSION SET CONTAINER={PDB};
CREATE TABLESPACE ARTIE_LOGMINER_TBS DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER USER C##ARTIE_USER DEFAULT TABLESPACE ARTIE_LOGMINER_TBS;
ALTER USER C##ARTIE_USER QUOTA UNLIMITED ON ARTIE_LOGMINER_TBS;

-- Grant permissions
GRANT CREATE SESSION TO C##ARTIE_USER CONTAINER=ALL; 
GRANT SET CONTAINER TO C##ARTIE_USER CONTAINER=ALL; 

-- Allows the Connector to use LogMiner
GRANT LOGMINING TO C##ARTIE_USER CONTAINER=ALL;

-- Flashback queries used for performing initial snapshots of the data
GRANT FLASHBACK ANY TABLE TO C##ARTIE_USER CONTAINER=ALL; 
GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER CONTAINER=ALL; 

-- Required for schema history when performing initial snapshots
GRANT SELECT_CATALOG_ROLE TO C##ARTIE_USER CONTAINER=ALL; 
GRANT EXECUTE_CATALOG_ROLE TO C##ARTIE_USER CONTAINER=ALL; 

-- Connector creates a table for explicitly managing the flushing of internal log buffers (LGWR)
GRANT CREATE TABLE TO C##ARTIE_USER CONTAINER=ALL;

GRANT CREATE SEQUENCE TO C##ARTIE_USER CONTAINER=ALL; 

-- Grant the user permission to read each schema and table you wish to sync
ALTER SESSION SET CONTAINER={PDB};
GRANT SELECT ON SCHEMA.TABLE TO C##ARTIE_USER CONTAINER=ALL;
-- Alternatively, you can grant access to all
-- GRANT SELECT ANY TABLE TO C##ARTIE_USER CONTAINER=ALL;

-- Grant the user access to the DBA_EXTENTS, DBA_TABLESPACES, DBA_SEGMENTS, and TRANSACTION system views. 
GRANT SELECT ON DBA_EXTENTS TO C##ARTIE_USER;
GRANT SELECT ON DBA_TABLESPACES TO C##ARTIE_USER;
GRANT SELECT ON DBA_SEGMENTS TO C##ARTIE_USER;
GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER;

-- Grant the user permission to run LogMiner
ALTER SESSION SET CONTAINER=CDB$ROOT;
GRANT SELECT ON SYS.V_$DATABASE TO C##ARTIE_USER;
GRANT SELECT ON SYS.V_$PARAMETER TO C##ARTIE_USER;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO C##ARTIE_USER;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO C##ARTIE_USER;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO C##ARTIE_USER;
GRANT EXECUTE ON DBMS_LOGMNR TO C##ARTIE_USER;
GRANT EXECUTE ON DBMS_LOGMNR_D TO C##ARTIE_USER;
GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER;
GRANT EXECUTE_CATALOG_ROLE TO C##ARTIE_USER;

CREATE USER ARTIE_USER IDENTIFIED BY {password};

CREATE TABLESPACE ARTIE_LOGMINER_TBS DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER USER ARTIE_USER DEFAULT TABLESPACE ARTIE_LOGMINER_TBS;
ALTER USER ARTIE_USER QUOTA UNLIMITED ON ARTIE_LOGMINER_TBS;

-- Grant permissions
GRANT CREATE SESSION TO ARTIE_USER; 
GRANT SET CONTAINER TO ARTIE_USER; 

-- Allows the Connector to use LogMiner
GRANT LOGMINING TO ARTIE_USER; 

-- Flashback queries used for performing initial snapshots of the data
GRANT FLASHBACK ANY TABLE TO ARTIE_USER; 
GRANT SELECT ANY TRANSACTION TO ARTIE_USER; 

-- Required for schema history when performing initial snapshots
GRANT SELECT_CATALOG_ROLE TO ARTIE_USER; 
GRANT EXECUTE_CATALOG_ROLE TO ARTIE_USER; 

-- Connector creates a table for explicitly managing the flushing of internal log buffers (LGWR)
GRANT CREATE TABLE TO ARTIE_USER; 

GRANT CREATE SEQUENCE TO ARTIE_USER;

-- Grant the user permission to read each schema and table you wish to sync
GRANT SELECT ON SCHEMA.TABLE TO ARTIE_USER;
-- Alternatively, you can grant access to all
-- GRANT SELECT ANY TABLE TO ARTIE_USER;

-- Grant the user permission to run LogMiner
GRANT SELECT ON SYS.V_$DATABASE TO ARTIE_USER;
GRANT SELECT ON SYS.V_$PARAMETER TO ARTIE_USER;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO ARTIE_USER;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO ARTIE_USER;
GRANT EXECUTE ON DBMS_LOGMNR TO ARTIE_USER;
GRANT EXECUTE ON DBMS_LOGMNR_D TO ARTIE_USER;
GRANT SELECT ANY TRANSACTION TO ARTIE_USER;
GRANT EXECUTE_CATALOG_ROLE TO ARTIE_USER;

Overriding primary keys

If you want to replicate tables that do not have primary keys, you can specify the unique index that you want to use as the primary key. Specify this under the “Advanced settings” tab in the table settings.

Frequently asked questions

Can I replicate from a standby?

Yes, from an Active Data Guard physical standby that’s open read-only. Artie reads the standby’s archived redo logs and never writes to the standby. Contact us to enable streaming from a standby for your pipeline. Configure the primary with:

ARCHIVELOG mode and supplemental logging, as described in Oracle setup. Both replicate to the standby.
The service account and its grants, as shown below. Users and grants replicate to the standby.
ARCHIVE_LAG_TARGET, so the primary switches logs regularly even when it’s quiet. Artie reads a change only after the standby has archived the log that holds it.

ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 300 SCOPE = BOTH;

A standby needs fewer grants than step 3 of the Oracle setup, because Artie only reads from it. Run this on the primary:

ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER C##ARTIE_USER IDENTIFIED BY YOUR_PASSWORD CONTAINER=ALL;

GRANT CREATE SESSION TO C##ARTIE_USER CONTAINER=ALL;
GRANT LOGMINING TO C##ARTIE_USER CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER CONTAINER=ALL;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##ARTIE_USER CONTAINER=ALL;
GRANT SELECT ON SYS.V_$DATABASE TO C##ARTIE_USER CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOG TO C##ARTIE_USER CONTAINER=ALL;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO C##ARTIE_USER CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO C##ARTIE_USER CONTAINER=ALL;

-- Grant read access to each table you want to replicate
ALTER SESSION SET CONTAINER={PDB};
GRANT SELECT ON SCHEMA_NAME.TABLE_NAME TO C##ARTIE_USER;

CREATE USER ARTIE_USER IDENTIFIED BY YOUR_PASSWORD;

GRANT CREATE SESSION TO ARTIE_USER;
GRANT LOGMINING TO ARTIE_USER;
GRANT SELECT ANY TRANSACTION TO ARTIE_USER;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO ARTIE_USER;
GRANT SELECT ON SYS.V_$DATABASE TO ARTIE_USER;
GRANT SELECT ON SYS.V_$LOG TO ARTIE_USER;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO ARTIE_USER;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO ARTIE_USER;

-- Grant read access to each table you want to replicate
GRANT SELECT ON SCHEMA_NAME.TABLE_NAME TO ARTIE_USER;

Configure the standby with:

Read-only access with redo apply running (Active Data Guard)
Archived logs kept until Artie has read them. If a log is deleted first, the pipeline stops until the log is restored or the tables are backfilled.

Use this query on the standby to verify its configuration:

SELECT DATABASE_ROLE, OPEN_MODE, LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
-- Expected: PHYSICAL STANDBY, READ ONLY WITH APPLY, ARCHIVELOG, YES

Changes from a standby arrive at least one log switch behind the primary, so ARCHIVE_LAG_TARGET sets your minimum replication lag.

Standbys of Oracle RAC databases, snapshot standbys, and far sync instances aren’t supported.

Getting Started

Pipelines

Connectors

Monitoring

Artie Dashboard

Oracle Source Connector: Setup and Configuration

Prerequisites

Oracle setup

1. Enable archive logs

2. Enable LogMiner

3. Creating the database user

Overriding primary keys

Frequently asked questions

Can I replicate from a standby?

​Prerequisites

​Oracle setup

​1. Enable archive logs

​2. Enable LogMiner

​3. Creating the database user

​Overriding primary keys

​Frequently asked questions

​Can I replicate from a standby?

Prerequisites

Oracle setup

1. Enable archive logs

2. Enable LogMiner

3. Creating the database user

Overriding primary keys

Frequently asked questions

Can I replicate from a standby?