13
Managing an Apply Process

A Streams apply process dequeues logical change records (LCRs) and user messages from a specific queue and either applies each one directly or passes it as a parameter to a user-defined procedure.

This chapter contains these topics:

Each task described in this section should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise.

See Also:

Creating, Starting, Stopping, and Dropping an Apply Process

This section contains instructions for creating, starting, stopping and dropping an apply process.

Creating an Apply Process

You can use any of the following procedures to create an apply process:

Each of the procedures in the DBMS_STREAMS_ADM package creates an apply process with the specified name if it does not already exist, creates a rule set for the apply process if the apply process does not have a rule set, and may add table, schema, or global rules to the rule set.

The CREATE_APPLY procedure creates an apply process, but does not create a rule set or rules for the apply process. However, the CREATE_APPLY procedure enables you to specify an existing rule set to associate with the apply process and a number of other options, such as event handlers, an apply user, an apply tag, and whether to apply captured or user-enqueued events.

Before you create an apply process, create a Streams queue to associate with the apply process, if one does not exist.

Note:

Depending on the configuration of the apply process you create, supplemental logging may be required at the source database on columns in the tables for which an apply process applies changes.

See Also:

"Creating a Streams Queue"
"Supplemental Logging" for information about when supplemental logging is required
"Specifying Supplemental Logging at a Source Database"

Example of Creating an Apply Process Using DBMS_STREAMS_ADM

The following is an example that runs the ADD_SCHEMA_RULES procedure in the DBMS_STREAMS_ADM package to create an apply process:

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name        => 'hr',
    streams_type       => 'apply',
    streams_name       => 'strm01_apply',
    queue_name         => 'strm01_queue',
    include_dml        => true,
    include_ddl        => false,
    include_tagged_lcr => false,
    source_database    => 'dbs1.net');
END;
/

Running this procedure performs the following actions:

Creates an apply process named strm01_apply that applies captured events to the local database. The apply process is created only if it does not already exist.
Associates the apply process with an existing queue named strm01_queue
Creates a rule set and associates it with the apply process, if the apply process does not have a rule set. The rule set uses the SYS.STREAMS$_EVALUATION_CONTEXT evaluation context. The rule set name is specified by the system.
Creates one rule that specifies that the apply process applies row LCRs that contain the results of DML changes to database objects in the hr schema. The rule name is specified by the system.
Adds the rule to the rule set associated with the apply process
Sets the apply_tag for the apply process to a value that is the hexadecimal equivalent of '00' (double zero). Redo entries generated by the apply process have a tag with this value.
Specifies that the apply process applies a row LCR only if it has a NULL tag, because the include_tagged_lcr parameter is set to false. This behavior is accomplished through the system-created rule for the apply process.
See Also:

"Apply Process Creation"

"System-Created Rules"

"Tags and Rules Created by the DBMS_STREAMS_ADM Package"

Examples of Creating an Apply Process Using DBMS_APPLY_ADM

The first example in this section creates an apply process that applies captured events, and the second example in this section creates an apply process that applies user-enqueued events. A single apply process cannot apply both captured and user-enqueued events.

See Also:

"Apply Process Creation"
"Event Processing Options" for more information about event handlers
"Tags and an Apply Process"
"Oracle to Non-Oracle Data Sharing with Streams" for information about configuring an apply process to apply events to a non-Oracle database using the apply_database_link parameter

Example of Creating an Apply Process to Apply Captured Events

The following is an example that runs the CREATE_APPLY procedure in the DBMS_APPLY_ADM package to create an apply process that applies captured events:

BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name          => 'strm01_queue',
    apply_name          => 'strm02_apply',
    rule_set_name       => 'strmadmin.strm01_rule_set',
    message_handler     => NULL,     
    ddl_handler         => 'hr.ddl_handler',
    apply_user          => 'hr',
    apply_database_link => NULL,
    apply_tag           => HEXTORAW('5'),
    apply_captured      => true);
END;
/

Running this procedure performs the following actions:

Creates an apply process named strm02_apply. An apply process with the same name must not exist.
Associates the apply process with the queue named strm01_queue
Associates the apply process with an existing rule set named strm01_rule_set
Specifies that the apply process does not use a message handler.
Specifies that the DDL handler is the ddl_handler PL/SQL procedure in the hr schema.
Specifies that the user who applies the changes is hr, and not the user who is running the CREATE_APPLY procedure (the Streams administrator).
Specifies that the apply process applies changes to the local database because the apply_database_link parameter is set to NULL.
Specifies that each redo entry generated by the apply process has a tag that is the hexadecimal equivalent of '5'.
Specifies that the apply process applies captured LCRs, and not user-enqueued events. Therefore, if an LCR that was constructed by a user application, not by by the capture process, is staged in the queue for the apply process, then this apply process does not apply the LCR.

Example of Creating an Apply Process to Apply User-Enqueued Events

The following is an example that runs the CREATE_APPLY procedure in the DBMS_APPLY_ADM package to create an apply process that applies user-enqueued events:

BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name          => 'strm01_queue',
    apply_name          => 'strm03_apply',
    rule_set_name       => 'strmadmin.strm02_rule_set',
    message_handler     => 'hr.mes_handler',     
    ddl_handler         => NULL,
    apply_user          => NULL,
    apply_database_link => NULL,
    apply_tag           => NULL,
    apply_captured      => false);
END;
/

Running this procedure performs the following actions:

Creates an apply process named strm03_apply. An apply process with the same name must not exist.
Associates the apply process with the queue named strm01_queue
Associates the apply process with an existing rule set named strm02_rule_set
Specifies that the message handler is the mes_handler PL/SQL procedure in the hr schema.
Specifies that the apply process does not use a DDL handler.
Specifies that the user who applies the changes is the user who runs the CREATE_APPLY procedure, because the apply_user parameter is NULL.
Specifies that the apply process applies changes to the local database, because the apply_database_link parameter is set to NULL.
Specifies that each redo entry generated by the apply process has a NULL tag.
Specifies that the apply process applies user-enqueued events, and not captured events.

Starting an Apply Process

You run the START_APPLY procedure in the DBMS_APPLY_ADM package to start an existing apply process. For example, the following procedure starts an apply process named strm01_apply:

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

Stopping an Apply Process

You run the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an existing apply process. For example, the following procedure stops an apply process named strm01_apply:

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

Dropping an Apply Process

You run the DROP_APPLY procedure in the DBMS_APPLY_ADM package to drop an existing apply process. For example, the following procedure drops an apply process named strm01_apply:

