Getting GoldenGate Heartbeats Working with SUPPRESSTRIGGERS

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.