Oracle XStream Multi-Tenant Setup

Prerequisites

Before setting up Xstream with Gluesync in a multi-tenant environment, ensure you have:

Oracle Database Enterprise Edition (12c or higher) with multi-tenant architecture
Container Database (CDB) and Pluggable Database (PDB) properly configured
A user with Xstream admin permissions in both CDB and PDB
A Xstream connect user with appropriate permissions
Database running in ARCHIVELOG mode

Initial Database Setup

alter system set db_recovery_file_dest_size = 5G;
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
alter system set enable_goldengate_replication=true;
shutdown immediate
startup mount
alter database archivelog;
alter database open;

Verify archive log mode:

archive log list

Create XStream tablespace in CDB:

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/CDB/xstream_adm_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

User Setup

Create XStream Admin User in CDB

CREATE USER c##xstreamadmin IDENTIFIED BY xsa
DEFAULT TABLESPACE xstream_adm_tbs
QUOTA UNLIMITED ON xstream_adm_tbs
CONTAINER=ALL;

GRANT CREATE SESSION TO c##xstreamadmin CONTAINER=ALL;

BEGIN
    DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
      grantee                 => 'c##xstreamadmin',
      privilege_type          => 'CAPTURE',
      grant_select_privileges => TRUE,
      container              => 'ALL'
    );
END;

Create XStream User in CDB

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

GRANT CREATE SESSION TO c##xstream CONTAINER=ALL;
GRANT CREATE TABLE TO c##xstream CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to c##xstream CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO c##xstream CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##xstream CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##xstream CONTAINER=ALL;

Configure Tables for CDC

Connect to the specific PDB and execute as sys as sysdba:

ALTER SESSION SET CONTAINER = your_pdb_name;

-- Replace with your actual tables
ALTER TABLE SCHEMA1.MYFIRSTTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA1.MYFIRSTTABLE TO c##xstream;

ALTER TABLE SCHEMA1.MYSECONDTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA1.MYSECONDTABLE TO c##xstream;

Configure XStream Outbound Server

Connect as c##xstreamadmin in the CDB and execute:

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  -- Replace with your tables (include PDB name)
  tables(1)  := 'PDB1.SCHEMA1.MYFIRSTTABLE';
  tables(2)  := 'PDB1.SCHEMA1.MYSECONDTABLE';

  -- Use NULL to avoid capturing all tables in schema
  schemas(1) := NULL;

  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
    server_name     => 'gsxout',
    capture_name    => 'gluesync_capture',
    table_names     => tables,
    schema_names    => schemas,
    source_database => 'PDB1'
  );
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##xstream'
  );
END;

Verification

Check Xstream setup status:

SELECT SERVER_NAME, CAPTURE_NAME, QUEUE_OWNER, QUEUE_NAME, SOURCE_DATABASE
FROM ALL_XSTREAM_OUTBOUND;

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

This feature is automatically enabled and available for use in this agent.

Important Notes

Container database (CDB) users must be prefixed with c##
Xstream capture process must be configured in the CDB
Tables must be referenced with their full path including PDB name
An Xstream Outbound server can only be used by one Gluesync instance
For load balancing, configure different Xstream Outbound servers for different Gluesync instances

For Gluesync-specific configuration, see Gluesync Configuration Guide.

For troubleshooting steps, see Xstream Troubleshooting Guide.