5 Managing XStream Out

Changing the Start SCN of an Outbound Server's Capture Process

This section describes changing the start SCN of the capture process for an outbound server.

To change the start SCN for a capture process: 

1. Determine whether the DBMS_XSTREAM_ADM package can manage the capture process. See "Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process".

   Based on the check, follow the appropriate instructions:

   • If the capture process can be managed using the DBMS_XSTREAM_ADM package, then proceed to Step 2.

   • If the capture process cannot be managed using the DBMS_XSTREAM_ADM package, then follow the instructions in Oracle Streams Concepts and Administration.

2. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

3. Check the first SCN of the capture process:

   COLUMN CAPTURE_PROCESS HEADING 'Capture Process Name' FORMAT A30
   COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999999999
    
   SELECT CAPTURE_NAME, FIRST_SCN FROM ALL_CAPTURE;
   
   CAPTURE_NAME                         First SCN
   ------------------------------ ---------------
   CAP$_XOUT_1                             604426
   

   When you reset the start SCN, the specified start SCN must be equal to or greater than the first SCN for the capture process.

4. Run the ALTER_OUTBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • start_scn - Specify the SCN from which the capture process begins to capture changes.

   If the capture process is enabled, then the ALTER_OUTBOUND procedure automatically stops and restarts the capture process when the start_scn parameter is non-NULL.

   If the capture process is disabled, then the ALTER_OUTBOUND procedure automatically starts the capture process when the start_scn parameter is non-NULL.

   Example 5-10 Setting the Start SCN of the Capture Process for an Outbound Server

   Run the following procedure to set the start SCN to 650000 for the capture process used by the xout outbound server:

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name => 'xout',
       start_scn    => 650000);
   END;
   /
   

Changing the Start Time of an Outbound Server's Capture Process

This section describes changing the start time of the capture process for an outbound server.

To change the start time for a capture process: 

1. Determine whether the DBMS_XSTREAM_ADM package can manage the capture process. See "Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process".

   Based on the check, follow the appropriate instructions:

   • If the capture process can be managed using the DBMS_XSTREAM_ADM package, then proceed to Step 2.

   • If the capture process cannot be managed using the DBMS_XSTREAM_ADM package, then follow the instructions in Oracle Streams Concepts and Administration.

2. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

3. Check the time that corresponds with the first SCN of the capture process:

   COLUMN CAPTURE_PROCESS HEADING 'Capture Process Name' FORMAT A30
   COLUMN FIRST_SCN HEADING 'First SCN' FORMAT A40
    
   SELECT CAPTURE_NAME, SCN_TO_TIMESTAMP(FIRST_SCN) FIRST_SCN FROM ALL_CAPTURE;
   
   CAPTURE_NAME                   First SCN
   ------------------------------ ----------------------------------------
   CAP$_XOUT_1                    05-MAY-10 08.11.17.000000000 AM
   

   When you reset the start time, the specified start time must be greater than or equal to the time that corresponds with the first SCN for the capture process.

4. Run the ALTER_OUTBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • start_time - Specify the time from which the capture process begins to capture changes.

   If the capture process is enabled, then the ALTER_OUTBOUND procedure automatically stops and restarts the capture process when the start_time parameter is non-NULL.

   If the capture process is disabled, then the ALTER_OUTBOUND procedure automatically starts the capture process when the start_time parameter is non-NULL.

   The following examples set the start_time parameter for the capture process that captures changes for an outbound server named xout.

   Example 5-11 Set the Start Time to a Specific Time

   Run the following procedure to set the start time to 05-MAY-10 11.11.17 AM for the capture process used by the xout outbound server:

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name => 'xout',
       start_time  => '05-MAY-10 11.11.17 AM');
   END;
   /
   

   Example 5-12 Set the Start Time Using the NUMTODSINTERVAL SQL Function

   Run the following procedure to set the start time to four hours earlier than the current time for the capture process used by the xout outbound server:

   DECLARE
     ts  TIMESTAMP;
   BEGIN
     ts := SYSTIMESTAMP - NUMTODSINTERVAL(4, 'HOUR');
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name => 'xout',
       start_time  => ts);
   END;
   /
   

Adding Schema Rules and Table Rules to an XStream Out Configuration

This section describes adding schema rules and table rules to an XStream Out configuration using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package. The ALTER_OUTBOUND procedure adds rules for both data manipulation language (DML) and data definition language (DDL) changes.

When you follow the instructions in this section, the ALTER_OUTBOUND procedure always adds rules for the specified schemas and tables to one of the outbound server's rule sets. If the DBMS_XSTREAM_ADM package can manage the outbound server's capture process, then the ALTER_OUTBOUND procedure also adds rules for the specified schemas and tables to one of the rule sets used by this capture process.

