Oracle Database Prerequisites for Oracle XStream CDC Source Connector for Confluent Cloud¶

This page includes the prerequisite steps for preparing and configuring an Oracle database and must be completed before you can proceed to configure the Oracle XStream CDC Source Connector in Confluent Cloud.

Your Oracle database administrator must complete the following steps before the Oracle XStream CDC Source connector can produce expected results:

Enable Oracle XStream
Configure ARCHIVELOG mode
Configure supplemental logging
Configure database users
Create XStream Out
Capture changes from Oracle RAC

Enable Oracle XStream¶

This section includes the commands to enable Oracle XStream.

Connect as a user with SYSDBA privileges. If working with a CDB, ensure you connect to the root container.

To enable XStream, run the following statement:

SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH

All instances in Oracle Real Application Clusters (RAC) must have the same setting.

Note

To use this connector with Oracle XStream Out, you must have a valid license from Confluent.

Configure ARCHIVELOG mode¶

To capture changes that are generated by the source database, the database must be in ARCHIVELOG mode. This ensures that Oracle does not overwrite online redo log files unless they are archived. Complete the following steps:

Connect as a user with SYSDBA privileges. If working with a CDB, ensure you connect to the root container.
Check if the database is in ARCHIVELOG mode:
```
SQL> SELECT LOG_MODE FROM V$DATABASE;
```
If the LOG_MODE shows ARCHIVELOG, you can skip the remaining steps.

Place the database in ARCHIVELOG mode:

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

Confirm the database is in ARCHIVELOG mode:

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

If you use Oracle Real Application Clusters (RAC), complete the following steps to enable ARCHIVELOG:

Connect as a user with SYSDBA privileges. If working with a CDB, ensure you connect to the root container.
Check if the database is in ARCHIVELOG mode:
```
SQL> SELECT LOG_MODE FROM V$DATABASE;
```
If the LOG_MODE shows ARCHIVELOG, you can skip the remaining steps.
Stop all database instances.
```
srvctl stop database -d <db_Name>
```

Start the database in mount state.

srvctl start database -d <db_Name> -o mount

Enable ARCHIVELOG mode.
```
SQL> ALTER DATABASE ARCHIVELOG;
```

Restart all database instances.

srvctl stop database -d <db_Name>

srvctl start database -d <db_Name>

Confirm the database is in ARCHIVELOG mode.

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

Configure supplemental logging¶

Supplemental logging at the source database places additional information in the redo log that the connector requires to create the change events.

There are two levels of supplemental logging:

Database supplemental logging: This enables supplemental logging for the entire database.
Table supplemental logging: This enables supplemental logging of specific tables using log groups.

Container database (CDB)¶

This is a prerequisite step for enabling database-level or table-level supplemental logging.

Connect as a user with SYSDBA privileges to the root container. This ensures the operation is performed on the entire CDB.
```
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
```

In addition to minimal supplemental logging, the connector requires additional supplemental logging to be enabled to ensure that the entire row, not just the modified columns, is logged. Confluent recommends enabling supplemental logging only for the tables of interest rather than for the entire database to minimize the amount of redo generated at the source database.

To enable supplemental logging for specific tables, run the following commands:

ALTER SESSION SET CONTAINER = <pdb_name>;
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To confirm, run the following query:

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

This should return YES for SUPPLEMENTAL_LOG_DATA_MIN and NO for SUPPLEMENTAL_LOG_DATA_ALL.

ALTER SESSION SET CONTAINER = <pdb_name>;
SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE = 'ALL COLUMN LOGGING' and OWNER = '<db_user>' and TABLE_NAME = '<table_name>';

This should return a record for the specified table.

To enable supplemental logging for the entire database, run the following commands:

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

To confirm, run the following query:
```
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
```
This should return YES for both SUPPLEMENTAL_LOG_DATA_MIN and SUPPLEMENTAL_LOG_DATA_ALL.

Non-container database (Non-CDB)¶

Note

The non-CDB architecture is deprecated in Oracle Database 12c and discontinued in Oracle Database 20c.

This is a prerequisite step for enabling database-level or table-level supplemental logging.

Connect as a user with SYSDBA privileges.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

In addition to minimal supplemental logging, the connector requires additional supplemental logging to be enabled to ensure that the entire row, not just the modified columns, is logged. Confluent recommends enabling supplemental logging only for the tables of interest rather than for the entire database to minimize the amount of redo generated at the source database.

To enable supplemental logging for specific tables, run the following commands:

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

To confirm, run the following query:

SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

This should return YES for SUPPLEMENTAL_LOG_DATA_MIN and NO for SUPPLEMENTAL_LOG_DATA_ALL.

SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE = 'ALL COLUMN LOGGING' and OWNER = '<db_user>' and TABLE_NAME = '<table_name>';

This should return a record for the specified table.

To enable supplemental logging for the entire database, run the following commands:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
```
To confirm, run the following query:
```
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
```
This should return YES for both SUPPLEMENTAL_LOG_DATA_MIN and SUPPLEMENTAL_LOG_DATA_ALL.

Configure database users¶

XStream Out defines two users:

Capture User: This user manages the XStream components and captures changes from the redo log.
Connect User: This user connects to the XStream outbound server to receive changes as logical change records (LCRs).

Each user is assigned with a specific set of privileges. The objective is to ensure that the database (connect) user, configured on the connector, has only the minimal set of privileges needed for connector operation, thereby enhancing security.

Container database (CDB)¶

Connect as a user with SYSDBA privileges to the root container.
Create a new tablespace for the XStream administrator.
```
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Change the DATAFILE configuration based on the database storage setup, whether it is on an OS file system or within Oracle ASM disk groups.

You must create the tablespace in all containers within the CDB, including the CDB root, all Pluggable Databases (PDBs), all application roots, and all application containers.

For example, if the CDB contains a single PDB named ORCLPDB1, you should then create a tablespace in that PDB.
```
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Create a new common user for the XStream administrator.

The following example creates a common user named c##cfltadmin in a CDB. Before you run this CREATE USER statement, ensure that the tablespace xstream_adm_tbs exists in all containers within the CDB.
```
CREATE USER c##cfltadmin IDENTIFIED BY password
  DEFAULT TABLESPACE xstream_adm_tbs
  QUOTA UNLIMITED ON xstream_adm_tbs
  CONTAINER=ALL;
```

Grant the CREATE SESSION and SET CONTAINER privileges.

GRANT CREATE SESSION, SET CONTAINER TO c##cfltadmin CONTAINER=ALL;

Run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_XSTREAM_AUTH package.

BEGIN
  DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee                 => 'c##cfltadmin',
    privilege_type          => 'CAPTURE',
    grant_select_privileges => TRUE,
    container               => 'ALL');
END;

The user executing subprograms in the DBMS_XSTREAM_AUTH package must have the SYSDBA administrative privilege and must exercise this privilege using AS SYSDBA at connect time.

For more information about the full list of privileges granted through this procedure, see the GRANT_ADMIN_PRIVILEGE Procedure section in the Oracle documentation.

Connect as a user with SYSDBA privileges to the root container.
Create a new tablespace for the XStream connect user.
```
CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Change the DATAFILE configuration based on the database storage setup, whether it is on an OS file system or within Oracle ASM disk groups.

You must create the tablespace in all containers within the CDB, including the CDB root, all Pluggable Databases (PDBs), all application roots, and all application containers.

For example, if the CDB contains a single PDB named ORCLPDB1, you should then create a tablespace in that PDB.
```
CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Create a new common user for the XStream connect user.

The following example creates a common user named c##cfltuser in a CDB. Before you run this CREATE USER statement, ensure that the tablespace xstream_tbs exists in all containers within the CDB.
```
CREATE USER c##cfltuser IDENTIFIED BY password
  DEFAULT TABLESPACE xstream_tbs
  QUOTA UNLIMITED ON xstream_tbs
  CONTAINER=ALL;
```

Grant the CREATE SESSION and SET CONTAINER privileges.

GRANT CREATE SESSION, SET CONTAINER TO c##cfltuser CONTAINER=ALL;

Grant the SELECT_CATALOG_ROLE role.

GRANT SELECT_CATALOG_ROLE TO c##cfltuser CONTAINER=ALL;

Grant privileges to snapshot table schema and data.

The connector acquires a ROW SHARE lock while capturing the table schema. These locks are held briefly and released once the schema is captured. They are not held when capturing the table data. For the initial consistent snapshot of the table data, the connector uses Oracle Flashback Query.

You can choose to grant privileges to either all tables using system privileges or to specific tables using object privileges.
To grant necessary privileges for all tables, use the following SQL statements:
GRANT SELECT ANY TABLE TO c##cfltuser CONTAINER=ALL; GRANT LOCK ANY TABLE TO c##cfltuser CONTAINER=ALL;
Additionally, if the connector is configured to snapshot the table data, grant the FLASHBACK ANY TABLE privilege:
GRANT FLASHBACK ANY TABLE TO c##cfltuser CONTAINER=ALL;
To grant necessary privileges for specific tables, use the following SQL statement:
ALTER SESSION SET CONTAINER = <pdb_name>; GRANT SELECT ON <schema>.<table> TO c##cfltuser;
Additionally, if the connector is configured to snapshot the table data, grant the FLASHBACK privilege for each table:
ALTER SESSION SET CONTAINER = <pdb_name>; GRANT FLASHBACK ON <schema>.<table> TO c##cfltuser;

The connector requires either the LOCK ANY TABLE system privilege or the SELECT object privilege to acquire a ROW SHARE lock while capturing the table schema.

Non-container database (Non-CDB)¶

Note

The non-CDB architecture is deprecated in Oracle Database 12c and discontinued in Oracle Database 20c.

Connect as a user with SYSDBA privileges.
Create a new tablespace for the XStream administrator.
```
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Change the DATAFILE configuration based on the database storage setup, whether it is on an OS file system or within Oracle ASM disk groups.
Create a new user for the XStream administrator.

