4 Managing XStream

This chapter provides instructions for managing XStream.

This chapter contains these topics:

Managing XStream Out
Managing XStream In

See Also:

Managing XStream Out

This section describes managing an XStream Out configuration.

This section contains these topics:

Checking Whether a Capture Process Can Be Managed Using DBMS_XSTREAM_ADM
Managing Rules for an XStream Out Configuration
Changing the Connect User for an Outbound Server
Changing the Capture User of the Capture Process for an Outbound Server
Dropping Oracle Streams Components in an XStream Out Configuration

Note:

With XStream Out, an Oracle Streams apply process functions as an outbound server. Therefore, you can use the instructions for managing an apply process to manage an outbound server. See Oracle Streams Concepts and Administration.

See Also:

Checking Whether a Capture Process Can Be Managed Using DBMS_XSTREAM_ADM

In some XStream Out configurations, you can use the DBMS_XSTREAM_ADM package to manage the capture process that captures changes for an outbound server. However, other configurations require that you use the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package to manage the capture process.

Specifically, the DBMS_XSTREAM_ADM package can manage an outbound server's capture process in the following ways:

Add rules to and remove rules from the capture process's rule sets
Change the capture user for the capture process
Drop the capture process

An outbound server's capture process can be managed by the DBMS_XSTREAM_ADM package in either of the following cases:

The capture process was created by the CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.
The queue used by the capture process was created by the CREATE_OUTBOUND procedure.

To check whether an outbound server's capture process can be managed by the DBMS_XSTREAM_ADM package, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
```
COLUMN SERVER_NAME HEADING 'Outbound Server Name' FORMAT A30
COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30

SELECT SERVER_NAME, 
       CAPTURE_NAME
  FROM DBA_XSTREAM_OUTBOUND;
```
Your output looks similar to the following:
```
Outbound Server Name           Capture Process Name
------------------------------ ------------------------------
XOUT                           CAP$_XOUT_4
```
If the Capture Process Name for an outbound server is non-NULL, then the DBMS_XSTREAM_ADM package can manage the capture process. In this case, you can also manage the capture process using the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package. However, it is usually better to manage the capture process for an outbound server using the DBMS_XSTREAM_ADM package when it is possible.

If the Capture Process Name for an outbound server is NULL, then the DBMS_XSTREAM_ADM package cannot manage the capture process. In this case, you must manage the capture process using the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package.

See Also:

Oracle Streams Concepts and Administration for information about managing a capture process using the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package

Managing Rules for an XStream Out Configuration

This section describes managing rules for an XStream Out configuration. Rules control which database changes are streamed to the outbound server and which database changes the outbound server streams to the client application.

This section contains these topics:

Adding Rules to an XStream Out Configuration
Removing Rules from an XStream Out Configuration

Adding Rules to an XStream Out Configuration

This section describes adding schema rules, table rules, and subset rules to an XStream Out configuration.

This section contains these topics:

Adding Schema Rules and Table Rules to an XStream Out Configuration
Adding Subset Rules to an Outbound Server's Positive Rule Set

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 a Capture Process Can Be Managed Using DBMS_XSTREAM_ADM". 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 see Oracle Streams Concepts and Administration.

To add schema rules and table rules to an XStream Out configuration, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
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.

Example 4-1 Adding Rules for the hr Schema, oe.orders Table, and oe.order_items Table to the Positive Rule Set

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

Example 4-2 Adding Rules for the hr Schema to the Negative Rule Set

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 data manipulation language (DML) changes to an outbound server's positive rule set. It does not add rules for data definition language (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, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
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.
- 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.
If subset rules should also be added to the rule set of a capture process or propagation that streams row logical change records (row LCRs) to the outbound server, then see Oracle Streams Concepts and Administration for information about adding rules to a rule set.

Example 4-3 Adding Rules That Stream Changes to a Subset of Rows in a Table

The following procedure creates rules that only evaluate to TRUE for row changes where the department_id value is 40 in the hr.employees table:

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

Example 4-4 Adding Rules That Stream Changes to a Subset Rows and Columns in a Table

The following procedure creates rules that only evaluate to TRUE for row changes where the department_id value is 40 for the hr.employees table, and the procedure creates delete column declarative rule-based transformations for the salary and commission_pct columns:

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

Removing Rules from an XStream Out Configuration

This section describes removing schema rules, table rules, and subset rules from an XStream Out configuration.

This section contains these topics:

