Prerequisites

Before configuring XStream Out, ensure that all containers in the CDB are in open read/write mode during XStream Out configuration.

Assumptions

This section makes the following assumptions:

• The capture process will be a local capture process, and it will run on the same database as the outbound server.

• The name of the outbound server is xout.

• Data manipulation language (DML) and data definition language (DDL) changes made to the oe.orders and oe.order_items tables in PDB pdb1.example.com are sent to the outbound server.

• DML and DDL changes made to the hr schema in the PDB pdb1.example.com are sent to the outbound server.

Figure 25-3 provides an overview of this XStream Out configuration.

Figure 25-3 Sample XStream Out Configuration Created Using CREATE_OUTBOUND for a PDB

Description of "Figure 25-3 Sample XStream Out Configuration Created Using CREATE_OUTBOUND for a PDB"

To create an outbound server using the CREATE_OUTBOUND procedure:

1. In SQL*Plus, connect to the root in the CDB (not to the PDB pdb1.example.com) as the XStream administrator.

2. Create the outbound server and other XStream components.

   1. Ensure that all containers in the source CDB are in open read/write mode.

   2. Run the CREATE_OUTBOUND procedure.

      Given the assumptions for this example, run the following CREATE_OUTBOUND procedure:

      DECLARE
        tables  DBMS_UTILITY.UNCL_ARRAY;
        schemas DBMS_UTILITY.UNCL_ARRAY;
      BEGIN
          tables(1)  := 'oe.orders';
          tables(2)  := 'oe.order_items';
          schemas(1) := 'hr';
        DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
          server_name     =>  'xout',
          source_database =>  'pdb1.example.com',
          table_names     =>  tables,
          schema_names    =>  schemas);
      END;
      /
      

      Note:

      To capture changes in all containers in a CDB, including the CDB root, all PDBs, all application roots, and all application PDBs, and send those changes to the XStream client application, you can omit the source_database parameter when you run the CREATE_OUTBOUND procedure.

   3. After the CREATE_OUTBOUND procedure completes successfully, optionally change the open mode of one or more containers if necessary.

   Running the procedure in Step b performs the following actions:

   • Configures supplemental logging for the oe.orders and oe.order_items tables and for all tables in the hr schema in the pdb1.example.com PDB.

   • Creates a queue with a system-generated name that is used by the capture process and the outbound server.

   • Creates and starts a capture process with a system-generated name with rule sets that instruct it to capture DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema from the pdb1.example.com PDB.

   • Creates and starts an outbound server named xout with rule sets that instruct it to send DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema to the client application.

   • Sets the current user as the connect user for the outbound server. In this example, the current user is the XStream administrator. The client application must connect to the database as the connect user to interact with the outbound server.

   Note:

   The server_name value cannot exceed 30 bytes.

   Tip:

   To capture and send all database changes from the pdb1.example.com database to the outbound server, specify NULL (the default) for the table_names and schema_names parameters.

3. Create and run the client application that will connect to the outbound server in the root of the CDB and receive the LCRs.

   When you run the client application, the outbound server is started automatically.

Prerequisites

Before configuring XStream Out, the following prerequisites must be met:

• Ensure that all containers in the CDB are in open read/write mode during XStream Out configuration.

• This example uses downstream capture. Therefore, you must configure log file transfer from the source database to a downstream database.

• If you want to use real-time downstream capture, then you must also add the required standby redo logs.

Assumptions

This section makes the following assumptions:

• The name of the outbound server is xout.

• The queue used by the outbound server is c##xstrmadmin.xstream_queue.

• The source database is the PDB pdb1.example.com in the CDB data.example.com.

• The capture process runs in the CDB capture.example.com.

• The outbound server runs in the CDB capture.example.com.

• DML and DDL changes made to the oe.orders and oe.order_items tables from the PDB pdb1.example.com are sent to the outbound server.

• DML and DDL changes made to the hr schema from the PDB pdb1.example.com are sent to the outbound server.

The following figure gives an overview of this XStream Out configuration.

Figure 25-4 Sample XStream Out Configuration Using Multiple CDBs and Downstream Capture

Description of "Figure 25-4 Sample XStream Out Configuration Using Multiple CDBs and Downstream Capture "

To configure XStream Out with downstream capture in CDBs:

