Enable 10046 tracing
Wednesday, April 11, 2012
Init.ora/spfile parameters
There are a few init.ora parameters that
need to be set for the sessions you want to trace. They are as
follows:
timed_statistics=true
This is only really required for the waits
or sql timing information. Should be enabled by default on most
system. Recommended.
max_dump_file_size=unlimited
This allows the trace file to grow as
required bty the trace. If this is not set to unlimited, then you
may find your trace file does not cover all activity, and you will see a
message similar to “*** DUMP FILE IS LIMITED TO xxxxx BYTES ***” at the end of
your trace file.. Recommended.
tracefile_identifier = ‘test_trace’
As string which is used to form part of
the trace filename when the trace file is created. This is useful
when you are tracing a number of sessions and you want to make it easier to
find the tracefile.
NOTE: to set these parameters the user
needs “alter session” privilege.
Enabling Tracing
There are various methods of enabling
tracing, from alter session to oradebug, but its worth noting that you can use
different methods to switch it on/off. For example you could user
dbms_support to turn it on and oradebug to switch off. Different
tools suite different options.
This is the easiest trace to setup, as it
involves issuing only one alter session commend either:
alter session set sql_trace=true;
If you want higher than a level 1 trace
then you must set the 10046 event to trace the session, for example:
alter session set events ‘10046 trace name context
forever, level 12’;
First you need to get the SID from
v$session, connect as sysdba in SQLPlus and you can use oradebug.
oradebug setorapid 16
oradebug event 10046 trace name context forever, level 12;
and to turn it off use:
oradebug setorapid 16
oradebug event 10046 trace name context off;
This may not be installed, but the source
is usually in $ORACLE_HOME/rdbms/admin/dbmssupp.sql, which must be run as SYS or a SYSDBA.
execute dbms_support.start_trace_in_session(:sid,:serial,waits=>true,binds=>false);
Where SID and SERIAL are taken from
v$session.
And to turn off
execute dbms_support.stop_trace_in_session(:sid,
:serial);
If you know the UNIX process ID, for
example, you got it from the “top” command, then you can use oradebug to turn
on tracing for that process. This is assuming it is a shadow server side
process, rather than any general oracle process. Please note that turning
on tracing for some of the mandatory oracle background processes, like PMON,
can sometimes cause the instance to crash!
test-sun-01.localnet[oracle]:TEST01:$ ps -ef
UID PID PPID C STIME
TTY TIME CMD
...
oracle 12449 12441 0 13:41:57
? 0:00 oracleTEST01
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
....
SQL> oradebug setospid 12449
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.
Wait for the session to run a
while....then turn off tracing and look for the trace file.
SQL> oradebug event 10046 trace name context
off;
Statement processed.
dbms_system.set_bool_param_in_session
(:sid,:serial,’timed_statistics’,true);
dbms_system.set_int_param_in_session
(:sid,:serial,’max_dump_file_size’,2147483647);
Tracing, can also be turned on using
dbms_system.set_ev procedure. This can in fact be used to set any
event. Be careful when using this as if you get the incorrect event
number all kinds of weird things can happen including instance crashes!
Here is an example of setting the 10046
trace event using set_ev procedure.
sys.dbms_system.set_ev(:sid,:serial,10046,8,’’)
And to turn off
sys.dbms_system.set_ev(:sid,:serial,10046,0,’’)
The dbms_system.read_ev procedure can be
used for this. As can be seen below:
declare
event_level number;
begin
dbms_system.read_ev(10046,event_level);
dbms_output.put_line('Event 10046 set at level '||
to_char(event_level));
end;
/
Trace files will usually be in the
user_dump_destination (udump), but in shared server configurations you will
also find trace file in background_dump_destination (bdump).
Oracle Multi-Threaded Server (MTS) connections
to the database are difficult to trace, as the trace information for the
session I n question is likely to be a number of trace files. You
need to match the session ID that begins with the line *** in all the trace
files, then put then in date/time order. This effectively leaves you with
a virtual trace file. Its quite tricky, especially if you have a
large number of shared server processes. Where possible try and
configure the client to use a dedicated server connection, so that you have one
trace file
Connection pooling applications are
difficult to trace as you need to trace the whole pool to make sure that you
get all the information required from the trace.
NOTE : Oracle 10g trace functionality has
been improved considerably in this area. In 10g the dbms_monitor
package can be used to trace sessions, and you can check which sessions have
tracing enabled by looking at dba_enabled_traces
If you find it difficult to trace a
session because it logs in and does stuff before you have time to trace, try
implementing a database logon trigger such as the one below:
CREATE OR REPLACE TRIGGER enable_10046_trace_trigger
after logon on database
-- 10046 TRACELEVELS
-- 0 - Turn off tracing.
-- 1 - Basic SQL_TRACE.
-- 4 - Level 1 plus Bind Variables.
-- 8 - Level 1 plus wait events.
-- 12 - Level 1 plus Bind Variables and Wait
event information.
begin
if user ='JOHND' then
execute immediate 'alter session set timed_statistics =
true';
execute immediate 'alter session set max_dump_file_size =
unlimited';
execute immediate 'alter session set tracefile_identifier =
''session_trace_energis''';
execute immediate 'alter session set events ''10046 trace
name context forever, level 4'' '; -- bind variables only
end if;
end;
create or replace trigger disable_10046_trace_trigger
logoff_audit_trigger
BEFORE LOGOFF ON database
begin
if user ='<username>' then
execute immediate 'alter session set events ''10046 trace name context off'' ';
end if;
end;
Do not forget to disable the tracing.
Or if you need to be a little more specific about the users/sessions you want to trace, then you could use something like the following:
CREATE OR REPLACE
TRIGGER SYS.enable_10046_trace_trigger
AFTER LOGON ON DATABASE
-- 10046 TRACELEVELS
-- 0 - Turn off tracing.
-- 1 - Basic SQL_TRACE.
-- 4 - Level 1 plus Bind Variables.
-- 8 - Level 1 plus wait events.
-- 12 - Level 1 plus Bind Variables and Wait event information.
DECLARE
v_exe v$session.program%TYPE; -- EXE (Program used to connect) - See v$session
v_sid v$session.SID%TYPE; -- SID for this session
v_osuser v$session.osuser%TYPE;
v_machine v$session.machine%TYPE;
v_ok VARCHAR (10);
-- Flag for everything OK - Can't be boolean coz decode doesn't like booleans
BEGIN
--To check the program need to get the sid for this session.
-- SID
SELECT SID
INTO v_sid
FROM v$mystat
WHERE ROWNUM < 2;
-- Program executable for this session
SELECT program
INTO v_exe
FROM v$session
WHERE SID = v_sid;
-- OSUSER Details
SELECT osuser
INTO v_osuser
FROM v$session
WHERE SID = v_sid;
--Machine Details
SELECT machine
INTO v_machine
FROM v$session
WHERE SID = v_sid;
IF USER = 'PHONE_USER' AND v_exe = 'phone.exe' AND v_osuser = 'smithj' and v_machine='WG1\WS00001'
THEN
EXECUTE IMMEDIATE 'alter session set timed_statistics = true';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size = unlimited';
EXECUTE IMMEDIATE 'alter session set tracefile_identifier = ''session_trace_trigger''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 4'' ';
DBMS_SESSION.set_identifier ('SQL TRACE ENABLED VIA LOGIN TRIGGER');
END IF;
END;
AFTER LOGON ON DATABASE
-- 10046 TRACELEVELS
-- 0 - Turn off tracing.
-- 1 - Basic SQL_TRACE.
-- 4 - Level 1 plus Bind Variables.
-- 8 - Level 1 plus wait events.
-- 12 - Level 1 plus Bind Variables and Wait event information.
DECLARE
v_exe v$session.program%TYPE; -- EXE (Program used to connect) - See v$session
v_sid v$session.SID%TYPE; -- SID for this session
v_osuser v$session.osuser%TYPE;
v_machine v$session.machine%TYPE;
v_ok VARCHAR (10);
-- Flag for everything OK - Can't be boolean coz decode doesn't like booleans
BEGIN
--To check the program need to get the sid for this session.
-- SID
SELECT SID
INTO v_sid
FROM v$mystat
WHERE ROWNUM < 2;
-- Program executable for this session
SELECT program
INTO v_exe
FROM v$session
WHERE SID = v_sid;
-- OSUSER Details
SELECT osuser
INTO v_osuser
FROM v$session
WHERE SID = v_sid;
--Machine Details
SELECT machine
INTO v_machine
FROM v$session
WHERE SID = v_sid;
IF USER = 'PHONE_USER' AND v_exe = 'phone.exe' AND v_osuser = 'smithj' and v_machine='WG1\WS00001'
THEN
EXECUTE IMMEDIATE 'alter session set timed_statistics = true';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size = unlimited';
EXECUTE IMMEDIATE 'alter session set tracefile_identifier = ''session_trace_trigger''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 4'' ';
DBMS_SESSION.set_identifier ('SQL TRACE ENABLED VIA LOGIN TRIGGER');
END IF;
END;
Run this script in SQLPlus as SYSDBA
rem what_events.sql
rem List the events that are set this this current session.
rem
declare
event_level number;
counter number;
begin
counter:=0;
for i in 10000..10999 loop
dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set
at level '|| to_char(event_level));
counter:=counter+1;
end if;
end loop;
if (counter= 0 ) then
dbms_output.put_line('No events set for this session');
end if;
end;
/
Labels: Enabling trace for another session that is already connected. 10046 logon trigger
0 comments:
Subscribe to:
Post Comments (Atom)
Powered by Blogger.