Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Streams Apply Handlers

Oracle Tips by Burleson 

Oracle Streams

Apply Process Configuration

  • Apply process architecture has three main components. They are the reader server, coordinator server and apply server.
     

  • Apply processes can be created by several different procedures. Apply processes can also be extended by setting up Apply handlers. Examples were included for how to use the add_table_rules procedure and the add_subset_rules procedure to create the Apply process.
     

  • Oracle provides many useful procedures that can be used to alter the attributes of the Apply process and its behavior.
     

  • To manage the Apply process, to stop and to start, and to handle the error transaction, there are many procedures. Information on these procedures has been included.

 

start_apply Procedure

The start_apply procedure instructs the Apply process to start applying events. When the Apply process is stopped for any maintenance or disabled for some error situation, the Apply process will have to be started manually. The start_apply procedure is used to achieve this result. The syntax for the procedure is shown below:

Syntax :
DBMS_APPLY_ADM.START_APPLY(
apply_name IN VARCHAR2);

For example, to start the Apply process named LN4_APPLY, the following SQL block can be used:

PROMPT Starting the Apply Process
begin
dbms_apply_adm.start_apply (apply_name => 'LN4_APPLY' ) ;
end ;
/

stop_apply Procedure

The stop_apply procedure stops the Apply process from applying events and rolls back any unfinished transactions being applied.

Syntax :
DBMS_APPLY_ADM.STOP_APPLY(
apply_name IN VARCHAR2,
force IN BOOLEAN DEFAULT false);

If the force parameter is set to TRUE, it stops the Apply process as soon as possible. If it is set to FALSE, it stops the Apply process after ensuring that there are no gaps in the set of applied transactions. The behavior of the Apply process depends on the setting specified for the force parameter and the setting specified for the commit_serialization Apply process parameter.

For example, to start the Apply process named LN4_APPLY, use the following SQL block:

PROMPT Starting the Apply Process
begin
dbms_apply_adm.stop_apply (apply_name => 'LN4_APPLY' ) ;
end ;
/

Setting the Apply Parameters

  • startup_seconds: The maximum number of seconds to wait for another instantiation of the same Apply process to finish.

  • time_limit: The Apply process stops as soon as possible after the specified number of seconds since it started.

  • trace_level: Set this parameter only under the guidance of Oracle Support Services.

  • transaction_limit: The Apply process stops after applying the specified number of transactions.

  • write_alert_log: If Y, the Apply process writes a message to the alert log on exit.

The following is the syntax of the procedure that can be used to set parameters:

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN VARCHAR2);

For example, the following procedure sets the parallelism parameter for a capture process named NY1_CAPTURE to 3.

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'ln4_apply',
parameter => 'parallelism',
value => 3 );
END;

The next chapter will focus on information about setting up Apply handlers. Examples, as well as details on how apply handlers help control the replication process, are included.

Apply Process Configuration

set_key_columns Procedure

Syntax
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name IN VARCHAR2,
{ column_list IN VARCHAR2, |
column_table IN DBMS_UTILITY.NAME_ARRAY, }
apply_database_link IN VARCHAR2 DEFAULT NULL);

Apply Process Configuration

set_dml_handler Procedure

user_procedure specifies the user-defined procedure that is invoked during the apply for the specified operation on the specified object.

set_enqueue_destination Procedure

The set_enqueue_destination procedure sets the queue such that an event that satisfies the specified rule is enqueued automatically by an Apply process.

Syntax
DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
rule_name IN VARCHAR2,
destination_queue_name IN VARCHAR2);
set_execute Procedure

The set_execute procedure specifies whether an event that satisfies the specified rule is executed by an Apply process.

Syntax :
DBMS_APPLY_ADM.SET_EXECUTE(
rule_name IN VARCHAR2,
execute IN BOOLEAN);

set_update_conflict_handler Procedure

The set_update_conflict_handler procedure adds, modifies, or removes an update conflict handler for the specified object.

