Configuring Oracle CDC (XStream)

Overview

Change Data Capture (CDC) enables ROMA Connect to synchronize data sources in real time and synchronously delete data tables.

ROMA Connect supports two CDC modes: XStream and LogMiner. This section describes how to enable the CDC function in XStream mode for the Oracle database. In addition, the CDC configuration varies according to the Oracle database type (CDB or non-CDB).

Prerequisites

• The Oracle database must have the OGG license (the OGG does not need to be installed).
• If the Oracle database is deployed in primary/secondary mode, the secondary Oracle database is not used.
• Fields of the binary type, such as BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, cannot be collected.

Configuration When the Oracle Database Is Not a CDB Database

It is recommended that the database administrator configure the CDC function.

1. Enable log archiving and XStream.
   1. Run the following command to connect to the database as user sys:

      In practice, you can connect to the database in multiple modes. The following uses the command line mode as an example.

      sqlplus /nolog
      CONNECT sys/password@host:port AS SYSDBA;

      Among them:

      • password indicates the password of user sys of the database. You can obtain the password from the database administrator.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
   2. Run the following command to enable Xstream:

      alter system set enable_goldengate_replication=true;
   3. Run the following command to check whether the log archiving function is enabled:

      archive log list;
      • If the message "Database log mode: No Archive Mode" is displayed, log archiving is disabled. Go to the next step.
      • If the message "Database log mode: No Archive Mode" is displayed, log archiving is enabled. Go to 1.g.
   4. Run the following command to set archive log parameters:

      alter system set db_recovery_file_dest_size = 100G;
      alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;

      Among them:

      • 100G indicates the size of the log file storage space. Set this parameter based on the site requirements.
      • /opt/oracle/oradata/recovery_area indicates the log storage path. Set this parameter based on the site requirements. Ensure that the path has been created in advance.
   5. Run the following command to enable log archiving function:
      NOTICE:

      • Enabling the log archiving function requires database restart, which will interrupt services. Exercise caution when performing this operation.
      • Archived logs occupy a large amount of disk space. If the disk space is full, services are affected. Therefore, you need to periodically delete expired archive logs.

      shutdown immediate;
      startup mount;
      alter database archivelog;
      alter database open;
   6. Run the following command to check whether the log archiving function is enabled:

      archive log list;

      If the message "Database log mode: Archive Mode" is displayed, log archiving is enabled.

   7. Run the following command to disconnect the database:

      exit;
2. Create an XStream user and grant permissions to the user.
   1. Run the following command to connect to the database instance as user sys:

      sqlplus sys/password@host:port/SID as sysdba

      Among them:

      • password indicates the password of user sys of the database. You can obtain the password from the database administrator.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
      • SID indicates the name of the instance where the data to be synchronized is located. Set this parameter based on the site requirements.
   2. Run the following commands to create an XStream administrator and configure permissions for the administrator:

      CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/orcl/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
      CREATE USER xstrmadmin IDENTIFIED BY password DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs;
      GRANT CREATE SESSION TO xstrmadmin;
      BEGIN
         DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
            grantee                 => 'xstrmadmin',
            privilege_type          => 'CAPTURE',
            grant_select_privileges => TRUE,
            container             => 'ALL'
         );
      END;

      Among them:

      • xstream_adm_tbs indicates the tablespace name of the XStream administrator. Set it based on site requirements.
      • /opt/oracle/oradata/orcl/xstream_adm_tbs.dbf indicates the tablespace file of the XStream administrator. Set this parameter based on site requirements.
      • xstrmadmin indicates the username of the XStream administrator. Set it based on site requirements.
      • password indicates the password of the XStream administrator. Set it based on site requirements.
      • container => 'ALL' This row is added only if the Oracle version is 12c or later. Otherwise, delete this row.
   3. Run the following commands to create a user for connect ROMA Connect to the database and configure permissions for the user:

      CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/orcl/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
      CREATE USER xstrm IDENTIFIED BY password DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs;
      GRANT CREATE SESSION TO xstrm;
      GRANT SELECT ON V_$DATABASE to xstrm;
      GRANT FLASHBACK ANY TABLE TO xstrm;
      GRANT SELECT ANY TABLE to xstrm;
      GRANT LOCK ANY TABLE TO xstrm;
      grant select_catalog_role to xstrm;

      Among them:

      • xstream_tbs indicates the tablespace name of the ROMA Connect connection user. Set it based on site requirements.
      • /opt/oracle/oradata/orcl/xstream_tbs.dbf indicates the tablespace file of the ROMA Connect connection user. Set it based on site requirements.
      • xstrm indicates the name of the ROMA Connect connection user. Set it based on site requirements.
      • password indicates the password of the ROMA Connect connection user. Set it based on site requirements.
   4. Run the following command to modify log record parameters:

      alter database add supplemental log data (all) columns;
   5. Run the following command to disconnect the database:

      exit;
