5 Replies Latest reply on May 11, 2014 3:20 AM by Renu-Oracle

    Discard/Skip/Ignore DML Transactions with Asynchronous CDC

    olunebur

      Dear all,

       

      We have configured CDC with LogMiner in our DB's and we are wondering how can we discard Specific DML statements in CDC. Our goal is to avoid propagating changes that affects whole tables and are unrelevant for the target Datastores.

       

      We have tried with stopping and restarting both Apply and Capture processes (dbms_apply_adm.stop_apply,dbms_capture_adm.stop_capture) without any success and neither altering the change set works.

       

      BEGIN

      DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(

      change_set_name => 'XXXXX',

      enable_capture => 'N');

      END;

       

      in the past we used "begin  dbms_apply_adm.set_parameter('XXXXXX','_ignore_transaction','1243213421')" to remove the capture of troublemaking queries,  but we cannot identify the transactions unless it is registered in Alert file or in the SPILL tables.

       

      Do you have any Idea of how can we achieve that? Our goal is avoid those changes to be propagated to the change tables.

       

       

       

      Thanks in advance for your help

        • 1. Re: Discard/Skip/Ignore DML Transactions with Asynchronous CDC
          Renu-Oracle

          Hi,

           

          In streams, DBMS_STREAMS.SET_TAG is used to ignore changes in the tagged sessions in the source database.


          But for a Change Data Capture configuration, the changes are still captured and applied because as far as CDC rules are concerned, the default rule settings does not include the clause [dml.is_null_tag = 'Y'] at its rule condition, therefore tagging a LCR will have no effect when the CDC Capture processes the LCR, and it will be captured anyway.

          The solution here is to "add" a new rule, in this case, a negative rule, either for the entire schema (DBMS_STREAMS_ADM.ADD_SCHEMA_RULES) or for each table (DBMS_STREAMS_ADM.ADD_TABLE_RULES) you plan to skip LCRs later using the SET_TAG.

          For instance, your negative rule would contain following parameters:

              inclusion_rule => FALSE
              and_condition => ':lcr.get_tag() = HEXTORAW(''50'')'


          The  "inclusion_rule => false"   makes it a negative, and the and_condition defines the TRUE condition to ignore the LCR, in this case when SET_TAG is set to "50".

          There should be no performance impact whatsoever.

            References :
               Negative rule to Capture
               Oracle Streams Tags


          Thanks,

          Renu

          1 person found this helpful
          • 2. Re: Discard/Skip/Ignore DML Transactions with Asynchronous CDC
            olunebur

            Hi Renu,

             

            I've tried this, but seems that is not working properly:

             

            BEGIN

                table_name      =>  'MY_TABLE',

                streams_type    =>  'capture',

                streams_name    =>  'CAPTURE_NAME',

                queue_name      =>  'QUEUE_NAME',

                inclusion_rule  =>  FALSE,

               and_condition => ':lcr.get_tag() = HEXTORAW(''50'')'

                );

            END;

            ____________________________________________________

            begin

            DBMS_STREAMS.SET_TAG(tag  =>  HEXTORAW('50'));

            update MY_TABLE set FIELD=FIELD where rownum<100;

              commit; 

            end;

            I've tried also with creating the rule, this way, but seems useless.

             

            BEGIN

              

              DBMS_STREAMS_ADM.ADD_TABLE_RULES(

              

                table_name      =>  'MY_TABLE',

                streams_type    =>  'capture',

                streams_name    =>  'CAPTURE_NAME',

                queue_name      =>  'CAPTURE_NAME',

                inclusion_rule  =>  FALSE,

               

                include_tagged_lcr => false

                );

             

            END;

             

            Regards

            • 3. Re: Discard/Skip/Ignore DML Transactions with Asynchronous CDC
              Renu-Oracle

              Hi,

               

              Was it working earlier?

              Can you try to test the same on other tables?

               

              Thanks,

              Renu

              • 4. Re: Discard/Skip/Ignore DML Transactions with Asynchronous CDC
                olunebur

                Hi,

                 

                I finally did it with this syntax:

                 

                BEGIN

                  

                  DBMS_STREAMS_ADM.ADD_TABLE_RULES(

                  

                    table_name      =>  'OWNER.TABLE',

                    streams_type    =>  'capture',

                    streams_name    =>  'STREAM_NAME',

                    queue_name      =>  'QUEUE_NAME',

                    inclusion_rule  =>  FALSE,

                    and_condition => ':lcr.get_tag() = HEXTORAW(''50'')',

                    include_tagged_lcr => TRUE

                    );

                 

                END;

                --------

                For the queries I want to discard I use this:

                 

                begin

                DBMS_STREAMS.SET_TAG(tag  =>  HEXTORAW('50'));

                commit;

                 

                DML STATEMENTS........

                commit;

                DBMS_STREAMS.SET_TAG(tag  =>  null);

                end;