Search docs.molo17.com
    A newer version of this documentation is available.
    View Latest (v2.3)

    The definitive guide to setup Xstream into Oracle database

    Prerequisites

    • Oracle database version 11.2g and above

    Setting up for Xstream

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

    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 :-)