<a id="connect-oracle-xstream-cdc-source-prereqs"></a>

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

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

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

- [Enable Oracle XStream](#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)
- [Validate prerequisites completion](#connect-oracle-xstream-cdc-source-prereqs-check-prerequisites)

<a id="connect-oracle-xstream-cdc-source-prereqs-enable-xstream"></a>

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

<a id="connect-oracle-xstream-cdc-source-prereqs-archivelog-mode"></a>

## 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
   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
   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP MOUNT;
   SQL> ALTER DATABASE ARCHIVELOG;
   SQL> ALTER DATABASE OPEN;
   ```
4. Confirm the database is in `ARCHIVELOG` mode:
   ```sql
   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
   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 <db_Name>
   ```
4. Start the database in mount state.
   ```text
   srvctl start database -d <db_Name> -o mount
   ```
5. Enable `ARCHIVELOG` mode.
   ```sql
   SQL> ALTER DATABASE ARCHIVELOG;
   ```
6. Restart all database instances.
   ```text
   srvctl stop database -d <db_Name>

   srvctl start database -d <db_Name>
   ```
7. Confirm the database is in `ARCHIVELOG` mode.
   ```sql
   SQL> SELECT LOG_MODE FROM V$DATABASE;

   LOG_MODE
   ------------
   ARCHIVELOG
   ```

<a id="connect-oracle-xstream-cdc-source-prereqs-supplemental-logging"></a>

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

<a id="supplemental-logging-user-privileges-cdb"></a>

### 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 = <pdb_name>;
   ALTER TABLE <schema name>.<table name> 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 = <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:
   ```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`.

<a id="supplemental-logging-user-privileges-non-cdb"></a>

### 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 <schema name>.<table name> 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 = '<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:
   ```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`.

<a id="connect-oracle-xstream-cdc-source-prereqs-user-privileges"></a>

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

<a id="user-privileges-cdb"></a>

### 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;
   ```
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;
   ```
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 `SELECT_CATALOG_ROLE` role.
   ```sql
   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](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 = <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:
   ```sql
   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.

<a id="user-privileges-non-cdb"></a>

### 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 `SELECT_CATALOG_ROLE` role.
   ```sql
   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](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 <schema>.<table> TO cfltuser;
   ```

   Additionally, if the connector is configured to snapshot the table data, grant the `FLASHBACK` privilege for each table:
   ```sql
   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.

<a id="connect-oracle-xstream-cdc-source-prereqs-check-xstream-out"></a>

## 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

<a id="configure-streams-pool-size"></a>

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

<a id="connect-oracle-xstream-cdc-source-prereqs-check-rac"></a>

## 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 => '<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.

<a id="connect-oracle-xstream-cdc-source-prereqs-check-amazon-rds"></a>

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

<a id="configure-archivelog-mode-rds"></a>

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

<a id="connect-oracle-xstream-cdc-source-prereqs-check-prerequisites"></a>

## Validate prerequisites completion

Download and execute the [orclcdc_readiness.sql](https://docs.confluent.io/kafka-connectors/oracle-xstream-cdc-source/current/_downloads/35f0e2f456c5dae965ee476492943e9e/orclcdc_readiness.sql)
PL/SQL script to confirm that your Oracle database meets the prerequisites for the Oracle XStream
CDC Source connector. This script performs a series of checks to ensure proper database configuration for the connector.

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

### Script parameters

The script accepts the following parameters:

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

### Usage examples

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

With a PDB:

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

Without a PDB:

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

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