Openflow Connector for Oracle: Configure the Oracle database¶

Configure the retention period for archived redo logs¶

You must enable the ARCHIVELOG mode to ensure that change data is available for replication.

If you use AWS RDS for Oracle, you must also configure the retention period for archived redo logs. Determine this period based on the volume of changes in the source database and your storage capacity.

To set the retention period, for example to 24 hours, follow the procedures in the following table:

Enable XStream and supplemental logging¶

To enable and configure XStream replication to capture and stream change data, run the following commands:

1. Enable XStream replication:

ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;

ALTER SYSTEM SET STREAMS_POOL_SIZE = 2560M;

To enable supplemental logging to ensure that the redo logs capture the information required for logical replication, run the following commands:

1. Confirm that the database is in ARCHIVELOG mode as shown in the following example:

   CopyExpand

   SELECT LOG_MODE, FORCE_LOGGING FROM V$DATABASE;
   

   Show lessSee more

   Scroll to top

   Snowflake recommends forcing logging on database or table space level.

2. Set the container to the root container and add supplemental logging to the database:

   CopyExpand

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

   Show lessSee more

   Scroll to top

   Alternatively, you can enable logging only on specific tables as shown in the following example:

   CopyExpand

   ALTER TABLE schema_name.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

   Show lessSee more

   Scroll to top

Create the 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 run 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 required 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:

     CopyExpand

     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;
     /
     

     Show lessSee more

     Scroll to top

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

     CopyExpand

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

     Show lessSee more

     Scroll to top

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.

The following 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.

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 choose only the specific tables you need.

The following examples show 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.

For information on how to configure XStream Outbound Server, see 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,
       include_ddl => TRUE
   );
   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,
        include_ddl => TRUE,
        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;
/

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 run as this user. This ensures that the dedicated capture user is associated with the XStream Outbound Server (XOUT1), allowing that user to capture change data.

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

Set up XStream for single-tenant databases¶

The default architecture for Oracle 12c and later is a multi-tenant architecture with a Container Database (CDB) and one or more Pluggable Databases (PDB).

If your Oracle database uses a single-tenant architecture, note the following differences in setting up XStream:

• Do not use ALTER SESSION SET CONTAINER commands. In a single-tenant database, there is only one instance, so container switching does not apply.

• Create only one xstream_adm_tbs tablespace. Do not create a second tablespace in a PDB.

• Do not use the C## prefix on user names. For example, create xstreamadmin instead of c##xstreamadmin and connectuser instead of c##connectuser. The C## prefix is required only in multi-tenant environments.

• Do not include CONTAINER=ALL or container => 'ALL' in any commands. These clauses grant privileges across multiple containers and do not apply in a single-tenant database.

Configure SSL connections (optional)¶

The Openflow Connector for Oracle supports encrypted SSL connections to the Oracle database using the TCPS (TCP with SSL) protocol. When SSL is enabled, both the database connection and the XStream connection use encrypted communication.

To use SSL, you must:

1. Enable TCPS on the Oracle database

2. Create a client wallet

Next steps¶

Configure the connector.