If you ever tried to use dbms_streams.set_tag function to avoid replicating DDL or DML in your Streams environment, we’ll you unpleasantly surprised that it doesn’t work (I hope you didn’t try it on production right way ).
This post deals with steps you have to implement to get it working.
First, every entry in the redo log files has a tag associated with it. The default value for this tag is null. The capture process default behavior is not to check for tags at all, in other words, it will read and process every entry in the redo log regardless of it’s tag value. But what we want is for the capture process to read and process null tags only and ignore non-null tags (these are the DML or DDL that will be skipped).
So the question, how to change the capture process behavior so it will ignore non-null tags?
The answer is by adjusting the capture rules for DML & DDL (depends on whether you need to ignore both operations).
The following scripts assume you have the default Streams configuration without adding any additional custom rules.
Get Capture Rule name and rule condition.
select rule_name, rule_condition
where rule_set_owner = ‘STRMADMIN’
and streams_type = ‘CAPTURE’;
It should return 2 rows, 1 rule for DML and the other for DDL.
Here how the DDL capture rule looks like before changing:
((((:ddl.get_object_owner() = ‘schema name’ or :ddl.get_base_table_owner() = ‘schema name’) and :ddl.get_source_database_name() = ‘database name’ )) and (:ddl.get_compatible() <= dbms_streams.compatible_10_2))
Now add condition to capture null tags only
rule_name => ‘CWSAPP66’,
condition => ‘((((:ddl.get_object_owner() = ”CWSAPP” or :ddl.get_base_table_owner() = ”CWSAPP”) and :ddl.get_source_database_name() = ”CWSRD” ))
and (:ddl.get_compatible() <= dbms_streams.compatible_10_2) and (:ddl.is_null_tag() = ”Y”))’,
evaluation_context => NULL);
Now from a new session
DDL or DML changes will not get replicated
dbms_streams.set_tag(null); — go back to replicating everything
Remember if using bi-directional replication, you have to adjust capture rules in every site.
Senior Oracle DBA