An apply process implicitly consumes information in an Oracle Streams environment. An 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.
The following topics describe managing Oracle Streams apply processes:
Each task described in this chapter should be completed by an Oracle Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide and the Enterprise Manager online Help for instructions on managing an apply process with Enterprise Manager
Oracle Streams Replication Administrator's Guide for more information about managing DML handlers, DDL handlers, and Oracle Streams tags for 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;
/
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions about starting an apply process with Oracle Enterprise ManagerYou 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;
/
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions about stopping an apply process with Oracle Enterprise ManagerThis 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 a message if it evaluates to TRUE for at least one rule in the positive rule set and discards a message 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:
Excluding the ADD_SUBSET_RULES procedure, these procedures can add rules to the positive rule set 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.
See Also:
"System-Created Rules"The following example 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 => 'streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'dbs1.example.com',
inclusion_rule => TRUE);
END;
/
Running this procedure performs the following actions:
Creates one rule that evaluates to TRUE for row LCRs that contain the results of DML changes to the hr.departments table. The rule name is system generated.
Creates one rule that evaluates to TRUE for DDL LCRs that contain DDL changes to the hr.departments table. The rule name is system generated.
Specifies that both rules evaluate to TRUE only for LCRs whose changes originated at the dbs1.example.com source database.
Adds the rules to the positive rule set associated with the apply process because the inclusion_rule parameter is set to TRUE.
The following example 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 => 'streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'dbs1.example.com',
inclusion_rule => FALSE);
END;
/
Running this procedure performs the following actions:
Creates one rule that evaluates to TRUE for row LCRs that contain the results of DML changes to the hr.regions table. The rule name is system generated.
Creates one rule that evaluates to TRUE for DDL LCRs that contain DDL changes to the hr.regions table. The rule name is system generated.
Specifies that both rules evaluate to TRUE only for LCRs whose changes originated at the dbs1.example.com source database.
Adds the rules to the negative rule set associated with the apply process because the inclusion_rule parameter is set to FALSE.
You 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 even if it is not in any other rule set.
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 from the positive rule set.
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 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 sets 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;
/
Note:
If an apply process that dequeues messages from a buffered queues does not have a positive or negative rule set, then the apply process dequeues all captured LCRs in its queue. Similarly, if an apply process that dequeues messages from a persistent queue does not have a positive or negative rule set, the apply process dequeues all persistent LCRs and persistent user messages in its queue.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 value, even if the parameter value is a number.
If the value parameter is set to NULL or is not specified, then the parameter is set to its default value.
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 key columns in the tables for which an apply process applies changes. supplemental logging might be required for other columns in these tables as well, depending on your configuration.
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions about setting an apply process parameter with Oracle Enterprise Manager
The DBMS_APPLY_ADM.SET_PARAMETER procedure in the Oracle Database PL/SQL Packages and Types Reference for detailed information about the apply process parameters
Oracle Streams Replication Administrator's Guide for more information about specifying supplemental logging
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, ensure that the apply user has the following privileges:
EXECUTE privilege on the rule sets used by the apply process
EXECUTE privilege on all custom rule-based transformation functions used in the rule set
EXECUTE privilege on all apply handler procedures
These privileges must be granted directly to the apply user. They cannot be granted through roles.
Note:
If Oracle Database Vault is installed, then the user who changes the apply user must be granted theBECOME USER system privilege. Granting this privilege to the user is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after apply user is changed, if necessary.A message handler is an apply handler that processes persistent user messages. The following sections contain instructions for setting and unsetting the message handler for an apply process:
See Also:
"Types of Messages That Can Be Processed with an Apply Process"
Oracle Streams Advanced Queuing User's Guide for an example that creates a message handler
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 oe schema.
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'strm03_apply',
message_handler => 'oe.mes_handler');
END;
/
The user who runs the ALTER_APPLY procedure must have EXECUTE privilege on the specified message handler. If the message handler is already set for an apply process, then you can run the ALTER_APPLY procedure to change the message handler for the apply process.
You unset 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 unsets the message handler for an apply process named strm03_apply.
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'strm03_apply',
remove_message_handler => TRUE);
END;
/
A precommit handler is an apply handler that can receive the commit information for a transaction and process the commit information in any customized way.
The following sections contain instructions for creating, setting, and unsetting 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 can apply captured LCRs, persistent LCRs, or persistent user messages. For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database. For a persistent LCRs and persistent user messages, the commit SCN is generated by the apply process.
The precommit handler procedure must conform to the following restrictions:
Any work that commits must be an autonomous transaction.
Any rollback must be to a named save point created in the procedure.
If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the messages in the transaction are moved to the error queue.
For example, a precommit handler can 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:
Oracle Streams Replication Administrator's Guide for more information about the DML handler referenced in this example
A precommit handler processes all commit directives dequeued by an apply process. When you set a precommit handler for an apply process, the apply process uses it to process all of the commit directives that it dequeues. An apply process can have only one precommit handler.
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 can also specify a precommit handler when you create an apply process using the CREATE_APPLY procedure in the DBMS_APPLY_ADM package. If the precommit handler is already set for an apply process, then you can run the ALTER_APPLY procedure to change the precommit handler for the apply process.
You unset 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 unsets the precommit handler for 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 messages 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 messages that satisfy a specific 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 action context of the rule to specify the queue. Any apply process in the local database with the employees5 rule in its positive rule set will enqueue a message into hr.change_queue if the message 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 messages into the specified queue. If the queue is a secure queue, then the apply user must be a secure queue user of the queue.
A message that has been enqueued using the SET_ENQUEUE_DESTINATION procedure is the same as any other message that is enqueued manually. Such messages can be manually dequeued, applied by an apply process created with the apply_captured parameter set to FALSE, or propagated to another queue.
Note:
The specified rule must be in the positive rule set for an apply process. If the rule is in the negative rule set for an apply process, then the apply process does not enqueue the message into the destination queue.
The apply process always enqueues messages into a persistent queue. It cannot enqueue messages into a buffered queue.
See Also:
"Enqueue Destinations for Messages During Apply" for more information about how the SET_ENQUEUE_DESTINATION procedure modifies the action context of the specified rule
You use the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package to remove a destination queue for messages that satisfy a specified 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, messages 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 a message into hr.change_queue if the message satisfies the employees5 rule.
This section contains instructions for setting an apply process execute directive for messages 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 messages that satisfy a specified 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 a message that satisfies the rule.
For example, to specify that apply processes do not execute messages 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 action context of the rule to specify the execution directive. Any apply process in the local database with the departments8 rule in its positive rule set will not execute a message if the message satisfies the departments8 rule. That is, if the message 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 a message that satisfies this rule to any apply handler.
Note:
The specified rule must be in the positive rule set for an apply process for the apply process to follow the execution directive. If the rule is in the negative rule set for an apply process, then the apply process ignores the execution directive for the rule.
The SET_EXECUTE procedure can be used with the SET_ENQUEUE_DESTINATION procedure if you want to enqueue messages that satisfy a particular rule into a destination queue without executing these messages. After a message is enqueued using the SET_ENQUEUE_DESTINATION procedure, it is the same as any message that is enqueued manually. Therefore, it can be manually dequeued, applied by an apply process, or propagated to another queue.
See Also:
"Execution Directives for Messages During Apply" for more information about how the SET_EXECUTE procedure modifies the action context of the specified rule
You use the SET_EXECUTE procedure in the DBMS_APPLY_ADM package to specify that apply processes execute messages that satisfy a specified rule. Specifically, you set the execute parameter in this procedure to TRUE for the rule. By default, each apply process executes messages that satisfy a rule in the positive rule set for the apply process, assuming that the message does not satisfy a rule in the negative rule set for the apply process. Therefore, you must 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 messages 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 a message if the message satisfies the departments8 rule. That is, if the message is an LCR, then an apply process applies the change in the LCR to the relevant database object. Also, an apply process sends a message that satisfies this rule to an apply handler if it is configured to do so.
An error handler handles errors resulting from a row LCR dequeued by any apply process that contains a specific operation on a specific table.
The following sections contain instructions for creating, setting, and unsetting an error handler:
See Also:
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 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 data type. 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:
Some conditions on the user procedure specified inSET_DML_HANDLER must be met for error handlers. See Oracle Streams Replication Administrator's Guide for information about these conditions.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 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:
The time stamp when the error occurred
The name of the apply process that raised the error
The user who caused the error (sender), which is the capture process name for captured LCRs, the synchronous capture name for persistent LCRs captured by the synchronous capture, or the name of the Oracle Streams Advanced Queuing (AQ) agent for persistent LCRs and persistent user messages enqueued by an application
The name of the object on which the DML operation was run, because errors for other objects might 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 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:
In SQL*Plus, connect to the database as the hr user.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Create the sequence used by the error handler to assign new primary key values:
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 Oracle Streams administrator ALL privilege on the sequence:
GRANT ALL ON reg_exception_s TO strmadmin;
Connect to the database as the Oracle Streams administrator.
Create the errorlog table:
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);
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 ANYDATA, error_stack_depth IN NUMBER, error_numbers IN DBMS_UTILITY.NUMBER_ARRAY, error_messages IN EMSG_ARRAY); END errors_pkg ; /
CREATE OR REPLACE PACKAGE BODY errors_pkg AS
PROCEDURE regions_pk_error (
message IN ANYDATA,
error_stack_depth IN NUMBER,
error_numbers IN DBMS_UTILITY.NUMBER_ARRAY,
error_messages IN EMSG_ARRAY )
IS
reg_id NUMBER;
ad 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 := 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. If you are modifying only one value in the LCR, then the GET_VALUE member function and SET_VALUE member procedure might be more convenient and more efficient.
See Also:
Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session and for information about specific error messages to handle in an error handlerAn 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 can either 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.
Set an 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;
/
If the error handler is already set, then you can run the SET_DML_HANDLER procedure to change the error handler.
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;
/
Note:
Theerror_handler parameter does not need to be specified.The following sections contain instructions for retrying and deleting apply errors:
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions on managing apply errors in Oracle Enterprise Manager
Oracle Streams Replication Administrator's Guide for information about the possible causes of apply errors
You can retry a specific error transaction or you can retry all error transactions for an apply process. You might need to make DML or DDL changes to database objects to correct the conditions that caused one or more apply errors before you retry error transactions. You can also have one or more capture processes or synchronous captures configured to capture changes to the same database objects, but you might not want the changes captured. In this case, you can set the session 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 sessionWhen you retry an error transaction, you can execute it immediately or send the error transaction to a user procedure for modifications before executing it. The following sections provide instructions for each method:
Retrying a Specific Apply Error Transaction Without a User Procedure
Retrying a Specific Apply Error Transaction with a User Procedure
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theEXECUTE_ERROR procedureAfter you correct the conditions that caused an apply error, you can retry the transaction by running the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package without specifying a user procedure. In this case, the transaction is executed without any custom processing.
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,
user_procedure => NULL);
END;
/
If execute_as_user is TRUE, then the apply process executes the transaction in the security context of the current user. If execute_as_user is FALSE, then the apply process executes the transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised.
In either case, the user who executes the transaction must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.
You can retry an error transaction by running the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package, and specify a user procedure to modify one or more messages in the transaction before the transaction is executed. The modifications should enable successful execution of the transaction. The messages in the transaction can be LCRs or user messages.
For example, consider a case in which an apply error resulted because of a conflict. Examination of the error transaction reveals that the old value for the salary column in a row LCR contained the wrong value. Specifically, the current value of the salary of the employee with employee_id of 197 in the hr.employees table did not match the old value of the salary for this employee in the row LCR. Assume that the current value for this employee is 3250 in the hr.employees table.
Given this scenario, the following user procedure modifies the salary in the row LCR that caused the error:
CREATE OR REPLACE PROCEDURE strmadmin.modify_emp_salary(
in_any IN ANYDATA,
error_record IN DBA_APPLY_ERROR%ROWTYPE,
error_message_number IN NUMBER,
messaging_default_processing IN OUT BOOLEAN,
out_any OUT ANYDATA)
AS
row_lcr SYS.LCR$_ROW_RECORD;
row_lcr_changed BOOLEAN := FALSE;
res NUMBER;
ob_owner VARCHAR2(32);
ob_name VARCHAR2(32);
cmd_type VARCHAR2(30);
employee_id NUMBER;
BEGIN
IF in_any.getTypeName() = 'SYS.LCR$_ROW_RECORD' THEN
-- Access the LCR
res := in_any.GETOBJECT(row_lcr);
-- Determine the owner of the database object for the LCR
ob_owner := row_lcr.GET_OBJECT_OWNER;
-- Determine the name of the database object for the LCR
ob_name := row_lcr.GET_OBJECT_NAME;
-- Determine the type of DML change
cmd_type := row_lcr.GET_COMMAND_TYPE;
IF (ob_owner = 'HR' AND ob_name = 'EMPLOYEES' AND cmd_type = 'UPDATE') THEN
-- Determine the employee_id of the row change
IF row_lcr.GET_VALUE('old', 'employee_id') IS NOT NULL THEN
employee_id := row_lcr.GET_VALUE('old', 'employee_id').ACCESSNUMBER();
IF (employee_id = 197) THEN
-- error_record.message_number should equal error_message_number
row_lcr.SET_VALUE(
value_type => 'OLD',
column_name => 'salary',
column_value => ANYDATA.ConvertNumber(3250));
row_lcr_changed := TRUE;
END IF;
END IF;
END IF;
END IF;
-- Specify that the apply process continues to process the current message
messaging_default_processing := TRUE;
-- assign out_any appropriately
IF row_lcr_changed THEN
out_any := ANYDATA.ConvertObject(row_lcr);
ELSE
out_any := in_any;
END IF;
END;
/
To retry a transaction with the transaction identifier 5.6.924 and process the transaction with the modify_emp_salary procedure in the strmadmin schema before execution, run the following procedure:
BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR(
local_transaction_id => '5.6.924',
execute_as_user => FALSE,
user_procedure => 'strmadmin.modify_emp_salary');
END;
/
Note:
The user who runs the procedure must haveSELECT privilege on DBA_APPLY_ERROR data dictionary view.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 specifyNULL 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.You can delete a specific error transaction or you can 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, to delete a transaction with the transaction identifier 5.4.312, run the following procedure:
EXEC DBMS_APPLY_ADM.DELETE_ERROR(local_transaction_id => '5.4.312');
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');
Note:
If you specifyNULL 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.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 Oracle 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 might 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.
See Also: