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:
where /u01/app is the diagnostic_dest directory.
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:
A future post will address how to perform a trace interpretation.