Oracle Database Prerequisites for Oracle XStream CDC Source Connector for Confluent Platform

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 Platform.

Your Oracle database administrator must complete the one of the following setups before the Oracle XStream CDC Source connector can produce expected results. You can set up the Oracle database prerequisites using one of the following approaches:

Automated setup

To simplify the setup, use an interactive SQL*Plus script. This script does not modify your database settings directly. Instead, it generates a secondary SQL script for your review and execution.

Download and execute the following script: generate_xstream_setup_script.sql.

The interactive PL/SQL tool prompts you for configuration details such as usernames, tables for capture, and storage settings and generates a customized setup script based on your environment.

Note the following:

• The script must be run by a user with the DBA role.

• The script is read-only and does not modify the database.

• The script requires an external file (default: tables-list.txt) to determine to define which tables to capture.

  • If you are capturing the entire database, the .txt file is not required.

  • The file must reside in the Oracle directory defined by SCRIPT_DIR. You must create the directory mapping before running the script if it does not exist.

  • The file must contain a single line of comma-separated, fully-qualified table names (for example, HR.EMPLOYEES,SALES.ORDERS).

• A database administrator (DBA) must review the generated secondary script for accuracy before executing it.

  Warning

  No automated rollback exists for changes made by the generated script.

• The generated script is saved in the current directory where the interactive script is executed.

Usage examples

To generate your setup script, open the SQL*Plus command-line tool, connect as a user with DBA privileges, and run the following command:

@generate_xstream_setup_script.sql

After you answer the interactive prompts, the tool outputs a secondary SQL file (for example, xstream_setup_script.sql) that contains the actual DDL and configuration commands for your database.

Use of the generate_xstream_setup_script.sql script is subject to the same terms and usage rights as the Oracle XStream CDC Source connector.

Manual setup

To execute the prerequisite steps manually, follow the sections below:

• 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

• Downstream capture database prerequisites

• Cascading downstream capture database prerequisites

• Validate prerequisites completion

Enable Oracle XStream

This section includes the commands to enable Oracle XStream.

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

2. To enable XStream, run the following statement:

   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:

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

2. Check if the database is in ARCHIVELOG mode:

   SELECT LOG_MODE FROM V$DATABASE;
   

   If the LOG_MODE shows ARCHIVELOG, you can skip the remaining steps.

3. Place the database in ARCHIVELOG mode:

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

4. Confirm the database is in ARCHIVELOG mode:

   SELECT LOG_MODE FROM V$DATABASE;
   
   LOG_MODE
   ------------
   ARCHIVELOG
   

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

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

2. Check if the database is in ARCHIVELOG mode:

   SELECT LOG_MODE FROM V$DATABASE;
   

   If the LOG_MODE shows ARCHIVELOG, you can skip the remaining steps.

3. Stop all database instances.

   srvctl stop database -d <db_Name>
   

4. Start the database in mount state.

   srvctl start database -d <db_Name> -o mount
   

5. Enable ARCHIVELOG mode.

   ALTER DATABASE ARCHIVELOG;
   

6. Restart all database instances.

   srvctl stop database -d <db_Name>
   
   srvctl start database -d <db_Name>
   

7. Confirm the database is in ARCHIVELOG mode.

   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:

1. Database supplemental logging: This enables supplemental logging for the entire database.

2. Table supplemental logging: This enables supplemental logging of specific tables using log groups.

Container database (CDB)

Prerequisite: Enable minimal supplemental logging

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

1. 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.

Enable supplemental logging for specific tables (recommended)

1. 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;
   

2. 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.

Enable supplemental logging for entire database

1. 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;
   

2. 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.

Prerequisite: Enable minimal supplemental logging

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

1. 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.

Enable supplemental logging for specific tables (recommended)

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

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

2. 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.

Enable supplemental logging for entire database

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

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

2. 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)

Configure an XStream administrator (capture user)

1. Connect as a user with SYSDBA privileges to the root container.

2. 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;
   

   Note

   Any future PDBs also require the tablespace. To avoid managing tablespaces across multiple PDBs, use the default USERS tablespace instead of creating custom tablespaces for Oracle XStream. This eliminates the need to create a tablespace in each PDB.

3. 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;
   

4. Grant the CREATE SESSION and SET CONTAINER privileges.

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