BEGIN
  DBMS_APPLY_ADM.DROP_APPLY(
    apply_name => 'strm01_apply');
END;
/

Managing the Rule Set for an Apply Process

This section contains instructions for completing the following tasks:

Specifying the Rule Set for an Apply Process

You specify the rule set that you want to associate with an apply process using the rule_set_name parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the rule set for an apply process named strm01_apply to strm02_rule_set.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name    => 'strm01_apply',
    rule_set_name => 'strmadmin.strm02_rule_set');
END;
/

Adding Rules to the Rule Set for an Apply Process

To add rules to the rule set for an apply process, you can run one of the following procedures:

The following is an example that runs the ADD_SCHEMA_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the rule set of an apply process named strm01_apply:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name         => 'oe.orders',
    streams_type       => 'apply',
    streams_name       => 'strm01_apply',
    queue_name         => 'strm01_queue',
    include_dml        => true,
    include_ddl        => true,
    source_database    => 'dbs1.net');
END;
/

Running this procedure performs the following actions:

Creates one rule that specifies that the apply process applies row LCRs that contain the results of DML changes to the oe.orders table. The rule name is specified by the system.
Creates one rule that specifies that the apply process applies DDL LCRs that contain the results of DDL changes to the oe.orders table. The rule name is specified by the system.
Adds the rule to the rule set associated with the apply process
Specifies that the apply process applies LCRs only from the dbs1.net source database.

See Also:
"System-Created Rules"

Removing a Rule from the Rule Set for an Apply Process

You specify that you want to remove a rule from the rule set for an existing apply process by running the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package. For example, the following procedure removes a rule named DEPARTMENTS3 from the rule set of a apply process named strm01_apply.

BEGIN
  DBMS_STREAMS_ADM.REMOVE_RULE(
    rule_name        => 'DEPARTMENTS3',
    streams_type     => 'apply',
    streams_name     => 'strm01_apply',
    drop_unused_rule => true);
END;
/

In this example, the drop_unused_rule parameter in the REMOVE_RULE procedure is set to true, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the drop_unused_rule parameter is set to false, then the rule is removed from the rule set, but it is not dropped from the database.

In addition, if you want to remove all of the rules in the rule set for the apply process, then specify NULL for the rule_name parameter when you run the REMOVE_RULE procedure.

Note:

If you drop all of the rules in the rule set for an apply process that applies captured events, then the apply process does not apply any captured events in its queue. Similarly, if you drop all of the rules in the rule set for an apply process that applies user-enqueued events, then the apply process does not apply any user-enqueued events in its queue.

Removing the Rule Set for an Apply Process

You specify that you want to remove the rule set from an apply process by setting the remove_rule_set parameter to true in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure removes the rule set from an apply process named strm01_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name      => 'strm01_apply',
    remove_rule_set => true);
END;
/

Note:

If you remove a rule set for an apply process that applied captured events, then the apply process applies all captured events in its queue. Similarly, if you remove a rule set for an apply process that applies user-enqueued events, then the apply process applies all user-enqueued events in its queue.

Setting an Apply Process Parameter

You set an apply process parameter using the SET_PARAMETER procedure in the DBMS_APPLY_ADM package. Apply process parameters control the way an apply process operates.

For example, the following procedure sets the commit_serialization parameter for an apply process named strm01_apply to none. This setting for the commit_serialization parameter enables the apply process to commit transactions in any order.

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name   => 'strm01_apply',
    parameter    => 'commit_serialization',
    value        => 'none');
END;
/

Note:

The value parameter is always entered as a VARCHAR2, even if the parameter value is a number.
If you set the parallelism apply process parameter to a value greater than 1, then you must specify a conditional supplemental log group at the source database for all of the unique key and foreign columns in the tables for which an apply process applies changes. Supplemental logging may be required for other columns in these tables as well, depending on your configuration.

See Also:

"Apply Process Parameters"
The DBMS_APPLY_ADM.SET_PARAMETER procedure in the Oracle9i Supplied PL/SQL Packages and Types Reference for detailed information about the apply process parameters
"Specifying Supplemental Logging at a Source Database"

Setting the Apply User for an Apply Process

The apply user is the user who applies all DML statements and DDL statements and who runs user-defined apply handlers. You set the apply user for an apply process using the apply_user parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the apply user for an apply process named strm03_apply to hr.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'strm03_apply',
    apply_user => 'hr');
END;
/

The user specified by the apply_user parameter must have the necessary privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. The specified user must also have dequeue privileges on the queue used by the apply process and privileges to execute the rule set and transformation functions used by the apply process. These privileges must be granted directly to the apply user; they cannot be granted through roles.

Managing the Message Handler for an Apply Process

This section contains instructions for setting and removing the message handler for an apply process.

See Also:

"Event Processing with an Apply Process"
Chapter 18, "Example Streams Messaging Environment" for an example of creating a message handler

Setting the Message Handler for an Apply Process

You set the message handler for an apply process using the message_handler parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the message handler for an apply process named strm03_apply to the mes_proc procedure in the hr schema.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name      => 'strm03_apply',
    message_handler => 'hr.mes_proc');
END;
/

Removing the Message Handler for an Apply Process

You remove the message handler for an apply process by setting the remove_message_handler parameter to true in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure removes the message handler from an apply process named strm03_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name             => 'strm03_apply',
    remove_message_handler => true);
END;
/

Managing a DML Handler

This section contains instructions for creating, setting, and removing a DML handler.

Creating a DML Handler

A DML handler must have the following signature:

PROCEDURE user_procedure (
   parameter_name   IN  SYS.AnyData);

Here, user_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is a SYS.AnyData encapsulation of a row LCR.

The following restrictions apply to the user procedure:

Do not execute COMMIT or ROLLBACK statements. Doing so may endanger the consistency of the transaction that contains the LCR.
If you are manipulating a row using the EXECUTE member procedure for the row LCR, then do not attempt to manipulate more than one row in a row operation. You must construct and execute manually any DML statements that manipulate more than one row.
If the command type is UPDATE or DELETE, then row operations resubmitted using the EXECUTE member procedure for the LCR must include the entire key in the list of old values. The key is the primary key, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure.
If the command type is INSERT, then row operations resubmitted using the EXECUTE member procedure for the LCR should include the entire key in the list of new values. Otherwise, duplicate rows are possible. The key is the primary key, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure.

