Setup Xstream into Oracle database multi-tenant

Prerequisites

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

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

Setting up for Xstream

Login as sys as sysdba into your database. Then execute the following commands:

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

Then, again as sys as sysdba into your database, we’re going to create xstream admin and grant permissions, 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;

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

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

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

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 grand permissions to c##xstrm user, again as sys as sysdba:

-- 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 c##xstrm;

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

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

ALTER TABLE SCHEMA2.MYFOURTHTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA2.MYFOURTHTABLE 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.

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

As a c##xstrmadmin 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 => 'c##xstrm'
  );
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.