Apply Process Configuration

set_dml_handler Procedure

The set_dml_handler procedure sets a user procedure as a DML handler for a specified operation on a specified object. The user procedure, acting as the handler, actually alters the apply behavior for the specified operation on the specified object. Whenever the intention is to perform extra action on the transaction, the handlers are used.

Syntax
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name IN VARCHAR2,
object_type IN VARCHAR2,
operation_name IN VARCHAR2,
error_handler IN BOOLEAN DEFAULT false,
user_procedure IN VARCHAR2,
apply_database_link IN VARCHAR2 DEFAULT NULL,
apply_name IN VARCHAR2 DEFAULT NULL);

Object Name is the table where the DML is intended. operation_name values can be INSERT, UPDATE, DELETE and LOB_UPDATE.

When error_handler is set to TRUE, the specified user procedure is run when a row LCR involving the specified operation on the specified object raises an Apply process error.


Chapter 6 -
Apply Handlers

In this chapter, the extensions to the Apply process will be presented through the creation of suitable user-defined procedures called the Apply handlers. In earlier chapters, the fact that the Apply process is an optional Oracle background process was presented.  The Apply process dequeues logical change records and user messages from a specific queue. 

The following sections also cover some sample procedures that are used as the handler routines.

Apply Process and Apply Handlers

The Apply process dequeues the LCRs and user messages from a queue. When the Apply process dequeues, the events may be applied to database objects directly as transactions or may be passed on as the parameters to user-defined procedures.  These procedures are the user-defined Apply handlers. The database changes may include the DML or DDL changes.

Figure 6.1 Apply Process and Apply Handlers

As shown in Figure 6.1, Apply handlers typically include a DML handler, a DDL handler, a Message handler and Pre-Commit handlers. The concept of handlers has been introduced in previous chapters.
 

Apply Process and Apply Handlers

Table 6.1 shows the summary of event processing options.

Apply Handler

Event Type

Default Apply Behavior

Scope

DML or Error Handler

Row LCR

Execute DML

One operation on one table

DDL Handler

DDL LCR

Execute DDL

Entire Apply process

Precommit Handler

Commit Directive (Row LCR and Non-LCR User message)

Commit transactions

Entire Apply process

Message Handler

Non-LCR User message

Creates error, if no handler is specified

Entire Apply process

Table 6.1 Summary of event processing options

DML and the Error Handler process row LCRs and execute the DML transactions. DML handlers act on a table and are specific to an SQL operation. If the SQL operation such as Delete, Update or Insert is not specified, the handler is applicable to all the operations on the table.

 

Apply Process and Apply Handlers

The DDL handler processes the DDL LCR events and executes the DDL operation. The scope of the DDL handler is for a given Apply process. Thus, there is only one DDL handler for an Apply process.

In case of a pre-commit handler, the commit directive for transactions which include the Row LCR(s) or non-LCR user messages is processed. The scope of the pre-commit handler is the entire Apply process.

The Message handler processes the non-LCR user messages and operates at the Apply process level.

The following guidelines are applicable to the Apply handlers:

  • DML handlers, DDL Handlers, and message handlers can execute an LCR by calling the LCR's EXECUTE member procedure.
     

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

  • When needed, an Apply handler can set a Streams session tag.
     

  • An Apply handler may call a Java stored procedure that is published or wrapped in a PL/SQL procedure.
     

  • Ensure that the procedure that is specified as the handler is available and is valid. If an Apply process tries to invoke an Apply handler that does not exist or is invalid, the Apply process aborts.
     

  • If an Apply handler invokes a procedure or function in an Oracle-supplied package, the user running the Apply handler must have direct EXECUTE privilege on the package. It is not sufficient to grant this privilege through a role.

 


The above text is an excerpt from:

Oracle Streams
High Speed Replication and Data Sharing

ISBN 0-9745993-5-2

by Madhu Tumma
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks