Oracle Database creates text files to help you diagnose session and server activities. Some of them are generated by the database itself automatically and others can be generated on demand by database administrators or developers. This post will address how to trace Oracle sessions and how to gather information to perform troubleshooting.

With the release of Oracle Database 11g, traces and core files are placed in a location controlled by the DIAGNOSTIC_DEST initialization parameter.

You can execute the following command to show the DIAGNOSTIC_DEST location:

SQL> show parameter diagnostic_dest

NAME              TYPE      VALUE
----------------- -------   ------------------------------------
diagnostic_dest   string    /u01/app

The following is a sample trace file name:

/u01/app/diag/rdbms/db01/db01/trace/db01_ora_11704.trc

where /u01/app is the diagnostic_dest directory.

Session tracing

This type of tracing can be used where the session is accessible to the user prior to the start of the statement(s) to be traced. To gather 10046 trace at the session level:

SQL> alter session set tracefile_identifier='10046';

Session altered.

SQL> alter session set timed_statistics = true;

Session altered.

SQL> alter session set statistics_level=all;

Session altered.

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

-- Execute some queries or operations to be traced here --

SQL> select * from dual;

D
-
X

SQL> exit;

If the session is not exited then the trace can be disabled using:

SQL> alter session set events '10046 trace name context off';

Session altered.

Tracing a process after it has started

You can use the oradebug command to attach to an existing session and initiate 10046 tracing.

The first step is to identify the session to be traced. For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:

SQL> SELECT p.pid,p.spid,s.sid
  2    FROM v$process p,v$session s
  3   WHERE s.paddr = p.addr
  4     AND s.sid = &SESSION_ID;
Enter value for session_id: 46
old   4: and s.sid = &SESSION_ID
new   4: and s.sid = 46

       PID SPID                            SID
---------- ------------------------ ----------
29 30763                            46

Note: SID value can be obtained from the V$SESSION dynamic view.

If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:

SQL> column line format a79
SQL> set heading off
SQL> SELECT 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  2    s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
  3    FROM v$session s , v$process p
  4   WHERE p.addr = s.paddr
  5     AND s.username <> ' ';

ospid: 30763 # '46,799' oracle ol62 SYS sqlplus@ol62 (TNS V1-V3)

Once the OS process id for the process has been determined then the trace can be initialized. Assume that the process to be traced has an os pid of 30763. Login to SQL*Plus as a dba and execute the following:

SQL> connect / as sysdba
Connected.
SQL> oradebug setospid 30763
Oracle pid: 29, Unix process pid: 30763, image: oracle@ol62 (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.

Note: OS PID is the operating system process ID number.

It is also possible to attach to a session via oradebug using the ‘setorapid’. Assume that the process to be traced has an ora pid of 29. Login to SQL*Plus again as a dba and execute the following:

SQL> connect / as sysdba
Connected.
SQL> oradebug setorapid 29
Oracle pid: 29, Unix process pid: 30763, image: oracle@ol62 (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.

Note: ORA PID is the Oracle process identifier for a given session.

To disable oradebug tracing once tracing is finished:

SQL> oradebug event 10046 trace name context off
Statement processed.

Instance wide tracing

Setting system-wide 10046 tracing can be useful for scenarios where a problem session is known to occur but cannot be identified in advance. In this situation, tracing can be enabled for a short period of time, the problem can then be reproduced and tracing disabled and the resultant traces searched for evidence of the problem.

This setting will trace every session that is created after the parameter is set. Existing sessions will not be traced. System-wide tracing can be enabled as follows:

SQL> alter system set events '10046 trace name context forever,level 12';

System altered.

And the setting can be disabled in all sessions by executing the following command:

SQL> alter system set events '10046 trace name context off';

System altered.

Via a Logon Trigger

There may be some situations where it is necessary to trace the activity of a specific user. In this case a logon trigger could be used. An example is provided below for userid “TEST”:

SQL> CREATE OR REPLACE TRIGGER SYS.set_trace
  2     AFTER LOGON ON DATABASE
  3     WHEN (USER like '&USERNAME')
  4  DECLARE
  5  BEGIN
  6     EXECUTE IMMEDIATE 
  7      'alter session set tracefile_identifier=''From_Trigger''';
  8     EXECUTE IMMEDIATE 
  9      'alter session set statistics_level=ALL';
 10     EXECUTE IMMEDIATE 
 11      'alter session set max_dump_file_size=UNLIMITED';
 12     EXECUTE IMMEDIATE 
 13      'alter session set events 
 14      ''10046 trace name context forever, level 12''';
 15  END set_trace;
 16  /
Enter value for username: TEST
old   3: WHEN (USER like '&USERNAME')
new   3: WHEN (USER like 'TEST')

Trigger created.

Trace file name will be created as “SID_ora_OSPID_From_Trigger.trc” name mask:

/u01/app/diag/rdbms/db01/db01/trace/db01_ora_32533_From_Trigger.trc

A future post will address how to perform a trace interpretation.

Link to script that contains the examples in this post.

 

 

 

 

 

 

Comment