Our previous chapters have presented examples for configuring your basic Streams environments. The configuration ensures the flow of data remains calm and consistent. Now that we have gotten our feet wet in the calm of the current, we understand the underlying principles that we can now build on to move into some white water. This chapter reviews the advanced functionality of Oracle Streams that provides flexibility and maneuverability of data throughout a diverse environment. Please note here that the Authors' intent is to provide a quick reference to these functionalities. In order to attempt to present the myriad of possible scenarios that can be addressed with these functionalities would be similar to attempting to empty Lake Michigan with a two gallon bucket! While the thought of seeing in how many ways we can combine these advanced features to create a totally awesome "water park" of Streams is tempting, the Authors have opted to provide an overview of the advanced features of Streams, with some basic examples of usage, and direction to Oracle documentation allowing our readers to choose which eddies, rapids, and forks they wish to further explore.
In this chapter, we take a quick look at the following advanced features of Streams:
Many of the advanced features addressed in this chapter have been introduced in Chapter 1, All the Pieces: The Parts of an Oracle 11g Streams Environment. Understanding how each feature interacts with the Streams' core components and other features is paramount to not only successfully implementing the feature in your design, but also in controlling the result.
As mentioned in Chapter 1, Synchronous Capture allows you to capture a DML data change (insert, update, delete, and merge) at the source when it is committed, rather than having LogMiner
capture the change from the redo
. While this can provide performance gains, it does so at the cost of some flexibility; as seen in the restrictions listed in Chapter 1. But remember, Synchronous Capture is NOT Synchronous replication as we explain in Chapter 1.
A Synchronous Capture process is a Streams client that uses internal mechanisms (think table triggers) to capture DML changes at the time they are committed to a table. The DML change is converted to an LCR format and enqueued to the capture queue. To avoid duplicating the capture of the same DML, it is highly recommended that you do not configure Synchronous Capture and regular Capture on the same table.
Synchronous Capture rules can only be added to a ruleset via DBMS_STREAMS_ADM.ADD_TABLE_RULES
or DBMS_STREAMS_ADM.ADD_TABLE_RULES
. Attempts to create or add Synchronous Capture rules with any other package procedure will cause the rules to be ignored. For a Synchronous Capture rule, you specify sync_capture
as the value for the streams_type
parameter.
Synchronous Capture rules can only be added to positive rulesets (inclusion_rule = TRUE
).
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.employees', streams_type => 'sync_capture', streams_name => 'capture_sync1', queue_name => 'strm_admin.HR_CAPTURE_Q', include_dml => TRUE, inclusion_rule => TRUE --default ); END; /
The above creates a Synchronous Capture Ruleset with the name of RULESET$_122
and Synchronous Capture with the name CAPTURE_SYNC1
. Please note that in your environment, you may have a different numeric value for your ruleset. You can view information for Synchronous Capture using the DBA_SYNC_CAPTURE
view:
SQL> select * from dba_sync_capture; CAPTURE_NAME QUEUE_NAME QUEUE_OWNER ------------------------------ ----------------------------- ----------------------- RULE_SET_NAME RULE_SET_OWNER CAPTURE_USER ------------------------------ ----------------------------- ----------- CAPTURE_SYNC1 HR_CAPTURE_Q STRM_ADMIN RULESET$_122 STRM_ADMIN STRM_ADMIN
We also see a new rule, EMPLOYEES121
associated with the ruleset in DBA_RULE_SET_RULES:
SQL> select rule_set_owner, rule_set_name, rule_owner, 2 rule_name, rule_set_rule_enabled, rule_set_rule_comment 3 from dba_rule_set_rules 4 where rule_set_name = 'RULESET$_122' 5 ; RULE_SET_OWNER RULE_SET_NAME RULE_OWNER --------------- --------------- --------------- RULE_NAME RULE_SET ------------------------------ -------- RULE_SET_RULE_COMMENT ----------------------------------------------- STRM_ADMIN RULESET$_122 STRM_ADMIN EMPLOYEES121 ENABLED "STRM_ADMIN"."RULESET$_122"
And the EMPLOYEES121
rule information in DBA_RULES
SQL> select rule_owner, rule_name, rule_condition 2 from dba_rules where rule_name = 'EMPLOYEES121'; RULE_OWNER RULE_NAME --------------- ---------------------- RULE_CONDITION -------------------------------------- STRM_ADMIN EMPLOYEES121 (((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'EMPLOYEES')) and :dml.is_null_tag() = 'Y' )
Rulesets can be assigned to one or more Oracle Streams Clients. If you wish to assign a Synchronous Capture ruleset to additional Oracle Synchronous capture clients, once it has been created using one of the two above-mentioned DBMS_STREAMS_ADM
and rule procedures, you can use the DBMS_CAPTURE_ADM. ALTER_SYNC_CAPTURE
to add that ruleset to the specified Synchronous Capture client as well.
BEGIN DBMS_CAPTURE_ADM.ALTER_SYNC_CAPTURE( capture_name => 'CAPTURE_SYNC2', rule_set_name => 'RULESET$_122'), END; /
Note: CAPTURE_SYNC2
would be Synchronous Capture process previously created using the DBMS_STREAMS_ADM.ADD_TABLE_RULES
or the DBMS_STREAMS_ADM.ADD_SUBSET_RULES
.
Once a Synchronous Capture ruleset is created, its conditions can be modified using the DBMS_RULE_ADM
package.
WARNING: Do not modify the :dml.get_object_name
and :dml.get_object_owner
conditions of a Synchronous Capture rule. This could cause the Synchronous Capture rule to not capture changes. However, other conditions can be added, deleted, or modified as desired.
As with regular capture, there can be only one capture user associated with a Synchronous Capture client. That capture user for Synchronous Capture requires explicit execute privileges to perform any custom rule-based transformations (this includes all transformation functions and all packages/procedures/functions invoked by any transformation functions), execute privilege on the synchronous capture rule set, and enqueue privileges on the associated capture queue.
If you decide that you wish to remove a Synchronous Capture rule from a ruleset, you can do so by using the DBMS_STREAMS_ADM.REMOVE_RULE
procedure.
BEGIN DBMS_STREAMS_ADM.REMOVE_RULE( rule_name => 'EMPLOYEES121', streams_type => 'sync_capture', streams_name => 'capture_sync2', ); END; /
You can convert a regular Capture process to a Synchronous Capture process and vice versa, but restrictions do apply. For information on these restrictions, please refer to the Switching From a Capture Process to a Synchronous Capture and Switching from a Synchronous Capture to a Capture Process sections of Chapter 15, in the Oracle Streams Concepts and Administration Guide.
For more information on Synchronous Capture, please refer to the Implicit Capture with Synchronous Capture section in Chapter 2, Managing a Synchronous Capture section in Chapter 15, and Monitoring a Synchronous Capture section in Chapter 24 of the Oracle Streams Concepts and Administration Manual 11gR2. For more information on the DBMS_STREAMS_ADM
and DBMS_CAPTURE_ADM
subprograms mentioned, please refer to the Oracle PL/SQL Packages and Types References Manual.