5. 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.

Configure an XStream connect user

1. Connect as a user with SYSDBA privileges to the root container.

2. 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;
   

   Note

   Any future PDBs also require the tablespace. To avoid managing tablespaces across multiple PDBs, use the default USERS tablespace instead of creating custom tablespaces for Oracle XStream. This eliminates the need to create a tablespace in each PDB.

3. 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;
   

4. Grant the CREATE SESSION and SET CONTAINER privileges.

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

5. Grant the SELECT_CATALOG_ROLE role.

   GRANT SELECT_CATALOG_ROLE TO c##cfltuser CONTAINER=ALL;
   

6. 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.

   Grant privileges to all tables

   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;
   

   Grant privileges to specific tables

   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.

Configure an XStream administrator (capture user)

1. Connect as a user with SYSDBA privileges.

2. 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.

3. 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;
   

4. Grant the CREATE SESSION privilege.

   GRANT CREATE SESSION TO cfltadmin;
   

5. 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.

Configure an XStream connect user

1. Connect as a user with SYSDBA privileges.

2. 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.

3. 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;
   

4. Grant the CREATE SESSION privilege.

   GRANT CREATE SESSION TO cfltuser;
   

5. Grant the SELECT_CATALOG_ROLE role.

   GRANT SELECT_CATALOG_ROLE TO cfltuser;
   

6. 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.

   Grant privileges to all tables

   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;
   

   Grant privileges to specific tables

   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.

Container database (CDB)

Note

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

Complete the following steps:

1. Connect to the root container as the XStream administrator

2. Run the CREATE_OUTBOUND procedure.

   The following invocation creates an outbound server (named xout), a queue, and a capture process (named xcap) 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',
        capture_name          =>  'xcap',
        table_names           =>  tables,
        schema_names          =>  schemas);
   END;
   

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

3. 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;
   

Non-container database (Non-CDB)

Note

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

Complete the following steps:

1. Connect to the database as the XStream administrator

2. Run the CREATE_OUTBOUND procedure.

   The following invocation creates an outbound server (named xout), a queue, and a capture process (named xcap) 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',
        capture_name          =>  'xcap',
        table_names           =>  tables,
        schema_names          =>  schemas);
   END;
   

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

3. 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.

SELECT FIRST_CHANGE# FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN = 'YES';

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 xcap and an outbound server named xout:

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

