Skip to main content
Delphix

Running Oracle Tracing While a Masking Job is Executing (KBA6127)

 

 

KBA

KBA# 6127

Issue

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.