I am configuring schema level Streams replication between two 11gR2 databases. I will exclude the two compression tables DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP .
I should create a rule for each schema and exclude the tables right?
Thanks in Advance.
you will need to have both positive rule set (schema level rules) and negative rule set (exclusiion of tables not to capture) for the capture process. set inclusion_rule parameter of DBMS_STREAMS_ADM.ADD_TABLE_RULES to FALSE to add rules to a negative ruleset.
and http://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_rules.htm#i1010994 for details.
Hello Mathias, Thanks for your reply.
I will read the Docs you pointed right away.
I wasn't very clear in my question, I will blame it on the lack of coffee.
I actually have the positive rules set and functioning smoothly. the problem I faced is when the database started the automated maintenance tasks specifically the segment adviser.
According to metalink Doc ID 1082323.1 It appears that the "Automated Maintenance Window" jobs Segment Advisor calls dbms_compression which creates two tables called DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP in users schema.
and since these tables are created with nologging option the apply processes aborted.
I have several schemas configured with positive rule set, my question is, should I create the negative rule set for each schema on its own right? or a negative rule for a table will handle the table regardless of in which schema it is being DDLed or DMLed?
Thanks in Advance.
You can create negative rule for two tables only to exclude from replication where you can specify the table name as OWNER.table_name. No need to create negative rule for schema.
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
How To Exclude A Table From Schema Capture And Replication When Using Schema Level Streams Replication [ID 239623.1]
Thanks Tarun for your reply.
I think when the segment adviser is executed for each schema in the database by the Automated maintenance Task, the temp tables are created and dropped under each schema that is being analyzed by the adviser, so each schema would have a OWNER.DBMS_TABCOMP_TEMP_UNCMP and OWNER.DBMS_TABCOMP_TEMP_CMP created and dropped right?
if that's corrected, and the in the rule i will have to specify the owner then I would need a negative rule in each schema right?
what do you think guys?
I will test it on the test environment and let you guys know the results
sorry for the late reply guys,
I initially disable the automated segment adviser to avoid the error temporarily. it worked fine, I was further continuing tests when I face an unpublished bug, when a distributed transaction fails, the capture to aborts and you'll have a LogMiner fatal error followed by ORA-600 krvuatla20 error.
metalink recommend to install 18.104.22.168 or upgrade to this version. so I'm in the middle of that now. I will post final results as soon as I finish.
thanks for your help guys.,