BEGIN
  DBMS_XSTREAM_ADM.SET_PARAMETER(
    streams_type => 'apply',
    streams_name => 'xout',
    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 xcap.

BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name              => 'xcap',
    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 xcap:

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

To view rules for an outbound server named xout:

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

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:

1. Create a DB parameter group with the enable_goldengate_replication initialization parameter set to true.

2. 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.

Downstream capture database prerequisites

This section includes the prerequisite steps for the downstream capture topology, in which the XStream capture process and outbound server run on a separate downstream database. This topology requires setup on two databases: the source primary and the downstream primary.

Naming convention

The SQL examples use the following identifiers. Replace them with your own values in production.

Property	Source database	Downstream database
DB_NAME (CDB / PDB)	SRCCDB / SRCPDB1	CAPCDB / CAPPDB1
DB_DOMAIN	EXAMPLE.COM	EXAMPLE.COM
DB_UNIQUE_NAME	SRCCDB_DB	CAPCDB
GLOBAL_NAME (CDB / PDB)	SRCCDB.EXAMPLE.COM / SRCPDB1.EXAMPLE.COM	CAPCDB.EXAMPLE.COM / CAPPDB1.EXAMPLE.COM
XStream administrator	c##cfltadmin	c##cfltadmin (same name and password as source)
XStream connect user	c##cfltuser	c##cfltuser (same name and password as source)
XStream queue	—	c##cfltadmin.xs_queue
Capture process	—	xs_capture
Outbound server	—	xout
TNS alias for peer database	capcdb (resolves to downstream)	srccdb (resolves to source)

Note

Confluent recommends setting GLOBAL_NAMES=TRUE in any distributed Oracle environment. Oracle XStream requires that the GLOBAL_NAME of each database in the configuration be unique. Query the current global name with SELECT * FROM GLOBAL_NAME; and rename it with ALTER DATABASE RENAME GLOBAL_NAME TO <new_name>;. Restart the database immediately after you rename the GLOBAL_NAME parameter.

Supported topologies

Non-CDB

For non-CDB databases, make these substitutions throughout the SQL examples:

• Drop the c## prefix from user names: use cfltadmin and cfltuser.

• Omit all CONTAINER=ALL clauses on CREATE USER, GRANT, and DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE calls.

• Omit ALTER SESSION SET CONTAINER = ... calls and per-PDB tablespace creation.

• In DBMS_CAPTURE_ADM.CREATE_CAPTURE and DBMS_XSTREAM_ADM.ADD_OUTBOUND, set source_database and source_root_name to the source’s global name (no PDB qualification); omit source_container_name.

• Omit source_container_name from DBMS_XSTREAM_ADM.ADD_SCHEMA_RULES.

• Omit database.pdb.name from the connector configuration.

Oracle RAC source

When the source is a RAC database:

• LOG_ARCHIVE_DEST_2 (Step 3.4) must be set on every source instance. Each instance ships its thread to the downstream’s RFS pool independently.

• Step 2.5 (standby redo logs) requires m+1 groups per source thread, assigned to the matching THREAD number. For an n-thread RAC source with m groups per thread, create n*(m+1) standby groups in total.

• The TNS alias for the source on the downstream (Step 2.2) should resolve through the source’s SCAN address so that the downstream DB link follows source instance failover.

Oracle RAC downstream

When the downstream is a RAC database:

• After Step 4.7 (CREATE_CAPTURE), set use_rac_service to Y so that the capture process runs on the RAC instance that owns the queue:

  BEGIN
    DBMS_XSTREAM_ADM.SET_PARAMETER(
      streams_type => 'capture',
      streams_name => 'xs_capture',
      parameter    => 'use_rac_service',
      value        => 'Y');
  END;
   /
  

• Step 4.10 (ADD_OUTBOUND) runs on a single instance. The outbound server registers a service so the connector can reattach through SCAN after instance failover.

• Set downstream.database.hostname to the downstream SCAN address and downstream.database.service.name to the auto-created XStream service name (visible in SERVICE_NAME of DBA_SERVICES on the downstream after ALTER_OUTBOUND).

Important

Apply the steps in the following order. Running them out of order can drive the capture process into an unrecoverable WAITING FOR REDO state. For example, calling START_CAPTURE before redo transport is in place causes this. For more detail, see the Step 4.11 warning.

Step 1: Configure database prerequisites on both databases

Configure each of the following on both the source and the downstream:

1. Enable Oracle XStream. For more information, see Enable Oracle XStream.

2. Configure ARCHIVELOG mode. The source must be in ARCHIVELOG mode. The downstream must also be in ARCHIVELOG mode for real-time downstream capture; it is not required for archived-log downstream capture. For more information, see Configure ARCHIVELOG mode.

3. Configure supplemental logging. Enable minimal supplemental logging at the CDB root (or database level for non-CDB), plus per-table or database-wide ALL COLUMNS logging on the source. For more information, see Configure supplemental logging.

4. Configure redo transport authentication. Redo transport sessions are authenticated using either SSL or a remote login password file. When using a remote login password file:

   • Copy the source’s password file (for example, orapwSRCCDB) to the password-file directory on the downstream host. ASM environments use Oracle’s pwcopy utility.

   • Rename the file to match the downstream SID (for example, orapwSRCCDB → orapwCAPCDB).

   • Ensure REMOTE_LOGIN_PASSWORDFILE is set to SHARED or EXCLUSIVE on both databases.

   Important

   Setting the same SYS password independently on both databases is not sufficient. Redo transport authenticates against the password file itself, so the file on the downstream must be a copy of the source file.

   For password file location and copy procedures, see Configure Redo Transport Authentication in the Oracle Data Guard documentation.

Step 2: Set up the downstream database

Configure the downstream before the source begins shipping redo.

1. Set the downstream’s database identity and restart (skip if already set):

   ALTER SYSTEM SET db_domain='EXAMPLE.COM'   SCOPE=SPFILE;
   ALTER SYSTEM SET GLOBAL_NAMES=TRUE         SCOPE=BOTH;
   ALTER DATABASE RENAME GLOBAL_NAME TO CAPCDB.EXAMPLE.COM;
   ALTER SYSTEM REGISTER;
   SHUTDOWN IMMEDIATE;
   STARTUP;
   

2. Add a TNS alias for the source in the downstream’s tnsnames.ora. The alias name must match the source’s DB_UNIQUE_NAME so that the public DB link in Step 4 resolves correctly:

   srccdb =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = <source_host>)(PORT = 1521)))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = SRCCDB_DB.EXAMPLE.COM)))
   

   In a RAC downstream, update tnsnames.ora on every node.