1. In SQL*Plus, connect to the root of the downstream capture CDB as the XStream administrator.

   In this example. the downstream capture CDB is capture.example.com.

2. Create the queue that will be used by the capture process.

   For example, run the following procedure:

   BEGIN
     DBMS_XSTREAM_ADM.SET_UP_QUEUE(
       queue_table => 'c##xstrmadmin.xstream_queue_table',
       queue_name  => 'c##xstrmadmin.xstream_queue');
   END;
   /
   

3. Optionally, create the database link from the root in the downstream capture CDB to the root in the source CDB.

   In this example, create a database link from the root in capture.example.com to the root in data.example.com. For example, if the user c##xstrmadmin is the XStream administrator on both databases, then create the following database link:

   CREATE DATABASE LINK data.example.com CONNECT TO c##xstrmadmin 
      IDENTIFIED BY password USING 'data.example.com';
   

4. Ensure that all containers in the source CDB are in open read/write mode.

5. If you did not create the database link in Step 3, then you must complete additional steps in the root of the source CDB.

   These steps are not required if you created the database link in Step 3.

   Run the BUILD procedure and ensure that required supplemental logging is specified for the database objects in the source CDB:

   1. Connect to the root in the source CDB as the XStream administrator.

   2. Run the DBMS_CAPTURE_ADM.BUILD procedure. For example:

      SET SERVEROUTPUT ON
      DECLARE
        scn  NUMBER;
      BEGIN
        DBMS_CAPTURE_ADM.BUILD(
          first_scn => scn);
        DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
      END;
      /
      First SCN Value = 409391
      

      This procedure displays the valid first SCN value for the capture process that will be created in the root in the capture.example.com CDB. Make a note of the SCN value returned because you will use it when you create the capture process in Step 6.

   3. Ensure that required supplemental logging is specified for the database objects in the source CDB.

      For this example, ensure that supplemental logging is configured for the hr schema, the oe.orders table, and the oe.order_items table in the pdb1.example.com PDB.

6. While connected to the root in the downstream capture CDB, create the capture process.

   For example, run the following procedure to create the capture process while connected as the XStream administrator to capture.example.com:

   BEGIN
     DBMS_CAPTURE_ADM.CREATE_CAPTURE(
       queue_name         => 'c##xstrmadmin.xstream_queue',
       capture_name       => 'real_time_capture',
       rule_set_name      => NULL,
       start_scn          => NULL,
       source_database    => NULL,
       use_database_link  => TRUE,
       first_scn          => NULL,
       logfile_assignment => 'implicit',
       source_root_name   => 'data.example.com',
       capture_class      => 'xstream');
   END;
   /
   

   If you did not create a database link in Step 3, then specify the SCN value returned by the DBMS_CAPTURE_ADM.BUILD procedure for the first_scn parameter.

   Do not start the capture process.

7. After the capture process is created, optionally change the open mode of one or more PDBs if necessary.

8. Run the ADD_OUTBOUND procedure.

   Given the assumption for this section, run the following ADD_OUTBOUND procedure:

   DECLARE
     tables  DBMS_UTILITY.UNCL_ARRAY;
     schemas DBMS_UTILITY.UNCL_ARRAY;
   BEGIN
       tables(1)  := 'oe.orders';
       tables(2)  := 'oe.order_items';
       schemas(1) := 'hr';
     DBMS_XSTREAM_ADM.ADD_OUTBOUND(
       server_name           =>  'xout',
       queue_name            =>  'c##xstrmadmin.xstream_queue',
       source_database       =>  'pdb1.example.com',
       table_names           =>  tables,
       schema_names          =>  schemas,
       source_root_name      => 'data.example.com',
       source_container_name => 'pdb1');
   END;
   /
   

   Running this procedure performs the following actions:

   • Creates an outbound server named xout. The outbound server has rule sets that instruct it to send DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema to the client application. The rules specify that these changes must have originated at the PDB pdb1.example.com in the CDB data.example.com. The outbound server dequeues LCRs from the queue c##xstrmadmin.xstream_queue.

   • Sets the current user as the connect_user for the outbound server. In this example, the current_user is the XStream administrator. The client application must connect to the database as the connect_user to interact with the outbound server.

   Note:

   The server_name value cannot exceed 30 bytes.

9. Create and run the client application that will connect to the outbound server and receive the LCRs.

   When you run the client application, the outbound server is started automatically at the downstream capture CDB.