Oracle Environment Preparation (XStream)

Version 

By enabling Change Data Capture (CDC) for the data source, you can synchronize real-time data from data sources and the physical delete of data tables using the Data Pipeline function of FineDataLink. Data Pipeline supports two CDC modes for Oracle: XStream and LogMiner. This document primarily introduces how to enable the XStream-based CDC for Oracle databases.

Configure the database following this document before executing a synchronization task.

Note:

Archived logs take up large disk space. Business operations may be impacted if the disk is full. Clean up expired archived logs regularly. For details, see Cleaning Oracle Archived Logs. 

• The Oracle database must have the OGG license.

• XStream Oracle operates similarly to PostgreSQL's replication slot. You cannot customize the task start point when using it to configure a pipeline task for incremental synchronization.

• Fields of the binary type, such as BFILE, LONG, LONG RAW, RAW, BLOB, CLOB, and NCLOB, cannot be collected.

• Check if the version is supported by Data Pipeline.

Data Pipeline supports data reading from Oracle databases of the following versions.

Run the following command to enter the database operation page.  

sqlplus / as sysdba 

You can also check the current database version, as shown in the following figure.

Note:

Follow the steps in this section if the source-end database has only one instance.

Enabling XStream 

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

Run the following command to connect to the database.

sqlplus / as sysdba

The enable_goldgate_replication parameter controls whether to use GoldenGate to enable XStream replication capability in Oracle. 

Run the following command to check whether it is enabled.

select VALUE from V$PARAMETER where name = 'enable_goldengate_replication'

If the return value is true, the configuration has been enabled. If it is false, run the following command to enable the configuration.

alter system set enable_goldengate_replication=true;

Check the enabling status again after configuration and confirm the return value is true, as shown in the following figure.

Enabling Archive Log 

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

Refer to Oracle Environment Preparation (LogMiner) to enable the archive log.

Creating an XStream Admin and Granting Privileges 

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

Run the following command in the connected database.

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

• XStream admin tablespace indicates the tablespace name of the XStream. Set it based on site requirements.

• XStream admin tablespace.dbf indicates the tablespace file of the XStream admin. Set it based on site requirements. The /opt/oracle/oradata/orcl/XStream admin tablespace.dbf path indicates where the DBF file is stored.

• Username indicates the username of the XStream admin. Set it based on site requirements. 

• Password indicates the password of the XStream admin. Set it based on site requirements.

• Add the container => 'ALL' row only if the Oracle version is 12c or later. Otherwise, delete this row.

Run the following statement to check whether the CDB is enabled for the database. If the return value is true or yes, the CDB is enabled. If it is false or no, the CDB is not enabled.

SELECT CDB FROM V$DATABASE;

Run the following command to configure privileges for the XStream admin.

GRANT create session, alter session, select any table, EXECUTE_CATALOG_ROLE TO Username;

Use the command to confirm the privilege is granted successfully.

select * from DBA_XSTREAM_ADMINISTRATOR;

FineDataLink Project Configuration 

Downloading and Installing Oracle Client

You can download Oracle Client.

Download the installation package of V 19.20 according to the operating system where the FineDataLink project is running. Extract the installation file and install Oracle Client.

Replacing the JAR Package

Copy the xstream and ojdbc JAR packages and paste them to the /webapps/webroot/WEB-INF/lib path in the FineDataLink installation directory.

For FineDataLink projects deployed on Windows systems:

Copy the rest of the Oracle Client files, paste them to the/jdk/jre/bin path of FineDataLink, and restart the FineDataLink project.

For FineDataLink projects deployed on Linux systems:

Copy the rest of the Oracle Client files, paste them to the/jdk/jre/bin/amd64 path of FineDataLink, and restart the FineDataLink project.

Creating an Oracle Data Connection in FineDataLink 

The Thin driver is not available as the connection to XStream Oracle relies on the OCI driver. Modify the data connection URL to the following one.

jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP address)(PORT=Port number))
(CONNECT_DATA=(SID=Database name)))

The value of Host is the IP address of the Oracle server, the value of PORT is the port number, and the value of SID is the database name.

Subsequent Operations 

Configure the pipeline task. For details, see Pipeline Task Configuration.

Enabling XStream 

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

Run the following command to connect to the database.

sqlplus / as sysdba

The enable_goldgate_replication parameter controls whether to use GoldenGate to enable XStream replication capability in Oracle. 

Run the following command to check whether it is enabled.