3. Create the directory layout for standby redo logs and the foreign archive destination:

   mkdir -p /opt/oracle/oradata/CAPCDB/standby
   mkdir -p /opt/oracle/oradata/CAPCDB/arc_dest/SRCCDB
   

4. Configure the local archive destination for the downstream’s own redo:

   ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
     'LOCATION=/opt/oracle/oradata/CAPCDB/archive_logs
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
   

5. Add standby redo logs sized to match or exceed the source’s online redo log size. For each redo thread on the source, the downstream needs one more group than the source has online redo log groups. Do not configure the logs in archived-log mode.

   Important

   Keep archived standby redo logs (real-time mode) and foreign archived logs (archived-log mode) in a directory separate from the downstream’s own archived online redo logs. Do not use the downstream’s recovery area to stage foreign archived logs.

   For more information on LOG_ARCHIVE_DEST_n parameters, see LOG_ARCHIVE_DEST_n Parameter Attributes in the Oracle documentation.

   Single-instance example (source has three groups, so create four standby groups for THREAD 1):

   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
     GROUP 4 ('/opt/oracle/oradata/CAPCDB/standby/slog4a.rdo') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
     GROUP 5 ('/opt/oracle/oradata/CAPCDB/standby/slog5a.rdo') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
     GROUP 6 ('/opt/oracle/oradata/CAPCDB/standby/slog6a.rdo') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
     GROUP 7 ('/opt/oracle/oradata/CAPCDB/standby/slog7a.rdo') SIZE 200M;
   

   For a two-node RAC source with three groups per thread, create eight standby groups — four per thread:

   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7  ('+RECO/CAPCDB/STANDBY/slog07.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 8  ('+RECO/CAPCDB/STANDBY/slog08.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9  ('+RECO/CAPCDB/STANDBY/slog09.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('+RECO/CAPCDB/STANDBY/slog10.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 11 ('+RECO/CAPCDB/STANDBY/slog11.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 12 ('+RECO/CAPCDB/STANDBY/slog12.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 13 ('+RECO/CAPCDB/STANDBY/slog13.log') SIZE 200M;
   ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 14 ('+RECO/CAPCDB/STANDBY/slog14.log') SIZE 200M;
   

   For more information, see Standby Redo Logs in the Oracle Data Guard documentation.

6. Set LOG_ARCHIVE_CONFIG to list both DB_UNIQUE_NAME values and add the foreign archive destination. Do not configure the LOG_ARCHIVE_DEST_2 in archived-log mode.

   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(SRCCDB_DB,CAPCDB)' SCOPE=BOTH;
   
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'LOCATION=/opt/oracle/oradata/CAPCDB/arc_dest/SRCCDB
      VALID_FOR=(STANDBY_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
   

Step 3: Set up the source database

1. Set the source’s database identity and restart:

   ALTER SYSTEM SET DB_UNIQUE_NAME='SRCCDB_DB' SCOPE=SPFILE;
   ALTER SYSTEM SET db_domain='EXAMPLE.COM'   SCOPE=SPFILE;
   ALTER SYSTEM SET GLOBAL_NAMES=TRUE         SCOPE=BOTH;
   ALTER DATABASE RENAME GLOBAL_NAME TO SRCCDB.EXAMPLE.COM;
   ALTER SYSTEM REGISTER;
   SHUTDOWN IMMEDIATE;
   STARTUP;
   

2. Add a TNS alias for the downstream in the source’s tnsnames.ora:

   capcdb =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = <downstream_host>)(PORT = 1521)))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = capcdb.example.com)))
   

   In a RAC source, update tnsnames.ora on every node.

3. Set LOG_ARCHIVE_CONFIG on the source to match the downstream’s setting:

   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(SRCCDB_DB,CAPCDB)' SCOPE=BOTH;
   