The following example creates a user named cfltadmin. Before you run this CREATE USER statement, ensure that the tablespace xstream_adm_tbs exists in the database.
```
CREATE USER cfltadmin IDENTIFIED BY password
  DEFAULT TABLESPACE xstream_adm_tbs
  QUOTA UNLIMITED ON xstream_adm_tbs;
```
Grant the CREATE SESSION privilege.
```
GRANT CREATE SESSION TO cfltadmin;
```

Run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_XSTREAM_AUTH package.

BEGIN
  DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee                 => 'cfltadmin',
    privilege_type          => 'CAPTURE',
    grant_select_privileges => TRUE);
END;

The user executing subprograms in the DBMS_XSTREAM_AUTH package must have the SYSDBA administrative privilege and must exercise this privilege using AS SYSDBA at connect time.

For more information about the full list of privileges granted through this procedure, see the GRANT_ADMIN_PRIVILEGE Procedure section in the Oracle documentation.

Connect as a user with SYSDBA privileges.
Create a new tablespace for the XStream connect user.
```
CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Change the DATAFILE configuration based on the database storage setup, whether it is on an OS file system or within Oracle ASM disk groups.
Create a new user for the XStream connect user.

The following example creates a user named cfltuser. Before you run this CREATE USER statement, ensure that the tablespace xstream_tbs exists in the database.
```
CREATE USER cfltuser IDENTIFIED BY password
  DEFAULT TABLESPACE xstream_tbs
  QUOTA UNLIMITED ON xstream_tbs;
```
Grant the CREATE SESSION privilege.
```
GRANT CREATE SESSION TO cfltuser;
```
Grant the SELECT_CATALOG_ROLE role.
```
GRANT SELECT_CATALOG_ROLE TO cfltuser;
```
Grant privileges to snapshot table schema and and data.

The connector acquires a ROW SHARE lock while capturing the table schema. These locks are held briefly and released once the schema is captured. They are not held when capturing the table data. For the initial consistent snapshot of the table data, the connector uses Oracle Flashback Query.

You can choose to grant privileges to either all tables using system privileges or to specific tables using object privileges.
To grant necessary privileges for all tables, use the following SQL statements:
GRANT SELECT ANY TABLE TO cfltuser; GRANT LOCK ANY TABLE TO cfltuser;
Additionally, if the connector is configured to snapshot the table data, grant the FLASHBACK ANY TABLE privilege:
GRANT FLASHBACK ANY TABLE TO cfltuser;
To grant necessary privileges for specific tables, use the following SQL statement:
GRANT SELECT ON <schema>.<table> TO cfltuser;
Additionally, if the connector is configured to snapshot the table data, grant the FLASHBACK privilege for each table:
GRANT FLASHBACK ON <schema>.<table> TO cfltuser;

The connector requires either the LOCK ANY TABLE system privilege or the SELECT object privilege to acquire a ROW SHARE lock while capturing the table schema.

Create XStream Out¶

The CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package is used to create a capture process, a queue, and an outbound server in a single database.

Note

Before configuring XStream Out, ensure that all containers in the CDB are open in read/write mode.

Complete the following steps:

Connect to the root container as the XStream administrator

Run the CREATE_OUTBOUND procedure.

The following invocation creates an outbound server (named xout), a queue, and a capture process that streams DML and DDL changes from the employees table in the sample schema from the ORCLPDB1 pluggable database.

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  tables(1)  := 'sample.employees';
  schemas(1) := NULL;
  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
     server_name           =>  'xout',
     source_container_name =>  'ORCLPDB1',
     table_names           =>  tables,
     schema_names          =>  schemas);
END;

For more information about the CREATE_OUTBOUND procedure and additional configuration options, see Oracle documentation.

After creating the outbound server, change the connect user to the one created in the Configue database users. This step should be performed while logged in as a user with the DBA role.
```
BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
     server_name  => 'xout',
     connect_user => 'c##cfltuser');
