Configure Oracle for ingestion into Databricks

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

This page describes the source database tasks required to ingest from Oracle into Databricks Lakeflow Connect.

The Oracle connector uses LogMiner in uncommitted transaction mode to read changes from online redo logs and archive logs.

Requirements

• Oracle version 12c or above (12c, 18c, 19c, 21c, 23ai, and 26ai).
• Archive log mode enabled.
• Supplemental logging enabled for the tables that you want to replicate. Primary-key supplemental logging is the minimum; full supplemental logging is required for tables that get UPDATE statements on primary-key or unique-key columns. Minimal supplemental logging alone is not sufficient. See Which supplemental logging method should I choose?.
• A primary (not standby) database. Oracle RAC and data encrypted with Transparent Data Encryption (TDE) with a closed wallet are not supported.
• For multi-tenant databases, a common user in CDB$ROOT with the required privileges.

Overview of source setup

Complete the following tasks in Oracle before you ingest data into Databricks. Run each step as the SYSDBA user, or as the ADMIN user for Amazon RDS databases.

1. Verify archive log mode and log retention.
2. Enable supplemental logging.
3. Create a replication user using the setup script.
4. Note the connection details, including the service name and database domain.

Step 1: Verify archive log mode and log retention

The Oracle integrated CDC connector reads from archive logs. The following query must return ARCHIVELOG:

SQL

SELECT LOG_MODE FROM V$DATABASE;

If the query returns NOARCHIVELOG, enable archive log mode before continuing.

Steps to enable archive logs

Non-RDS database

For standard Oracle databases:

SQL

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

Amazon RDS database

For Oracle on AWS RDS, enable automatic backups.

Ensure archive log retention

Databricks recommends retaining archive logs for at least 48 hours. If Oracle purges archive logs before the pipeline can process them, you must perform a full refresh on the affected tables. Plan your disk capacity accordingly to store the retained archive log files.

Non-RDS database

Run the following in Recovery Manager (RMAN).

SQL

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Amazon RDS database

For Oracle on AWS RDS, refer to AWS docs.

SQL

BEGIN
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '48');
END;

Step 2: Enable supplemental logging

The connector requires at least primary-key supplemental logging on each table that you replicate. Full supplemental logging is required for tables that get UPDATE statements on primary-key or unique-key columns. For details, see Which supplemental logging method should I choose?.

You can enable supplemental logging on each table, or at the database level so that every table inherits it. The commands differ depending on whether your database runs on Amazon RDS.

To enable primary-key supplemental logging at the table level:

SQL

ALTER TABLE <schema>.<table> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

To enable full supplemental logging at the table level:

SQL

ALTER TABLE <schema>.<table> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To enable primary-key supplemental logging at the database level (optional; every table inherits it):

Non-RDS database

SQL

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Amazon RDS database

SQL

BEGIN
  rdsadmin.rdsadmin_util.alter_supplemental_logging(
    p_action => 'ADD',
    p_type   => 'PRIMARY KEY'
  );
END;

Refer to AWS docs for more details.

Step 3: Create a replication user using the setup script

Databricks provides an Oracle PL/SQL setup tool (dbx_oracle_setup_util) that automates user creation and privilege grants for CDC. The package exposes the following procedures:

Procedure	Description
create_user(...)	Creates a CDC replication user with a specified default tablespace, temporary tablespace, and unlimited quota on the default tablespace.
grant_permissions(...)	Grants the system and object privileges required for LogMiner CDC. See Oracle database user requirements.
grant_select_permissions(...)	Grants SELECT on every table in a schema.
grant_select_on_table(...)	Grants SELECT on a specific table.
validate_setup(...)	Validates the database environment, required database configuration, replication user, and required privileges.
drop_user(...)	Drops a previously created replication user. Use this to clean up or to re-create the user.

Procedure	Description
create_user(...)	Creates a CDC replication user with a specified default tablespace, temporary tablespace, and unlimited quota on the default tablespace.
grant_permissions(...)	Grants the system and object privileges required for LogMiner CDC. See Oracle database user requirements.
grant_select_permissions(...)	Grants SELECT on every table in a schema.
grant_select_on_table(...)	Grants SELECT on a specific table.
validate_setup(...)	Validates the database environment, required database configuration, replication user, and required privileges.
drop_user(...)	Drops a previously created replication user. Use this to clean up or to re-create the user.

Install the setup tool

1. Download the setup tool: dbx-oracle-setup-package.sql.

2. Run the script to create the dbx_oracle_setup_util PL/SQL package. Run it with SYSDBA privileges (or as the ADMIN user on Amazon RDS).

   For a multi-tenant database, run the script in the CDB$ROOT container.

Create the replication user

Create a dedicated replication user. The username must be uppercase. For a multi-tenant database (CDB), the username must start with C## so that a common user is created. For a non-CDB database, do not use the C## prefix.

