Oracle9i Streams Release 2 (9.2) Part Number A96571-01 |
|
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.
This section contains instructions for creating, starting, stopping and dropping an apply process.
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_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 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:
|
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:
strm01_apply
that applies captured events to the local database. The apply process is created only if it does not already exist.strm01_queue
SYS.STREAMS$_EVALUATION_CONTEXT
evaluation context. The rule set name is specified by the system.hr
schema. The rule name is specified by the system.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.
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 => '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:
strm02_apply
. An apply process with the same name must not exist.strm01_queue
strm01_rule_set
ddl_handler
PL/SQL procedure in the hr
schema.hr
, and not the user who is running the CREATE_APPLY
procedure (the Streams administrator).apply_database_link
parameter is set to NULL
.'5'
.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:
strm03_apply
. An apply process with the same name must not exist.strm01_queue
strm02_rule_set
mes_handler
PL/SQL procedure in the hr
schema.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 strm01_apply
:
BEGIN DBMS_APPLY_ADM.DROP_APPLY( apply_name => 'strm01_apply'); END; /
This section contains instructions for completing the following tasks:
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; /
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
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:
oe.orders
table. The rule name is specified by the system.oe.orders
table. The rule name is specified by the system.dbs1.net
source database.
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.
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; /
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; /
See Also:
|
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.
This section contains instructions for setting and removing the message handler for an apply process.
See Also:
|
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; /
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, setting, and removing 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:
COMMIT
or ROLLBACK
statements. Doing so may endanger the consistency of the transaction that contains the LCR.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.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.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; /
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; /
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; /
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 |
See Also:
|
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; /
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; /
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; /
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 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:
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, 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) ; 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; /
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; /
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; /
This section contains instructions for setting and removing the 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; /
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; /
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.
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:
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; /
See Also:
|
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; /
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; /
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.
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 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 |
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 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 |
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:
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:
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_TABLE_INSTANTIATION_SCN
, SET_SCHEMA_INSTANATIATION_SCN
, and SET_GLOBAL_INSTANTIATION_SCN
procedures in the DBMS_APPLY_ADM
package.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
.
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.
A full database export and full database import sets the following instantiation SCNs at the import database:
A full database or schema level export and schema level import sets the following instantiation SCNs at the import database:
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:
|
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:
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.
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; /
See Also:
|
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
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:
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:
|
If you decide to reset the start SCN for the existing capture process to perform point-in-time recovery, then complete the following steps:
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. |
The following statement is an example of the query to perform:
SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
STOP_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package.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; /
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_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package.If you decide to create a new capture process to perform point-in-time recovery, then complete the following steps:
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.
The following statement is an example of the query to perform:
SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
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.
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_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_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package.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;
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 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.
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.
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.
|
![]() Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|