END;
```

Note

The non-CDB architecture is deprecated in Oracle Database 12c and discontinued in Oracle Database 20c.

Complete the following steps:

Connect to the database as the XStream administrator

Run the CREATE_OUTBOUND procedure.

The following invocation creates an outbound server (named xout), a queue, and a capture process that streams DML and DDL changes from the employees table in the sample schema.

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  tables(1)  := 'sample.employees';
  schemas(1) := NULL;
  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
     server_name           =>  'xout',
     table_names           =>  tables,
     schema_names          =>  schemas);
END;

For more information about the CREATE_OUTBOUND procedure and additional configuration options, see Oracle documentation.

After creating the outbound server, change the connect user to the one created in the Configue database users. This step should be performed while logged in as a user with the DBA role.
```
BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
     server_name  => 'xout',
     connect_user => 'cfltuser');
END;
```

Modify the capture parameter CHECKPOINT_RETENTION_TIME to a value appropriate for the database. Typically, this value can be reduced from 60 days (default) to 7 days. As a best practice, retain at least 1 day of Streams LogMiner information in the database.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name              => 'CAP$_XOUT_624',
    checkpoint_retention_time => 7);
END;

Query the DBA_XSTREAM_OUTBOUND view to retrieve the capture name associated with this outbound server.

SELECT CAPTURE_NAME FROM ALL_XSTREAM_OUTBOUND WHERE SERVER_NAME = 'XOUT';

Note

A capture process created using either the DBMS_CAPTURE_ADM.CREATE_CAPTURE or DBMS_XSTREAM_ADM.CREATE_OUTBOUND procedure can use existing data dictionary information in the redo log from a previous run of the DBMS_CAPTURE_ADM.BUILD procedure, or automatically run the DBMS_CAPTURE_ADM.BUILD procedure to extract the data dictionary to the redo log, provided certain conditions outlined in the CREATE_CAPTURE procedure documentation are met. If there are any in-flight transactions, then the BUILD procedure waits until these transactions commit before completing.

To determine if the DBMS_CAPTURE_ADM.BUILD procedure has been run on the source database, query the FIRST_CHANGE# column in the V$ARCHIVED_LOG dynamic performance view where the DICTIONARY_BEGIN column is set to YES. Any SCN value returned from this query can be used provided the redo log containing that SCN value is still available.

To determine if there are in-flight transactions, query the V$TRANSACTION view (or GV$TRANSACTION for RAC environments).

Whenever a capture rule for a table is added, either explicitly using the DBMS_XSTREAM_ADM.ADD_TABLE_RULES procedure or implicitly though procedures like DBMS_XSTREAM_ADM.CREATE_OUTBOUND, the DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION procedure is automatically run. If there are any in-flight transactions involving this table, then the procedure waits until these transactions commit before completing.

Note

Oracle XStream components use space in the SYSAUX tablespace. For example, Oracle XStream can spill LCR for transactions from memory to disk when certain thresholds are met, such as the age of the LCRs or the number of LCRs in a transaction. The spilled data is stored in the SYSAUX tablespace. Make sure to size the SYSAUX tablespace adequately to handle such transactions.

You can query the V$SYSAUX_OCCUPANTS view to monitor the occupants of the SYSAUX tablespace. To increase the size of the tablespace, either increase the size of an existing data file or add a new one to the tablespace. For more information, see Managing Tablespaces chapter in the Oracle Database Administrator’s Guide.

Capture changes from Oracle RAC¶

The connector supports capturing changes from an Oracle Real Application Clusters (RAC) database. You must set the capture parameter use_rac_service to Y for the capture process using the procedure DBMS_CAPTURE_ADM.SET_PARAMETER. This ensures that the capture process runs on the same Oracle RAC instance as its queue.

BEGIN
  DBMS_CAPTURE_ADM.SET_PARAMETER(
    capture_name => '<CAPTURE_NAME>',
    parameter    => 'use_rac_service',
    value        => 'Y');
END;

You can query the DBA_XSTREAM_OUTBOUND view to retrieve the capture name for the outbound server.

If the current owner instance of the queue becomes unavailable, ownership of the queue is automatically transferred to another instance in the cluster. The capture process and the outbound server are restarted automatically on the new owner instance. The connector will restart and attempt to reconnect to the cluster using the configured connection properties.

Multiple outbound server processes that use the same capture process must run in the same Oracle RAC instance as the capture process.

For more information on using Oracle XStream with an Oracle RAC database, see XStream Out and Oracle Real Application Clusters section in the Oracle documentation.