Oracle® Streams Concepts and Administration 10g Release 1 (10.1) Part Number B10727-01 |
|
|
View PDF |
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 chapter should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
See Also:
|
This section contains instructions for creating, starting, stopping and dropping an apply process. It contains the following topics:
You can use any of the following procedures to create an apply process:
DBMS_STREAMS_ADM.ADD_TABLE_RULES
DBMS_STREAMS_ADM.ADD_SUBSET_RULES
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES
DBMS_STREAMS_ADM.ADD_MESSAGE_RULE
DBMS_APPLY_ADM.CREATE_APPLY
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 either a positive or negative rule set for the apply process if the apply process does not have such a rule set, and may add table rules, schema rules, global rules, or a message rule to the rule set.
The CREATE_APPLY
procedure in the DBMS_APPLY_ADM
package 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, either as a positive or a negative rule set, and a number of other options, such as apply handlers, an apply user, an apply tag, and whether to apply captured or user-enqueued events.
Before you create an apply process, create a SYS.AnyData
queue to associate with the apply process, if one does not exist.
See Also:
|
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:
|
The following is an example that runs the ADD_SCHEMA_RULES
procedure in the DBMS_STREAMS_ADM
package to create an apply process that applies captured events:
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', inclusion_rule => true); END; /
Running this procedure performs the following actions:
strm01_apply
that applies captured events to the local database. The apply process is created only if it does not already exist.strm01_queue
inclusion_rule
parameter is set to true
. The rule set uses the SYS.STREAMS$_EVALUATION_CONTEXT
evaluation context. The rule set name is specified by the system.TRUE
for row LCRs that contain the results of DML changes to database objects in the hr
schema. The rule name is specified by the system.inclusion_rule
parameter is set to true
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.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.dbs1.net
source database. The rules in the apply process rule sets determine which events are dequeued by the apply process. If the apply process dequeues an LCR with a source database that is different than dbs1.net
, then an error is raised.The following is an example that runs the ADD_MESSAGE_RULE
procedure in the DBMS_STREAMS_ADM
package to create an apply process:
BEGIN DBMS_STREAMS_ADM.ADD_MESSAGE_RULE( message_type => 'oe.order_typ', rule_condition => ':msg.order_status = 1', streams_type => 'apply', streams_name => 'strm02_apply', queue_name => 'strm02_queue', inclusion_rule => true); END; /
Running this procedure performs the following actions:
strm02_apply
that dequeues user-enqueued events of oe.order_typ
type and sends them to the message handler for the apply process. The apply process is created only if it does not already exist.strm02_queue
inclusion_rule
parameter is set to true
. The rule set name is specified by the system, and the rule set does not use an evaluation context.TRUE
for user-enqueued events that satisfy the rule condition. The rule uses a system-created evaluation context for the message type. The rule name and the evaluation context name are specified by the system.inclusion_rule
parameter is set to true
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, including any redo entries generated by a message handler, have a tag with this value.
Note: You can use the |
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:
|
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 => 'strm03_queue', apply_name => 'strm03_apply', rule_set_name => 'strmadmin.strm03_rule_set', message_handler => NULL, ddl_handler => 'strmadmin.history_ddl', apply_user => 'hr', apply_database_link => NULL, apply_tag => HEXTORAW('5'), apply_captured => true, precommit_handler => NULL, negative_rule_set_name => NULL, source_database => 'dbs1.net'); END; /
Running this procedure performs the following actions:
strm03_apply
. An apply process with the same name must not exist.strm03_queue
strm03_rule_set
. This rule set is the positive rule set for the apply process.history_ddl
PL/SQL procedure in the strmadmin
schema. The user who runs the CREATE_APPLY
procedure must have EXECUTE
privilege on the history_ddl
PL/SQL procedure. An example in the Oracle Streams Replication Administrator's Guide creates this procedure.hr
, and not the user who is running the CREATE_APPLY
procedure (the Streams administrator).apply_database_link
parameter is set to NULL
.'5'
.dbs1.net
source database. The rules in the apply process rule sets determine which events are dequeued by the apply process. If the apply process dequeues an LCR with a source database that is different than dbs1.net
, then an error is raised.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 => 'strm04_queue', apply_name => 'strm04_apply', rule_set_name => 'strmadmin.strm04_rule_set', message_handler => 'strmadmin.mes_handler', ddl_handler => NULL, apply_user => NULL, apply_database_link => NULL, apply_tag => NULL, apply_captured => false, precommit_handler => NULL, negative_rule_set_name => NULL); END; /
Running this procedure performs the following actions:
strm04_apply
. An apply process with the same name must not exist.strm04_queue
strm04_rule_set
. This rule set is the positive rule set for the apply process.mes_handler
PL/SQL procedure in the strmadmin
schema. The user who runs the CREATE_APPLY
procedure must have EXECUTE
privilege on the mes_handler
PL/SQL procedure.CREATE_APPLY
procedure, because the apply_user
parameter is NULL
.apply_database_link
parameter is set to NULL
.NULL
tag.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; /
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; /
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 strm02_apply
:
BEGIN DBMS_APPLY_ADM.DROP_APPLY( apply_name => 'strm02_apply', drop_unused_rule_sets => true); END; /
Because the drop_unused_rule_sets
parameter is set to true
, this procedure also drops any rule sets used by the strm02_apply
apply process, unless a rule set is used by another Streams client. If the drop_unused_rule_sets
parameter is set to true
, then both the positive and negative rule set for the apply process may be dropped. If this procedure drops a rule set, then it also drops any rules in the rule set that are not in another rule set.
An error is raised if you try to drop an apply process and there are errors in the error queue for the specified apply process. Therefore, if there are errors in the error queue for an apply process, delete the errors before dropping the apply process.
This section contains instructions for completing the following tasks:
You can specify one positive rule set and one negative rule set for an apply process. The apply process applies an event if it evaluates to TRUE
for at least one rule in the positive rule set and discards an event if it evaluates to TRUE
for at least one rule in the negative rule set. The negative rule set is evaluated before the positive rule set.
You specify an existing rule set as the positive rule set for an existing apply process using the rule_set_name
parameter in the ALTER_APPLY
procedure. This procedure is in the DBMS_APPLY_ADM
package.
For example, the following procedure sets the positive 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; /
You specify an existing rule set as the negative rule set for an existing apply process using the negative_rule_set_name
parameter in the ALTER_APPLY
procedure. This procedure is in the DBMS_APPLY_ADM
package.
For example, the following procedure sets the negative rule set for an apply process named strm01_apply
to strm03_rule_set
.
BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'strm01_apply', negative_rule_set_name => 'strmadmin.strm03_rule_set'); END; /
To add rules to the rule set for an apply process, you can run one of the following procedures:
DBMS_STREAMS_ADM.ADD_TABLE_RULES
DBMS_STREAMS_ADM.ADD_SUBSET_RULES
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES
Excluding the ADD_SUBSET_RULES
procedure, these procedures can add rules to the positive or negative rule set for an apply process. The ADD_SUBSET_RULES
procedure can add rules only to the positive rule set for an apply process.
The following is an example that runs the ADD_TABLE_RULES
procedure in the DBMS_STREAMS_ADM
package to add rules to the positive rule set of an apply process named strm01_apply
:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.departments', streams_type => 'apply', streams_name => 'strm01_apply', queue_name => 'strm01_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net', inclusion_rule => true); END; /
Running this procedure performs the following actions:
TRUE
for row LCRs that contain the results of DML changes to the hr.departments
table. The rule name is specified by the system.TRUE
for DDL LCRs that contain DDL changes to the hr.departments
table. The rule name is specified by the system.TRUE
only for LCRs whose changes originated at the dbs1.net
source databaseinclusion_rule
parameter is set to true
The following is an example that runs the ADD_TABLE_RULES
procedure in the DBMS_STREAMS_ADM
package to add rules to the negative rule set of an apply process named strm01_apply
:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.regions', streams_type => 'apply', streams_name => 'strm01_apply', queue_name => 'strm01_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net', inclusion_rule => false); END; /
Running this procedure performs the following actions:
TRUE
for row LCRs that contain the results of DML changes to the hr.regions
table. The rule name is specified by the system.TRUE
for DDL LCRs that contain DDL changes to the hr.regions
table. The rule name is specified by the system.TRUE
only for LCRs whose changes originated at the dbs1.net
source databaseinclusion_rule
parameter is set to false
You specify that you want to remove a rule from a 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 positive rule set of an 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, inclusion_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.
If the inclusion_rule
parameter is set to false
, then the REMOVE_RULE
procedure removes the rule from the negative rule set for the apply process, not the positive rule set.
In addition, if you want to remove all of the rules in a rule set for the apply process, then specify NULL
for the rule_name
parameter when you run the REMOVE_RULE
procedure.
You specify that you want to remove a rule set from an existing apply process using the ALTER_APPLY
procedure in the DBMS_APPLY_ADM
package. This procedure can remove the positive rule set, negative rule set, or both. Specify true
for the remove_rule_set
parameter to remove the positive rule set for the apply process. Specify true
for the remove_negative_rule_set
parameter to remove the negative rule set for the apply process.
For example, the following procedure removes both the positive and negative rule set from an apply process named strm01_apply
.
BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'strm01_apply', remove_rule_set => true, remove_negative_rule_set => true); END; /
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; /
See Also:
|
The apply user is the user who applies all DML changes and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. Set the apply user for an apply process using the apply_user
parameter in the ALTER_APPLY
procedure in the DBMS_APPLY_ADM
package.
To change the apply user, the user who invokes the ALTER_APPLY
procedure must be granted DBA
role. Only the SYS
user can set the apply_user
to SYS
.
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; /
Running this procedure grants the new apply user dequeue privilege on the queue used by the apply process and configures the user as a secure queue user of the queue. In addition, make sure the apply user has the following privileges:
These privileges must be granted directly to the apply user. They cannot be granted through roles.
This section contains instructions for setting and removing the message handler for an apply process.
See Also:
|
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_handler
procedure in the strmadmin
schema.
BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'strm03_apply', message_handler => 'strmadmin.mes_handler'); END; /
The user who runs the ALTER_APPLY
procedure must have EXECUTE
privilege on the specified message handler.
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; /
This section contains instructions for creating, specifying, and removing the precommit handler for an apply process.
A precommit handler must have the following signature:
PROCEDURE handler_procedure ( parameter_name IN NUMBER);
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 commit SCN from an internal commit directive in the queue used by the apply process.
You can use a precommit handler to record information about commits processed by an apply process. The apply process may apply captured or user-enqueued events. For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database, but for a user-enqueued event, the commit SCN is generated by the apply process.
The precommit handler procedure must conform to the following restrictions:
If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the events in the transaction are moved to the error queue.
For example, a precommit handler may be used for auditing the row LCRs applied by an apply process. Such a precommit handler is used with one or more separate DML handlers to record the source database commit SCN for a transaction, and possibly the time when the apply process applies the transaction, in an audit table.
Specifically, this example creates a precommit handler that is used with a DML handler that records information about row LCRs in the following table:
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, commit_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;
The DML handler inserts a row in the strmadmin.history_row_lcrs
table for each row LCR processed by an apply process. The precommit handler created in this example inserts a row into the strmadmin.history_row_lcrs
table when a transaction commits.
Create the procedure that inserts the commit information into the history_row_lcrs
table:
CREATE OR REPLACE PROCEDURE strmadmin.history_commit(commit_number IN NUMBER) IS BEGIN -- Insert commit information into the history_row_lcrs table INSERT INTO strmadmin.history_row_lcrs (timestamp, commit_scn) VALUES (SYSDATE, commit_number); END; /
See Also:
|
A precommit handler processes all commit directives dequeued by an apply process. Set the precommit handler for an apply process using the precommit_handler
parameter in the ALTER_APPLY
procedure in the DBMS_APPLY_ADM
package. For example, the following procedure sets the precommit handler for an apply process named strm01_apply
to the history_commit
procedure in the strmadmin
schema.
BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'strm01_apply', precommit_handler => 'strmadmin.history_commit'); END; /
You may also specify a precommit handler when you create an apply process using the CREATE_APPLY
procedure in the DBMS_APPLY_ADM
package.
You remove the precommit handler for an apply process by setting the remove_precommit_handler
parameter to true
in the ALTER_APPLY
procedure in the DBMS_APPLY_ADM
package. For example, the following procedure removes the precommit handler from an apply process named strm01_apply
.
BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'strm01_apply', remove_precommit_handler => true); END; /
This section contains instructions for setting a destination queue into which apply processes that use a specified rule in a positive rule set will enqueue events that satisfy the rule. This section also contains instructions for removing destination queue settings.
You use the SET_ENQUEUE_DESTINATION
procedure in the DBMS_APPLY_ADM
package to set a destination queue for events that satisfy a certain rule. For example, to set the destination queue for a rule named employees5
to the queue hr.change_queue
, run the following procedure:
BEGIN DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION( rule_name => 'employees5', destination_queue_name => 'hr.change_queue'); END; /
This procedure modifies the specified rule's action context to specify the queue. Any apply process in the local database with the employees5
rule in its positive rule set will enqueue an event into hr.change_queue
if the event satisfies the employees5
rule. If you want to change the destination queue for the employees5
rule, then run the SET_ENQUEUE_DESTINATION
procedure again and specify a different queue.
The apply user of each apply process using the specified rule must have the necessary privileges to enqueue events into the specified queue. If the queue is a secure queue, then the apply user must be a secure queue user of the queue.
An event that has been enqueued into an queue using the SET_ENQUEUE_DESTINATION
procedure is the same as any other user-enqueued event. Such events can be manually dequeued, applied by an apply process created with the apply_captured
parameter set to false
, or propagated to another queue.
See Also:
|
You use the SET_ENQUEUE_DESTINATION
procedure in the DBMS_APPLY_ADM
package to remove a destination queue for events that satisfy a certain rule. Specifically, you set the destination_queue_name
parameter in this procedure to NULL
for the rule. When a destination queue specification is removed for a rule, events that satisfy the rule are no longer enqueued into the queue by an apply process.
For example, to remove the destination queue for a rule named employees5
, run the following procedure:
BEGIN DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION( rule_name => 'employees5', destination_queue_name => NULL); END; /
Any apply process in the local database with the employees5
rule in its positive rule set no longer enqueues an event into hr.change_queue
if the event satisfies the employees5
rule.
This section contains instructions for setting an apply process execute directive for events that satisfy a specified rule in the positive rule set for the apply process.
You use the SET_EXECUTE
procedure in the DBMS_APPLY_ADM
package to specify that apply processes do not execute events that satisfy a certain rule. Specifically, you set the execute
parameter in this procedure to false
for the rule. After setting the execution directive to false
for a rule, an apply process with the rule in its positive rule set does not execute an event that satisfies the rule.
For example, to specify that apply processes do not execute events that satisfy a rule named departments8
, run the following procedure:
BEGIN DBMS_APPLY_ADM.SET_EXECUTE( rule_name => 'departments8', execute => false); END; /
This procedure modifies the specified rule's action context to specify the execution directive. Any apply process in the local database with the departments8
rule in its positive rule set will not execute an event if the event satisfies the departments8
rule. That is, if the event is an LCR, then an apply process does not apply the change in the LCR to the relevant database object. Also, an apply process does not send an event that satisfies this rule to any apply handler.
See Also:
|
You use the SET_EXECUTE
procedure in the DBMS_APPLY_ADM
package to specify that apply processes execute events that satisfy a certain rule. Specifically, you set the execute
parameter in this procedure to true
for the rule. By default, each apply process executes events that satisfy a rule in the positive rule set for the apply process, assuming that the event does not satisfy a rule in the negative rule set for the apply process. Therefore, you need to set the execute
parameter to true
for a rule only if this parameter was set to false
for the rule in the past.
For example, to specify that apply processes executes events that satisfy a rule named departments8
, run the following procedure:
BEGIN DBMS_APPLY_ADM.SET_EXECUTE( rule_name => 'departments8', execute => true); END; /
Any apply process in the local database with the departments8
rule in its positive rule set will execute an event if the event satisfies the departments8
rule. That is, if the event is an LCR, then an apply process applies the change in the LCR to the relevant database object. Also, an apply process sends an event that satisfies this rule to an apply handler if it is configured to do so.
This section contains instructions for creating, setting, and removing 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 |
Running an error handler results in one of the following outcomes:
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 a row with the same primary key value as an insert row LCR 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:
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:
hr.regions
table with a region_id
value of 6
and a region_name
value of 'LILLIPUT'
.hr.regions
table with a region_id
value of 6
and a region_name
value of 'BROBDINGNAG'
.strmadmin.errorlog
table.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:
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
.
ALL
privilege on the sequence:
GRANT ALL ON reg_exception_s TO strmadmin;
errorlog
table by connecting as the Streams administrator and running the following statement:
CONNECT strmadmin/strmadminpw CREATE TABLE strmadmin.errorlog( logdate DATE, apply_name VARCHAR2(30), sender VARCHAR2(100), object_name VARCHAR2(32), command_type VARCHAR2(30), errnum NUMBER, errmsg VARCHAR2(2000), text VARCHAR2(2000), lcr SYS.LCR$_ROW_RECORD);
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 ; /
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); apply_name 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 ; -- Get the name of the sender and the name of the apply process ad := DBMS_STREAMS.GET_INFORMATION('SENDER'); ret := ad.GETVARCHAR2(errlog_rec.sender); apply_name := DBMS_STREAMS.GET_STREAMS_NAME(); -- 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, apply_name, 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', 'n'); 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; /
See Also:
Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session |
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. You either can set an error handler for a specific apply process, or you can set an error handler as a general error handler that is used by all apply processes that apply the specified operation to the specified table.
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.
In this example, the apply_name
parameter is set to NULL
. Therefore, the error handler is a general error handler that is used by all of the apply processes in the database.
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, apply_name => NULL); END; /
You unset 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 unsets 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, apply_name => NULL); END; /
This section contains instructions for retrying and deleting apply errors.
See Also:
|
The following sections describe how to retry a specific error transaction and how to retry all error transactions for an apply process. You may need to make DML or DDL changes to database objects to correct the conditions that caused one or more apply errors before you retry apply error transactions. You may also have one or more capture processes configured to capture changes to the same database objects. However, you may not want the changes captured. In this case, you can set the tag to a value that will not be captured for the session that makes the changes.
See Also:
Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session |
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.
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 strm01_apply
, you can run the following procedure:
BEGIN DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS( apply_name => 'strm01_apply', execute_as_user => false); END; /
Note: If you specify |
The following sections describe how to delete a specific error transaction and how to delete all error transactions for an apply process.
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');
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. For example, to delete all of the error transactions for an apply process named strm01_apply
, you can run the following procedure:
EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'strm01_apply');