Set trace on user sessions

Oracle 10046 sesssion tracing

There are different ways to enable tracing on an Oracle database user session. They are:

  1. Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procudure.
  2. Using oradebug ipc setorapid/setospiod
  3. Using a Logon trigger

The first 2 methods are used used when the sessions are already connected to the database. For the first method, use the session ID, Serial# and execute the procedure to start tracing.

Using oradebug

1) Connect to the database Instance, On RAC, connect to the right instance.

$ connect / as sysdba

2) Get the OS PID of the connected session and run the command
Eg:

oradebug setospid 16211

Alternatively, Oracle PID can also be used to start the tracing.
Eg:

oradebug setorapid 27853

Set the tracing level to 12 for an exhaustive trace(4 and 8 are lower levels).

  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12

Once the tracing is done, tracing can be disabled using the below procedure:

connect “/ as sysdba”

oradebug setospid 16211
oradebug event 10046 trace name context off

Using a LOGON trigger

Using a LOGON trigger may be the preferred method to trace sessions in situations where it is difficult to start tracing when user sessions get active after getting connected to the database. Also, in RAC environments where the services are configured as multi-preferred, it may be difficult to logon to each instance to set the tracing. In such scenarios, setting a logon trigger at the database can help to set tracing on sessions. Here are the steps:

1) Check to see if “alter session” privilege is granted to the required users. Else grant “alter session” privilege to the users.

For eg, to trace the sessions of SCOTT and HR users:

grant alter session to SCOTT;
grant alter session to HR;

2) Create a logon trigger at the database level:

CREATE OR REPLACE TRIGGER SYS.set_trace_scott
  AFTER LOGON ON DATABASE
  WHEN (USER in ('SCOTT','HR'))
  DECLARE
      lcommand varchar(200);
  BEGIN<br />
      EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
      EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;

3) Trace files are generated once users sessions are established. Check the user_dump_dest for the trace files once the sessions are active. On RAC, Identify the instance to which the user(s) sessions got connected and check the appropriate server.

4) Once the trace files are obtained, remove the trigger to disable further tracing without fail. Otherwise, there are high chances of generating huge trace files which may fill up the filesystem. You may also want to remove the ALTER SESSION privilege from the users if granted for this purpose.

Use Oracle supplied utility TRCA(Trace Analyzer) to analyze the trace files.

drop trigger SYS.set_trace_scott;

Good luck in finding what you are looking for!

Share

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>