Getting GoldenGate Heartbeats Working with SUPPRESSTRIGGERS

Mar 9, 2012 / By Marc Fielding

Tags: , , ,

Those of you who have seen me present about GoldenGate will know that I recommend using a heartbeat table to monitor GoldenGate lag. The heartbeat table is a great way to monitor GoldenGate replication because it can follow a single SQL insert through each major GoldenGate replication process, and report the replication lag attributable to each. And if you haven’t seen my presentation, I’m getting a revised and updated version ready for Collaborate 2012 this April.

However, this monitoring script relies on triggers, so if you have DBOPTIONS SUPPRESSTRIGGERS enabled, it will cause the entire replicat to error out with primary key violation errors. The issue prompted me to look into how this option is actually implemented.

According to the GoldenGate reference guide, DBOPTIONS SUPPRESSTRIGGERS requires Oracle version 10.2.0.5+ or 11.2.0.2+ (but not 11gR1), as well as privileges granted via DBMS_STREAMS_AUTH or DBMS_GOLDENGATE_AUTH.

I kicked off a replicat process with DBOPTIONS SUPPRESSTRIGGERS under truss, which traces and reports system calls, as well as filedescriptor I/O. I told truss to report all traffic written to filedescriptor 11, which happens to be the Oracle database connection.

truss -f -r 11 ./replicat PARAMFILE dirprm/replicat.prm
...
8508/1:           \0\0\0\0 K B E G I N   s y s . d b m s _ x s t r e a m _ g g . S
8508/1:            E T _ F O O _ T R I G G E R _ S E S S I O N _ C O N T X T   ( f
8508/1:            i r e = > T R U E ) ;   E N D ;\0\0\001\0\0\001\0\0\0\0\0\0\0\0

The fragment from the filedescriptor writes contains a call to an undocumented PL/SQL package, dbms_xstream_gg. Better formatted, the call is:

BEGIN sys.dbms_xstream_gg.set_foo_trigger_session_contxt ( fire => true );
/

Testing this out in my own session. Note I’m using “ggs” as my GoldenGate user; modify this if you’re using something else.

SQL> conn ggs
Enter password:
Connected.
SQL> create table trigger_test as select * from dual;

Table created.

SQL> create or replace trigger trigger_test_aiu
after insert or update on trigger_test
begin
        dbms_output.put_line('Trigger body');
end;
/
  2    3    4    5
Trigger created.

SQL> set serveroutput on
SQL> insert into trigger_test select * from dual;
Trigger body

1 row created.

So we can see that the text “Trigger body” is printed every time trigger_test is inserted into. Now trying a call to set_foo_trigger_session_contxt:

SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);

PL/SQL procedure successfully completed.

SQL> insert into trigger_test select * from dual;

1 row created.

Note that we don’t see “Trigger body” anymore: the trigger didn’t run.

The next question: how do we integrate this with the heartbeat infrastructure? We can use SQLEXEC to call a PL/SQL stored procedure, and the EXEC SOURCEROW option tells GoldenGate to run it for every row processed for the heartbeat table. Running a stored procedure for every row isn’g a good idea for high-volume tables, however with one insert every 5 minutes, it works for the heartbeat table. The problem with SQLEXEC SPNAME is that it has a limited set of stored procedure parameters that map to row values and built-in GoldenGate functions, and I couldn’t find any that simply returns the FALSE that dbms_xstream_gg.set_foo_trigger_session_contxt expects. So I created a wrapper function instead, and it doesn’t require any parameters at all:

grant execute on dbms_xstream_gg to ggs;
create or replace procedure ggs.triggers_on is
begin
    sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>false);
end;
/

The grant is required because the permissions on dbms_stream_gg are granted through a role, and PL/SQL requires more direct grants.

The SQLEXECs themselves get added to the replicat configuration for the heartbeat table, HB_Rep.inc. There are actually two replicats: one for th heartbeat table itself, and another to populate heartbeat history. I add a SQLEXEC to each: (since the stored procedure is listed twice, a unique ID is mandatory)

