Skip Headers
Oracle® Database XStream Guide
11g Release 2 (11.2)

Part Number E16545-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 Managing XStream

This chapter provides instructions for managing XStream.

This chapter contains these topics:

See Also:

About Managing XStream

This chapter describes managing an XStream Out configuration and an XStream In configuration. This chapter provides instructions for modifying the database components that are part of an XStream configuration, such as outbound severs, inbound servers, capture processes, and rules.

The main interface for managing XStream database components is PL/SQL. Specifically, use the following Oracle supplied PL/SQL packages to manage XStream:

Managing XStream Out

This section describes managing an XStream Out configuration.

This section contains these topics:

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 Database 2 Day + Data Replication and Integration Guide and Oracle Streams Concepts and Administration.

Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process

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

The DBMS_XSTREAM_ADM package can manage an outbound server's capture process 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: 

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

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

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

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 see Oracle Streams Concepts and Administration for instructions about adding 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. See Oracle Streams Concepts and Administration for instructions.

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.

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

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

Example 4-4 Adding Rules That Stream Changes to a Subset of 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. The procedure also 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;
/

See Also:

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

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 Oracle Streams Concepts and Administration 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 Oracle Streams Concepts and Administration 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 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 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 DBA_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 4-7 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 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.

The connect user is the user who can attach to the outbound server to retrieve the LCR stream. The client application must attach to the outbound server as the connect user.

You can change the connect user when a client application must connect to an outbound server as a different user. Ensure that the connect user is granted the required privileges.

See Also:

"CREATE_OUTBOUND Procedure" for information about the privileges required by a connect user

To change the connect user for an outbound server: 

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

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

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

    • server_name - Specify the name of the outbound server.

    • connect_user - Specify the new connect user.

    Example 4-8 Changing the Connect User for an Outbound Server

    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.

You can change the capture user when the capture process must capture changes in a different security domain. Ensure that the capture user is granted the required privileges. When you change the capture user, the ALTER_OUTBOUND procedure grants the new capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user.

Note:

If Oracle Database Vault is installed, then the user who changes the capture user must be granted the BECOME USER system privilege. Granting this privilege to the user is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after capture user is changed, if necessary.

See Also:

"CREATE_OUTBOUND Procedure" for information about the privileges required by a capture user

To change the capture user of the capture process for an outbound server: 

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

    To change the capture user, the user who invokes the ALTER_OUTBOUND procedure must be granted DBA role. Only the SYS user can set the capture user to SYS.

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

  2. 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 3.

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

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

    • server_name - Specify the name of the outbound server.

    • capture_user - Specify the new capture user.

    Example 4-9 Changing the Capture User of the Capture Process for an Outbound Server

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

Changing the Start SCN or Start Time of the Capture Process for an Outbound Server

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

This section describes changing the start system change number (SCN) or start time for a capture process that captures changes for an outbound server using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

The start SCN is the SCN from which a capture process begins to capture changes. The start time is the time from which a capture process begins to capture changes. When you reset a start SCN or start time for a capture process, ensure that the required redo log files are available to the capture process.

Typically, you reset the start SCN or start time for a capture process if point-in-time recovery was performed on one of the destination databases that receive changes from the capture process.

This section contains these topics:

Changing the Start SCN of the Capture Process for an Outbound Server

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. 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. 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 DBA_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.

  3. 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 4-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 the Capture Process for an Outbound Server

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. 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. 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 DBA_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.

  3. 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 4-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 4-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;
    /
    

Dropping 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 the preceding conditions is not met, then the DROP_OUTBOUND procedure only drops the outbound server. It does not drop the queue.

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

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: 

  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 DROP_OUTBOUND procedure.

    Example 4-13 Dropping an Outbound Server

    To drop an outbound server named xout, run the following procedure:

    exec DBMS_XSTREAM_ADM.DROP_OUTBOUND('xout');
    

See Also:

Managing XStream In

This section describes managing an XStream In inbound server configuration.

This section contains these topics:

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 Database 2 Day + Data Replication and Integration Guide and Oracle Streams Concepts and Administration.

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.

You can change the apply user when a client application must connect to an inbound server as a different user or when you want to apply changes using the privileges associated with a different user. Ensure that the apply user is granted the required privileges.

See Also:

"CREATE_INBOUND Procedure" for information about the privileges required by an apply user

To change the apply user for an inbound server: 

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

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

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

    • server_name - Specify the name of the inbound server.

    • apply_user - Specify the new apply user.

    Example 4-14 Changing the Apply User for an Inbound Server

    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:

Managing Eager Errors Encountered by an Inbound Server

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

An inbound server can encounter an eager error when it cannot access all of the LCRs in an error transaction. The EAGER ERROR error type typically means that an LCR raised an error while the inbound server was receiving and applying LCRs in a large transaction. If an error transaction is not an eager error transaction, then it is referred to as a normal error transaction.

Normal error transactions and eager error transactions must be managed differently. An inbound server moves a normal error transaction, including all of its LCRs, to the error queue, but an inbound server does not move an eager error transaction to the error queue.

The following statements apply to both normal error transactions and eager error transactions:

  • The ALL_APPLY_ERROR and the DBA_APPLY_ERROR view contain information (metadata) about the error transaction.

  • The inbound server does not apply the error transaction.

Table 4-1 explains the options for managing a normal error transaction.

Table 4-1 Options Available for Managing a Normal Error Transaction

Action Mechanisms Description

Delete the error transaction