4. Configure redo transport to the downstream (LOG_ARCHIVE_DEST_2). The form depends on the capture mode:

   Real-time downstream capture

   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'SERVICE=CAPCDB ASYNC NOREGISTER
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=CAPCDB' SCOPE=BOTH;
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
   

   Archived-log downstream capture

   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'SERVICE=CAPCDB ASYNC NOREGISTER
      TEMPLATE=+RECO/arc_dest/SRCCDB/%t_%s_%r.arc
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=CAPCDB' SCOPE=BOTH;
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
   

   NOREGISTER is mandatory in both modes. ASYNC is recommended to avoid impacting source database performance.

   For Oracle RAC source: run this ALTER SYSTEM on every source instance. Each thread ships its redo independently to the downstream’s RFS pool.

5. Configure the XStream administrator user as documented for local capture. See Configure database users. In addition, grant REMOTE ADMIN ACCESS so the downstream’s DB link can invoke source-side XStream procedures during CREATE_CAPTURE:

   BEGIN
     DBMS_XSTREAM_AUTH.GRANT_REMOTE_ADMIN_ACCESS(
       grantee => 'c##cfltadmin');
   END;
   /
   

   For more information about this grant, see the GRANT_REMOTE_ADMIN_ACCESS Procedure in the Oracle documentation.

6. Configure the XStream connect user. See Configure database users. The same user with the same password must also be created on the downstream in Step 4.4.

Step 4: Create XStream Out on the downstream

1. Create the XStream administrator user on the downstream with the same name and password as on the source. Grant capture privileges per Configure database users. The downstream administrator does not need GRANT_REMOTE_ADMIN_ACCESS.

2. Grant CREATE DATABASE LINK to the downstream’s administrator:

   GRANT CREATE DATABASE LINK TO c##cfltadmin CONTAINER=ALL;
   

3. Create a public DB link in the downstream’s CDB root pointing at the source. The link name must match the source’s global name; USING references the TNS alias added in Step 2.2:

   CREATE PUBLIC DATABASE LINK srccdb.example.com USING 'srccdb';
   

   Note

   Oracle XStream requires both a public and a private database link from the downstream to the source. The public link is created here; the private link is created in Step 4.5.

4. Create the XStream connect user on the downstream with the same name and password as on the source. Privilege requirements differ by role:

   • On the source, the connect user needs the same snapshot privileges as documented in the local capture setup.

   • On the downstream, the connect user only attaches to the outbound server, so CREATE SESSION, SET CONTAINER, and SELECT_CATALOG_ROLE are sufficient.

5. Connect as the XStream administrator and create a private DB link used by CREATE_CAPTURE:

   CONNECT c##cfltadmin/<password>;
   ALTER SESSION SET CONTAINER = CDB$ROOT;
   CREATE DATABASE LINK srccdb.example.com
     CONNECT TO c##cfltadmin
     IDENTIFIED BY <password>
     USING 'srccdb';
   

6. Set up the XStream queue:

   BEGIN
     DBMS_XSTREAM_ADM.SET_UP_QUEUE(
       queue_table => 'c##cfltadmin.xs_queue_tbl',
       queue_name  => 'c##cfltadmin.xs_queue');
   END;
   /
   

   For more information on using the DBMS_XSTREAM_ADM.SET_UP_QUEUE procedure, see SET_UP_QUEUE Procedure in the Oracle documentation.

7. Create the capture process. use_database_link => TRUE directs DBMS_CAPTURE_ADM to invoke source-side procedures over the DB link:

   BEGIN
     DBMS_CAPTURE_ADM.CREATE_CAPTURE(
       queue_name        => 'c##cfltadmin.xs_queue',
       capture_name      => 'xs_capture',
       capture_user      => 'c##cfltadmin',
       source_database   => 'SRCCDB',
       source_root_name  => 'SRCCDB',
       use_database_link => TRUE,
       capture_class     => 'XStream');
   END;
   /
   

   For Non-CDB: set source_database and source_root_name to the source’s global name (no PDB qualification).

   For Oracle RAC downstream: immediately after CREATE_CAPTURE, set use_rac_service to Y. For details, see Supported topologies.

   For more information on using the DBMS_CAPTURE_ADM.CREATE_CAPTURE procedure, see CREATE_CAPTURE Procedure in the Oracle documentation.

