Setup Xstream into Oracle database single-tenant

Prerequisites

Oracle database version 11.2.0.4 (11.2g) and above configured as single-tenant

If you’re looking for support on a multi-tenant installation please have a look here at this link.

Setting up for Xstream

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/u01/app/oracle/oradata/orcl/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Then, again as sys into your database, we’re going to create xstream admin and grant permissions, execute:

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;

again, this time we’re going to create xstream user and grant permissions, execute:

CREATE TABLESPACE xstream_tbs DATAFILE '/u01/app/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;

Add support to CDC to your specific tables

Now it’s the turn to add logging capabilities to the tables involved in your replication process. We’re going to alter all tables involved and grant permissions to xstream user, again as sys:

-- replace me with your tables list
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;

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

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

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.

To proceed with the following query statements please make sure you connect as xstream admin user you’ve created earlier.

As a xstream admin user run the following query making the changes accordingly to your needs (tables and schemas per your requirements):

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN

  -- replace me with your tables list
  tables(1)  := 'SCHEMA1.MYFIRSTTABLE';
  tables(2)  := 'SCHEMA1.MYSECONDTABLE';
  tables(3)  := 'SCHEMA2.MYTHIRDTABLE';
  tables(4)  := 'SCHEMA2.MYFOURTHTABLE';

  -- replace me with your schemas list
  schemas(1) := 'SCHEMA1';
  schemas(2) := 'SCHEMA1';
  schemas(3) := 'SCHEMA2';
  schemas(4) := 'SCHEMA2';
  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;

That’s it! Happy CDC :-)

Status checks

If you need to check if the setup has been done properly, Oracle give us a snippet to see if Xstream is up & running. To perform the check you need to run this query:

SELECT CAPTURE_NAME, STATE FROM V_$XSTREAM_CAPTURE;

this query will output the list of outbound server(s) running with their current queried status.

To check if there is any relevant alert instead, you can run this query:

SELECT * FROM DBA_OUTSTANDING_ALERTS;

Troubleshooting

Here following the list of common errors.

ORA-01031: Insufficient privileges: means that you’ve executed the DBMS_XSTREAM_ADM.CREATE_OUTBOUND steps with the wrong user (maybe when still logged in as sys as sysdba). Delete it at redo the steps following the instructions above;
ORA-02248: Invalid option for ALTER SESSION: means that you are explicy set a value in either a cdbName or pdbName inside oracle object in your config.json. Since you’re running an Oracle single-tenant instance you have to remove them from the config.