A DML handler can be used for any customized processing of row LCRs. For example, the handler may modify an LCR and then execute it using the EXECUTE member procedure for the LCR. When you execute a row LCR in a DML handler, the apply process applies the row LCR without calling any DML handler or error handler for the row LCR.

You may also use a DML handler for recording the history of DML changes. For example, a DML handler may insert information about an LCR it processes into a table and then apply the LCR using the EXECUTE member procedure. To create such a DML handler, first create a table to hold the history information:

CREATE TABLE strmadmin.history_row_lcrs(
  timestamp             DATE,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(10),
  scn                   NUMBER,
  old_values            SYS.LCR$_ROW_LIST,
  new_values            SYS.LCR$_ROW_LIST)
    NESTED TABLE old_values STORE AS old_values_ntab
    NESTED TABLE new_values STORE AS new_values_ntab;

Then, create the procedure that inserts the information in the row LCR into the history_row_lcrs table and executes the row LCR:

CREATE OR REPLACE PROCEDURE history_dml(in_any IN SYS.ANYDATA)  
 IS
  lcr   SYS.LCR$_ROW_RECORD;
  rc    PLS_INTEGER;
 BEGIN
  -- Access the LCR
  rc := in_any.GETOBJECT(lcr);
  -- Insert information in the LCR into the history_row_lcrs table
  INSERT INTO strmadmin.history_row_lcrs VALUES 
   (SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(),   
    lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), 
    lcr.GET_TAG(), lcr.GET_TRANSACTION_ID(), lcr.GET_SCN(),
    lcr.GET_VALUES('OLD'), lcr.GET_VALUES('NEW'));
  --  Apply row LCR
  lcr.EXECUTE(true);
END;
/

Note:

You must specify an unconditional supplemental log group at the source database for any columns needed by a DML handler at the destination database. This example DML handler does not require any additional supplemental logging because it simply records information about the row LCR and does not manipulate the row LCR in any other way.

Setting a DML Handler

A DML handler processes each row LCR dequeued by any apply process that contains a specific operation on a specific table. You can specify multiple DML handlers on the same table, to handle different operations on the table. All apply processes that apply changes to the specified table in the local database use the specified DML handler.

You set the DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the DML handler for UPDATE operations on the hr.locations table. Therefore, when any apply process that applies changes locally dequeues a row LCR containing an UPDATE operation on the hr.locations table, the apply process sends the row LCR to the history_dml PL/SQL procedure in the strmadmin schema for processing. The apply process does not apply a row LCR containing such a change directly.

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'hr.locations',
    object_type         => 'TABLE',
    operation_name      => 'UPDATE',
    error_handler       => false,
    user_procedure      => 'strmadmin.history_dml',
    apply_database_link => NULL);
END;
/

Note:

If an apply process applies changes to a remote non-Oracle database, then it may use a different DML handler for the same table. You can run the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package to specify a DML handler for changes that will be applied to a remote non-Oracle database by setting the apply_database_link parameter to a non-NULL value.

Removing a DML Handler

You remove a DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run that procedure, set the user_procedure parameter to NULL for a specific operation on a specific table. For example, the following procedure removes the DML handler for UPDATE operations on the hr.locations table. After the DML handler is removed, any apply process that applies changes locally will apply a row LCR containing such a change directly.

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name    => 'hr.locations',
    object_type    => 'TABLE',
    operation_name => 'UPDATE',
    error_handler  => false,
    user_procedure => NULL);
END;
/

Managing the DDL Handler for an Apply Process

This section contains instructions for creating, specifying, and removing the DDL handler for an apply process.

Note:

All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, then a commit is performed automatically.

See Also:

"Event Processing with an Apply Process"
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the EXECUTE member procedure for LCR types

Creating a DDL Handler for an Apply Process

A DDL handler must have the following signature:

PROCEDURE handler_procedure (
   parameter_name   IN  SYS.AnyData);

Here, handler_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is a SYS.AnyData encapsulation of a DDL LCR.

A DDL handler can be used for any customized processing of DDL LCRs. For example, the handler may modify the LCR and then execute it using the EXECUTE member procedure for the LCR. When you execute a DDL LCR in a DDL handler, the apply process applies the LCR without calling the DDL handler again.

You may also use a DDL handler to record the history of DDL changes. For example, a DDL handler may insert information about an LCR it processes into a table and then apply the LCR using the EXECUTE member procedure.

To create such a DDL handler, first create a table to hold the history information:

CREATE TABLE strmadmin.history_ddl_lcrs(
  timestamp             DATE,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  object_type           VARCHAR2(18),
  ddl_text              CLOB,
  logon_user            VARCHAR2(32),
  current_schema        VARCHAR2(32),
  base_table_owner      VARCHAR2(32),
  base_table_name       VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(10),
  scn                   NUMBER);

Then, create the procedure that inserts the information in the DDL LCR into the history_ddl_lcrs table and executes the DDL LCR:

CREATE OR REPLACE procedure history_ddl(in_any IN SYS.ANYDATA)  
 IS
   lcr       SYS.LCR$_DDL_RECORD;
   rc        PLS_INTEGER;
   ddl_text  CLOB;
 BEGIN
   -- Access the LCR
   rc := in_any.GETOBJECT(lcr);
   DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
   lcr.GET_DDL_TEXT(ddl_text);
   --  Insert DDL LCR information into history_ddl_lcrs table
   INSERT INTO strmadmin.history_ddl_lcrs VALUES( 
     SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(), 
     lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_OBJECT_TYPE(), 
     ddl_text, lcr.GET_LOGON_USER(), lcr.GET_CURRENT_SCHEMA(), 
     lcr.GET_BASE_TABLE_OWNER(), lcr.GET_BASE_TABLE_NAME(), lcr.GET_TAG(), 
     lcr.GET_TRANSACTION_ID(), lcr.GET_SCN());
   --  Apply DDL LCR
   lcr.EXECUTE();
   -- Free temporary LOB space
   DBMS_LOB.FREETEMPORARY(ddl_text);
END;
/

Setting the DDL Handler for an Apply Process

A DDL handler processes all DDL LCRs dequeued by an apply process. You set the DDL handler for an apply process using the ddl_handler parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the DDL handler for an apply process named strm02_apply to the history_ddl procedure in the strmadmin schema.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name  => 'strm02_apply',
    ddl_handler => 'strmadmin.history_ddl');
END;
/

Removing the DDL Handler for an Apply Process

A DDL handler processes all DDL LCRs dequeued by an apply process. You remove the DDL handler for an apply process by setting the remove_ddl_handler parameter to true in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure removes the DDL handler from an apply process named strm02_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name         => 'strm02_apply',
    remove_ddl_handler => true);
END;
/

Managing an Error Handler

This section contains instructions for creating, setting, and removing an error handler.

Creating an Error Handler

You create an error handler by running the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package and setting the error_handler parameter to true.

An error handler must have the following signature:

PROCEDURE user_procedure (
     message             IN SYS.AnyData,
     error_stack_depth   IN NUMBER,
     error_numbers       IN DBMS_UTILITY.NUMBER_ARRAY,
     error_messages      IN emsg_array);

Here, user_procedure stands for the name of the procedure. Each parameter is required and must have the specified datatype. However, you can change the names of the parameters. The emsg_array parameter must be a user-defined array that is a PL/SQL table of type VARCHAR2 with at least 76 characters.

Note:

Certain restrictions on the user procedure specified in SET_DML_HANDLER must be met for error handlers. See "Creating a DML Handler" for information about these restrictions.

Running an error handler results in one of the following outcomes:

The error handler successfully resolves the error, applies the row LCR if appropriate, and returns control back to the apply process.
The error handler fails to resolve the error, and the error is raised. The raised error causes the transaction to be rolled back and placed in the error queue.

If you want to retry the DML operation, then have the error handler procedure run the EXECUTE member procedure for the LCR.

The following example creates an error handler named regions_pk_error that resolves primary key violations for the hr.regions table. At a destination database, assume users insert rows into the hr.regions table and an apply process applies changes to the hr.regions table that originated from a capture process at a remote source database. In this environment, there is a possibility of errors resulting from users at the destination database inserting rows with the same primary key value as LCRs applied from the source database.

This example creates a table in the strmadmin schema called errorlog to record the following information about each primary key violation error on the hr.regions table:

The timestamp when the error occurred
The user who caused the error (sender), which is the capture process name for captured LCRs or the name of the AQ agent for user-enqueued LCRs
The name of the object on which the DML operation was run, because errors for other objects may be logged in the future
The type of command used in the DML operation
The name of the constraint violated
The error message
The LCR that caused the error

This error handler resolves only errors that are caused by a primary key violation on the hr.regions table. To resolve this type of error, the error handler modifies the region_id value in the row LCR using a sequence and then executes the row LCR to apply it. If other types of errors occur, then you can use the row LCR you stored in the errorlog table to resolve the error manually.

For example, the following error is resolved by the error handler:

At the destination database, a user inserts a row into the hr.regions table with a region_id value of 6 and a region_name value of 'LILLIPUT'.
At the source database, a user inserts a row into the hr.regions table with a region_id value of 6 and a region_name value of 'BROBDINGNAG'.
A capture process at the source database captures the change described in Step 2.
A propagation job propagates the LCR containing the change from a queue at the source database to the queue used by the apply process at the destination database.
When the apply process tries to apply the LCR, an error results because of a primary key violation.
The apply process invokes the error handler to handle the error.
The error handler logs the error in the strmadmin.errorlog table.
The error handler modifies the region_id value in the LCR using a sequence and executes the LCR to apply it.

Complete the following steps to create the regions_pk_error error handler:

Create the sequence used by the error handler to assign new primary key values by connecting as hr user and running the following statement:
```
CONNECT hr/hr

CREATE SEQUENCE hr.reg_exception_s START WITH 9000;
```
This example assumes that users at the destination database will never insert a row into the hr.regions table with a region_id greater than 8999.
Grant the Streams administrator ALL privilege on the sequence:
```
GRANT ALL ON reg_exception_s TO strmadmin;
```

Create the errorlog table by connecting as the Streams administrator and running the following statement:

CONNECT strmadmin/strmadminpw

CREATE TABLE strmadmin.errorlog(
  logdate       DATE,
  sender        VARCHAR2(100) ,
  object_name   VARCHAR2(32),
  command_type  VARCHAR2(30),
  errnum        NUMBER,
  errmsg        VARCHAR2(2000),
  text          VARCHAR2(2000),
  lcr           SYS.LCR$_ROW_RECORD);

Create a package that includes the regions_pk_error procedure:

CREATE OR REPLACE PACKAGE errors_pkg 
AS
 TYPE emsg_array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
 PROCEDURE regions_pk_error( 
   message            IN SYS.ANYDATA ,
   error_stack_depth  IN NUMBER ,
   error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY,
   error_messages     IN EMSG_ARRAY);
END errors_pkg ;
/

Create the package body that includes the regions_pk_error procedure:

CREATE OR REPLACE PACKAGE BODY errors_pkg AS
 PROCEDURE regions_pk_error ( 
   message            IN SYS.ANYDATA,
   error_stack_depth  IN NUMBER,
   error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY,
   error_messages     IN EMSG_ARRAY )
 IS
  reg_id     NUMBER;
  ad         SYS.ANYDATA;
  lcr        SYS.LCR$_ROW_RECORD;
  ret        PLS_INTEGER;
  vc         VARCHAR2(30) ;
  errlog_rec errorlog%ROWTYPE ;
  ov2        SYS.LCR$_ROW_LIST;
 BEGIN
  -- Access the error number from the top of the stack.
  -- In case of check constraint violation,
  -- get the name of the constraint violated
  IF error_numbers(1) IN ( 1 , 2290 ) THEN
   ad  := DBMS_STREAMS.GET_INFORMATION('CONSTRAINT_NAME');
   ret := ad.GetVarchar2(errlog_rec.text);
  ELSE 
   errlog_rec.text := NULL ;
  END IF ;
  ad  := DBMS_STREAMS.GET_INFORMATION('SENDER');
  ret := ad.GETVARCHAR2(errlog_rec.sender);
  -- Try to access the LCR
  ret := message.GETOBJECT(lcr);
  errlog_rec.object_name  := lcr.GET_OBJECT_NAME() ;
  errlog_rec.command_type := lcr.GET_COMMAND_TYPE() ;
  errlog_rec.errnum := error_numbers(1) ;
  errlog_rec.errmsg := error_messages(1) ;
  INSERT INTO strmadmin.errorlog VALUES (SYSDATE, errlog_rec.sender,
       errlog_rec.object_name, errlog_rec.command_type, 
       errlog_rec.errnum, errlog_rec.errmsg, errlog_rec.text, lcr);
  -- Add the logic to change the contents of LCR with correct values
  -- In this example, get a new region_id number 
  -- from the hr.reg_exception_s sequence
  ov2 := lcr.GET_VALUES('NEW');
  FOR i IN 1 .. ov2.count
  LOOP
    IF ov2(i).column_name = 'REGION_ID' THEN
     SELECT hr.reg_exception_s.NEXTVAL INTO reg_id FROM DUAL; 
     ov2(i).data := Sys.AnyData.ConvertNumber(reg_id) ;
    END IF ;
  END LOOP ;
  -- Set the NEW values in the LCR
  lcr.SET_VALUES(value_type => 'NEW', value_list => ov2);
 -- Execute the modified LCR to apply it
  lcr.EXECUTE(true);
 END regions_pk_error;
END errors_pkg;
/

Note:

For subsequent changes to the modified row to be applied successfully, you should converge the rows at the two databases as quickly as possible. That is, you should make the region_id for the row match at the source and destination database. If you do not want these manual changes to be recaptured at a database, then use the SET_TAG procedure in the DBMS_STREAMS package to set the tag for the session in which you make the change to a value that is not captured.
This example error handler illustrates the use of the GET_VALUES member function and SET_VALUES member procedure for the LCR. However, if you are modifying only one value in the LCR, then the GET_VALUE member function and SET_VALUE member procedure may be more convenient and more efficient.

Setting an Error Handler

An error handler handles errors resulting from a row LCR dequeued by any apply process that contains a specific operation on a specific table. You can specify multiple error handlers on the same table, to handle errors resulting from different operations on the table. All apply processes that apply changes to the specified table in the local database use the specified error handler.

You can set the error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run this procedure to set an error handler, set the error_handler parameter to true.

For example, the following procedure sets the error handler for INSERT operations on the hr.regions table. Therefore, when any apply process dequeues a row LCR containing an INSERT operation on the local hr.regions table, and the row LCR results in an error, the apply process sends the row LCR to the strmadmin.errors_pkg.regions_pk_error PL/SQL procedure for processing. If the error handler cannot resolve the error, then the row LCR and all of the other row LCRs in the same transaction are moved to the error queue.

Run the following procedure to set the error handler:

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'hr.regions',
    object_type         => 'TABLE',
    operation_name      => 'INSERT',
    error_handler       => true,
    user_procedure      => 'strmadmin.errors_pkg.regions_pk_error',
    apply_database_link => NULL);
END;
/

Removing an Error Handler

You remove an error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run that procedure, set the user_procedure parameter to NULL for a specific operation on a specific table.

For example, the following procedure removes the error handler for INSERT operations on the hr.regions table:

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name    => 'hr.regions',
    object_type    => 'TABLE',
    operation_name => 'INSERT',
    user_procedure => NULL);
END;
/

Note:

The error_handler parameter need not be specified.

Managing the Substitute Key Columns for a Table

This section contains instructions for setting and removing the substitute key columns for a table.

See Also:

"Substitute Key Columns"

Setting Substitute Key Columns for a Table

When an apply process applies changes to a table, substitute key columns can either replace the primary key columns for a table that has a primary key or act as the primary key columns for a table that does not have a primary key. You set the substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. This setting applies to all of the apply processes that apply local changes to the database.

For example, to set the substitute key columns for the hr.employees table to the first_name, last_name, and hire_date columns, replacing the employee_id column, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_KEY_COLUMNS(
    object_name         => 'hr.employees',
    column_list         => 'first_name,last_name,hire_date');
END;
/

Note:

You must specify an unconditional supplemental log group at the source database for all of the columns specified as substitute key columns in the column_list or column_table parameter at the destination database. In this example, you would specify an unconditional supplemental log group including the first_name, last_name, and hire_date columns in the hr.employees table.
If an apply process applies changes to a remote non-Oracle database, then it may use different substitute key columns for the same table. You can run the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package to specify substitute key columns for changes that will be applied to a remote non-Oracle database by setting the apply_database_link parameter to a non-NULL value.

See Also:

Removing the Substitute Key Columns for a Table

You remove the substitute key columns for a table by specifying NULL for the column_list or column_table parameter in the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. If the table has a primary key, then the table's primary key is used by any apply process for local changes to the database after you remove the substitute primary key.

For example, to remove the substitute key columns for the hr.employees table, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_KEY_COLUMNS(
    object_name  => 'hr.employees',
    column_list  => NULL);
END;
/

Managing Streams Conflict Resolution

This section contains instructions for creating, specifying, and removing update conflict handlers a table. All apply processes running on a database that apply changes to the specified table use the specified update conflict handler.

Setting an Update Conflict Handler

You set an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. You can use one of the following prebuilt methods when you create an update conflict resolution handler:

OVERWRITE
DISCARD
MAXIMUM
MINIMUM

For example, suppose a Streams environment captures changes to the hr.jobs table at dbs1.net and propagates these changes to the dbs2.net destination database, where they are applied. In this environment, applications can perform DML changes on the hr.jobs table at both databases, but, if there is a conflict for a particular DML change, then the change at the dbs1.net database should always overwrite the change at the dbs2.net database. In this environment, you can accomplish this goal by specifying an OVERWRITE handler at the dbs2.net database.

To specify an update conflict handler for the hr.jobs table in the hr schema at the dbs2.net database, run the following procedure at dbs2.net:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.assignments',
      method_name       => 'OVERWRITE',
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

Note:

The resolution_column is not used for OVERWRITE and DISCARD methods, but one of the columns in the column_list still must be specified.
You must specify a conditional supplemental log group at the source database for all of the columns in the column_list at the destination database. In this example, you would specify a conditional supplemental log group including the job_title, min_salary, and max_salary columns in the hr.jobs table at the dbs1.net database.
Conflict resolution does not support LOB columns. Therefore, you should not include LOB columns in the column_list parameter when running SET_UPDATE_CONFLICT_HANDLER.

See Also:

"Specifying Supplemental Logging at a Source Database"
"Multiple Source Databases in an Oracle-Only Environment" for an example Streams environment that illustrates using the MAXIMUM prebuilt method for time-based conflict resolution

Modifying an Existing Update Conflict Handler

You can modify an existing update conflict handler by running the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. To update an existing conflict handler, specify the same table and resolution column as the existing conflict handler.

To modify the update conflict handler created in "Setting an Update Conflict Handler", you specify the hr.jobs table and the job_title column as the resolution column. You can modify this update conflict handler by specifying a different type of prebuilt method or a different column list, or both. However, if you want to change the resolution column for an update conflict handler, then you must remove and re-create the handler.

