# 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 that supports local capture only. 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`](/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: ```sql @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](#connect-oracle-xstream-cdc-source-prereqs-enable-xstream) - [Configure ARCHIVELOG mode](#connect-oracle-xstream-cdc-source-prereqs-archivelog-mode) - [Configure supplemental logging](#connect-oracle-xstream-cdc-source-prereqs-supplemental-logging) - [Configure database users](#connect-oracle-xstream-cdc-source-prereqs-user-privileges) - [Create XStream Out](#connect-oracle-xstream-cdc-source-prereqs-check-xstream-out) - [Capture changes from Oracle RAC](#connect-oracle-xstream-cdc-source-prereqs-check-rac) - [Working with Amazon RDS for Oracle](#connect-oracle-xstream-cdc-source-prereqs-check-amazon-rds) - [Downstream capture database prerequisites](#connect-oracle-xstream-cdc-source-prereqs-downstream) - [Cascading downstream capture database prerequisites](#connect-oracle-xstream-cdc-source-prereqs-cascading) - [Validate prerequisites completion](#connect-oracle-xstream-cdc-source-prereqs-check-prerequisites) ### 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: ```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: ```sql 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: ```sql 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: ```sql SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; ``` 4. 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`: 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: ```sql SELECT LOG_MODE FROM V$DATABASE; ``` If the `LOG_MODE` shows `ARCHIVELOG`, you can skip the remaining steps. 3. Stop all database instances. ```text srvctl stop database -d ``` 4. Start the database in mount state. ```text srvctl start database -d -o mount ``` 5. Enable `ARCHIVELOG` mode. ```sql ALTER DATABASE ARCHIVELOG; ``` 6. Restart all database instances. ```text srvctl stop database -d srvctl start database -d ``` 7. 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: 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. ```sql 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: ```sql ALTER SESSION SET CONTAINER = ; ALTER TABLE . ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 2. To confirm, run the following query: ```sql 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`. ```sql ALTER SESSION SET CONTAINER = ; SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE = 'ALL COLUMN LOGGING' and OWNER = '' and 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: ```sql ALTER SESSION SET CONTAINER = CDB$ROOT; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 2. To confirm, run the following query: ```sql 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. ```sql 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: ```sql ALTER TABLE . ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 2. To confirm, run the following query: ```sql 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`. ```sql SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE = 'ALL COLUMN LOGGING' and OWNER = '' and 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: ```sql ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 2. To confirm, run the following query: ```sql 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. ```sql 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. ```sql 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. ```sql 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. ```sql GRANT CREATE SESSION, SET CONTAINER TO c##cfltadmin CONTAINER=ALL; ``` 5. Run the `GRANT_ADMIN_PRIVILEGE` procedure in the `DBMS_XSTREAM_AUTH` package. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_AUTH.html#GUID-6B7B3EC6-6204-4349-84DD-183D1A5DC8CE) 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. ```sql 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. ```sql 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. ```sql 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. ```sql GRANT CREATE SESSION, SET CONTAINER TO c##cfltuser CONTAINER=ALL; ``` 5. Grant the connect user read access to the required catalog views. You can grant the connect user access to the required catalog views in one of two ways: by granting the `SELECT_CATALOG_ROLE` role, or by granting `SELECT` on an explicit list of views. ### Grant SELECT_CATALOG_ROLE (recommended) Confluent recommends granting `SELECT_CATALOG_ROLE` for most deployments. Because Oracle maintains the role’s membership, it automatically covers the catalog views that later connector versions read, so no privilege changes are required during an upgrade. ```sql GRANT SELECT_CATALOG_ROLE TO c##cfltuser CONTAINER=ALL; ``` ### Grant SELECT on specific catalog views If your security policy does not permit `SELECT_CATALOG_ROLE`, grant `SELECT` on the following catalog views instead. This list is specific to a connector version, so an upgrade may introduce new privilege requirements. Before you upgrade, review the required privileges on this page and in the [changelog](changelog.md#connect-oracle-xstream-cdc-source-changelog) for the target version, and grant any additions first. Otherwise, a newly required privilege could prevent the connector from starting, and some capabilities may remain disabled until you grant the corresponding privilege. ```sql GRANT SELECT ON SYS.V_$DATABASE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$VERSION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$THREAD TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$INSTANCE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$OSSTAT TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$PARAMETER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$TRANSACTION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_CAPTURE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_CAPTURE_PARAMETERS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$XSTREAM_CAPTURE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_OUTBOUND TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_OUTBOUND_PROGRESS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$XSTREAM_OUTBOUND_SERVER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_APPLY TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_APPLY_PROGRESS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_APPLY_PARAMETERS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_RULES TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_OUT_SUPPORT_MODE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_ADMINISTRATOR TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_REGISTERED_ARCHIVED_LOG TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$XSTREAM_TRANSACTION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$STREAMS_POOL_STATISTICS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$LOG TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$LOGFILE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$ARCHIVE_DEST TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$ARCHIVE_DEST_STATUS 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/flashback.html#GUID-0BD1F4BD-894B-47BA-A44B-74BCA4CFD60E). 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: ```sql 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: ```sql 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: ```sql ALTER SESSION SET CONTAINER = ; GRANT SELECT ON . TO c##cfltuser; ``` Additionally, if the connector is configured to snapshot the table data, grant the `FLASHBACK` privilege for each table: ```sql ALTER SESSION SET CONTAINER = ; GRANT FLASHBACK ON . 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. ```sql 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. ```sql CREATE USER cfltadmin IDENTIFIED BY password DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs; ``` 4. Grant the `CREATE SESSION` privilege. ```sql GRANT CREATE SESSION TO cfltadmin; ``` 5. Run the `GRANT_ADMIN_PRIVILEGE` procedure in the `DBMS_XSTREAM_AUTH` package. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_AUTH.html#GUID-6B7B3EC6-6204-4349-84DD-183D1A5DC8CE) 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. ```sql 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. ```sql CREATE USER cfltuser IDENTIFIED BY password DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs; ``` 4. Grant the `CREATE SESSION` privilege. ```sql GRANT CREATE SESSION TO cfltuser; ``` 5. Grant the connect user read access to the required catalog views. You can grant the connect user access to the required catalog views in one of two ways: by granting the `SELECT_CATALOG_ROLE` role, or by granting `SELECT` on an explicit list of views. ### Grant SELECT_CATALOG_ROLE (recommended) Confluent recommends granting `SELECT_CATALOG_ROLE` for most deployments. Because Oracle maintains the role’s membership, it automatically covers the catalog views that later connector versions read, so no privilege changes are required during an upgrade. ```sql GRANT SELECT_CATALOG_ROLE TO cfltuser; ``` ### Grant SELECT on specific catalog views If your security policy does not permit `SELECT_CATALOG_ROLE`, grant `SELECT` on the following catalog views instead. This list is specific to a connector version, so an upgrade may introduce new privilege requirements. Before you upgrade, review the required privileges on this page and in the [changelog](changelog.md#connect-oracle-xstream-cdc-source-changelog) for the target version, and grant any additions first. Otherwise, a newly required privilege could prevent the connector from starting, and some capabilities may remain disabled until you grant the corresponding privilege. ```sql GRANT SELECT ON SYS.V_$DATABASE TO cfltuser; GRANT SELECT ON SYS.V_$VERSION TO cfltuser; GRANT SELECT ON SYS.V_$THREAD TO cfltuser; GRANT SELECT ON SYS.GV_$INSTANCE TO cfltuser; GRANT SELECT ON SYS.GV_$OSSTAT TO cfltuser; GRANT SELECT ON SYS.GV_$PARAMETER TO cfltuser; GRANT SELECT ON SYS.GV_$TRANSACTION TO cfltuser; GRANT SELECT ON SYS.DBA_CAPTURE TO cfltuser; GRANT SELECT ON SYS.DBA_CAPTURE_PARAMETERS TO cfltuser; GRANT SELECT ON SYS.GV_$XSTREAM_CAPTURE TO cfltuser; GRANT SELECT ON SYS.DBA_XSTREAM_OUTBOUND TO cfltuser; GRANT SELECT ON SYS.DBA_XSTREAM_OUTBOUND_PROGRESS TO cfltuser; GRANT SELECT ON SYS.GV_$XSTREAM_OUTBOUND_SERVER TO cfltuser; GRANT SELECT ON SYS.DBA_APPLY TO cfltuser; GRANT SELECT ON SYS.DBA_APPLY_PROGRESS TO cfltuser; GRANT SELECT ON SYS.DBA_APPLY_PARAMETERS TO cfltuser; GRANT SELECT ON SYS.DBA_XSTREAM_RULES TO cfltuser; GRANT SELECT ON SYS.DBA_XSTREAM_OUT_SUPPORT_MODE TO cfltuser; GRANT SELECT ON SYS.DBA_XSTREAM_ADMINISTRATOR TO cfltuser; GRANT SELECT ON SYS.DBA_REGISTERED_ARCHIVED_LOG TO cfltuser; GRANT SELECT ON SYS.GV_$XSTREAM_TRANSACTION TO cfltuser; GRANT SELECT ON SYS.GV_$STREAMS_POOL_STATISTICS TO cfltuser; GRANT SELECT ON SYS.V_$LOG TO cfltuser; GRANT SELECT ON SYS.V_$LOGFILE TO cfltuser; GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO cfltuser; GRANT SELECT ON SYS.GV_$ARCHIVE_DEST TO cfltuser; GRANT SELECT ON SYS.GV_$ARCHIVE_DEST_STATUS 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/flashback.html#GUID-0BD1F4BD-894B-47BA-A44B-74BCA4CFD60E). 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: ```sql 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: ```sql GRANT FLASHBACK ANY TABLE TO cfltuser; ``` ### Grant privileges to specific tables To grant necessary privileges for specific tables, use the following SQL statement: ```sql GRANT SELECT ON . TO cfltuser; ``` Additionally, if the connector is configured to snapshot the table data, grant the `FLASHBACK` privilege for each table: ```sql GRANT FLASHBACK ON . 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. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_XSTREAM_ADM.html#GUID-A602ED86-0F5A-4A27-92A0-55D5ADC0AF0D). 3. After creating the outbound server, change the connect user to the one created in the [Configue database users](#connect-oracle-xstream-cdc-source-prereqs-user-privileges). This step should be performed while logged in as a user with the `DBA` role. ```sql 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. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_XSTREAM_ADM.html#GUID-A602ED86-0F5A-4A27-92A0-55D5ADC0AF0D). 3. After creating the outbound server, change the connect user to the one created in the [Configue database users](#connect-oracle-xstream-cdc-source-prereqs-user-privileges). This step should be performed while logged in as a user with the `DBA` role. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-59CD1818-374A-4A48-AB06-717DFEBBE40D) 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. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_ADM.html#GUID-B78D97B1-F479-4E8A-86DC-E82505F08EFA) procedure. - For a capture process, set [MAX_SGA_SIZE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-5A56325D-7613-4FDE-BBB9-0704B990E51F) parameter with `streams_type` to `capture`. - For an outbound server (apply process), set [MAX_SGA_SIZE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_APPLY_ADM.html#GUID-8814ED8C-682B-4B48-A79B-DB6BE63C3309) 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`: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-STREAMS_POOL_STATISTICS.html) view. - For more information, see [Configure the Streams pool](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/configuring-xstream-out.html#GUID-41E37368-E434-4AD7-A5E8-4786E41C8C94) in the Oracle documentation. - For guidance on troubleshooting unresponsive connectors related to Streams pool usage, see [The XStream Out Client Application Is Unresponsive](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/troubleshooting-xstream-out.html#GUID-21256A5A-6017-4FBA-BA96-925A7DD98CA5) 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`. ```sql BEGIN DBMS_CAPTURE_ADM.ALTER_CAPTURE( capture_name => 'xcap', checkpoint_retention_time => 7); END; ``` For more information, see [Capture Process Checkpoints and XStream Out](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/xstream-out-concepts.html#GUID-E3FF6FC1-6136-4748-9DFB-BB157CB77560) 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PROCESSES.html) and [SESSIONS](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SESSIONS.html) 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/monitoring-xstream-out.html#GUID-AAD5D552-A91B-4842-AA3A-95FD9356D2D9) 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/general-xstream-concepts.html#GUID-41229989-0893-4463-8063-3FD28D94A233) 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_XSTREAM_RULES.html) view displays information about all XStream rules in the database. ##### Rules examples To view rules for a capture process named `xcap`: ```sql SELECT * FROM DBA_XSTREAM_RULES WHERE STREAMS_TYPE = 'CAPTURE' and STREAMS_NAME = 'XCAP'; ``` To view rules for an outbound server named `xout`: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html) 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. ```sql BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/xstream-out-concepts.html#GUID-A058CE29-4D13-4EB2-ACDE-29DC6B7F2CDE) 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html). It does not support CDBs on Amazon RDS for Oracle. The connector also does not support [Amazon RDS Custom for Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/working-with-custom-oracle.html), 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Creating.html). #### 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.BackupRetention.html). The following example sets the archived redo log retention period to 24 hours: ```sql 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.RetainRedoLogs.html) 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: ```sql 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: ```sql 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)](#supplemental-logging-user-privileges-non-cdb). For more information on `rdsadmin.rdsadmin_util.alter_supplemental_logging` procedure, see [Setting supplemental logging](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Log.html#Appendix.Oracle.CommonDBATasks.AddingSupplementalLogging) 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)](#user-privileges-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: ```sql CREATE TABLESPACE xstream_adm_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED; ``` To create a new tablespace for the XStream connect user: ```sql CREATE TABLESPACE xstream_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED; ``` For more information on creating tablespaces, see [Creating and sizing tablespaces](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.TablespacesAndDatafiles.html#Appendix.Oracle.CommonDBATasks.CreatingTablespacesAndDatafiles). ### 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 ;`. 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](#xstream-downstream-capture-step-3-4-log-archive)) 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)](#xstream-downstream-capture-step-2-5-standby) 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](#xstream-downstream-capture-step-2-2-tns-alias)) 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](#xstream-downstream-capture-step-4-7-create-capture) (`CREATE_CAPTURE`), set `use_rac_service` to `Y` so that the capture process runs on the RAC instance that owns the queue: ```sql BEGIN DBMS_XSTREAM_ADM.SET_PARAMETER( streams_type => 'capture', streams_name => 'xs_capture', parameter => 'use_rac_service', value => 'Y'); END; / ``` * [Step 4.10](#xstream-downstream-capture-step-4-10-outbound) (`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](#xstream-downstream-capture-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](#connect-oracle-xstream-cdc-source-prereqs-enable-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](#connect-oracle-xstream-cdc-source-prereqs-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](#connect-oracle-xstream-cdc-source-prereqs-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](https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html#GUID-0DC30726-3471-4588-BFE0-9CA0736328E2) 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): ```sql 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](#xstream-downstream-capture-prereqs-rac-downstream-out) resolves correctly: ```text srccdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(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: ```text 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: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/LOG_ARCHIVE_DEST_n-parameter-attributes.html#GUID-2E1845CC-B3CA-4409-BACB-6AB40D6D42E5) in the Oracle documentation. Single-instance example (source has three groups, so create four standby groups for `THREAD 1`): ```sql 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: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/xstrm/configuring-xstream-out.html#GUID-6914DCCC-DA99-4AD9-AF59-E240BB5BCA66) 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. ```sql 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: ```sql 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`: ```text capcdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(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: ```sql 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 ```sql 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 ```sql 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](#connect-oracle-xstream-cdc-source-prereqs-user-privileges). In addition, grant `REMOTE ADMIN ACCESS` so the downstream’s DB link can invoke source-side XStream procedures during `CREATE_CAPTURE`: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_AUTH.html#ARPLS-GUID-7AABD748-2BC8-4EA7-8184-07BFC4DA9442) in the Oracle documentation. 6. **Configure the XStream connect user.** On the source, the connect user runs the initial snapshot and schema queries, so it needs read access to a subset of the catalog views plus the snapshot privileges. Create it with the same name and password you use on the downstream in [Step 4.4](#xstream-downstream-capture-step-4-4-user). Grant `CREATE SESSION` and `SET CONTAINER`, then grant read access to the catalog views the source connection reads. You can grant `SELECT_CATALOG_ROLE` in place of the explicit list. ```sql GRANT CREATE SESSION, SET CONTAINER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$DATABASE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$VERSION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$THREAD TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$INSTANCE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$OSSTAT TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$PARAMETER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$TRANSACTION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_OUT_SUPPORT_MODE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_ADMINISTRATOR TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$LOG TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$LOGFILE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$ARCHIVE_DEST TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$ARCHIVE_DEST_STATUS TO c##cfltuser CONTAINER=ALL; ``` Also grant the snapshot privileges (`SELECT ANY TABLE` and `LOCK ANY TABLE`, and `FLASHBACK ANY TABLE` if the connector snapshots table data), or the equivalent object privileges, as described in [Configure database users](#connect-oracle-xstream-cdc-source-prereqs-user-privileges). For a non-CDB source, create the user without the `c##` prefix and omit `CONTAINER=ALL`. #### 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](#connect-oracle-xstream-cdc-source-prereqs-user-privileges). The downstream administrator does not need `GRANT_REMOTE_ADMIN_ACCESS`. 2. **Grant** `CREATE DATABASE LINK` to the downstream’s administrator: ```sql 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](#xstream-downstream-capture-step-2-2-tns-alias): ```sql 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](#xstream-downstream-capture-step-4-5-private-link). 4. **Create the XStream connect user** on the downstream with the same name and password as on the source. On the downstream, the connect user only attaches to the outbound server, so it needs `CREATE SESSION`, `SET CONTAINER`, and read access to the catalog views listed below. It does **not** need snapshot privileges. You can grant `SELECT_CATALOG_ROLE` in place of the explicit list. ```sql GRANT CREATE SESSION, SET CONTAINER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$DATABASE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$VERSION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$THREAD TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$INSTANCE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$PARAMETER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_CAPTURE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_CAPTURE_PARAMETERS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$XSTREAM_CAPTURE TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_OUTBOUND TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_OUTBOUND_PROGRESS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$XSTREAM_OUTBOUND_SERVER TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_APPLY TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_APPLY_PROGRESS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_APPLY_PARAMETERS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_RULES TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_XSTREAM_ADMINISTRATOR TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.DBA_REGISTERED_ARCHIVED_LOG TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$XSTREAM_TRANSACTION TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.GV_$STREAMS_POOL_STATISTICS TO c##cfltuser CONTAINER=ALL; GRANT SELECT ON SYS.V_$LOGFILE TO c##cfltuser CONTAINER=ALL; ``` For a non-CDB source, create the user without the `c##` prefix and omit `CONTAINER=ALL`. 5. **Connect as the XStream administrator and create a private DB link** used by `CREATE_CAPTURE`: ```sql CONNECT c##cfltadmin/; ALTER SESSION SET CONTAINER = CDB$ROOT; CREATE DATABASE LINK srccdb.example.com CONNECT TO c##cfltadmin IDENTIFIED BY USING 'srccdb'; ``` 6. **Set up the XStream queue**: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_ADM.html#GUID-9C28901F-0AA0-4152-925F-347DEE930AEF) 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: ```sql 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](#xstream-downstream-capture-prereqs-rac-downstream). **Required for all downstream topologies:** after `CREATE_CAPTURE`, and before the capture process is started for the first time, set the capture process’s `_SEND_STREAMS_DICTIONARY` parameter to `INFINITE`. Oracle Support recommends this setting for downstream capture configurations. It gives every replicated source object a data dictionary entry on the apply side, so that the apply process knows the object and column information of the source objects. Without it, objects created before the capture process’s first SCN have no such entry: ```sql BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name => 'xs_capture', parameter => '_SEND_STREAMS_DICTIONARY', value => 'INFINITE'); END; / ``` Confirm the parameter before you continue. The following query returns one row, with `VALUE` set to `INFINITE` and `SET_BY_USER` set to `YES`. `DBA_CAPTURE_PARAMETERS` stores capture names in uppercase: ```sql SELECT PARAMETER, VALUE, SET_BY_USER FROM DBA_CAPTURE_PARAMETERS WHERE CAPTURE_NAME = UPPER('xs_capture') AND PARAMETER = '_SEND_STREAMS_DICTIONARY'; ``` #### WARNING If you omit this step, the capture process silently skips DML and DDL changes for tables that were created before its first SCN. Tables created after the first SCN stream normally, no error is raised in `DBA_CAPTURE` or `DBA_APPLY`, and the connector keeps running while producing no change events for the skipped tables. If you have already created the capture process but have not started it yet, set the parameter now and continue with the remaining steps. No other action is needed. Setting the parameter on a capture process that has already been started does not recover the tables it is already skipping. For recovery steps, see [No change events for pre-existing tables](troubleshooting.md#xstream-downstream-missing-events-pre-existing-tables). For more information on using the `DBMS_CAPTURE_ADM.SET_PARAMETER` procedure, see [SET_PARAMETER Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-5A56325D-7613-4FDE-BBB9-0704B990E51F) in the Oracle documentation. For more information on using the `DBMS_CAPTURE_ADM.CREATE_CAPTURE` procedure, see [CREATE_CAPTURE Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-59CD1818-374A-4A48-AB06-717DFEBBE40D) 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: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_ADM.html#GUID-20F14596-2155-442E-8A99-77824E405B91) in the Oracle documentation. 9. **For real-time capture only**, set `downstream_real_time_mine` to `Y`. Omit this step for archived-log mode: ```sql 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. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_ADM.html#GUID-3645325A-7127-4438-935D-2B64E075352A) 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: ```sql 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. ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XSTREAM_ADM.html#GUID-DBD1382B-52F9-4213-B3ED-1CF7B22CF322) in the Oracle documentation. ### Start capture process ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-F640B5B3-AD03-43A5-BBC9-1CAEA0E35051) in the Oracle documentation. 11. **Force a log switch on the source** immediately after `START_CAPTURE` returns. Run on the source database: ```sql -- 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 _.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](#xstream-downstream-capture-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](troubleshooting.md#xstream-downstream-capture-monitoring). ### 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](cascading-downstream-capture.md#xstream-cascading-restrictions). **Before starting these steps**, complete all steps in [Downstream capture database prerequisites](#connect-oracle-xstream-cdc-source-prereqs-downstream), 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](#xstream-downstream-capture-step-2-downstream-setup)) 2. Source initial setup (Downstream capture database prerequisites [Step 3](#xstream-downstream-capture-step-3-source-setup)), with `LOG_ARCHIVE_DEST_STATE_2=DEFER` until the standby is up 3. Standby creation using Oracle Recovery Manager (RMAN) duplicate ([Step C1](#xstream-cascading-prereqs-c1)) 4. Standby `DG_CONFIG` and cascade `DEST_3` ([Step C3](#xstream-cascading-prereqs-c3)) 5. Source `DEST_2` enabled ([Step C2](#xstream-cascading-prereqs-c2)) 6. Standby opened in Active Data Guard mode ([Step C4](#xstream-cascading-prereqs-c4)) 7. Downstream `DG_CONFIG` adjusted ([Step C5](#xstream-cascading-prereqs-c5)) 8. XStream creation on the downstream (Downstream capture database prerequisites [Step 4](#xstream-downstream-capture-prereqs-rac-downstream-out)) #### Naming convention for the standby In addition to the identifiers in the [downstream capture naming table](#connect-oracle-xstream-cdc-source-prereqs-downstream), 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/oracle-data-guard-concepts.html) 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: ```sql 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: ```sql 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: ```sql -- 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. To support switchover and failover, set the same `LOG_ARCHIVE_DEST_2` on the standby database, with `SERVICE` pointing at the source’s TNS alias (`srccdb`) and `DB_UNIQUE_NAME=SRCCDB_DB`, then set `LOG_ARCHIVE_DEST_STATE_2=ENABLE`: ```sql ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=srccdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SRCCDB_DB' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; ``` Because the destination is `VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)`, it stays inactive until the standby is promoted to primary. #### 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 ```sql 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 ```sql 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. To support switchover and failover, set the same `LOG_ARCHIVE_DEST_3` on the primary database and set `LOG_ARCHIVE_DEST_STATE_3=ENABLE`. `STANDBY_ROLE` keeps it inactive while that database holds the primary role, and it activates when the database is demoted to standby and begins cascading redo to the downstream. #### 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: ```sql 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`: ```sql ALTER SYSTEM SET LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(SRCCDB_DB,SRCCDB_STBY,CAPCDB)' SCOPE=BOTH; ``` The downstream’s `LOG_ARCHIVE_DEST_2` (the foreign archive landing location from [Step 2.6](#xstream-downstream-capture-step-2-6-log-archive) of the base prerequisites), its TNS alias for `srccdb` ([Step 2.2](#xstream-downstream-capture-step-2-2-tns-alias)), and the DB links to the source ([Step 4.3](#xstream-downstream-capture-step-4-3-public-link) and [Step 4.5](#xstream-downstream-capture-step-4-5-private-link)) 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. To support switchover and failover, add a TNS alias for the standby database on the downstream as well. ### Validate prerequisites completion Download and execute the [`orclcdc_readiness.sql`](/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: ```sql @orclcdc_readiness.sql C##CFLTADMIN C##CFLTUSER XOUT ORCLPDB1 LOCAL ``` Without a PDB: ```sql @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): ```sql @orclcdc_readiness.sql C##CFLTADMIN C##CFLTUSER XOUT SRCPDB1 SOURCE ``` On the source database (non-CDB): ```sql @orclcdc_readiness.sql CFLTADMIN CFLTUSER XOUT '' SOURCE ``` On the downstream database (CDB or non-CDB): ```sql @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.