The definitive guide to setup Xstream into Oracle database

Prerequisites

Oracle database version 11.2g and above

Setting up for Xstream

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/u02/app/oracle/oradata/ORCLCDB/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

Once done, login as sys as sysdba into your pluggable database. Then execute the following commands:

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/u02/app/oracle/oradata/ORCLCDB/orclpdb1/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

Then, again as sys as sysdba into your container database, execute:

CREATE USER c##xstrmadmin IDENTIFIED BY xsa 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;

CREATE TABLESPACE xstream_tbs DATAFILE '/u02/app/oracle/oradata/ORCLCDB/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

As sys as sysdba run that into your pluggable database:

CREATE TABLESPACE xstream_tbs DATAFILE '/u02/app/oracle/oradata/ORCLCDB/orclpdb1/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

As sys as sysdba run into your container database:

CREATE USER c##xstrm IDENTIFIED BY xs
DEFAULT TABLESPACE xstream_tbs
QUOTA UNLIMITED ON xstream_tbs
CONTAINER=ALL

GRANT CREATE SESSION TO c##xstrm CONTAINER=ALL

GRANT CREATE TABLE 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

Add support to CDC to your specific tables

For each table that you would like to enable Xstream to listed changes from you should perform the following steps.

Login as sys as sysdba and then run that into your pluggable database, note that you should replace $name with your table name:

ALTER TABLE $name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

GRANT SELECT, INSERT, UPDATE, DELETE ON $name TO c##xstrm

Final steps

This final step covers the setup of Xstream outbound stream. Please keep in mind that:

tables: are the list of tables that you are considering to enable the CDC into, keeping NULL as per the example means that the CDC will be enabled in all the tables present in the schema specified there after. You won’t keep that for any production workload/environment;
schemas: the schemas where the tables are belonging to;
server_name: the name of your Xstream server, for a Gluesync standard setup we are going to gsxout;
capture_name: the name of your data capture output, for a Gluesync standard setup we are going to gluesync_capture.

Before runnig the scripts you should login as Xstream user we have just created in the steps before: c##xstrmadmin and password xsa into your container database. Then execute:

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  tables(1)  := NULL;
  schemas(1) := '$gluesyncName';
  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
    server_name     => 'gsxout',
    capture_name    => 'gluesync_capture',
    table_names     => tables,
    schema_names    => schemas
  );
END;

BEGIN
  DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
    capture_name   => 'gluesync_capture',
    attribute_name => 'username'
  );
END;

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

That’s it! Happy CDC :-)