Openflow Connector for Oracle: Configure Oracle database¶

Enable XStream and Supplemental Logging¶

Enable XStream replication and supplemental logging to capture and stream change data. Supplemental logging captures necessary redo log information for logical replication.

ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
SELECT LOG_MODE FROM V$DATABASE;

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Create XStream Administrator User¶

An XStream administrator user is required to manage XStream components, including the creation and alteration of outbound servers. You can either create a dedicated user for this purpose or use an existing user, provided that the necessary XStream administration privileges are granted (see the next section).

The following example details the setup of a dedicated XStream administrator user in the root container of a CDB.

Connect as SYSDBA or a user with appropriate privileges and execute the following commands:

-- Switch to the root container.
ALTER SESSION SET CONTAINER = CDB$ROOT;

--  Create a tablespace for the XStream administrator user.
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/path/to/your/cdb/xstream_adm_tbs.dbf'
   SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Switch to the Pluggable Database (PDB) and create a tablespace there.
ALTER SESSION SET CONTAINER = YOUR_PDB_NAME;

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/path/to/your/pdb/xstream_adm_tbs.dbf'
   SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Switch back to the root container to create the common user.
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Create the XStream administrator user.
-- Note  'c##' prefix indicates a common user in a CDB environment, and CONTAINER=ALL grants privileges across all containers.
-- Replace "YOUR_XSTREAM_ADMIN_PASSWORD" with a strong, secure password.

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

Grant XStream Administrator Privileges¶

Grant the necessary privileges to the XStream administrator user based on your Oracle Database version.

• For Oracle Database 19c and 21c

  1. Connect as SYSDBA or a user with appropriate privileges.

  2. Grant necessary system privileges to the XStream administrator by running the following command:

     GRANT CREATE SESSION, SET CONTAINER, EXECUTE ANY PROCEDURE, LOGMINING TO c##xstreamadmin CONTAINER=ALL;
     
     -- Grant XStream administration privileges using DBMS_XSTREAM_AUTH.
     -- This procedure grants the necessary permissions to manage XStream capture processes across all containers.
     
     
     BEGIN
       DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
         grantee                 => 'c##xstreamadmin',
         privilege_type          => 'CAPTURE',
         grant_select_privileges => TRUE,
         container               => 'ALL');
     END;
     /
     

     Copy

• For Oracle Database 23c

  1. Connect as SYSDBA or a user with appropriate privileges.

  2. Grant necessary system privileges and XStream roles for Oracle Database 23c by running the following command:

     GRANT CREATE SESSION, SET CONTAINER, EXECUTE ANY PROCEDURE, LOGMINING, XSTREAM_CAPTURE
       TO c##xstreamadmin CONTAINER=ALL;
     

     Copy

Configure XStream server connect user¶

The Snowflake Openflow Connector utilizes a dedicated connect user to establish a connection to the XStream Outbound Server and receive change data. This user requires specific privileges to facilitate replication:

• Read from XStream Outbound Server: The user must be able to access the change data stream from the configured XStream Outbound Server.

• Select from Data Dictionary Views: The connect user needs SELECT access to various data dictionary views. This can be achieved by granting SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY. If granting SELECT ANY DICTIONARY is not desired due to company policy, the user specifically needs SELECT access to the following views:

  • ALL_USERS

  • ALL_TABLES

  • ALL_TAB_COLS

  • ALL_CONS_COLUMNS

  • ALL_CONSTRAINTS

  • V$DATABASE

• Select from Source Tables: The user must have SELECT privileges on all tables that are intended for replication.

Below is an example of how to set up such a user in the root container of the CDB. The example assumes that the database also has a PDB containing tables to be replicated.

-- Connect as SYSDBA or a user with appropriate privileges
-- Switch to the root container.

ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Create the connect user.
-- Replace "YOUR_CAPTURE_USER_PASSWORD" with a strong, secure password.
CREATE USER c##connectuser IDENTIFIED BY "YOUR_CAPTURE_USER_PASSWORD"
    CONTAINER=ALL;

-- Grant necessary privileges to the connect user.
-- You can choose to grant access to specific tables
-- instead of SELECT ANY TABLE for more granular control,
-- for example, GRANT SELECT ON schema.table TO c##connectuser;
GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO c##connectuser CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##connectuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##connectuser CONTAINER=ALL;