8. Add capture rules for each schema or table to capture. For downstream capture, source_database, source_root_name, and source_container_name are required; without them the rule cannot be evaluated against LCRs that arrive over the redo log transport:

   BEGIN
     DBMS_XSTREAM_ADM.ADD_SCHEMA_RULES(
       schema_name           => 'dbuser',
       streams_type          => 'capture',
       streams_name          => 'xs_capture',
       queue_name            => 'c##cfltadmin.xs_queue',
       include_dml           => TRUE,
       include_ddl           => TRUE,
       source_database       => 'SRCPDB1',
       source_root_name      => 'SRCCDB',
       source_container_name => 'SRCPDB1');
   END;
   /
   

   For Non-CDB: drop source_container_name, and set both source_database and source_root_name to the source’s global name.

   For more information on using the DBMS_XSTREAM_ADM.ADD_SCHEMA_RULES/DBMS_XSTREAM_ADM.ADD_TABLE_RULES procedure, see ADD_SCHEMA_RULES/ADD_TABLE_RULES Procedure in the Oracle documentation.

9. For real-time capture only, set downstream_real_time_mine to Y. Omit this step for archived-log mode:

   BEGIN
     DBMS_XSTREAM_ADM.SET_PARAMETER(
       streams_type => 'capture',
       streams_name => 'xs_capture',
       parameter    => 'downstream_real_time_mine',
       value        => 'Y');
   END;
   /
   

10. Add the outbound server, apply rules, set the connect user, and start capture.

    Add outbound server

    Provide the queue and capture process created.

    BEGIN
       DBMS_XSTREAM_ADM.ADD_OUTBOUND(
       server_name           => 'xout',
       queue_name            => 'c##cfltadmin.xs_queue',
       capture_name          => 'xs_capture',
       include_dml           => FALSE,
       include_ddl           => FALSE,
       source_database       => 'SRCPDB1',
       source_root_name      => 'SRCCDB',
       source_container_name => 'SRCPDB1');
    END;
    

    For Non-CDB: drop source_container_name, and set both source_database and source_root_name to the source’s global name.

    For more information on using the DBMS_XSTREAM_ADM.ADD_OUTBOUND procedure, see ADD_OUTBOUND Procedure in the Oracle documentation.

    Apply rules

    Add apply rules for each schema or table to capture. For downstream capture, source_database, source_root_name, and source_container_name are required; without them the rule cannot be evaluated against LCRs that arrive over the redo log transport:

    BEGIN
       DBMS_XSTREAM_ADM.ADD_SCHEMA_RULES(
          schema_name           => 'dbuser',
          streams_type          => 'apply',
          streams_name          => 'xout',
          queue_name            => 'c##cfltadmin.xs_queue',
          include_dml           => TRUE,
          include_ddl           => TRUE,
          source_database       => 'SRCPDB1',
          source_root_name      => 'SRCCDB',
          source_container_name => 'SRCPDB1');
    END;
    

    For Non-CDB: drop source_container_name, and set both source_database and source_root_name to the source’s global name.

    Set connect user

    Change the connect user. Perform this step while logged in as a user with the DBA role.

    BEGIN
       DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
          server_name  => 'xout',
          connect_user => 'c##cfltuser');
    END;
    

    For more information on using the DBMS_XSTREAM_ADM.ALTER_OUTBOUND procedure, see ALTER_OUTBOUND Procedure in the Oracle documentation.

    Start capture process

    BEGIN
       DBMS_CAPTURE_ADM.START_CAPTURE(
          capture_name => 'xs_capture');
    END;
    

    For more information on using the DBMS_CAPTURE_ADM.START_CAPTURE procedure, see START_CAPTURE Procedure in the Oracle documentation.