3. Create an XStream outbound server.
   NOTE:

   An XStream outbound server can be used only in one integration task. If the database needs to be used in multiple integration tasks, you need to create multiple XStream outbound servers.

   1. Run the following command to connect to the database instance as user xstrmadmin:

      sqlplus xstrmadmin/password@host:port/SID

      Among them:

      • xstrmadmin indicates the username of the XStream administrator, which is created in 2.b.
      • password indicates the password of the XStream administrator, which is configured in 2.b.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
      • SID indicates the name of the instance where the data to be synchronized is located. Set this parameter based on the site requirements.
   2. Run the following command to create an XStream outbound server:

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

      Among them:

      • ROMA indicates the schemas where the data table to be synchronized is located, that is, the schemas from which the CDC needs to capture data. Set this parameter based on site requirements.
      • dbzxout indicates the outbound server name, which must be unique. Set this parameter based on site requirements.
   3. Run the following command to disconnect the database:

      exit;
   4. Run the following command to connect to the database instance as user sys:

      sqlplus sys/password@host:port/SID as sysdba

      Among them:

      • password indicates the password of user sys of the database. You can obtain the password from the database administrator.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
      • SID indicates the name of the instance where the data to be synchronized is located. Set this parameter based on the site requirements.
   5. Run the following command to allow user xstrm to connect to the XStream outbound server:

      BEGIN
         DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
            server_name  => 'dbzxout',
            connect_user => 'xstrm'
         );
      END;

      Among them:

      • dbzxout is the outbound server name, which is configured in 3.b.
      • xstrm is the username for connecting to ROMA Connect. It is configured in 2.c.
   6. Run the following command to disconnect the database:

      exit;

Configuration When the Oracle Database Is a CDB Database

It is recommended that the database administrator configure the CDC function.

1. Enable log archiving and XStream.
   1. Run the following command to connect to the database as user sys:

      In practice, you can connect to the database in multiple modes. The following uses the command line mode as an example.

      sqlplus /nolog
      CONNECT sys/password@host:port AS SYSDBA;

      Among them:

      • password indicates the password of user sys of the database. You can obtain the password from the database administrator.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
   2. Run the following command to enable Xstream:

      alter system set enable_goldengate_replication=true;
   3. Run the following command to check whether the log archiving function is enabled:

      archive log list;
      • If the message "Database log mode: No Archive Mode" is displayed, log archiving is disabled. Go to the next step.
      • If the message "Database log mode: No Archive Mode" is displayed, log archiving is enabled. Go to 1.g.
   4. Run the following command to set archive log parameters:

      alter system set db_recovery_file_dest_size = 100G;
      alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;

      Among them:

      • 100G indicates the size of the log file storage space. Set this parameter based on the site requirements.
      • /opt/oracle/oradata/recovery_area indicates the log storage path. Set this parameter based on the site requirements. Ensure that the path has been created in advance.
   5. Run the following command to enable log archiving function:
      NOTICE:

      • Enabling the log archiving function requires database restart, which will interrupt services. Exercise caution when performing this operation.
      • Archived logs occupy a large amount of disk space. If the disk space is full, services are affected. Therefore, you need to periodically delete expired archive logs.

      shutdown immediate;
      startup mount;
      alter database archivelog;
      alter database open;
   6. Run the following command to check whether the log archiving function is enabled:

      archive log list;

      If the message "Database log mode: Archive Mode" is displayed, log archiving is enabled.

   7. Run the following command to disconnect the database:

      exit;