The following example creates a user named C##CDCREPL with default tablespace USERS and temporary tablespace TEMP:

SQL

BEGIN
  DBX_ORACLE_SETUP_UTIL.CREATE_USER('C##CDCREPL', '<password>', 'USERS', 'TEMP');
END;
/

note

Do not use the SYS or SYSTEM user for replication.

Grant database level privileges

Grant the replication user the privileges required for LogMiner CDC. The tool automatically chooses the correct grant method for your environment (standard grants, or Amazon RDS rdsadmin grants), and sets CONTAINER_DATA=ALL for multi-tenant databases.

SQL

BEGIN
  DBX_ORACLE_SETUP_UTIL.GRANT_PERMISSIONS('C##CDCREPL');
END;
/

For the full list of privileges that the tool grants, see Oracle database user requirements.

Grant SELECT privileges on tables

Grant the replication user SELECT on every table you replicate. The setup tool provides two procedures for this:

SQL

BEGIN
  -- to grant SELECT on all tables in a schema
  DBX_ORACLE_SETUP_UTIL.GRANT_SELECT_PERMISSIONS('C##CDCREPL', '<schema_to_replicate>', '<container_name>');
  -- to grant SELECT on specific tables in the schema
  DBX_ORACLE_SETUP_UTIL.GRANT_SELECT_ON_TABLE('C##CDCREPL', '<schema_to_replicate>', '<table_name>', '<container_name>');
END;
/

For a non-CDB database, omit the <container_name> argument.

You can also grant SELECT manually on individual tables:

SQL

GRANT SELECT ON <schema>.<table> TO C##CDCREPL;

The UI can only show tables that the replication user has SELECT permission on.

note

Oracle's SELECT ANY TABLE privilege grants read access to every table in the database in one statement. Avoid it outside of a development database, because it exposes tables that you might not want the replication user to read.

Validate the setup

Validate the database environment, required database configuration, replication user, and required privileges:

SQL

BEGIN
  DBX_ORACLE_SETUP_UTIL.VALIDATE_SETUP('C##CDCREPL');
END;
/

Step 4: Note the connection details

When you create the Unity Catalog connection, you need the following details about your Oracle database. See Create an Oracle connection.

Service name

The connector connects to Oracle using a service name.

• For a single-tenant (non-CDB) database, use the service name of the database.
• For a multi-tenant (CDB) database, use the CDB$ROOT service name. The connector connects to CDB$ROOT to read changes for all pluggable databases (PDBs), then resolves PDB service names automatically. See Multi-tenant (CDB) databases.

Database domain

If your database has the DB_DOMAIN initialization parameter set, Oracle registers each service with the listener using a domain-qualified name (for example, FREEPDB1.example.com rather than FREEPDB1). This is common in environments fronted by Oracle Connection Manager (CMAN).

When DB_DOMAIN is set, the CDB$ROOT service name that you provide in the Unity Catalog connection must include the domain suffix (for example, newcorp.example.com). To check the current value:

SQL

SELECT value FROM v$parameter WHERE name = 'db_domain';

The connector automatically appends the discovered DB_DOMAIN to PDB service names. You only need to supply the domain-qualified CDB$ROOT service name in the connection. For details, see Create an Oracle connection.

Multi-tenant (CDB) databases

For a multi-tenant database:

• Run the setup script in the CDB$ROOT container.
• The replication user must be a common user (the C## prefix).
• The setup tool sets CONTAINER_DATA=ALL on the user so that it can read change data across all containers.
• In the Unity Catalog connection, use the CDB$ROOT service name (domain-qualified if DB_DOMAIN is set).

Multi-tenant Amazon RDS for Oracle instances are not supported.

Case sensitivity

By default, Oracle treats unquoted identifiers as case-insensitive and converts them to uppercase. If you enclose an identifier in double quotes during creation, Oracle preserves the case.

When you create the Unity Catalog connection, use an uppercase username unless the database stores it in lowercase. When you specify schema, table, and column names in your pipeline, the case must match how Oracle stores the identifier.

LogMiner limitations

LogMiner does not support the following data types and storage attributes. If a table contains any of these, LogMiner ignores the entire table:

• BFILE
• Nested tables and VARRAY collections
• Objects with nested tables
• Tables with identity columns
• Temporal validity columns
• PKREF columns
• PKOID columns (object type columns)
• Nested table attributes and standalone nested table columns

In addition:

• Table and column names cannot exceed 30 characters.
• Data types and features added after Oracle Database 12c Release 2 (12.2) are not supported. This includes BOOLEAN, VECTOR, and JSON.

Data type mappings

For the mapping of Oracle data types to Databricks types, see Oracle integrated CDC connector reference.

Next steps

• Review the privilege requirements.
• Create an Oracle connection.