For example, suppose the environment changes, and you want changes from dbs1.net to be discarded in the event of a conflict, whereas previously changes from dbs1.net overwrote changes at dbs2.net. You can accomplish this goal by specifying a DISCARD handler at the dbs2.net database.

To modify the existing update conflict handler for the hr.jobs table in the hr schema at the dbs2.net database, run the following procedure:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.assignments',
      method_name       => 'DISCARD',
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

Removing an Existing Update Conflict Handler

You can remove an existing update conflict handler by running the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. To remove a an existing conflict handler, specify NULL for the method, and specify the same table, column list, and resolution column as the existing conflict handler.

For example, suppose you want to remove the update conflict handler created in "Setting an Update Conflict Handler" and then modified in "Modifying an Existing Update Conflict Handler". To remove this update conflict handler, run the following procedure:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.assignments',
      method_name       => NULL,
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

Managing Apply Errors

This section contains instructions for retrying and deleting apply errors.

See Also:

"The Error Queue"
"Checking for Apply Errors"
"Displaying Detailed Information About Apply Errors"
"Considerations for Applying DML Changes to Tables" for information about the possible causes of apply errors

Retrying Apply Error Transactions

The following sections describe how to retry a specific error transaction and how to retry all error transactions for an apply process.

Retrying a Specific Apply Error Transaction

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. For example, 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);
END;
/

If execute_as_user is true, then the apply process reexecutes the transaction in the security context of the current user. If execute_as_user is false, then the apply process reexecutes 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. This user must also have dequeue privileges on the queue used by the apply process.

Retrying All Error Transactions for an Apply Process

After you correct the conditions that caused all of the apply errors for an apply process, you can retry all of the error transactions by running the EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package. For example, to retry all of the error transactions for an apply process named apply_oe, you can run the following procedure:

BEGIN
  DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(
    apply_name       => 'apply_oe',
    execute_as_user  => false);
END;
/

Note:

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

Deleting Apply Error Transactions

The following sections describe how to delete a specific error transaction and how to delete all error transactions for an apply process.

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. For example, 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 Apply Process

If none of the error transactions in the error queue 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. For example, to delete all of the error transactions for an apply process named apply_oe, you can run the following procedure:

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

Note:

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

Setting Instantiation SCNs at a Destination Database

In a Streams environment that shares information between multiple databases, a source database is the database where changes are generated in the redo log. Suppose an environment has the following characteristics:

A capture process will capture changes to tables at the source database.
The changes to the tables will be propagated to a destination database and applied there.
The destination database already contains some or all of the tables for which changes will be captured, propagated, and applied.

In such an environment, the tables that already exist at the destination database are not instantiated. That is, because these tables already exist at the destination database, they are not created at the destination by exporting them at the source database and then importing them at the destination database. Instead, the apply process at the destination database must be instructed explicitly to apply changes that committed after a specific SCN for each source database table. The instantiation SCN for the tables specifies this SCN.

The instantiation SCN for a database object controls which LCRs that contain changes to the database object are ignored by an apply process and which LCRs are applied by an apply process. If the commit SCN of an LCR for a database object from a source database is less than or equal to the instantiation SCN for that database object at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR. Also, if there are multiple source databases for a shared database object at a destination database, then an instantiation SCN must be set for each source database, and the instantiation SCN may be different for each source database.

You can set instantiation SCNs in one of the following ways:

Perform instantiation of the relevant database objects by exporting them at the source database and importing them into the destination database. In this case, the instantiation creates the database objects at the destination database, populates them with the data from the source database, and sets the relevant instantiation SCNs.
Perform a metadata only export/import by setting the ROWS parameter to n during export at the source database or import at the destination database, or both. In this case, the database objects are instantiated, but no data is imported.
Set the instantiation SCN using the SET_TABLE_INSTANTIATION_SCN, SET_SCHEMA_INSTANATIATION_SCN, and SET_GLOBAL_INSTANTIATION_SCN procedures in the DBMS_APPLY_ADM package.

Setting Instantiation SCNs Using Export/Import

This section discusses setting instantiation SCNs by performing an export/import. The information in this section applies to both metadata export/import operations and to export/import operations that import rows.

To set instantiation SCNs for database objects using Export/Import, first export them at the source database with the OBJECT_CONSISTENT export parameter set to Y, or use a more stringent degree of consistency. Then, import them at the destination database with the STREAMS_INSTANTIATION import parameter set to Y.

Note:

If a non-NULL instantiation SCN already exists for a database object at a destination database that performs an import, then the import does not update the instantiation SCN for that database object.
During an export for a Streams instantiation, make sure no DDL changes are made to objects being exported.

The following sections describe the instantiation SCNs set for different types of export/import operations. These sections refer to prepared tables. Prepared tables are tables that have been prepared for instantiation using the PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, or PREPARE_GLOBAL_INSTANTIATION procedures in the DBMS_CAPTURE_ADM package. A table must be a prepared table before export in order for an instantiation SCN to be set for it during import. However, the database and schemas do not need to be prepared before the export in order for their instantiation SCNs to be set during import.

Full Database Export and Full Database Import

A full database export and full database import sets the following instantiation SCNs at the import database:

The database level, or global, instantiation SCN
The schema level instantiation SCN for each imported schema
The table level instantiation SCNs for each prepared table that is imported

Full Database or Schema Level Export and Schema Level Import

A full database or schema level export and schema level import sets the following instantiation SCNs at the import database:

The schema level instantiation SCN for each imported schema
The table level instantiation SCN for each prepared tables that is imported

Full Database, Schema, or Table Level Export and Table Level Import

Any export that includes one or more tables and a table level import sets the table level instantiation SCN for each prepared table that is imported at the import database.

See Also:

"Setting Export and Import Parameters Relevant to Streams" and Oracle9i Database Utilities for information about using Export/Import
"Configuring a Capture-Based Streams Environment" for more information about performing export/import operations to set instantiation SCNs when configuring a Streams environment
"Preparing Database Objects for Instantiation at a Source Database"

Setting Instantiation SCNs Using a DBMS_APPLY_ADM Package Procedure

You can set an instantiation SCN at a destination database for a specified table, a specified schema, or an entire database using one of the following procedures in the DBMS_APPLY_ADM package:

SET_TABLE_INSTANTIATION_SCN
SET_SCHEMA_INSTANTIATION_SCN
SET_GLOBAL_INSTANTIATION_SCN

