Oracle XStream Single-Tenant Setup

Prerequisites

Before setting up Xstream with Gluesync, ensure you have:

A single-tenant Oracle database (version 11.2.0.4 or higher)
A user with Xstream admin permissions
A Xstream connect user with read/write access to target tables
Appropriate database privileges for Xstream operations

Initial Database Setup

alter system set db_recovery_file_dest_size = 5G; -- put your desired value
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile; -- put your desired value
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:

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/ORCL/xstream_adm_tbs.dbf' -- put your desired value
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

User Setup

Create XStream Admin User

CREATE USER xstreamadmin IDENTIFIED BY xsa
DEFAULT TABLESPACE xstream_adm_tbs
QUOTA UNLIMITED ON xstream_adm_tbs;

GRANT CREATE SESSION TO xstreamadmin;

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

Create XStream User

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

CREATE USER xstream IDENTIFIED BY xs
DEFAULT TABLESPACE xstream_tbs
QUOTA UNLIMITED ON xstream_tbs;

GRANT CREATE SESSION TO xstream;
GRANT CREATE TABLE TO xstream;
GRANT SELECT ON V_$DATABASE to xstream;
GRANT FLASHBACK ANY TABLE TO xstream;
GRANT SELECT_CATALOG_ROLE TO xstream;
GRANT EXECUTE_CATALOG_ROLE TO xstream;

Configure Tables for CDC

Execute the following as sys as sysdba:

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

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

Configure XStream Outbound Server

Connect as xstreamadmin and execute:

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  -- Replace with your tables
  tables(1)  := 'SCHEMA1.MYFIRSTTABLE';
  tables(2)  := '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
  );
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 => 'xstream'
  );
END;

Verification

Check Xstream setup status:

SELECT SERVER_NAME, CAPTURE_NAME, QUEUE_OWNER, QUEUE_NAME
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

An Xstream Outbound server can only be used by one Gluesync instance
For load balancing, configure different Xstream Outbound servers for different Gluesync instances
Avoid enabling CDC on all tables in a schema unless necessary
Monitor system resources to prevent capture process from pausing due to flow control

For Gluesync-specific configuration, see Gluesync Configuration Guide.

For troubleshooting steps, see Xstream Troubleshooting Guide.