Create XStream Outbound Server¶

The XStream Outbound Server captures changes from redo logs for consumption by the Openflow Connector. Define which schemas or tables to replicate. For more information see DBMS_XSTREAM_ADM.CREATE_OUTBOUND Documentation.

Important considerations for replication scope:

• If a table is included in the XStream Outbound filtering rules command, it will not be replicated.

• A table or schema included here must also be defined in the connector parameters for it to be replicated. You can include an entire schema in the server filtering rules and later, in the connector parameters, specify only certain tables within that schema for replication.

Below are three examples of how to set up the XStream Outbound Server based on different replication needs. In practice, when setting up your XStream outbound server on your production environment, you should be selective about what changes you capture. Capturing everything can have serious consequences for your database’s performance and resource usage. To avoid a significant hit to your CPU and network, and to prevent your queues from being filled with irrelevant data, it’s essential to use a granular approach. The best way to do this is with the DBMS_XSTREAM_ADM.ADD_TABLE_RULES procedure, which lets you specifically choose only the tables you need.

More information on how to configure XStream Outbound Server can be found in the Oracle documentation: Configuring XStream Out.

Example 1: Capture all tables from all schemas in the root container and all PDBs

-- Connect as a user with XStream admin privileges to the root container.
-- Ensure serveroutput is enabled to see messages from the PL/SQL block.
SET SERVEROUTPUT ON;

DECLARE
    tables  DBMS_UTILITY.UNCL_ARRAY;
    schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
   -- To replicate all tables in all schemas across all containers, set both to NULL.
   tables(1) := NULL;
   schemas(1) := NULL;
   DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
       server_name => 'XOUT1',
       table_names => tables,
       schema_names => schemas
   );
   DBMS_OUTPUT.PUT_LINE('XStream Outbound Server created.');
   EXCEPTION
   WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error creating XStream Outbound Server: ' || SQLERRM);
       RAISE;
END;
/

Example 2: Capture all tables from a single schema in a Pluggable Database (PDB)

-- Connect as a user with XStream admin privileges to the root container.
-- Ensure serveroutput is enabled to see messages from the PL/SQL block.
SET SERVEROUTPUT ON;

DECLARE
    tables  DBMS_UTILITY.UNCL_ARRAY;
    schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
    -- To replicate all tables in a schemas in the single PDB, set source_container_name.
    tables(1) := NULL;
    schemas(1) := 'schema_name';
    DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
        server_name => 'XOUT1',
        table_names => tables,
        schema_names => schemas,
        source_container_name => 'YOUR_PDB_NAME'
    );
    DBMS_OUTPUT.PUT_LINE('XStream Outbound Server created.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error creating XStream Outbound Server: ' || SQLERRM);
      RAISE;
END;
/

Example 3: Capture all tables from a single schema in the root container

-- Connect as a user with XStream admin privileges to the root container.
-- Ensure serveroutput is enabled to see messages from the PL/SQL block.
SET SERVEROUTPUT ON;

DECLARE
    tables  DBMS_UTILITY.UNCL_ARRAY;
   schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
    -- To replicate all tables in all schemas in the root container
   -- do not set the set source_container_name
   tables(1) := NULL;
   schemas(1) :='your_schema';
   DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
      server_name => 'XOUT1',
      table_names => tables,
      schema_names => schemas
   );
   DBMS_OUTPUT.PUT_LINE('XStream Outbound Server created.');
   EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error creating XStream Outbound Server: ' || SQLERRM);
        RAISE;
END;
/

Set up the XStream Outbound Server Connect User¶

Set the connect user on the XStream Outbound Server. This ensures that the previously created connect user is associated with the XStream Outbound Server (XOUT1), allowing it to receive change data.

BEGIN
    DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
        server_name  => 'XOUT1',
        connect_user => 'c##connectuser');
   END;
/

Set up the XStream Outbound Server Capture User¶

If you configured a separate capture user, configure the XStream Outbound Server to use it. This ensures that the dedicated capture user is associated with the XStream Outbound Server (XOUT1), allowing it to capture change data.

If you want the data to be captured by the same user that created the server (the administrator), skip this step.

BEGIN
    DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
        server_name  => 'XOUT1',
      capture_user => 'yourcaptureuser');
END;
/