Implications of setting SP_OCT_REPLICATE_TRIGGER to 1 in a two-way replication (4296892)

The parameter SP_OCT_REPLICATE_TRIGGER is very useful if one intends to replicate CREATE TRIGGER to the target. It facilitates trigger creation on secondary by way of SharePlex replication once it is created on primary. The parameter does have implications if set on the secondary side that happens to be a node in a two-way replication and is due to a bug. The bug only exists in SharePlex 9.1.2 or below. This bug results in CREATE TRIGGER DDLs replicate in the form of infinite loop between the nodes of an active-active replication if the nodes have the parameter SP_OCT_REPLICATE_TRIGGER set to 1.

The following testing illustrates the point.

I have a two way replication set up between two servers as shown:

Source:

sp_ctrl (alvsupl14:9111)> view config config1

datasource:o.ORA11GR2

#source tables      target tables           routing map

sha91.table1        sha91.table1           alvsupl18@o.ora11gr2

sp_ctrl (alvsupl14:9111)> activate config config1

sp_ctrl (alvsupl14:9111)> list param modified capture

Oracle Capture parameters:

Parameter  Name                 Actual Value                           Units   Set At

------------------------------  ------------------------------------  -------  ---------------

SP_OCT_REPLICATE_TRIGGER        1                                     # enhan  Restart Process

   Default   Value:     0

Target:

sp_ctrl (alvsupl18:9111)> view config config1

datasource:o.ora11gr2

#source tables      target tables           routing map

sha91.table1        sha91.table1           alvsupl14@o.ORA11GR2

sp_ctrl (alvsupl18:9111)> activate config config1

sp_ctrl (alvsupl18:9111)> list param modified capture

Oracle Capture parameters:

Parameter  Name                 Actual Value                           Units   Set At

------------------------------  ------------------------------------  -------  ---------------

SP_OCT_REPLICATE_TRIGGER        1                                     # enhan  Restart Process

   Default   Value:     0

I then execute a CREATE TRIGGER via a non-SharePlex user on the trusted (or designated) source as below:

SQL> CREATE OR REPLACE TRIGGER t6

  2    BEFORE

  3      INSERT OR

  4      UPDATE OF num_col OR

  5      DELETE

  6    ON sha91.table1

  7  BEGIN

  8    CASE

  9      WHEN INSERTING THEN

      DBMS_OUTPUT.PUT_LINE('Inserting');

 10   11      WHEN UPDATING('num_col') THEN

 12        DBMS_OUTPUT.PUT_LINE('Updating number column');

        WHEN DELETING THEN

 13   14        DBMS_OUTPUT.PUT_LINE('Deleting');

  END CASE;

 15   16  END;

 17  /

Trigger created.

SQL>

Now, if I view the event log on these two nodes (or instances of SharePlex), I will see an infinite loop showing that the CREATE TRIGGER DDL keeps replicating between these two nodes in an uncontrollable manner. Though the replicated trigger is in a ‘DISABLED’ state, it still inundates the logs and causes unnecessary activity in these two nodes. Imagine if there were more than 2 nodes, such loopback will result in the CREATE TRIGGER DDL messages growing exponentially. Here are the messages seen in both nodes (shown here only for one node):

sp_ctrl (alvsupl18:9111)> show log reverse

Notice   2018-04-20 18:42:23.009634 8610 471447296 s:7 Poster: Replicated DDL "ALTER TRIGGER t6 DISABLE"  (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]

Notice   2018-04-20 18:42:18.985076 8610 473495296 s:2 Poster: Replicated DDL "ALTER TRIGGER t6 DISABLE"  (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]

Notice   2018-04-20 18:42:14.066710 8610 471447296 s:7 Poster: Replicated DDL "ALTER TRIGGER t6 DISABLE"  (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]

Notice   2018-04-20 18:42:10.022626 8610 472471296 s:8 Poster: Replicated DDL "ALTER TRIGGER t6 DISABLE"  (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]

Notice   2018-04-20 18:42:05.132847 8610 473495296 s:2 Poster: Replicated DDL "ALTER TRIGGER t6 DISABLE"  (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]

Notice   2018-04-20 18:39:29.921424 8645 1949853536 Capture: The database supplemental logging state = Enabled, primary_key = Enabled, unique_key = Enabled, foreign_key = Enabled  (capturing from o.ora11gr2) [module oct]

To get out of the above quagmire, one need to stop Capture and Export on the secondary node, reset the parameter SP_OCT_REPLICATE_TRIGGER back to its default value 0 and then restart Capture and Export. This will control the Tsunami of these uncontrollable messages as follows:

sp_ctrl (alvsupl18:9111)> reset param SP_OCT_REPLICATE_TRIGGER

sp_ctrl (alvsupl18:9111)> stop capture

sp_ctrl (alvsupl18:9111)> start capture

So, the moral of the story is, do not set the parameter on the secondary node(s) in a multi-master replication and only set it on primary node. This will result in loopback and consequent infinite loop,  not to mention the excessive logging in SharePlex logs and the overhead involved when such DDLs loopback between node(s). When it is only set on primary node, the purpose is served in the sense that the trigger gets created on the secondary node(s), albeit the trigger is DISABLED to begin with. This is applicable till the bug is resolved.

The bug is expected to be resolved in SharePlex 9.1.3 so that CREATE TRIGGER is never looped back to the primary if such DDL is executed by Post process. This way, even if the CREATE TRIGGER is executed on secondary via a direct user activity and not a SharePlex user, it will loop back to primary but the primary will never send it back to secondary, since Post will apply such DDL on primary by way of 2 way replication. This will avoid infinite loop.