DBMS_APPLY_ADM.DELETE_ERROR

DBMS_APPLY_ADM.DELETE_ALL_ERRORS

Oracle Enterprise Manager

The error transaction is deleted from the error queue, and the metadata about the error transaction is deleted. An inbound server does not try to reexecute the transaction when the inbound server is restarted. The transaction is not applied.

Execute the error transaction

DBMS_APPLY_ADM.EXECUTE_ERROR

DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS

Oracle Enterprise Manager

The error transaction in the error queue is executed. If there are no errors during execution, then the transaction is applied. If an LCR raises an error during execution, then the normal error transaction is moved back to the error queue.

Retain the error transaction

None. (The error transaction is retained automatically.)

The error transaction remains in the error queue even if the inbound server is restarted. The metadata about the error transaction is also retained. The transaction is not applied.


Table 4-2 explains the options for managing an eager error transaction.

Table 4-2 Options Available for Managing an Eager Error Transaction

Action Mechanisms Description

Delete error transaction

DBMS_APPLY_ADM.DELETE_ERROR

DBMS_APPLY_ADM.DELETE_ALL_ERRORS

Oracle Enterprise Manager

The metadata about the eager error transaction is deleted. When the inbound server is restarted, it attempts to execute the transaction as an eager transaction. If the inbound server does not encounter an error during execution, then the transaction is applied successfully. If the inbound server encounters an error during execution, then the eager error transaction is recorded.

Retain error transaction

None. (The metadata about the error transaction is retained automatically.)

The metadata about the eager error transaction is retained. When the inbound server is restarted, it attempts to execute the transaction as a normal transaction.

Specifically, the inbound server spills the transaction to disk and attempts to execute the transaction. If the inbound server does not encounter an error during execution, then the transaction is applied successfully. If the inbound server encounters an error during execution, then the transaction becomes a normal error transaction. In this case, the LCR that raised the error and all of the other LCRs in the transaction are moved to the error queue. After the normal error transaction is moved to the error queue, you must manage the error transaction as a normal error transaction (not an eager error transaction).


Note:

If you attempt to execute an eager error transaction manually using the DBMS_APPLY_ADM package or Oracle Enterprise Manager, then the following error is raised:
ORA-26909: cannot re-execute an eager error

An eager error transaction cannot be executed manually. Instead, it is executed automatically when the inbound server is enabled.

To manage an eager error transaction encountered by an inbound server: 

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

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

  2. Query the ERROR_TYPE column in the ALL_APPLY_ERROR data dictionary view:

    SELECT APPLY_NAME, ERROR_TYPE FROM ALL_APPLY_ERROR;
    

    Follow the appropriate instructions based on the error type:

  3. Examine the error message raised by the LCR, and determine the cause of the error.

    See Oracle Database 2 Day + Data Replication and Integration Guide for information about checking for apply errors using Oracle Enterprise Manager.

    See Oracle Streams Concepts and Administration for information about checking for apply errors using the DBA_APPLY_ERROR data dictionary view.

  4. If possible, determine how to avoid the error, and make any changes necessary to avoid the error.

    Oracle Streams Concepts and Administration contains information about common apply errors.

  5. Either retain the error transaction or delete the error transaction:

    • Delete the error transaction only if you have corrected the problem. The inbound server reexecutes the transaction when it is enabled.

    • Retain the error transaction if you cannot correct the problem now or if you plan to reexecute it in the future.

    See Table 4-2 for more information about these choices.

    Caution:

    It might not be possible to recover a normal error transaction that is deleted. Before deleting the error transaction, ensure that the error type is EAGER ERROR.

    See Oracle Database 2 Day + Data Replication and Integration Guide for information about deleting an error transaction using Oracle Enterprise Manager.

    See Oracle Streams Concepts and Administration information about deleting an error transaction using the DBMS_APPLY_ADM package.

  6. If the inbound server is disabled, then start the inbound server.

    Query the STATUS column in the ALL_APPLY_ERROR view to determine whether the inbound server is enabled or disabled.

    If the disable_on_error apply parameter is set to Y for the inbound server, then the inbound server becomes disabled when it encounters the error and remains disabled.

    If the disable_on_error apply parameter is set to N for the inbound server, then the inbound server stops and restarts automatically when it encounters the error.

    See Table 4-2 for information about how the inbound server handles the error transaction based on your choice in Step 5.

    See Oracle Database 2 Day + Data Replication and Integration Guide for information about starting an apply process (or inbound server) using Oracle Enterprise Manager.

    See Oracle Streams Concepts and Administration for information about starting an apply process (or inbound server) using the DBMS_APPLY_ADM package.

Note:

If you have both purchased a license for the Oracle GoldenGate product and have enabled the XStream optimizations for Oracle Streams by running the DBMS_XSTREAM_ADM.ENABLE_GG_XSTREAM_FOR_STREAMS procedure, then an apply process in an Oracle Streams configuration can encounter errors of the EAGER ERROR type. Use the instructions in this section to manage eager apply process errors. When the XStream optimizations for Oracle Streams are not enabled, apply processes cannot encounter eager errors.

Dropping 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 inbound server and the queue.

  • The inbound server is the only subscriber to the queue.

If either one of the preceding 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: 

  1. Connect to the inbound 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 DROP_INBOUND procedure.

    Example 4-15 Dropping an Inbound Server

    To drop an inbound server named xin, run the following procedure:

    exec DBMS_XSTREAM_ADM.DROP_INBOUND('xin');