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
Working with Amazon RDS for Oracle
Validate prerequisites completion

Enable Oracle XStream¶

This section includes the commands to enable Oracle XStream.

Connect as a user with SYSDBA privileges. If working with a container database (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.

To confirm, run the following query:

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'enable_goldengate_replication';

If the return value is true, the setting is enabled.

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 supplemental logging for all columns 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 supplemental logging for all columns 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¶

Setup¶

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;
```

XStream Out and in-flight transactions¶

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.

Configuration¶

Configure Streams pool size¶

Oracle XStream components, such as capture processes and outbound servers, use memory from the Streams pool. The pool also stores LCR from the buffered queue. You can control the memory (in MB) allocated to these components using the MAX_SGA_SIZE parameter.

To configure MAX_SGA_SIZE parameter, use the DBMS_XSTREAM_ADM.SET_PARAMETER procedure.

For a capture process, set MAX_SGA_SIZE parameter with streams_type to capture.
For an outbound server (apply process), set MAX_SGA_SIZE parameter with streams_type to apply.

As a best practice, set the MAX_SGA_SIZE parameter to 1 GB for both the capture process and outbound server. You can then increase or decrease based on workload requirements.

Note

The total SGA memory allocated to all components using the Streams pool must be less than the value set for the STREAMS_POOL_SIZE initialization parameter.

The following example sets MAX_SGA_SIZE to 1 GB for both a capture process named xs_capture and an outbound server named xs_outbound:

BEGIN
  DBMS_XSTREAM_ADM.SET_PARAMETER(
    streams_type => 'capture',
    streams_name => 'xs_capture',
    parameter    => 'max_sga_size',
    value        => '1024');
END;

BEGIN
  DBMS_XSTREAM_ADM.SET_PARAMETER(
    streams_type => 'apply',
    streams_name => 'xs_outbound',
    parameter    => 'max_sga_size',
    value        => '1024');
END;

To monitor the Streams pool’s current usage, you can query the V$STREAMS_POOL_STATISTICS view.

For more information, see Configure the Streams pool in the Oracle documentation.
For guidance on troubleshooting unresponsive connectors related to Streams pool usage, see The XStream Out Client Application Is Unresponsive in the Oracle documentation.

Configure checkpoint retention time¶

The checkpoint retention time is the number of days a capture process retains checkpoints before automatically purging them. You can change the retention time using the CHECKPOINT_RETENTION_TIME capture process parameter. Typically, this value can be reduced from 60 days (default) to 7 days. As a best practice, you should retain at least one day of Streams LogMiner information in the database.

The following example sets CHECKPOINT_RETENTION_TIME to 7 days for a capture process named xs_capture.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name              => 'xs_capture',
    checkpoint_retention_time => 7);
END;

For more information, see Capture Process Checkpoints and XStream Out in the Oracle documentation.

Configure Processes and Sessions¶

Oracle XStream components, such as capture processes, propagations, and outbound servers, use processes that run in the background. You may need to increase the values of the PROCESSES and SESSIONS initialization parameters to accommodate these background processes in addition to the other background processes within the Oracle Database.

To monitor session information for XStream components, see Monitoring Session Information About XStream Out Components in the Oracle documentation. This guide provides a query to retrieve session information about each XStream component in a database.

Rules and filtering¶

Oracle XStream components, such as capture processes and outbound servers, uses Rules and Rule Sets to determine which LCRs to stream between components.

For a capture process, if a change in the redo log satisfies the configured rule sets, the capture process captures it. Otherwise, it discards the change.
For an outbound server, if an LCR satisfies the configured rule sets, the outbound server sends it to the XStream client application (connector). Otherwise, it discards the LCR.

Rules support filtering database changes at various levels of granularity, including global, schema, and table levels. The DBMS_XSTREAM_ADM package provides several PL/SQL procedures to create system-generated rules:

Procedures that create or alter an outbound server and the rules. These procedures include CREATE_OUTBOUND, ADD_OUTBOUND, and ALTER_OUTBOUND.
Procedures that add rules to an existing XStream component, such as a capture process or an outbound server. These procedures include the ADD_*_RULES procedures, such as ADD_SCHEMA_RULES and ADD_TABLE_RULES.

The DBA_XSTREAM_RULES view displays information about all XStream rules in the database.

Rules examples¶

To view rules for a capture process named xs_capture:

SELECT * FROM DBA_XSTREAM_RULES WHERE STREAMS_TYPE = 'CAPTURE' and STREAMS_NAME = 'XS_CAPTURE';

To view rules for an outbound server named xs_outbound:

SELECT * FROM DBA_XSTREAM_RULES WHERE STREAMS_TYPE = 'APPLY' and STREAMS_NAME = 'XS_OUTBOUND';

Filtering options¶

In addition to the rules configured within Oracle XStream, the connector provides its own filtering options through configuration properties such as table.include.list and table.exclude.list. These properties allow users to specify which tables to include or exclude during both the snapshot and streaming phases.

Ensure consistency between the rules configured in Oracle XStream and the connector’s filtering configuration. Any mismatch between the two can result in change events not being captured or streamed as expected.

Storage considerations¶

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.

Working with Amazon RDS for Oracle¶

The connector supports Oracle Database 19c using the non-CDB architecture on Amazon RDS for Oracle. It does not support CDBs on Amazon RDS for Oracle.

The connector also does not support Amazon RDS Custom for Oracle, including both CDB and non-CDB architectures.

This section only highlights the differences in the prerequisite setup steps specific to Amazon RDS for Oracle.

Because Amazon RDS for Oracle is a managed service, you do not have access to a user with SYSDBA privileges. Instead, use the master user account to perform any administrative tasks described in this documentation that requires SYSDBA privileges.

Enable Oracle XStream¶

To enable Oracle XStream on Amazon RDS for Oracle:

Create a DB parameter group with the enable_goldengate_replication initialization parameter set to true.
Assign this parameter group to your DB instance.

For more information, see Creating a DB parameter group in Amazon RDS.

Configure ARCHIVELOG mode¶

To enable ARCHIVELOG mode on your Amazon RDS for Oracle instance, set a non-zero backup retention period. Amazon RDS for Oracle automatically disables ARCHIVELOG mode when backup retention is set to 0 days. For more information, see Backup retention period.

The following example sets the archived redo log retention period to 24 hours:

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

For more information on the rdsadmin.rdsadmin_util.set_configuration procedure, see Retaining archived redo logs section of the Amazon RDS documentation.

Note

Archived redo logs are stored on your DB instance. Ensure your instance has sufficient available storage to accommodate them.

Configure supplemental logging¶

Amazon RDS for Oracle does not provide the ALTER DATABASE privilege. To enable minimum database-level supplemental logging, run the following PL/SQL procedure:

BEGIN
  rdsadmin.rdsadmin_util.alter_supplemental_logging(
    p_action => 'ADD');
END;

To enable supplemental logging for all columns for the entire database, run the following PL/SQL procedure:

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

Confluent recommends enabling supplemental logging only for the tables of interest, rather than for the entire database, to minimize the amount of redo generated on the source database. For steps to enable supplemental logging at the table level, see Enable supplemental logging for specific tables (recommended).

For more information on rdsadmin.rdsadmin_util.alter_supplemental_logging procedure, see Setting supplemental logging section of the Amazon RDS documentation.

Configure database users¶

To configure the database users, follow the instructions provided in the Non-container database (Non-CDB) section.

Amazon RDS for Oracle does not support specifying physical file names when creating data files. As a result, ensure that file names are not specified in the DATAFILE clause of the CREATE TABLESPACE statement.

To create a new tablespace for the XStream administrator:

CREATE TABLESPACE xstream_adm_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;

To create a new tablespace for the XStream connect user:

CREATE TABLESPACE xstream_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;

For more information on creating tablespaces, see Creating and sizing tablespaces.

Validate prerequisites completion¶

Download and execute the orclcdc_readiness.sql PL/SQL script to confirm that your Oracle database meets the prerequisites for the Oracle XStream CDC Source connector. This script performs a series of checks to ensure proper database configuration for the connector.

Note

The script must be run by a user with the DBA role.
This is a read-only script and does not make any changes to the database.

Script parameters¶

The script accepts the following parameters:

Capture database user: User who manages the XStream components and captures changes from the redo log (default: C##CFLTADMIN).
Connect database user: User who connects to the XStream outbound server to receive changes as LCRs (default: C##CFLTUSER).
Outbound server name: Name of the outbound server that the connector uses to receive LCRs (default: XOUT).
Pluggable database (PDB) name: Name of the PDB from which the connector captures changes. Required only when capturing changes from a PDB in a multitenant environment (default: NULL).

Usage examples¶

The following examples run the script using the capture user C##CFLTADMIN, connect user C##CFLTUSER, outbound server XOUT, and optionally a PDB named ORCLPDB1.

With a PDB:

@orclcdc_readiness.sql C##CFLTADMIN C##CFLTUSER XOUT ORCLPDB1

Without a PDB:

@orclcdc_readiness.sql C##CFLTADMIN C##CFLTUSER XOUT ''

Note that the use of the orclcdc_readiness.sql script is subject to the same usage rights and terms as the Oracle XStream CDC Source connector.