Having worked a lot with Oracle Streams, I decided to utilize DBMS_STREAMS_ADM.SET_TAG for that task.
I found a relevant MOS document How to exclude ddl in IE (integrated extract) issued from a specific user? (Doc ID 2107293.1) pretty fast.
The document suggested putting TRANLOGOPTIONS EXCLUDETAG into an extract parameter file and than all DDL statements preceeding with DBMS_STREAMS_SET.TAG should not be replicated.
Unfortunately the provided solution did not work for me.
I used OGG Server 12.2.0.1.160823 on Linux x86-64 (which means that I applied the latest bundle patch available at the moment).
The issue can be easily reproduced.
The database version used in this test was 12.1.0.2.160719.
Here are my extract and replicat parameter files:
GGSCI (misha2 as tc_ogg_replicat@db2) 112> view params etag EXTRACT ETAG USERIDALIAS db1_tc_ogg_extract TRANLOGOPTIONS EXCLUDETAG 34 LOGALLSUPCOLS EXTTRAIL ./dirdat/tc DDL INCLUDE ALL TABLE TC.*; GGSCI (misha2 as tc_ogg_replicat@db2) 113> view params rtag REPLICAT RTAG USERIDALIAS db2_tc_ogg_replicat MAP TC.*, TARGET TC.*;Test schema TC will be used for demonstration and I setup the extract to exclude any LCRs with tag 34.
Then I am going to create and start the OGG processes with the following script:
I used two databases in my setup - db1 and db2 that I named SOURCE and TARGET in the rest of this blog post for the sake of clarity.
The extract configured to capture the changes from db1 database and write data to trail files.
The replicat reads the trail files and applies the changes into db2 database.
Next I am about to run the following code in the source database from which the extract captures changes:
Havind done that, I got next results in SOURCE database:
TC@SOURCE> select * from t;
ID MSG
---------- ----------
1 NO TAG
2 TAG 12
3 TAG 34
4 TAG 56
And that in TARGET:
TC@TARGET> select * from t order by id;
ID MSG
---------- ----------
1 NO TAG
2 TAG 12
4 TAG 56
Notice that an insert of a record with ID=3, MSG="TAG 34" was not replicated because we have filtered out that record on the extract.That works flawlessly being executed for DML, but it does not work for DDL:
TC@SOURCE> exec dbms_streams_adm.set_tag( hextoraw('12'))
PL/SQL procedure successfully completed.
TC@SOURCE>
TC@SOURCE> alter table t add tag12 int;
Table altered.
TC@SOURCE>
TC@SOURCE> exec dbms_streams_adm.set_tag( hextoraw('34'))
PL/SQL procedure successfully completed.
TC@SOURCE>
TC@SOURCE> alter table t add tag34 int;
Table altered.
TC@SOURCE> describe t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
MSG VARCHAR2(10)
TAG12 NUMBER(38)
TAG34 NUMBER(38)
I have added two columns to the table and the second one, TAG34, should not have been replicated.But in fact, both of the commands were replicated:
TC@TARGET> describe t Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) MSG VARCHAR2(10) TAG12 NUMBER(38) TAG34 NUMBER(38)It means that aforementioned MOS document How to exclude ddl in IE (integrated extract) issued from a specific user? (Doc ID 2107293.1) does not work in that configuration which, by the way, I think is quite typical.
Luckily, Oracle Support published another note after we concluded our work on the SR: EXCLUDETAG Issue With Integrated Extract While Excluding DDL For Specific User (Doc ID 2185538.1).
We need to put the following line into an extract parameter file to get it working: tranlogoptions _dbfilterddl
GGSCI (misha2 as tc_ogg_replicat@db2) 168> view params etag EXTRACT ETAG USERIDALIAS db1_tc_ogg_extract TRANLOGOPTIONS EXCLUDETAG 34 TRANLOGOPTIONS _dbfilterddl LOGALLSUPCOLS EXTTRAIL ./dirdat/tc DDL INCLUDE ALL TABLE TC.*;Having added that, the same code adding two columns worked as it should:
TC@SOURCE> describe t Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) MSG VARCHAR2(10) TAG12 NUMBER(38) TAG34 NUMBER(38) TC@TARGET> describe t Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) MSG VARCHAR2(10) TAG12 NUMBER(38)It means that TAG34 column, being added with tag 34, was not replicated.
There is also a new line in a ggserr.log file that was not present when I started the extract without _dbfilterddl:
Logmining server DDL filtering enabled.
tl;dr
EXCLUDETAG parameter does not work when a tag is set using DBMS_STREAMS_ADM.SET_TAG in OGG 12.2.0.1.160823. It prevents replicating only DML commands and does not restrict DDL commands from being replicated.
We could use underscope parameter _dbfilterddl in the extract parameter file like in the following line:
TRANLOGOPTIONS _dbfilterddlThis way we may restrict both DDL and DML commands from being replicated when the appropriate tag is set (or any in case we use "EXCLUDETAG +" in the extract parameter file).