To determine whether the DBMS_XSTREAM_ADM package can manage the outbound server's capture process, see "Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process". If the DBMS_XSTREAM_ADM package cannot manage the outbound server's capture process, then the ALTER_OUTBOUND procedure adds rules to the outbound server's rule set only. In this case, if rules for same schemas and tables should be added to the capture process's rule set as well, then use the ADD_*_RULES procedures in the DBMS_XSTREAM_ADM package to add them.

In addition, if the capture process is running on a different database than the outbound server, then add schema and table rules to the propagation that sends logical change records (LCRs) to the outbound server's database. Use the ADD_*_PROPAGATION_RULES procedures in the DBMS_XSTREAM_ADM package to add them.

To add schema rules and table rules to an XStream Out configuration: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the ALTER_OUTBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • table_names - Specify the tables for which to add rules, or specify NULL to add no table rules.

   • schema_name - Specify the schemas for which to add rules, or specify NULL to add no schema rules.

   • add - Specify TRUE so that the rules are added. (Rules are removed if you specify FALSE.)

   • inclusion_rule - Specify TRUE to add rules to the positive rule set of the outbound server, or specify FALSE to add rules to the negative rule set of the outbound server. If the DBMS_XSTREAM_ADM package can manage the outbound server's capture process, then rules are also added to this capture process's rule set.

The following examples add rules to the configuration of an outbound server named xout.

BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name    => 'xout',
    table_names    => 'oe.orders, oe.order_items',
    schema_names   => 'hr',
    add            => TRUE,
    inclusion_rule => TRUE);
END;
/

BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name    => 'xout',
    table_names    => NULL,
    schema_names   => 'hr',
    add            => TRUE,
    inclusion_rule => FALSE);
END;
/

Adding Subset Rules to an Outbound Server's Positive Rule Set

This section describes adding subset rules to an outbound server's positive rule set using the ADD_SUBSET_OUTBOUND_RULES procedure in the DBMS_XSTREAM_ADM package. The ADD_SUBSET_OUTBOUND_RULES procedure only adds rules for DML changes to an outbound server's positive rule set. It does not add rules for DDL changes, and it does not add rules to a capture process's rule set.

To add subset rules to an outbound server's positive rule set: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the ADD_SUBSET_OUTBOUND_RULES procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • table_name - Specify the table for which you want to capture and stream a subset of data.

   • condition - Specify the subset condition, which is similar to the WHERE clause in a SQL statement, to stream changes to a subset of rows in the table.

   • column_list - Specify the subset of columns to keep or discard, or specify NULL to keep all of the columns.

   • keep - Specify TRUE to keep the columns listed in the column_list parameter, or specify FALSE to discard the columns in the column_list parameter.

   When column_list is non-NULL and keep is set to TRUE, the procedure creates a keep columns declarative rule-based transformation for the columns listed in column_list.

   When column_list is non-NULL and keep is set to FALSE, the procedure creates a delete column declarative rule-based transformation for each column listed in column_list.

3. If subset rules should also be added to the rule set of a capture process or propagation that streams row LCRs to the outbound server, then use the ADD_*_RULES procedures in the DBMS_XSTREAM_ADM package to add them.

DECLARE
  cols DBMS_UTILITY.LNAME_ARRAY;
BEGIN
    cols(1) := 'employee_id';
    cols(2) := 'first_name';
    cols(3) := 'last_name';
    cols(4) := 'email';
    cols(5) := 'phone_number';
    cols(6) := 'hire_date';
    cols(7) := 'job_id';
    cols(8) := 'salary';
    cols(9) := 'commission_pct';
    cols(10) := 'manager_id';
    cols(11) := 'department_id';
  DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
    server_name => 'xout',
    table_name  => 'hr.employees',
    condition   => 'department_id=40',
    column_list => cols); 
END;
/

BEGIN
  DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
    server_name => 'xout',
    table_name  => 'hr.employees',
    condition   => 'department_id=40',
    column_list => 'salary,commission_pct', 
    keep        => FALSE);
END;
/

Adding Rules With Custom Conditions to XStream Out Components

Some of the procedures that create rules in the DBMS_XSTREAM_ADM package include an and_condition parameter. This parameter enables you to add conditions to system-created rules. The condition specified by the and_condition parameter is appended to the system-created rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr.

To add a rule with a custom condition to an XStream Out component: 

1. Connect to the database running the XStream Out component as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run an ADD_*_RULES procedure and specify the custom condition in the and_condition parameter.

   See "System-Created Rules and XStream" for information about these procedures.