If you set the instantiation SCN for a schema using SET_SCHEMA_INSTANTIATION_SCN, then you should set the instantiation SCN for each table in the schema using SET_TABLE_INSTANTIATION_SCN. Similarly, if you set the instantiation SCN for a database using SET_GLOBAL_INSTANTIATION_SCN, then you should set the instantiation SCN for each schema in the database using SET_SCHEMA_INSTANTIATION_SCN

Table 13-1 lists each procedure and the types of statements for which they set an instantiation SCN.

Table 13-1 Set Instantiation SCN Procedures and the Statements They Cover

Procedure Sets Instantiation SCN for Examples

Procedure	Sets Instantiation SCN for	Examples
`SET_TABLE_INSTANTIATION_SCN`	DML and DDL statements on tables, except `CREATE` `TABLE` DDL statements on table indexes and table triggers	`UPDATE` `ALTER TABLE` `DROP TABLE` `CREATE`, `ALTER`, or `DROP` `INDEX` on a table `CREATE`, `ALTER`, or `DROP` `TRIGGER` on a table
`SET_SCHEMA_INSTANTIATION_SCN`	DDL statements on users, except `CREATE` `USER` DDL statements on all database objects that have a non-`PUBLIC` owner, except for those DDL statements handled by a table-level instantiation SCN	`CREATE` `TABLE` `ALTER` `USER` `DROP` `USER` `CREATE` `PROCEDURE`
`SET_GLOBAL_INSTANTIATION_SCN`	DDL statements on database objects other than users with no owner DDL statements on database objects owned by public `CREATE` `USER` statements	`CREATE` `USER` `CREATE` `TABLESPACE`

SET_TABLE_INSTANTIATION_SCN

DML and DDL statements on tables, except CREATE TABLE

DDL statements on table indexes and table triggers

UPDATE

ALTER TABLE

DROP TABLE

CREATE, ALTER, or DROP INDEX on a table

CREATE, ALTER, or DROP TRIGGER on a table

SET_SCHEMA_INSTANTIATION_SCN

DDL statements on users, except CREATE USER

DDL statements on all database objects that have a non-PUBLIC owner, except for those DDL statements handled by a table-level instantiation SCN

CREATE TABLE

ALTER USER

DROP USER

CREATE PROCEDURE

SET_GLOBAL_INSTANTIATION_SCN

DDL statements on database objects other than users with no owner

DDL statements on database objects owned by public

CREATE USER statements

CREATE USER

CREATE TABLESPACE

The following example sets the instantiation SCN for the hr.departments table at the hrdb2.net database to the current SCN by running the following procedure at the source database hrdb1.net:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@HRDB2.NET(
    source_object_name    => 'hr.departments',
    source_database_name  => 'hrdb1.net',
    instantiation_scn     => iscn);
END;
/

Note:

If a relevant instantiation SCN is not present, then an error is raised during apply.
The SET_SCHEMA_INSTANTIATION_SCN procedure does not set the instantiation SCN for any of the tables in the schema.
The SET_GLOBAL_INSTANTIATION_SCN procedure does not set the instantiation SCN for any of the schemas in the database.
If an apply process applies changes to a remote non-Oracle database, then set the apply_database_link parameter to the database link used for remote apply when you set the instantiation SCN.

See Also:

Chapter 10, "Configuring a Streams Environment" for more information when to set instantiation SCNs when you are configuring a Streams environment
"Single Source Database in a Heterogeneous Environment" for a detailed example that uses the SET_TABLE_INSTANTIATION_SCN procedure
The information about the DBMS_APPLY_ADM package in the Oracle9i Supplied PL/SQL Packages and Types Reference for more information about which instantiation SCN can be used for a DDL LCR
"Instantiation in an Oracle to Non-Oracle Environment"

Performing Database Point-in-Time Recovery in a Streams Environment

Point-in-time recovery is the recovery of a database to a specified noncurrent time, SCN, or log sequence number. If point-in-time recovery is required at a destination database in a Streams environment, then you must reapply the captured changes that had already been applied after the point-in-time of the recovery.

For each relevant capture process, you can choose either of the following methods to perform point-in-time recovery at a destination database in a Streams environment

Reset the start SCN for the existing capture process that captures the changes that are applied at the destination database.
Create a new capture process to capture the changes that must be reapplied at the destination database.

Resetting the start SCN for the capture process is simpler than creating a new capture process. However, if the capture process captures changes that are applied at multiple destination databases, then the changes are resent to all the destination databases, including the ones that did not perform point-in-time recovery. If a change is already applied at a destination database, then it is discarded by the apply process, but you may not want to use the network and computer resources required to resend the changes to multiple destination databases. In this case, you can create and temporarily use a new capture process and a new propagation job that propagates changes only to the destination database that was recovered.

Both of these methods reapply only captured events at the destination database, not user-enqueued events. Also, if the destination database to be recovered is also a source database, then do not use either of these methods. In this case, you must manually resynchronize the data at all destination databases.

The following sections provide instructions for each task:

Resetting the Start SCN for the Existing Capture Process to Perform Recovery
Creating a New Capture Process to Perform Recovery

Note:
If there are multiple apply processes at the destination database where you performed point-in-time recovery, then complete one of the tasks in this section for each apply process.
See Also:

Oracle9i Backup and Recovery Concepts for more information about point-in-time recovery

"The Start SCN for a Capture Process"

Resetting the Start SCN for the Existing Capture Process to Perform Recovery

If you decide to reset the start SCN for the existing capture process to perform point-in-time recovery, then complete the following steps:

If you are not using directed networks between the source database and destination database, then drop the propagation job that propagates changes from the source queue at the source database to the destination queue at the destination database. Use the DROP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to drop the propagation.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the propagation job at each intermediate database in the path to the destination database, including the propagation job at the source database.

Note:

You must drop the appropriate propagation job(s). Disabling them is not sufficient. You will re-create the propagation job(s) in Step 6, and dropping them now ensures that only events created after resetting the start SCN for the capture process are propagated.

See Also:

"Directed Networks"

Perform the point-in-time recovery at the destination database.
Query for the oldest message number from the source database for the apply process at the destination database. Then, make a note of the results of the query. The oldest message number is the earliest system change number (SCN) that may need to be applied.

The following statement is an example of the query to perform:
```
SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
```
Stop the existing capture process using the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package.
Reset the start SCN of the existing capture process.