MAP GGS.GGS_HEARTBEAT, TARGET GGS.GGS_HEARTBEAT,
SQLEXEC (ID heartbeat SPNAME triggers_on, NOPARAMS, EXEC SOURCEROW),
...
MAP GGS.GGS_HEARTBEAT, TARGET GGS.GGS_HEARTBEAT_HISTORY,
SQLEXEC (ID history SPNAME triggers_on, NOPARAMS, EXEC SOURCEROW),
...

Now that we have the triggers turned off, how do we turn them back off? A limitation of SQLEXEC is that it runs before the actual row operation, not after. I worked around this by adding the snippet to the end of the trigger code itself to disable triggers once it is run.

CREATE OR REPLACE TRIGGER GGS.GGS_HEARTBEAT_TRIG
BEFORE INSERT OR UPDATE ON GGS_HEARTBEAT
FOR EACH ROW
BEGIN
...
-- Disable trigger execution now that the trigger has fired
sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);
END ;
/

 

CREATE OR REPLACE TRIGGER GGS.GGS_HEARTBEAT_TRIG_HIST
BEFORE INSERT OR UPDATE ON GGS_HEARTBEAT_HISTORY
FOR EACH ROW
BEGIN
...
-- Disable trigger execution now that the trigger has fired
sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);
END ;
/

Be careful testing inserts into the heartbeat tables from non-GoldenGate sessions, as it will cause triggers to stop firing for your session until you log out or call sys.dbms_xstream_gg. set_foo_trigger_session_contxt( fire=>false );

A kind analyst at Oracle Support pointed out later that this could also be done with duplicate MAP statements as documented in note 969538.1; the drawback of this alternative is that we need to turn on ALLOWDUPTARGETMAP, disabling what I consider an important sanity check for duplicate MAP statements.

Turning on heartbeat generation and running the replicat, it no longer errors out. It generates detailed current and historical lag data, leaving the SUPPRESSTRIGGERS configuration intact and working.

5 Responses to “Getting GoldenGate Heartbeats Working with SUPPRESSTRIGGERS”

  • […] recently blogged about an issue getting GoldenGate heartbeats to work with SUPPRESSTRIGGERS, and I thought the findings would be of interest beyond the GoldenGate community. In short, […]

  • Roman says:

    The other option is to mark trigger firing property:

    exec dbms_ddl.set_trigger_firing_property('GGATE_OWNER','GGS_HEARTBEAT_TRIG_HIST', FALSE);
    exec dbms_ddl.set_trigger_firing_property('GGATE_OWNER','GGS_HEARTBEAT_TRIG', FALSE);

  • Marc Fielding says:

    Hi Roman,

    Thanks for the comment! My understanding of dbms_ddl.set_trigger_firing_property is that it can set a trigger to fire normally, or to fire once. But not to stop firing entirely. According to the docs, it only applies to changes from streams apply processes as well:

    “if a user session or an application makes a change, then the trigger continues to fire, regardless of the firing property.”

  • Roman says:

    this is from GG12c Doc:


    SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS
    Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)

    SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.

    To allow a specific trigger to fire, you can use the following SQLEXEC statement in the Replicat parameter file, where trigger_owner is the owner of the trigger and trigger_name is the name of the trigger.

    SQLEXEC ‘dbms_ddl.set_trigger_firing_property(trigger_owner “trigger_name”, FALSE);’
    Note:
    Once this SQLEXEC is executed with FALSE, the trigger will continue to fire until the command is run again with a setting of TRUE.

    NOSUPPRESSTRIGGERS allows target triggers to fire. To use [NO]SUPPRESSTRIGGERS, the Replicat user must have the privileges granted through the dbms_goldengate_auth.grant_admin_privilege package. This procedure is part of the Oracle database installation. See the database documentation for more information.

    The USERID or USERIDALIAS parameter must precede a DBOPTIONS statement that contains SUPPRESSTRIGGERS or NOSUPPRESSTRIGGERS.

    • Roman says:

      Sorry, mismatched the tags.

      For GG12c i’ve changed firing property for heartbeat triggers. And now they are fired normally.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>