11. Force a log switch on the source immediately after START_CAPTURE returns. Run on the source database:

    -- Run on the source database.
    ALTER SYSTEM ARCHIVE LOG CURRENT;
    

    Use ARCHIVE LOG CURRENT, not SWITCH LOGFILE:

    • SWITCH LOGFILE switches only the current instance’s online redo log.

    • ARCHIVE LOG CURRENT switches and archives the current log on all instances, then waits for the archives to complete.

    For Oracle RAC source: this distinction is critical. SWITCH LOGFILE only affects the connected instance, leaving other threads unswitched. Use ARCHIVE LOG CURRENT to force shipping from all instances at once.

    This step serves two purposes:

    • Confirms redo transport is working. The archived log should appear in the downstream’s configured foreign-archive directory.

    • Completes capture initialization. The capture process needs fresh redo on the downstream to run its LogMiner dictionary build before transitioning to a delivery-ready state.

    Warning

    If SELECT STATE FROM V$XSTREAM_CAPTURE; on the downstream shows WAITING FOR REDO: FILE <thread>_<seq>.dbf, the capture process is in an unrecoverable state. Issuing log switches at this point may appear to resolve the problem, but the underlying issue persists: any time capture restarts, files archived while capture was down are silently skipped, and gaps are not auto-resolved. The only fix is to drop and recreate the capture process. To avoid this, always run the log switch immediately after START_CAPTURE.

Step 5: Verify health

After START_CAPTURE and the log switch in prerequisites Step 4.11, verify that all three layers: redo transport, capture, and the outbound server — are healthy. For queries and expected steady state see Downstream capture monitoring and troubleshooting.

Cascading downstream capture database prerequisites

The cascading topology adds a third database, a Physical Standby with Active Data Guard, between the source primary and the downstream primary.

Note

Active Data Guard required. Only Active Data Guard (ADG; Physical Standby with READ ONLY WITH APPLY) is supported as the middle hop. Standard Data Guard, Logical Standby, Snapshot Standby, and chained cascading are not supported. See Restrictions.

Before starting these steps, complete all steps in Downstream capture database prerequisites, with the source-side redo transport adjustments in Step C2 replacing the Step 3.4 settings from the base prerequisites. The recommended execution order is:

1. Downstream initial setup (Downstream capture database prerequisites Step 2)

2. Source initial setup (Downstream capture database prerequisites Step 3), with LOG_ARCHIVE_DEST_STATE_2=DEFER until the standby is up

3. Standby creation using Oracle Recovery Manager (RMAN) duplicate (Step C1)

4. Standby DG_CONFIG and cascade DEST_3 (Step C3)

5. Source DEST_2 enabled (Step C2)

6. Standby opened in Active Data Guard mode (Step C4)

7. Downstream DG_CONFIG adjusted (Step C5)

8. XStream creation on the downstream (Downstream capture database prerequisites Step 4)

Naming convention for the standby

In addition to the identifiers in the downstream capture naming table, the standby database uses:

Property	Standby database
DB_NAME (CDB / PDB)	SRCCDB / SRCPDB1 (same as source; RMAN duplicate preserves these)
DB_DOMAIN	EXAMPLE.COM
DB_UNIQUE_NAME	SRCCDB_STBY
GLOBAL_NAME (CDB / PDB)	SRCCDB.EXAMPLE.COM / SRCPDB1.EXAMPLE.COM (same as source)
TNS alias for the standby (on source)	srccdb_stby
TNS alias for the source (on standby)	srccdb

The standby’s DB_UNIQUE_NAME must differ from the source’s. Its GLOBAL_NAME matches the source’s because RMAN duplicate preserves the database identity; this is standard Data Guard behavior and does not conflict with the distinct global name requirement.

Step C1: Provision the standby database

This is standard Oracle Data Guard physical standby setup. For SQL specifics, see the Oracle Database Data Guard Concepts and Administration documentation. At a high level:

1. Copy the source’s password file to the standby host so that redo transport authentication succeeds. Automatic Storage Management (ASM) environments use Oracle’s pwcopy utility.

2. Run RMAN DUPLICATE TARGET DATABASE FOR STANDBY from the primary. Either the active form (FROM ACTIVE DATABASE) or the backup form (FROM BACKUP) is supported.

3. Add standby redo logs on the standby at the same size and one greater group count than the source’s online redo logs.

4. Set LOG_ARCHIVE_CONFIG on the standby to list all three DB_UNIQUE_NAME values:

   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(SRCCDB_DB,SRCCDB_STBY,CAPCDB)' SCOPE=BOTH;
   

Step C2: Adjust the source’s redo transport