To reset the start SCN for an existing capture process, run the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package and set the start_scn parameter to the value you recorded from the query in Step 3. For example, to reset the start SCN for a capture process named strm01_capture to the value 829381993, run the following ALTER_CAPTURE procedure:
```
BEGIN
  DBMS_CAPTURE_ADM.ALTER_CAPTURE(
    capture_name  =>  'strm01_capture',
    start_scn     =>  829381993);
END;
/
```
If you are not using directed networks between the source database and destination database, then create a new propagation job to propagate changes from the source queue to the destination queue using the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package. Specify the rule set used by the original propagation job for the rule_set_name parameter when you create the propagation job.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then create a new propagation job at each intermediate database in the path to the destination database, including the propagation job at the source database.
Start the existing capture process using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

Creating a New Capture Process to Perform Recovery

If you decide to create a new capture process to perform point-in-time recovery, then complete the following steps:

If you are not using directed networks between the source database and destination database, then drop the propagation job that propagates changes from the source queue at the source database to the destination queue at the destination database. Use the DROP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to drop the propagation.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the propagation job that propagates events between the last intermediate database and the destination database. You do not need to drop the propagation jobs at the other intermediate databases nor at the source database.

Note:
You must drop the appropriate propagation job. Disabling it not sufficient.

See Also:
"Directed Networks"
Perform the point-in-time recovery at the destination database.
Query for the oldest message number from the source database for the apply process at the destination database. Then, make a note of the results of the query. The oldest message number is the earliest system change number (SCN) that may need to be applied.

The following statement is an example of the query to perform:
```
SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
```
Create a queue at the source database to be used by the capture process using the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then create a queue at each intermediate database in the path to the destination database, including the new queue at the source database. Do not create a new queue at the destination database.
If you are not using directed networks between the source database and destination database, then create a new propagation job to propagate changes from the source queue created in Step 4 to the destination queue using the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package. Specify the rule set used by the original propagation job for the rule_set_name parameter when you create the propagation job.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then create a propagation job at each intermediate database in the path to the destination database, including the propagation job from the source database to the first intermediate database. These propagation jobs propagate changes captured by the capture process you will create in Step 6 between the queues created in Step 4.
Create a new capture process at the source database using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package. Set the source_queue parameter to the queue you created in Step 4, the rule_set_name parameter to the rule set used by the original capture process, and the start_scn parameter to the value you recorded from the query in Step 3. If the rule set used by the original capture process captures events that should not be sent to the destination database that was recovered, then you can create and use a smaller, customized rule set that shares some rules with the original rule set.
Start the capture process you created in Step 6 using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.
When the oldest message number of the apply process at the recovered database is approaching the capture number of the original capture process at the source database, stop the original capture process using the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

At the destination database, you can use the following query to determine the oldest message number from the source database for the apply process:
```
SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
```
At the source database, you can use the following query to determine the capture number of the original capture process:
```
SELECT CAPTURE_NAME, CAPTURE_MESSAGE_NUMBER FROM V$STREAMS_CAPTURE;
```
When the oldest message number of the apply process at the recovered database is beyond the capture number of the original capture process at the source database, drop the new capture process created in Step 6.
If you are not using directed networks between the source database and destination database, then drop the new propagation job created in Step 5.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the new propagation job at each intermediate database in the path to the destination database, including the new propagation job at the source database.
If you are not using directed networks between the source database and destination database, then remove the queue created in Step 4.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the new queue at each intermediate database in the path to the destination database, including the new queue at the source database. Do not drop the queue at the destination database.
If you are not using directed networks between the source database and destination database, then create a propagation job that propagates changes from the original source queue at the source database to the destination queue at the destination database. Use the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to create the propagation. Specify the rule set used by the original propagation job for the rule_set_name parameter when you create the propagation job.

If you are using directed networks, and there are intermediate databases between the source database and destination database, then re-create the propagation job from the last intermediate database to the destination database. You dropped this propagation job in Step 1.
Start the capture process you stopped in Step 8.

All of the steps after Step 7 can be deferred to a later time, or they can be done as soon as the condition described in Step 8 is met.

13 Managing an Apply Process

Creating, Starting, Stopping, and Dropping an Apply Process

Creating an Apply Process

Example of Creating an Apply Process Using DBMS_STREAMS_ADM

Examples of Creating an Apply Process Using DBMS_APPLY_ADM

Example of Creating an Apply Process to Apply Captured Events

Example of Creating an Apply Process to Apply User-Enqueued Events

Starting an Apply Process

Stopping an Apply Process

Dropping an Apply Process

Managing the Rule Set for an Apply Process

Specifying the Rule Set for an Apply Process

Adding Rules to the Rule Set for an Apply Process

Removing a Rule from the Rule Set for an Apply Process

Removing the Rule Set for an Apply Process

Setting an Apply Process Parameter

Setting the Apply User for an Apply Process

Managing the Message Handler for an Apply Process

Setting the Message Handler for an Apply Process

Removing the Message Handler for an Apply Process

Managing a DML Handler

Creating a DML Handler

Setting a DML Handler

Removing a DML Handler

Managing the DDL Handler for an Apply Process

Creating a DDL Handler for an Apply Process

Setting the DDL Handler for an Apply Process

Removing the DDL Handler for an Apply Process

Managing an Error Handler

Creating an Error Handler

Setting an Error Handler

Removing an Error Handler

Managing the Substitute Key Columns for a Table

Setting Substitute Key Columns for a Table

Removing the Substitute Key Columns for a Table

Managing Streams Conflict Resolution

Setting an Update Conflict Handler

Modifying an Existing Update Conflict Handler

Removing an Existing Update Conflict Handler

Managing Apply Errors

Retrying Apply Error Transactions

Retrying a Specific Apply Error Transaction

Retrying All Error Transactions for an Apply Process

Deleting Apply Error Transactions

Deleting a Specific Apply Error Transaction

Deleting All Error Transactions for an Apply Process

Setting Instantiation SCNs at a Destination Database

Setting Instantiation SCNs Using Export/Import

Full Database Export and Full Database Import

Full Database or Schema Level Export and Schema Level Import

Full Database, Schema, or Table Level Export and Table Level Import

Setting Instantiation SCNs Using a DBMS_APPLY_ADM Package Procedure

Table 13-1 Set Instantiation SCN Procedures and the Statements They Cover

Performing Database Point-in-Time Recovery in a Streams Environment

Resetting the Start SCN for the Existing Capture Process to Perform Recovery

Creating a New Capture Process to Perform Recovery

13
Managing an Apply Process