2. Create an XStream user and grant permissions to the user.
   1. Run the following command to connect to the database instance as user sys:

      sqlplus sys/password@host:port/SID as sysdba

      Among them:

      • password indicates the password of user sys of the database. You can obtain the password from the database administrator.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
      • SID indicates the name of the CDB database where the data to be synchronized is located. Set this parameter based on the site requirements.
   2. Run the following commands to create an XStream administrator and configure permissions for the administrator:

      CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
      alter session set container = ORCLPDB1;
      CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
      alter session set container = CDB$ROOT;
      
      CREATE USER c##xstrmadmin IDENTIFIED BY password DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs CONTAINER=ALL;
      GRANT CREATE SESSION, SET CONTAINER TO c##xstrmadmin CONTAINER=ALL;
      BEGIN
         DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
            grantee                 => 'c##xstrmadmin',
            privilege_type          => 'CAPTURE',
            grant_select_privileges => TRUE,
            container               => 'ALL'
         );
      END;

      Among them:

      • xstream_adm_tbs indicates the tablespace name of the XStream administrator. Set it based on site requirements.
      • /opt/oracle/oradata/ORCLCDB/xstream_adm_tbs.dbf indicates the tablespace file of the XStream administrator in the CDB. Set it based on site requirements.
      • ORCLPDB1 indicates the name of the PDB database.
      • /opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_adm_tbs.dbf indicates the tablespace file of the XStream administrator in the PDB. Set it based on site requirements.
      • c##xstrmadmin indicates the username of the XStream administrator. Set it based on site requirements.
      • password indicates the password of the XStream administrator. Set it based on site requirements.
   3. Run the following commands to create a user for connect ROMA Connect to the database and configure permissions for the user:

      CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
      alter session set container = ORCLPDB1;
      CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
      alter session set container = CDB$ROOT;
      
      CREATE USER c##xstrm IDENTIFIED BY password DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs CONTAINER=ALL;
      GRANT CREATE SESSION TO c##xstrm CONTAINER=ALL;
      GRANT SET CONTAINER TO c##xstrm CONTAINER=ALL;
      GRANT SELECT ON V_$DATABASE to c##xstrm CONTAINER=ALL;
      GRANT FLASHBACK ANY TABLE TO c##xstrm CONTAINER=ALL;
      GRANT SELECT ANY TABLE to c##xstrm CONTAINER=ALL;
      GRANT LOCK ANY TABLE TO c##xstrm CONTAINER=ALL;
      grant select_catalog_role to c##xstrm CONTAINER=ALL;

      Among them:

      • xstream_tbs indicates the tablespace name of the ROMA Connect connection user. Set it based on site requirements.
      • /opt/oracle/oradata/ORCLCDB/xstream_tbs.dbf indicates the tablespace file of the ROMA Connect connection user in the CDB. Set it based on site requirements.
      • ORCLPDB1 indicates the name of the PDB database.
      • /opt/oracle/oradata/ORCLCDB/ORCLPDB1/xstream_tbs.dbf indicates the tablespace file of the ROMA Connect connection user in the PDB. Set it based on site requirements.
      • c##xstrm indicates the name of the ROMA Connect connection user. Set it based on site requirements.
      • password indicates the password of the ROMA Connect connection user. Set it based on site requirements.
   4. Run the following command to modify log record parameters:

      alter database add supplemental log data (all) columns;
   5. Run the following command to disconnect the database:

      exit;
3. Create an XStream outbound server.
   NOTE:

   An XStream outbound server can be used only in one integration task. If the database needs to be used in multiple integration tasks, you need to create multiple XStream outbound servers.

   1. Run the following command to connect to the database instance as user c##xstrmadmin:

      sqlplus c##xstrmadmin/password@host:port/SID

      Among them:

      • c##xstrmadmin indicates the username of the XStream administrator, which is created in 2.b.
      • password indicates the password of the XStream administrator, which is configured in 2.b.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
      • SID indicates the name of the CDB database where the data to be synchronized is located. Set this parameter based on the site requirements.
   2. Run the following command to create an XStream outbound server:

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

      Among them:

      • ROMA indicates the schemas where the data table to be synchronized is located, that is, the schemas from which the CDC needs to capture data. Set this parameter based on site requirements.
      • dbzxout indicates the outbound server name, which must be unique. Set this parameter based on site requirements.
   3. Run the following command to disconnect the database:

      exit;
   4. Run the following command to connect to the database instance as user sys:

      sqlplus sys/password@host:port/SID as sysdba

      Among them:

      • password indicates the password of user sys of the database. You can obtain the password from the database administrator.
      • host indicates the IP address of the server where the database instance is located. Set this parameter based on the site requirements.
      • port indicates the port used by the database instance. Set this parameter based on the site requirements.
      • SID indicates the name of the CDB database where the data to be synchronized is located. Set this parameter based on the site requirements.
   5. Run the following command to allow user c##xstrm to connect to the XStream outbound server:

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

      Among them:

      • dbzxout is the outbound server name, which is configured in 3.b.
      • c##xstrm is the username for connecting to ROMA Connect. It is configured in 2.c.
   6. Run the following command to disconnect the database:

      exit;