In the cascading topology, the source ships redo to the standby rather than directly to the downstream. Make these two adjustments to Step 3 of the downstream capture prerequisites:

1. Update LOG_ARCHIVE_CONFIG on the source to include the standby:

   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(SRCCDB_DB,SRCCDB_STBY,CAPCDB)' SCOPE=BOTH;
   

2. Repoint LOG_ARCHIVE_DEST_2 at the standby’s TNS alias. NOREGISTER is omitted because Data Guard transport to a standby operates inside the DG configuration. Set STATE_2=DEFER until the standby is fully built (after Step C1), then enable it:

   -- During source setup (before standby is up): create deferred.
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'SERVICE=SRCCDB_STBY ASYNC
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=SRCCDB_STBY' SCOPE=BOTH;
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;
   
   -- After standby is up and accepting redo: enable.
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
   

   The source no longer carries a LOG_ARCHIVE_DEST_n pointing at the downstream. The downstream is reached through the cascade in Step C3.

Step C3: Configure the cascade destination on the standby

Add LOG_ARCHIVE_DEST_3 on the standby to forward cascaded redo to the downstream as source redo lands in the standby redo logs:

Real-time cascade

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=
  'SERVICE=CAPCDB ASYNC NOREGISTER
   VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
   DB_UNIQUE_NAME=CAPCDB' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

Archived-log cascade

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=
  'SERVICE=CAPCDB NOREGISTER
   TEMPLATE=+RECO/arc_dest/SRCCDB/%t_%s_%r.arc
   VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
   DB_UNIQUE_NAME=CAPCDB' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) has two effects:

• STANDBY_LOGFILES makes Oracle forward redo from the standby redo logs (where source redo arrives), not from the standby’s online redo logs.

• STANDBY_ROLE ensures this destination only activates while the database is in the standby role. After a Data Guard switchover or failover, the role flips and this destination automatically deactivates.

Step C4: Open the standby in Active Data Guard mode

The standby must run in READ ONLY WITH APPLY for the cascade to forward redo as it arrives:

ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  USING CURRENT LOGFILE DISCONNECT;

USING CURRENT LOGFILE enables real-time apply on the standby. The cascade activates as soon as Step C3 is in place and the standby has standby redo logs receiving from the source.

Step C5: Adjust the downstream’s DG_CONFIG

The downstream must accept cascaded redo from the standby, not directly from the source. Update its LOG_ARCHIVE_CONFIG:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
  'DG_CONFIG=(SRCCDB_STBY,CAPCDB)' SCOPE=BOTH;

The downstream’s LOG_ARCHIVE_DEST_2 (the foreign archive landing location from Step 2.6 of the base prerequisites), its TNS alias for srccdb (Step 2.2), and the DB links to the source (Step 4.3 and Step 4.5) are all unchanged. These must continue to point at the source primary, not the standby, because the operations they support are not permitted on a read-only standby.

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 logical change records (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).

• Role: The database role being validated. The default value is LOCAL. Specify one of the following options:

  • LOCAL: The capture process and outbound server run on the same database as the source database. This option matches the original colocated topology.

  • SOURCE: The source database in a downstream-capture topology. The script skips the outbound server and capture process checks because those components reside on the downstream database.

  • DOWNSTREAM: The downstream database that hosts the capture process and outbound server in a downstream-capture topology. The script skips the PDB and snapshot-privilege checks because those are validated on the source database.

  Note

  To skip a trailing parameter, pass an empty string (‘’). If you omit a parameter entirely, the script prompts you for a value; pressing Enter at the prompt accepts the default value. Automated invocations must pass ‘’ explicitly.

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.

Colocated capture (default LOCAL role)

With a PDB:

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

Without a PDB:

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

Downstream capture topology

In a downstream capture topology, you must execute the script twice: once against the source database (using the SOURCE role) and once against the downstream database (using the DOWNSTREAM role). The PDB argument applies only when running the script against the source database.

On the source database (CDB with a PDB):

@orclcdc_readiness.sql C##CFLTADMIN C##CFLTUSER XOUT SRCPDB1 SOURCE

On the source database (non-CDB):

@orclcdc_readiness.sql CFLTADMIN CFLTUSER XOUT '' SOURCE

On the downstream database (CDB or non-CDB):

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

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.