BEGIN 
  DBMS_XSTREAM_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.departments',
    streams_type        =>  'capture',
    streams_name        =>  'xout_capture',
    queue_name          =>  'xstream_queue',
    include_dml         =>  TRUE,
    include_ddl         =>  TRUE,
    include_tagged_lcr  =>  TRUE,
    source_database     =>  'dbs1.example.com',
    inclusion_rule      =>  TRUE,
    and_condition       =>  ':lcr.get_tag() = HEXTORAW(''02'')');
END;
/

If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then ensure that this procedure only generates the appropriate rule. Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify TRUE for the include_dml parameter and FALSE for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify FALSE for the include_dml parameter and TRUE for the include_ddl parameter.

For example, the GET_OBJECT_TYPE member function only applies to DDL LCRs. Therefore, if you use this member function in an and_condition, then specify FALSE for the include_dml parameter and TRUE for the include_ddl parameter.

Removing Schema Rules and Table Rules From an XStream Out Configuration

This section describes removing schema rules and table rules from an XStream Out configuration using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package. The ALTER_OUTBOUND procedure removes rules for both DML and DDL changes.

When you follow the instructions in this section, the ALTER_OUTBOUND procedure always removes rules for the specified schemas and tables from one of the outbound server's rule sets. If the DBMS_XSTREAM_ADM package can manage the outbound server's capture process, then the ALTER_OUTBOUND procedure also removes rules for the specified schemas and tables from one of the rule sets used by this capture process.

To determine whether the DBMS_XSTREAM_ADM package can manage the outbound server's capture process, see "Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process". If the DBMS_XSTREAM_ADM package cannot manage the outbound server's capture process, then the ALTER_OUTBOUND procedure removes rules from the outbound server's rule set only. In this case, if you must remove the rules for same schemas and tables from the capture process's rule set as well, then see "Removing Rules Using the REMOVE_RULE Procedure" for instructions.

In addition, if the capture process is running on a different database than the outbound server, then remove the schema and table rules from the propagation that sends LCRs to the outbound server's database. See "Removing Rules Using the REMOVE_RULE Procedure" for instructions.

To remove schema rules and table rules from an XStream Out configuration: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the ALTER_OUTBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • table_names - Specify the tables for which to remove rules, or specify NULL to remove no table rules.

   • schema_name - Specify the schemas for which to remove rules, or specify NULL to remove no schema rules.

   • add - Specify FALSE so that the rules are removed. (Rules are added if you specify TRUE.)

   • inclusion_rule - Specify TRUE to remove rules from the positive rule set of the outbound server, or specify FALSE to remove rules from the negative rule set of the outbound server. If the DBMS_XSTREAM_ADM package can manage the outbound server's capture process, then rules are also removed from this capture process's rule set.

   The following examples remove rules from the configuration of an outbound server named xout.

   Example 5-19 Removing Rules for the hr Schema, oe.orders Table, and oe.order_items Table from the Positive Rule Set

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name    => 'xout',
       table_names    => 'oe.orders, oe.order_items',
       schema_names   => 'hr',
       add            => FALSE,
       inclusion_rule => TRUE);
   END;
   /
   

   Example 5-20 Removing Rules for the hr Schema from the Negative Rule Set

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name    => 'xout',
       table_names    => NULL,
       schema_names   => 'hr',
       add            => FALSE,
       inclusion_rule => FALSE);
   END;
   /
   

Removing Subset Rules from an Outbound Server's Positive Rule Set

This section describes removing subset rules from an outbound server's positive rule set using the REMOVE_SUBSET_OUTBOUND_RULES procedure in the DBMS_XSTREAM_ADM package. The REMOVE_SUBSET_OUTBOUND_RULES procedure only removes rules for DML changes. It does not remove rules for DDL changes, and it does not remove rules from a capture process's rule set.

To remove subset rules from an outbound server's positive rule set: 

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Determine the rule names for the subset rules by running the following query:

   SELECT RULE_OWNER, SUBSETTING_OPERATION, RULE_NAME 
      FROM ALL_XSTREAM_RULES 
      WHERE SUBSETTING_OPERATION IS NOT NULL;
   

3. Run the REMOVE_SUBSET_OUTBOUND_RULES procedure, and specify the rules to remove from the list of rules displayed in Step 2.

   For example, assume that Step 2 returned the following results:

   RULE_OWNER                     SUBSET RULE_NAME
   ------------------------------ ------ ------------------------------
   XSTRMADMIN                     INSERT EMPLOYEES71
   XSTRMADMIN                     UPDATE EMPLOYEES72
   XSTRMADMIN                     DELETE EMPLOYEES73
   

   Example 5-21 Removing Subset Rules From an Outbound Server's Positive Rule Set

   To remove these rules from the positive rule set of the xout outbound server, run the following procedure:

   BEGIN
     DBMS_XSTREAM_ADM.REMOVE_SUBSET_OUTBOUND_RULES(
       server_name      => 'xout',
       insert_rule_name => 'xstrmadmin.employees71', 
       update_rule_name => 'xstrmadmin.employees72', 
       delete_rule_name => 'xstrmadmin.employees73');
   END;
   /
   

4. If subset rules should also be removed from the rule set of a capture process and propagation that streams row LCRs to the outbound server, then see "Removing Rules Using the REMOVE_RULE Procedure" for information about removing rules.

Removing Rules Using the REMOVE_RULE Procedure

This section describes removing a single rule from an XStream Out component's rule set or all rules from the rule set using the REMOVE_RULE procedure in the DBMS_XSTREAM_ADM package. The XStream Out component can be a capture process, propagation, or outbound server.

The REMOVE_RULE procedure only can remove rules for both DML and DDL changes, and it can remove rules from either the component's positive rule set or negative rule set.

To remove a single rule or all rules from an outbound server's rule set: 

1. Connect to the database running the XStream Out component as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Determine the rule name and XStream component name.

   See "Monitoring XStream Rules" for a query that displays this information.

3. Run the REMOVE_RULE procedure.

   Example 5-22 Removing a Rule From an Outbound Server's Rule Set

   This example removes a rule named orders12 from positive rule set of the xout outbound server.

   BEGIN
     DBMS_XSTREAM_ADM.REMOVE_RULE(
      rule_name      => 'orders12',
      streams_type   => 'APPLY',
      streams_name   => 'xout',
      inclusion_rule => TRUE);
   /
   

The inclusion_rule parameter is set to TRUE to indicate the positive rule set.

BEGIN
  DBMS_XSTREAM_ADM.REMOVE_RULE(
   rule_name      => NULL,
   streams_type   => 'APPLY',
   streams_name   => 'xout',
   inclusion_rule => FALSE);
/

The rule_name parameter is set to NULL to specify that all of the rules are removed from the rule set, and the inclusion_rule parameter is set to FALSE to indicate the negative rule set.

Adding a Declarative Rule-Based Transformation That Renames a Table

Use the RENAME_TABLE procedure in the DBMS_XSTREAM_ADM package to add a declarative rule-based transformation that renames a table in a row LCR. The example in this section adds a declarative rule-based transformation to the jobs12 rule in the xstrmadmin schema.

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following procedure:

   BEGIN 
     DBMS_XSTREAM_ADM.RENAME_TABLE(
       rule_name       => 'xstrmadmin.jobs12',
       from_table_name => 'hr.jobs',
       to_table_name   => 'hr.assignments', 
       step_number     => 0,
       operation       => 'ADD');
   END;
   /
   

The declarative rule-based transformation added by this procedure renames the table hr.jobs to hr.assignments in a row LCR when the rule jobs12 evaluates to TRUE for the row LCR. If more than one declarative rule-based transformation is specified for the jobs12 rule, then this transformation follows default transformation ordering because the step_number parameter is set to 0 (zero). In addition, the operation parameter is set to ADD to indicate that the transformation is being added to the rule, not removed from it.

The RENAME_TABLE procedure can also add a transformation that renames the schema in addition to the table. For example, in the previous example, to specify that the schema should be renamed to oe, specify oe.assignments for the to_table_name parameter.

Adding a Declarative Rule-Based Transformation That Adds a Column

Use the ADD_COLUMN procedure in the DBMS_XSTREAM_ADM package to add a declarative rule-based transformation that adds a column to a row in a row LCR. The example in this section adds a declarative rule-based transformation to the employees35 rule in the xstrmadmin schema.

1. Connect to the outbound server database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following procedure:

   BEGIN 
     DBMS_XSTREAM_ADM.ADD_COLUMN(
       rule_name    => 'xstrmadmin.employees35',
       table_name   => 'hr.employees',
       column_name  => 'birth_date', 
       column_value => ANYDATA.ConvertDate(NULL),
       value_type   => 'NEW',
       step_number  => 0,
       operation    => 'ADD');
   END;
   /
   

The declarative rule-based transformation added by this procedure adds a birth_date column of data type DATE to an hr.employees table row in a row LCR when the rule employees35 evaluates to TRUE for the row LCR.

Notice that the ANYDATA.ConvertDate function specifies the column type and the column value. In this example, the added column value is NULL, but a valid date can also be specified. Use the appropriate ANYDATA function for the column being added. For example, if the data type of the column being added is NUMBER, then use the ANYDATA.ConvertNumber function.

The value_type parameter is set to NEW to indicate that the column is added to the new values in a row LCR. You can also specify OLD to add the column to the old values.

If more than one declarative rule-based transformation is specified for the employees35 rule, then the transformation follows default transformation ordering because the step_number parameter is set to 0 (zero). In addition, the operation parameter is set to ADD to indicate that the transformation is being added, not removed.