Activate Oracle Tracing

Question:

If the SDE logging is not sufficient to find out the reason for the bug, it may be necessary to look at the ORACLE log file. How to activate tracing for Oracle?

Answer:

• Start UT application.
• Go ahead close before the problem
• Determine the corresponding Oracle session and activate the session (see below)
• Execute the problematically function
• Trace files are reproduced at the server. These files can be converted by TKPROF. Please always send original and TKPROF file.

Detailed instruction for activate tracing in Oracle:

1) Determine <sid>,<serial#>:

SQLPLUS: Connect with

 sys/<pwd>@<tns_service> as sysdba  (has to be done as sysdba)

 select sid,serial#,username,machine from v$session;

2) Activate tracing for this Oracle session

 exec sys.dbms_system.set_ev(<sid>,<serial#>,10046,12,''); (that are two separate apostrophe)

  12 means: 10046 EVENT levels:
         1  - Enable standard SQL_TRACE functionality (Default)
         4  - As Level 1 PLUS trace bind values
         8  - As Level 1 PLUS trace waits
              This is especially useful for spotting latch wait etc.
              but can also be used to spot full table scans and index scans.
         12 - As Level 1 PLUS both trace bind values and waits

A trace file is created on server in directory <user_dump_dest> (Parameter in init-file) usually %ORACLE_HOME%\admin\<sid>\udump

Finding the right trace file is quite tricky, because this could not be determined by the file name. Mostly it can be isolate by the date and time. Within the trace file the SID and SERIAL can be found in the header. Also the accessing application is mentioned.

  *** SESSION ID:(<sid>,<serial#>) ...
  ...
  *** MODULE NAME:(ArcMap.exe) ...

In the Command shell this can be converted into a readable format by

  TKPROF <input file> <output file> 

Oracle errors can mostly be found in the upper part of the file. By adding a sort parameter the Oracle traces could also be useful for analyzing performance issues.

Further information:

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/performing-application-tracing.html