Removing Schema Rules and Table Rules From an XStream Out Configuration
Removing Subset Rules From an Outbound Server's Positive Rule Set

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 data manipulation language (DML) and data definition language (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 a Capture Process Can Be Managed Using DBMS_XSTREAM_ADM". 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 rules for same schemas and tables should be removed from the capture process's rule set as well, then see Oracle Streams Concepts and Administration for instructions.

To remove schema rules and table rules from an XStream Out configuration, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
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 4-5 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 4-6 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 data manipulation language (DML) changes. It does not remove rules for data definition language (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, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

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

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

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

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
------------------------------ ------ ------------------------------
STRMADMIN                      INSERT EMPLOYEES71
STRMADMIN                      UPDATE EMPLOYEES72
STRMADMIN                      DELETE EMPLOYEES73

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 => 'strmadmin.employees71', 
    update_rule_name => 'strmadmin.employees72', 
    delete_rule_name => 'strmadmin.employees73');
END;
/

If subset rules should also be removed from the rule set of a capture process or propagation that streams row logical change records (row LCRs) to the outbound server, then see Oracle Streams Concepts and Administration for information about removing rules.

Changing the Connect User for an Outbound Server

A client application can connect to an outbound server as the connect user. This section describes changing the connect user for an outbound server using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

To change the connect user for an outbound server, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

The Oracle Streams administrator must be granted the DBA role to change the connect user for an outbound server.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the ALTER_OUTBOUND procedure, and specify the new connect user in the connect_user parameter.

For example, to change the connect user to hr for an outbound server named xout, run the following procedure:
```
BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name  => 'xout',
    connect_user => 'hr');
END;
/
```

Changing the Capture User of the Capture Process for an Outbound Server

A capture user is the user in whose security domain a capture process captures changes from the redo log. This section describes changing the capture user for a capture process that captures changes for an outbound server using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

To change the connect user for an outbound server, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Determine whether the DBMS_XSTREAM_ADM package can manage the capture process. See "Checking Whether a Capture Process Can Be Managed Using DBMS_XSTREAM_ADM".

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

If the capture process cannot be managed using the DBMS_XSTREAM_ADM package, then follow the instructions in Oracle Streams Concepts and Administration.
Run the ALTER_OUTBOUND procedure, and specify the new capture user in the capture_user parameter.

For example, to change the capture user to hq_admin for an outbound server named xout, run the following procedure:
```
BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name  => 'xout',
    capture_user => 'hq_admin');
END;
/
```

Dropping Oracle Streams Components in an XStream Out Configuration

This section describes dropping an outbound server using the DROP_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

This procedure always drops the specified outbound server. This procedure also drops the queue used by the outbound server if both of the following conditions are met:

The queue was created by the ADD_OUTBOUND or CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.
The outbound server is the only subscriber to the queue.

If either one of these conditions is not met, then the DROP_OUTBOUND procedure only drops the outbound server. It does not drop the capture process or the queue.

This procedure also drops the capture process for the outbound server if both of the following conditions are met:

The procedure can drop the outbound server's queue.
The DBMS_XSTREAM_ADM package can manage the outbound server's capture process. See "Checking Whether a Capture Process Can Be Managed Using DBMS_XSTREAM_ADM".

If the procedure can drop the queue but cannot manage the capture process, then it drops the queue without dropping the capture process.

To drop an outbound server, complete the following steps:

Connect to the outbound server database as the Oracle Streams administrator.

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

For example, to drop an outbound server named xout, run the following procedure:
```
exec DBMS_XSTREAM_ADM.DROP_OUTBOUND('xout');
```

See Also:

Oracle Streams Concepts and Administration for information about dropping a queue or a capture process

Managing XStream In

This section describes managing an XStream inbound server configuration.

This section contains these topics:

Changing the Apply User for an Inbound Server
Dropping Oracle Streams Components in an XStream In Configuration

Note:

With XStream In, an Oracle Streams apply process functions as an inbound server. Therefore, you can use the instructions for managing an apply process to manage an inbound server. See Oracle Streams Concepts and Administration.

See Also:

Changing the Apply User for an Inbound Server

An inbound server applies messages in the security domain of its apply user, and the client application must attach to the inbound server as the apply user. This section describes changing the apply user for an inbound server using the ALTER_INBOUND procedure in the DBMS_XSTREAM_ADM package.

To change the apply user for an inbound server, complete the following steps:

Connect to the inbound server database as the Oracle Streams administrator.

The Oracle Streams administrator must be granted the DBA role to change the apply user for an inbound server.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the ALTER_INBOUND procedure, and specify the new apply user in the apply_user parameter.

For example, to change the apply user to hr for an inbound server named xin, run the following procedure:
```
BEGIN
  DBMS_XSTREAM_ADM.ALTER_INBOUND(
    server_name => 'xin',
    apply_user  => 'hr');
END;
/
```

See Also:

"Security Model" for information about the security requirements for configuring and managing XStream
Oracle Streams Concepts and Administration

Dropping Oracle Streams Components in an XStream In Configuration

This section describes dropping an inbound server using the DROP_INBOUND procedure in the DBMS_XSTREAM_ADM package.

This procedure always drops the specified inbound server. This procedure also drops the queue for the inbound server if both of the following conditions are met:

One call to the CREATE_INBOUND procedure created the queue.
The inbound server is the only subscriber to the queue.

If either one of these conditions is not met, then the DROP_INBOUND procedure only drops the inbound server. It does not drop the queue.

To drop an inbound server, complete the following steps:

Connect to the inbound server database as the Oracle Streams administrator.

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

For example, to drop an inbound server named xin, run the following procedure:
```
exec DBMS_XSTREAM_ADM.DROP_INBOUND('xin');
```