Test Negtive Role Set in a stream environmnet- 10g_new_feature

Below is the process of the testing.

Primary Side. O01DMS0
Target Side. O01LEE3

[@more@]

PURPOSE
-------
This Notes explains how to exclude (filter out) specific tables from
the capture process and replication, when using schema level Streams
replication.

SCOPE & APPLICATION
-------------------
To be used by DBA's as a reference when configuring and implementing
the Oracle Streams capture process for schema replication.

PROBLEM
-------
You have configured the Streams capture process at the schema level for
replication and you want to exclude a specific table in the schema from
the capture process and replication.

SOLUTION
--------
The Streams manual has information on how to accomplish this in
Chapter 15 Managing Rules and Rule-Based Transformations, Altering a Rule.

10g New Feature - Negative Rule Set
-----------------------------------
10G provides negative rule sets, which would be useful for this situation.
This is a new 10g feature. You can define a negative rule set to
eliminate unwanted tables from the capture process.

For example, the negative rule condition here eliminates table
'unwantedtable' in the SCOTT schema. Use ADD_TABLE_RULES to
specify the table. Specify the inclusion_rule => FALSE clause
in the ADD_TABLE_RULES command to place the rule in the
negative rule set.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(
table_name => 'hr.unwantedtable',
streams_type => 'capture',
streams_name => 'strmadmin_capture',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'dbs1.net',
inclusion_rule => false --specifies the negative rule set
);
END;
/

A Streams client with a negative rule set, but no positive rule set,
discards a message if any rule in the negative rule set evaluates to
TRUE for the message. However, if all of the rules in a negative
rule set evaluate to FALSE for the message, then the Streams client
performs its task for the message.

You can associate a positive and a negative rule set with a Streams client.
In a replication environment, a Streams client performs an action if an
LCR satisfies its rule sets. In general, a change satisfies the rule sets
for a Streams client if no rules in the negative rule set evaluate to TRUE
for the LCR, and at least one rule in the positive rule set evaluates to TRUE
for the LCR. If a Streams client is associated with both a positive and
negative rule set, then the negative rule set is always evaluated first.
See 10.2 Streams Concepts and Administration
Chapter 6 How Rules Are Used in Streams

Below is the process of the testing.

Primary Side. O01DMS0
Target Side. O01LEE3

###########################################################################################

1 Stop the stream.

1.1> Stop the capture process at primary side.

SQL> begin
2 dbms_capture_adm.stop_capture(
3 capture_name => 'capture_primary');
4 end;
5 /

PL/SQL procedure successfully completed.

begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_primary');
end;
/

1.2> Stop the apply process at Target Side.

SQL> begin
2 dbms_apply_adm.stop_apply(
3 apply_name => 'apply_lee');
4 end;
5 /

PL/SQL procedure successfully completed.

begin
dbms_apply_adm.stop_apply(
apply_name => 'apply_lee');
end;
/

###########################################################################################

2 Add rule to current steam environment at primary side.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.az',
streams_type => 'capture',
streams_name => 'capture_primary',
queue_name => 'strmadmin.primary_queue',
include_dml => true,
include_ddl => true,
source_database => 'O01DMS0.LOCAL.COM',
inclusion_rule => false);
END;
/

###########################################################################################
3 Start the stream.

3.1> Start apply process at Target Side.

SQL> begin
2 dbms_apply_adm.start_apply(
3 apply_name => 'apply_lee');
4 end;
5 /

PL/SQL procedure successfully completed.

begin
dbms_apply_adm.start_apply(
apply_name => 'apply_lee');
end;
/

3.2> Start capture process at primary side.

SQL> begin
2 dbms_capture_adm.start_capture(
3 capture_name => 'capture_primary');
4 end;
5 /

PL/SQL procedure successfully completed.

begin
dbms_capture_adm.start_capture(
capture_name => 'capture_primary');
end;
/

###########################################################################################
4 Testing

4.1> At primary side.

SQL> insert into az values (1000,'A row excluded from replication');

1 row created.

SQL> commit;

Commit complete.

SQL> create table ax (id number);

Table created.

SQL>
SQL> select * from az;

ID NAME
---------- --------------------------------
111 Test 2
1000 A row excluded from replication
1
11

4.2> At target side.

SQL> select * from az;

ID NAME
---------- --------------------------------
1
11
111 Test 2

SQL> select * from az;

ID NAME
---------- --------------------------------
1
11
111 Test 2

SQL> desc ax
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NUMBER

We can see table AZ is no longer in sync with the one at primary side.
SQL> select * from az;

ID NAME
---------- --------------------------------
1
11
111 Test 2

SQL>