10 Managing XStream In

This chapter provides instructions for managing XStream.

This chapter contains these topics:

This chapter does not cover using rules, rule sets, or rule-based transformations with inbound servers. By default, an inbound server does not use rules or rule sets. Therefore, an inbound server applies all of the logical change records (LCRs) sent to it by an XStream client application. However, to filter the LCRs sent to an inbound server, you can add rules and rule sets to an inbound server using the DBMS_XSTREAM_ADM and DBMS_RULE_ADM packages. You can also specify rule-based transformations using the DBMS_XSTREAM_ADM package.

See Also:

Oracle Streams Concepts and Administration for information about using rules, rule sets, and rule-based transformations

About Managing XStream In

This chapter describes managing an XStream In configuration. This chapter provides instructions for modifying the database components that are part of an XStream In configuration, such as inbound servers.

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

See Also:

Starting an Inbound Server

A inbound server must be enabled for it to receive logical change records (LCRs) from an XStream client application and apply the LCRs. You run the START_APPLY procedure in the DBMS_APPLY_ADM package to start an existing inbound server.

To start 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 START_APPLY procedure in the DBMS_APPLY_ADM package, and specify the inbound server for the apply_name parameter.

The following example starts a inbound server named xin.

Example 10-1 Starting an Outbound Server Named xout

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'xin');
END;
/

See Also:

The Oracle Enterprise Manager Cloud Control online help for instructions about starting an apply process or an inbound server with Oracle Enterprise Manager Cloud Control

Stopping an Inbound Server

You run the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an existing inbound server. You might stop an inbound server when you are troubleshooting a problem in an XStream configuration.

To stop 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 STOP_APPLY procedure in the DBMS_APPLY_ADM package, and specify the inbound server for the apply_name parameter.

The following example stops a inbound server named xin.

Example 10-2 Stopping an Inbound Server Named xout

BEGIN
  DBMS_APPLY_ADM.STOP_APPLY(
    apply_name => 'xin');
END;
/

See Also:

The Oracle Enterprise Manager Cloud Control online help for instructions about stopping an apply process or an inbound server with Oracle Enterprise Manager Cloud Control

Setting an Apply Parameter for an Inbound Server

You set an apply parameter for an inbound server using the SET_PARAMETER procedure in the DBMS_XSTREAM_ADM package. Apply parameters control the way an inbound server operates.

To set an inbound server apply parameter: 

  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 SET_PARAMETER procedure in the DBMS_XSTREAM_ADM package, and specify the following parameters:

    • streams_name - Specify the name of the inbound server.

    • streams_type - Specify apply.

    • parameter - Specify the name of the apply parameter.

    • value - Specify the value for the apply parameter.

The following example sets the parallelism parameter for an inbound server named xin to 4.

Example 10-3 Setting an Outbound Server Parameter

BEGIN
  DBMS_XSTREAM_ADM.SET_PARAMETER(
    streams_name => 'xin',
    streams_type => 'apply'
    parameter    => 'parallelism',
    value        => '4');
END;
/

Note:

  • The value parameter is always entered as a VARCHAR2 value, even if the parameter value is a number.

  • If the value parameter is set to NULL or is not specified, then the parameter is set to its default value.

See Also:

Changing the Apply User for an Inbound Server

An inbound server applies LCRs 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:

Oracle Database PL/SQL Packages and Types Referencefor 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 in the DBMS_XSTREAM_ADM package, and specify the following parameters:

    • server_name - Specify the name of the inbound server.

    • apply_user - Specify the new apply user.

    Example 10-4 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 Apply Errors

Apply errors result when an inbound server tries to apply an LCR, and an error is raised. When an apply error occurs, the LCR that caused the error and all of the other LCRs in the same transaction are moved to the error queue.

The following sections contain instructions for retrying and deleting apply errors:

See Also:

Retrying Apply Error Transactions

You can retry a specific error transaction, or you can retry all error transactions for an inbound server. You might need to make DML or DDL changes to database objects to correct the conditions that caused one or more apply errors before you retry error transactions.

This section contains these topics:

See Also:

Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session

Retrying a Specific Apply Error Transaction

When you retry an error transaction, you can execute it immediately or send the error transaction to a user procedure for modifications before executing it. The following sections provide instructions for each method:

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the EXECUTE_ERROR procedure
Retrying a Specific Apply Error Transaction Without a User Procedure

After you correct the conditions that caused an apply error, you can retry the transaction by running the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package without specifying a user procedure. In this case, the transaction is executed without any custom processing.

To retry a specific apply error transaction without a user procedure: 

  1. In SQL*Plus, connect to the database as the XStream administrator.

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

  2. Run the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package, and specify the transaction identifier.

    To retry a transaction with the transaction identifier 5.4.312, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.EXECUTE_ERROR(
    local_transaction_id => '5.4.312',
    execute_as_user      => FALSE,
    user_procedure       => NULL);
END;
/

If execute_as_user is TRUE, then the inbound server executes the transaction in the security context of the current user. If execute_as_user is FALSE, then the inbound server executes the transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised.

In either case, the user who executes the transaction must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers.

Retrying a Specific Apply Error Transaction With a User Procedure

You can retry an error transaction by running the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package and specify a user procedure to modify one or more LCRs in the transaction before the transaction is executed. The modifications should enable successful execution of the transaction.

For example, consider a case in which an apply error resulted because of a conflict. Examination of the error transaction reveals that the old value for the salary column in a row LCR contained the wrong value. Specifically, the current value of the salary of the employee with employee_id of 197 in the hr.employees table did not match the old value of the salary for this employee in the row LCR. Assume that the current value for this employee is 3250 in the hr.employees table. The example in this section creates a procedure to resolve the error.

To retry a specific apply error transaction with a user procedure: 

  1. In SQL*Plus, connect to the database as the XStream administrator.

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

  2. Given this scenario described previously, create the following user procedure to modify the salary in the row LCR that caused the error:

    CREATE OR REPLACE PROCEDURE xstrmadmin.modify_emp_salary(
  in_any                        IN      ANYDATA,
  error_record                  IN      ALL_APPLY_ERROR%ROWTYPE,
  error_message_number          IN      NUMBER,
  messaging_default_processing  IN OUT  BOOLEAN,
  out_any                       OUT     ANYDATA)
AS
  row_lcr          SYS.LCR$_ROW_RECORD;
  row_lcr_changed  BOOLEAN := FALSE;
  res              NUMBER;
  ob_owner         VARCHAR2(32);
  ob_name          VARCHAR2(32);
  cmd_type         VARCHAR2(30);
  employee_id      NUMBER;
BEGIN
  IF in_any.getTypeName() = 'SYS.LCR$_ROW_RECORD' THEN
    -- Access the LCR
    res := in_any.GETOBJECT(row_lcr);
    -- Determine the owner of the database object for the LCR
    ob_owner := row_lcr.GET_OBJECT_OWNER;
    -- Determine the name of the database object for the LCR
    ob_name := row_lcr.GET_OBJECT_NAME;
    -- Determine the type of DML change
    cmd_type := row_lcr.GET_COMMAND_TYPE;
    IF (ob_owner = 'HR' AND ob_name = 'EMPLOYEES' AND cmd_type = 'UPDATE') THEN
      -- Determine the employee_id of the row change
      IF row_lcr.GET_VALUE('old', 'employee_id') IS NOT NULL THEN
        employee_id := row_lcr.GET_VALUE('old', 'employee_id').ACCESSNUMBER();
        IF (employee_id = 197) THEN
          -- error_record.message_number should equal error_message_number
          row_lcr.SET_VALUE(
          value_type => 'OLD',
          column_name => 'salary',
          column_value => ANYDATA.ConvertNumber(3250));
          row_lcr_changed := TRUE;
        END IF;
      END IF;
    END IF;
  END IF;
  -- Specify that the inbound server continues to process the current message
  messaging_default_processing := TRUE;
  -- assign out_any appropriately
  IF row_lcr_changed THEN
    out_any := ANYDATA.ConvertObject(row_lcr);
  ELSE
    out_any := in_any;
  END IF;
END;
/

  3. Run the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package, and specify the transaction identifier and the user procedure.

    To retry a transaction with the transaction identifier 5.6.924 and process the transaction with the modify_emp_salary procedure in the xstrmadmin schema before execution, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.EXECUTE_ERROR(
    local_transaction_id => '5.6.924',
    execute_as_user      => FALSE,
    user_procedure       => 'xstrmadmin.modify_emp_salary');
END;
/

Note:

The user who runs the procedure must have SELECT privilege on ALL_APPLY_ERROR data dictionary view.

Retrying All Error Transactions for an Inbound Server

After you correct the conditions that caused all of the apply errors for an inbound server, you can retry all of the error transactions by running the EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package.

To retry all error transactions for an inbound server: 

  1. In SQL*Plus, connect to the database as the XStream administrator.

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

  2. Run the EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package, and specify the name of the inbound server.

    To retry all of the error transactions for an inbound server named xin, run the following procedure:

    BEGIN
  DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(
    apply_name       => 'xin',
    execute_as_user  => FALSE);
