In March 2012, I put together a blog post entitled Disabling Oracle triggers on a per-session basis, outlining a way to suspend trigger execution for the current session through a PL/SQL call. Commenter Bryan posted a comment saying he couldn’t get it working in 220.127.116.11:
Unfortunately Oracle seems to have disabled this use in 18.104.22.168, and most likely 12.1 as well. Boo-Hiss! This is needed functionality for DBAs!
A new parameter: enable_goldengate_replication
I tried this on an Oracle 22.214.171.124 system, and I indeed got an error:
SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); BEGIN sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); END; * ERROR at line 1: ORA-26947: Oracle GoldenGate replication is not enabled. ORA-06512: at "SYS.DBMS_XSTREAM_GG_INTERNAL", line 46 ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 13 ORA-06512: at line 1
A quick look at
oerr gives a path forward, assuming you do indeed have a GoldenGate license:
[[email protected] ~]$ oerr ora 26947 26947, 00000, "Oracle GoldenGate replication is not enabled." // *Cause: The 'enable_goldengate_replication' parameter was not set to 'true'. // *Action: Set the 'enable_goldengate_replication' parameter to 'true' // and retry the operation. // Oracle GoldenGate license is needed to use this parameter.
The Oracle reference gives a bit more info
ENABLE_GOLDENGATE_REPLICATION controls services provided by the RDBMS for Oracle GoldenGate (both capture and apply services). Set this to true to enable RDBMS services used by Oracle GoldenGate.
The RDBMS services controlled by this parameter also include (but are not limited to):
Service to suppress triggers used by GoldenGate Replicat
As do the GoldenGate 12.1.2 docs:
The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for an Oracle 126.96.36.199 database. This is required for all modes of Extract and Replicat.
To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.
So here goes nothing:
SQL> alter system set enable_goldengate_replication=true; System altered. SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); BEGIN sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_XSTREAM_GG_INTERNAL", line 46 ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 13 ORA-06512: at line 1
Another error: missing privileges. I checked and double-checked that the required GoldenGate privileges were indeed assigned.
Tracing and permission checks
It’s time to run a 100046 trace (SQL trace) to see what’s really going on.
SQL> alter session set events '10046 trace name context forever, level 12'; Session altered. SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); BEGIN sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_XSTREAM_GG_INTERNAL", line 46 ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 13 ORA-06512: at line 1
And tracefile does show some interesting information. A few of the more interesting snippets:
PARSING IN CURSOR #140324121137184 len=76 dep=0 uid=91 oct=47 lid=91 tim=1388531465245781 hv=1323338123 ad='6c1f63a0' sqlid='gvq73797f12cb' BEGIN sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); END; END OF STMT ... PARSING IN CURSOR #140324121064984 len=187 dep=1 uid=0 oct=3 lid=0 tim=1388531465246387 hv=2028900049 ad='6c128db8' sqlid='aa9h2ajwfx3qj' SELECT COUNT(*) FROM ( SELECT GP.USERNAME FROM DBA_GOLDENGATE_PRIVILEGES GP WHERE GP.USERNAME = :B1 UNION ALL SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTEE=:B1 AND GRANTED_ROLE='DBA' ) END OF STMT ... Bind#0 ... value="GGS" ... Bind#1 ... value="GGS" ...
The SQL statement is actually checking two things. The first is looking for the current username in the
dba_goldengate_privileges view. This view isn’t listed in the Oracle 11.2 documentation, but it does appear in the 12c docs:
ALL_GOLDENGATE_PRIVILEGES displays details about Oracle GoldenGate privileges for the user.
Oracle GoldenGate privileges are granted using the DBMS_GOLDENGATE_AUTH package.
DBA_GOLDENGATE_PRIVILEGES displays details about Oracle GoldenGate privileges for all users who have been granted Oracle GoldenGate privileges.
USER_GOLDENGATE_PRIVILEGES displays details about Oracle GoldenGate privileges. This view does not display the USERNAME column.
I had previously run
dbms_goldengate_auth to grant privs here, so should be OK.
The second check simply verifies that the
DBA role had been granted to the current user, again as recommended by the documentation. (A side note: in previous versions, I had avoided granting the overly broad DBA role to the GoldenGate user in favor of specific grants for the objects it uses. There’s no reason for the GoldenGate user to need to read and modify data objects that aren’t related to its own replication activities for example. And I would argue that it helps avoid errors such as putting the wrong schema in a map statement when permissions are restricted. But sadly it’s no longer possible in the world of 188.8.131.52.)
Running the query manually to verify that the grants are indeed in place:
SQL> SELECT COUNT(*) FROM ( SELECT GP.USERNAME FROM DBA_GOLDENGATE_PRIVILEGES GP WHERE GP.USERNAME = 'GGS' UNION ALL SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTEE='GGS' AND GRANTED_ROLE='DBA' ); COUNT(*) ---------- 2
Looks good, so that doesn’t seem to be the problem.
Tracing #2: system properties
Back to the 10046 tracefile:
PARSING IN CURSOR #140324119717656 len=45 dep=1 uid=0 oct=3 lid=0 tim=1388531465253124 hv=3393782897 ad='78ae2b40' sqlid='9p6bq1v54k13j' select value$ from sys.props$ where name = :1 END OF STMT ... Bind#0 ... value="GG_XSTREAM_FOR_STREAMS" ... FETCH #140324119717656:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=1,plh=415205717,tim=1388531465254441
Because this SQL statement involves an ordinary
select without an aggregate function, I can look at the
FETCH line in the tracefile to get the number of rows returned. In this case it’s
r=0, meaning no rows returned.
The query itself is looking for a system property I haven’t seen before: GG_XSTREAM_FOR_STREAMS. A Google search returns only a single result: the PDF version of the Oracle 11.2 XStream guide. Quoting:
This procedure enables XStream capabilities and performance optimizations for Oracle
This procedure is intended for users of Oracle Streams who want to enable XStream
capabilities and optimizations. For example, you can enable the optimizations for an
Oracle Streams replication configuration that uses capture processes and apply
processes to replicate changes between Oracle databases.
These capabilities and optimizations are enabled automatically for XStream
components, such as outbound servers, inbound servers, and capture processes that
send changes to outbound servers. It is not necessary to run this procedure for
When XStream capabilities are enabled, Oracle Streams components can stream ID key
LCRs and sequence LCRs. The XStream performance optimizations improve efficiency
in various areas, including:
? LCR processing
? Handling large transactions
? DML execution during apply
? Dependency computation and scheduling
? Capture process parallelism
On the surface, I don’t see what this would have to do with trigger execution, but I’m going to try enabling it as per the newly read document anyway:
SQL> exec dbms_xstream_adm.ENABLE_GG_XSTREAM_FOR_STREAMS(enable=>true); PL/SQL procedure successfully completed. SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); BEGIN sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_XSTREAM_GG_INTERNAL", line 46 ORA-06512: at "SYS.DBMS_XSTREAM_GG", line 13 ORA-06512: at line 1
Tracing #3: process names
Onto the next SQL in the tracefile:
PARSING IN CURSOR #140324120912848 len=114 dep=1 uid=0 oct=3 lid=0 tim=1388531465255628 hv=1670585998 ad='6c2d6098' sqlid='a9mwtndjt67nf' SELECT COUNT(*) FROM V$SESSION S, V$PROCESS P WHERE P.ADDR = S.PADDR AND S.PROGRAM LIKE 'extract%' AND p.spid = :1 END OF STMT ... Bind#0 ... value="2293"
Now we look in v$session, to see if a session associated with the process with OS PID 2293 (which happens to be the SPID of our current shadow process) has a PROGRAM column starting with the word extract. extract is, naturally, the name of the GoldenGate executable that captures data from the source system. In a GoldenGate system, however, trigger suppression does not happen in the extract process at all, but rather the replicat process that applies changes on the target system. So I’m going to skip this check and move on to the next one in the tracefile:
PARSING IN CURSOR #140324120905624 len=169 dep=1 uid=0 oct=3 lid=0 tim=1388531465257346 hv=3013382849 ad='6c122b38' sqlid='38pkvxattt4q1' SELECT COUNT(*) FROM V$SESSION S, V$PROCESS P WHERE P.ADDR = S.PADDR AND (S.MODULE LIKE 'OGG%' OR S.MODULE = 'GoldenGate') AND S.PROGRAM LIKE 'replicat%' AND p.spid = :1 END OF STMT ... Bind#0 ... value="2293"
This SQL is similar to the previous one, but instead of looking for a program called
extract, it looks for one called
replicat, and adds an extra check, so see if the
module column either starts with
OGG or is called
GoldenGate. And since it’s the
replicat process that does trigger disabling in GoldenGate, this check is likely to be related.
To make this check succeed, I’m going to have to change both the
module columns in v$session for the current session. of the two,
module is much easier to modify: a single call to dbms_application_info.set_module. But modifying
program is less straightforward. One approach is to use Java code with Oracle’s JDBC Thin driver and setting the aptly-named
v$session.program property, as explained in De Roeptoeter. But I’m hoping to stay with something I can do in SQL*Plus. If you’ve looked through a packet trace of a SQL*Net connection being established, you will know that the program name is passed by the client at the time of connection establishment, so could be modified by either modifying the network packet in transit. This is also complex to get working, as it also involves fixing checksums and the like. There’s a post on Slavik’s blog with a sample OCI C program that modifies its program information. Again more complexity thn I’d like, but it gave me an idea: if the
program is populated by the name of the client-side executable, why don’t we simply copy sqlplus to a name that the
dbms_xstream_gg likes better?
[[email protected] ~]$ cp $ORACLE_HOME/bin/sqlplus ./replicat [[email protected] ~]$ ./replicat ggs SQL*Plus: Release 184.108.40.206.0 Production on Mon Dec 30 14:09:05 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exec dbms_application_info.set_module('OGG',''); PL/SQL procedure successfully completed. SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true); PL/SQL procedure successfully completed.
So it looks like you can disable triggers per-session in 18.104.22.168 just like previous versions, but need to jump through a few more hoops to do it. A few conclusions to draw:
- Oracle patchsets, while normally intended to include bugfixes, can have major changes to underlying functionality too. See Jeremy Schneider’s post on adaptive log file sync for an even more egregious example. So before applying a patchset, test thoroughly!
- The enforcement of full DBA privileges for the GoldenGate user in Oracle 22.214.171.124 requires very broad permissions to use GoldenGate, which can be a concern in security-conscious or consolidated environments.
TL;DR: Yes you can still disable triggers per-session in Oracle 126.96.36.199, but you have to have a GoldenGate license, set the
enable_goldengate_replication parameter, use a program name that starts with
replicat, and set your module to
Great find, Marc.
I ran to the same problem last year and
was looking for a work-around.
Thanks Joseph; glad the info was useful for you.
Amusingly, Oracle Veridata 12.1.3 has an option for the session-based disabling of triggers for data-repair jobs. It uses the exact mechanism described here, and fails when the target database is 188.8.131.52.
Very interesting; thanks for sharing!
thanks.. very helpful.
Just be really careful setting ENABLE_GOLDENGATE_REPLICATION – especially if you are using other replication products. It changes/enables and sets a number of things internally which can break Shareplex replication – for example. This was on SP 7 – Have not dared to try it on SP 8 yet… :)
Thanks for your feedback/tips.