select VALUE from V$PARAMETER where name = 'enable_goldengate_replication'

If the return value is true, the configuration has been enabled. If it is false, run the following command to enable the configuration.

alter system set enable_goldengate_replication=true;

Check the enabling status again after configuration and confirm the return value is true, as shown in the following figure.

Enabling Archive Log 

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

Refer to Oracle Environment Preparation (LogMiner) to enable the archive log.

Creating a Tablespace in the CDB 

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

Use the following statement to query existing tablespaces.

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files;

Create a tablespace according to the returned path.

CREATE TABLESPACE XStream admin tablespace DATAFILE 
'/u01/app/oracle/oradata/fr/XStream admin tablespace.dbf' SIZE 25M 
REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

• XStream admin tablespace indicates the tablespace name of the XStream admin. Set it based on site requirements.

• XStream admin tablespace.dbf indicates the tablespace file of the XStream admin. Set it based on site requirements. The /u01/app/oracle/oradata/fr/XStream admin tablespace.dbf path indicates where the DBF file is stored.

Creating a Tablespace in the PDB 

Note:

Ensure that there is an XStream tablespace in the PDB.

Use the command in SQL*Plus to check the current PDB status, as shown in the following figure.

show pdbs;

If the PDB tablespace is not open, use the following command to open it.

ALTER PLUGGABLE DATABASE ORCLPDB1 OPEN;

Use the following command to switch to the PDB mode.

PDB name refers to the name of the PDB.

alter session set container=PDB name;

Query the tablespace first in the PDB mode.

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files;

If no tablespace exists, create one.

CREATE TABLESPACE XStream admin tablespace DATAFILE 
'/u01/app/oracle/oradata/fr/orclpdb1/XStream admin tablespace.dbf' SIZE 25M 
REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

• XStream admin tablespace indicates the tablespace name of the XStream admin. Set it based on site requirements.

• XStream admin tablespace.dbf indicates the tablespace file of the XStream admin. Set it based on site requirements. The /u01/app/oracle/oradata/fr/orclpdb1/XStream admin tablespace.dbf path indicates where the DBF file is stored.

Note:

PDB tablespaces are stored in directories one level deeper than CDB tablespaces.

Creating a User and Granting Privileges 

Return to the CDB, create a user, and grant privileges. Use the following command to switch to the CDB.

alter session set container=CDB$ROOT;

Use the following command to create a user and grant privileges.

CREATE USER "Username" IDENTIFIED BY Password DEFAULT TABLESPACE XSTREAM_ADM_TBS QUOTA UNLIMITED ON XSTREAM_ADM_TBS container = all;
GRANT CREATE SESSION,SET CONTAINER TO "Username" container = all;
 
BEGIN
   DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
      grantee                 => 'Username',
      privilege_type          => 'CAPTURE',
      grant_select_privileges => TRUE,
      container             => 'ALL'
   );
END;
GRANT  alter session, select any table, EXECUTE_CATALOG_ROLE,SELECT ANY DICTIONARY TO "Username" container = all;

• Username indicates the username of the XStream admin. Set it based on site requirements.

• Password indicates the password of the XStream admin. Set it based on site requirements.

Note:

Type a slash (/) after END when running statements in SQL*PLUS, as shown in the following figure. 

Run the following command to continue granting privileges.

grant restricted session TO "Username" container=all;

Run the command to grant table creation privileges.

GRANT CREATE TABLE TO Username CONTAINER=ALL;

Run the command to confirm the table creation privilege is granted.

SELECT PRIVILEGE FROM USER_SYS_PRIVS WHERE PRIVILEGE = 'CREATE TABLE';

Now, the account can create tables normally.

Run the following commands if the privileges on inserting tables and functions are required.

grant create sequence to "Username" CONTAINER=ALL;
grant create TRIGGER to "Username" CONTAINER=ALL;

Note:

Username indicates the username of the XStream admin. Set it based on site requirements.

Use the command to confirm the privilege is granted successfully.

select * from DBA_XSTREAM_ADMINISTRATOR;

FineDataLink Project Configuration 

For details, see the same section in the chapter "Operations on Source-End CDB" of this article.

Creating an Oracle Data Connection in FineDataLink 

Modify the database to a PDB when creating a data connection, as shown in the following figure.

Subsequent Operations 

Click Data Source Permission Detection after setting the data source and confirm that the data source passes the detection, as shown in the following figure.