END;
/

Note:

If you specify NULL for the apply_name parameter, and you have multiple inbound servers, then all of the apply errors are retried for all of the inbound servers.

Deleting Apply Error Transactions

You can delete a specific error transaction or you can delete all error transactions for an inbound server.

This section contains these topics:

Deleting a Specific Apply Error Transaction

If an error transaction should not be applied, then you can delete the transaction from the error queue using the DELETE_ERROR procedure in the DBMS_APPLY_ADM package.

To delete a specific apply error transaction: 

  1. In SQL*Plus, connect to the database as the XStream administrator.

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

  2. Run the DELETE_ERROR procedure in the DBMS_APPLY_ADM package, and specify the transaction identifier.

    To delete a transaction with the transaction identifier 5.4.312, run the following procedure:

    EXEC DBMS_APPLY_ADM.DELETE_ERROR(local_transaction_id => '5.4.312');

Deleting All Error Transactions for an Inbound Server

If none of the error transactions should be applied, then you can delete all of the error transactions by running the DELETE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package.

To delete all error transactions for an inbound server: 

  1. In SQL*Plus, connect to the database as the XStream administrator.

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

  2. Run the DELETE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package, and specify the name of the inbound server.

    To delete all of the error transactions for an inbound server named xin, run the following procedure:

    EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'xin');

Note:

If you specify NULL for the apply_name parameter, and you have multiple inbound servers, then all of the apply errors are deleted for all of the inbound servers.

Managing Eager Errors Encountered by an Inbound Server

As a performance optimization, an inbound server can use eager apply to begin to apply large transactions before it receives the commit LCR. See "Optimizing XStream In Performance for Large Transactions" for information about eager apply.

An inbound server can encounter an error while eagerly applying a transaction. Because all of the LCRs are not available for the transaction, an EAGER ERROR is recorded for this failed transaction. In this case, an entry in the ALL_APPLY_ERROR view shows an eager error for the transaction, but the LCRs are not recorded in the error queue. 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.

An eager error causes the inbound server to stop. When it restarts, if the error queue has an EAGER ERROR for the restarting transaction, then the transaction is started as a normal transaction. That is, the LCRs in the large transaction spill to disk, and the inbound server begins to apply them only after the commit LCR is received.

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

  • The ALL_APPLY_ERROR and ALL_APPLY_ERROR_MESSAGES views contain information (metadata) about the error transaction.

  • The inbound server does not apply the error transaction.

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

Table 10-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 Cloud Control

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 Cloud Control

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 10-2 explains the options for managing an eager error transaction.

Table 10-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 Cloud Control

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 Cloud Control, then the following error is raised: 
ORA-26909: cannot reexecute 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 "Checking for Apply Errors" and "Displaying Detailed Information About Apply Errors" for information about checking for apply errors using data dictionary views.

    See Oracle Enterprise Manager Cloud Control online help for information about checking for apply errors using Oracle Enterprise Manager Cloud Control.

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

    See Chapter 12, "Troubleshooting XStream In" for information about common apply errors and solutions for them.

  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.

      For example, to delete a transaction with the transaction identifier 5.4.312, run the following procedure:

      EXEC DBMS_APPLY_ADM.DELETE_ERROR(local_transaction_id => '5.4.312');

    • Retain the error transaction if you cannot correct the problem now or if you plan to reexecute it in the future. No action is necessary to retain the error transaction. It remains in the error queue until it is reexecuted or deleted.

    See Table 10-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 "Deleting Apply Error Transactions" for more information about deleting an error transaction using the DBMS_APPLY_ADM package.

    See the Oracle Enterprise Manager Cloud Control online help for information about deleting an error transaction using Oracle Enterprise Manager Cloud Control.

  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 10-2 for information about how the inbound server handles the error transaction based on your choice in Step 5.

    See "Starting an Inbound Server" for information about starting an inbound server or apply process using the DBMS_APPLY_ADM package.

    See the Oracle Enterprise Manager Cloud Control online help for information about starting an inbound server or apply process using Oracle Enterprise Manager Cloud Control.

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.

See Also:

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 10-5 Dropping an Inbound Server

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

exec DBMS_XSTREAM_ADM.DROP_INBOUND('xin');

If the inbound server's queue is not dropped automatically, then run the REMOVE_QUEUE procedure to drop it.

Example 10-6 Dropping an Inbound Server's Queue

To drop a queue named xin_queue, run the following procedure:

exec DBMS_XSTREAM_ADM.REMOVE_QUEUE('xin_queue');

See Also:

Oracle Database PL/SQL Packages and Types Reference