Running Oracle Tracing While a Masking Job is Executing (KBA6127)
KBA
KBA# 6127Issue
Oracle is returning errors based on the masked values received by the Masking engine. For example, duplicate values or NULL being updated in the table, violating constraints.
Prerequisites
An Oracle sysdba account is required to place a trigger and run the trace.
Applicable Delphix Versions
- Click here to view the versions of the Delphix engine to which this article applies
-
Major Release All Sub Releases 6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0 5.3
5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0 5.2
5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1
5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0
5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
4.3
4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2
4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1
4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0
Resolution
Create a trigger on the Oracle database, making sure to replace MASKING_DB_USER with the actual database user in the Masking connector:
CREATE OR REPLACE TRIGGER USER_TRACING_SESSION AFTER LOGON ON DATABASE BEGIN IF USER = 'MASKING_DB_USER'THEN execute immediate 'alter session set events ''10046 trace name context forever, level 1'''; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; /
Note 1: Different levels (in the 10046 event) produce different output in the trace file. If you just want error numbers, then "level 1" is fine. If you would also like to see the bind variables and/or wait events (which will produce much larger trace files) then you can alter the level number as per below:
Level 1 - enable the standard SQL_TRACE facility
Level 4 - enable standard SQL_TRACE but also capture bind variable values
Level 8 - enable standard SQL_TRACE but also capture wait events
Level 12 - enable standard SQL_TRACE and include both bind variables and waits
Note 2: The user mentioned in the logon trigger will require the "alter session" privilege.
Troubleshooting
It is possible to disable the trigger as required:
ALTER TRIGGER USER_TRACING_SESSION DISABLE;
ALTER TRIGGER USER_TRACING_SESSION ENABLE;
Running the Masking job while the trigger is enabled, it will produce several trace files (*.trc) in the same location as the Oracle alert log.
Those files will contain all the SQL statements run by the Masking engine while selecting and updating the data.