Tracing Oracle Session
Is there any way to set tracefile identifier in another session using dbms_system, dbms_session, dbms_support, dbms_monitor etc?
not that I'm aware of, it is something the session itself would have to do.
Tracingng Sessions with Event 10046
10046 Event is used to trace SQL statements (similar to sql_trace=true) with additional details depending on the tracing level which is specified.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
12 – As Level 1 PLUS both trace bind values and waits
There are various ways to enable the tracing
1) Using Alter session command
Tracing own session.
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
To Turn it off
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
To Turn it off
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
To trace other session
2) Using DBMS_SESSION
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE procedure disables the trace for the invoking session.
For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
3) Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);
Where SID and SERIAL# points to the session being traced and can be retrieved from V$SESSION
To turn it off
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);
4) Using DBMS_MONITOR
Starting from Oracle 10g, there is a new package which can be used to enable tracing.
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)
To turn it off
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => sid, serial_num => serial#);
Please refer to following documentation link for more details
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i8759
5) Using ORADEBUG
It's traditional method for old oracle versions.
connect / as sysdba
Sql>oradebug setospid 1234
Sql>oradebug unlimit
Sql>oradebug event 10046 trace name context forever,level 12
Here 1234 is the OS process Id for the session to be traced.
sql>oradebug tracefile_name
Above command will show the location of the trace file
To disable oradebug tracing
Sql> oradebug event 10046 trace name context off
Whichever method you use, Oracle will create the trace files in the server’s user_dump_dest directory.
========≠=========================
To generate trace files for your own session
------------------------------------------------
alter session set sql_trace=True
alter session set timed_statistics=true
alter session set max_dump_file_size=unlimited
alter session set tracefile_identifier = 'My_File.Trc'
alter session set events '10046 trace name context forever, level 12'
Do some transactions like select insert anything
Go to command prompt
CMD>tkprof My_File.Trc My_File.Txt
It will generate tkprof files for the trace files.
alter session set sql_trace=false
To see where trace files are generated execute following statment.
Select Value from V$Parameter Where Upper(name ) = 'USER_DUMP_DEST'
This is to see the trace file name
-----------------------------
select c.value
'/'
d.instance_name
2) Using DBMS_SESSION
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE procedure disables the trace for the invoking session.
For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
3) Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);
Where SID and SERIAL# points to the session being traced and can be retrieved from V$SESSION
To turn it off
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);
4) Using DBMS_MONITOR
Starting from Oracle 10g, there is a new package which can be used to enable tracing.
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)
To turn it off
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => sid, serial_num => serial#);
Please refer to following documentation link for more details
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i8759
5) Using ORADEBUG
It's traditional method for old oracle versions.
connect / as sysdba
Sql>oradebug setospid 1234
Sql>oradebug unlimit
Sql>oradebug event 10046 trace name context forever,level 12
Here 1234 is the OS process Id for the session to be traced.
sql>oradebug tracefile_name
Above command will show the location of the trace file
To disable oradebug tracing
Sql> oradebug event 10046 trace name context off
Whichever method you use, Oracle will create the trace files in the server’s user_dump_dest directory.
========≠=========================
To generate trace files for your own session
------------------------------------------------
alter session set sql_trace=True
alter session set timed_statistics=true
alter session set max_dump_file_size=unlimited
alter session set tracefile_identifier = 'My_File.Trc'
alter session set events '10046 trace name context forever, level 12'
Do some transactions like select insert anything
Go to command prompt
CMD>tkprof My_File.Trc My_File.Txt
It will generate tkprof files for the trace files.
alter session set sql_trace=false
To see where trace files are generated execute following statment.
Select Value from V$Parameter Where Upper(name ) = 'USER_DUMP_DEST'
This is to see the trace file name
-----------------------------
select c.value
'/'
d.instance_name
'_ora_'
a.spid
'.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
Or you can also Use DBMS_SESSION to set trace in your own session
The next method for setting trace in our own session also is done using a built in package, this time DBMS_SESSION. here it is:
SQL> -- in your own session using dbms_session
SQL> exec dbms_session.set_sql_trace(true);
PL/SQL procedure successfully completed.
SQL> -- execut some code
SQL> exec dbms_session.set_sql_trace(false);
PL/SQL procedure successfully completed.
** Remember trace files or tkprof files will reside in the server
To generate trace files for other sessions--------------------------------------------
To get session information query v$session view. Following query displays all the sessions
Select V.SID, V.SERIAL#, V.USER#, V.USERNAME, V.PROGRAM
From V$Session V
Where V.USERNAME Is Not Null
If you are having oracle 9i as database then you have to use dbms_support package to generate trace files for other sessions.
Dbms_Support package by default it will not be available to get that you need to execute the script which will be available
$ORACLE_HOME/rdbms/admin/dbmssupp.sql. The base install script is dbmssupp.sql script and is run as sysdba.
DBMS_SUPPORT package is preferred over DBMS_SYSTEM and is fully supported by Oracle.
To execute the Dbms_Support package in other schemas, should have the execute privileges on Dbms_Support.
Start Tracing -- equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing
SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false); .
Stop Tracing SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL);
For Example
----------
-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
New tracing methods in Oracle 10g - DBMS_MONITOR
Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.
The new functionality works in three levels. You can use the old SID / SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.
let's now take a look at some of the features of this package.
Setting trace with DBMS_MONITOR using SID / SERIAL#
Trace can be set for the current user session, for the current session or for another users session. First lets look at tracing another users session. First we need to get the SID and SERIAL# - we will use SCOTT connected through SQL*Plus as our sample session:
SQL> select s.sid,s.serial#,s.username
2 from v$session s, v$process p
3 where s.paddr=p.addr
SQL> /
...
SID SERIAL# USERNAME
---------- ---------- ------------------------------
248 153 SCOTT
258 61 DBSNMP
251 418 SYSMAN
255 961 SYS
249 215
27 rows selected.
SQL>
OK as with previous methods we can use a SID / SERIAL# pair of 248 and 153. lets set trace for this user session:
SQL> exec dbms_monitor.session_trace_enable(248,153,TRUE,FALSE);
PL/SQL procedure successfully completed.
SQL> -- execute some sql
SQL> -- in the other session
SQL> -- turn trace off
SQL> exec dbms_monitor.session_trace_disable(248,153);
PL/SQL procedure successfully completed.
SQL>
Setting trace at the session level using DBMS_MONITOR
The same procedures can be used to set trace for the session by omitting the serial#. This is demonstrated next:
SQL> exec dbms_monitor.session_trace_enable(248);
PL/SQL procedure successfully completed.
SQL> -- execute some sql in the other session
SQL> -- turn off trace
SQL> exec dbms_monitor.session_trace_disable(248);
PL/SQL procedure successfully completed.
SQL> -- or you can turn it on with
SQL> exec dbms_monitor.session_trace_enable(248,null);
PL/SQL procedure successfully completed.
SQL> -- turn off again with:
SQL> exec dbms_monitor.session_trace_disable(248,null);
PL/SQL procedure successfully completed.
SQL>
Setting trace for the current session using DBMS_MONITOR
Setting trace for the current user session is done by leaving out the SID and SERIAL# altogether by setting them to NULL. Here is an example:
SQL> -- trace the current session
SQL> exec dbms_monitor.session_trace_enable(null,null);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> -- turn it off again
SQL> exec dbms_monitor.session_trace_disable(null,null);
PL/SQL procedure successfully completed.
SQL> -- to get waits and binds do
SQL> exec dbms_monitor.session_trace_enable(null,null,true,true);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> -- then turn off tracec
SQL> exec dbms_monitor.session_trace_disable(null,null);
PL/SQL procedure successfully completed.
SQL> -- or turn it on like this
SQL> exec dbms_monitor.session_trace_enable();
PL/SQL procedure successfully completed.
SQL> -- execute some SQL and then turn off trace
SQL> exec dbms_monitor.session_trace_disable();
PL/SQL procedure successfully completed.
SQL>
That completes some of the ways to use DBMS_MONITOR to set trace using SID, SERIAL#, or at the session level or for the current session.
Set trace using a client identifier
Tracing using the client identifier allows trace to be set across multiple sessions as many Oracle shadow processes can work on behalf of one client. Also trace is persistent across all instances and restarts. First we need to see how the client identifier is set. This can be done using the DBMS_SESSION package as follows:
SQL> exec dbms_session.set_identifier('pete id');
PL/SQL procedure successfully completed.
SQL>
We can check now for a specific identifier in the V$SESSION view with the client_identifier column.
SQL> select s.username,s.client_identifier
2 from v$session s,v$process p
3 where s.paddr=p.addr
4 and client_identifier is not null;
USERNAME
------------------------------
CLIENT_IDENTIFIER
----------------------------------------------------------------
SCOTT
pete id
OK, now we can use this information to set trace for this client identifier as follows:
SQL> exec dbms_monitor.client_id_trace_enable('pete id',true,false);
PL/SQL procedure successfully completed.
SQL> -- wait for the client session to do something
SQL> -- turn off trace as follows:
SQL> exec dbms_monitor.client_id_trace_disable('pete id');
PL/SQL procedure successfully completed.
SQL>
That was quite easy!. next let's look at setting trace at the service, module action levels.
Setting trace for service/module/action with DBMS_MONITOR
This method of setting trace acts hierarchically. The first level is that trace is set globally for the whole database (all instances) You can override this by setting an instance name in the call to turn on trace. For this example as I am on a single instance database I will leave this parameter at its default. There are three levels to the hierarchy. If we set ACTION to NULL then all actions for the module and service are traced. The next level, if we set MODULE to NULL then all actions for all modules for the specified service name are traced. The trace will be collected into multiple trace files and the new tool trcsess must be used to collate all the trace files into one usable file.
The service name can be set using the package DBMS_SERVICE and the procedure CREATE_SERVICE. Here is an example:
SQL> exec dbms_service.create_service('Test Service','test network');
PL/SQL procedure successfully completed.
SQL> -- it can be deleted with
SQL> exec dbms_service.delete_service('Test Service');
PL/SQL procedure successfully completed.
SQL>
The service name can quite often be set already by the tool. It could be used to group together a set of programs / modules that perform some business task. Next let's see how the module and actions can be set.
SQL> -- set action
SQL> exec dbms_application_info.set_action('PAYMENT');
PL/SQL procedure successfully completed.
SQL> -- set the module
SQL> exec dbms_application_info.set_module('ACCOUNTS','PAYMENT');
PL/SQL procedure successfully completed.
SQL>
To view the relevant service names, modules and actions for sessions in the database you can use the v$SESSION view as follows:
SQL> col service_name for a15 wrapped
SQL> col username for a15 wrapped
SQL> col module for a15 wrapped
SQL> col action for a15 wrapped
SQL> select s.username,s.service_name,s.module,s.action
2 from v$session s,v$process p
3 where s.paddr=p.addr;
...
USERNAME SERVICE_NAME MODULE ACTION
--------------- --------------- --------------- ---------------
SYSMAN SANS
SYSMAN SANS OEM.SystemPool
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)
SYS$USERS
SYS SANS ACCOUNTS PAYMENT
SCOTT SANS SQL*Plus
...
29 rows selected.
SQL>
As we deleted our sample service name set up with DBMS_SERVICE.CREATE_SERVICE we will just use the default value SANS inserted by Oracle in our test case. Let's test some of the methods of setting trace with this functionality.
SQL> -- set trace for all modules and actions for SANS service name
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS',DBMS_MONITOR.ALL_MODULES,DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS');
PL/SQL procedure successfully completed.
SQL> -- now trace all actions for service SANS and module accounts
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS',DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- now turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS');
PL/SQL procedure successfully completed.
SQL> -- finally test service SANS, module ACCOUNTS and action PAYMENT
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS','PAYMENT',TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS','PAYMENT');
PL/SQL procedure successfully completed.
SQL> -- you can turn on or off binds and waits as well or use the waits=>true
SQL> -- syntax instead.
SQL>
OK, that wraps up the new procedures in 10g that can be used to turn on trace in different ways to capture true end to end trace for multi-tier applications. You should also be aware that DBMS_MONITOR also provides procedures to enable statistic gathering at the same levels of client identifier and service name/module/action. These statistics are stored and can then be accessed by selecting from V$SERV_MOD_ACT_STATS and V$CLIENT_STATS views. I will not detail those procedures here as this short paper is concentrating on trace only.
Oracle Event Tracing
Oracle provides various diagnostic tools for debugging the RDBMS. Certain events
can be turned on to dump diagnstic information to trace files. Next, some
INIT.ORA parameters are available that can be used while diagnosing memory and
disk corruptions. There parameters are not set during normal operation of the
database, due they affect the performance. In the following article, some useful
events will be closer explained.
Database hang problems
If you experience database hang problems during grants of roles or privileges a
state dump may help you to solve the problem. You can get a state dump by doing
the following:
Cleanup not needed TRACE Files
$ sqlplus /nolog
SQL> connect sys/manager as sysdba
SQL> alter session set events
immediate trace name systemstate level 10';
SQL> exit;
Log out, otherwise subsequent trace dumps overwrite the existing trace file.
Generally if a session is executing DBMS_PIPE, DBMS_SQL or
DBMS_SHARED_POOL at the time a grant is issued on any of these, a hang can
happen. By killing the session using these packages (or shutting down the process
if possible) the hang can be resolved.
Out of sync file headers and controlfiles
In some shutdown scenarios (such as a system crash) file headers and controlfiles
may get out of sync. If you get "ORA-01207: file is more recent than controlfile -
old controlfile " type errors, you can troubleshoot using the following events:
$ sqlplus /nolog
SQL> connect sys/manager as sysdba
SQL> startup mount
SQL> alter session set events
immediate trace name controlf level 10';
SQL> alter session set events
immediate trace name file_hdrs level 10';
SQL> alter session set events
immediate trace name redohdr level 10';
These commands will generate a single trace file located in your user_dump_dest
directory containing information about the controlfile and data/redolog files. In an
emergency, you can use the recover clause "using backup controlfile" to possibly
recover from this scenario. However, make sure you do a full cold backup before
attempting recovery.
Tracing ORA-60x Severity Errors
Oracle Event Tracing Page 1 of 2
http://www.akadia.com/services/ora_events.html 24/Mar/09
If you find any ORA-60x errors in you ALERT.ORA system file, you should check,
why the database dumps an errorstack to the trace file. The following example
dumps the error stack every time a process encounters the ORA-604 error.
SQL> alter session set events
604 trace name errorstack forever';
Useful Events set in INIT.ORA in case of an emergency
Oracle can use event setpoints in the initialization file for tracking and validation of
objects. The last event setting (10231) will allow export to extract all data, except
the corrupted block, from a table. You should only use these under guidance from
Oracle support.
# Sets integrity checking for tables
event = "10210 trace name context forever, level 10"
# Sets integrity checking for indexes
event = "10211 trace name context forever, level 10"
# Allows export to extract all data, except the corrupted blocks
event = "10231 trace name context forever, level 10"
# Identify corrupted rollback segment when database cannot be started
event = "10015 trace name context forever"
Where to find Event Numbers ?
Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg
cd $ORACLE_HOME/rdbms/mesg
vi oraus.msg
/10015
10015, 00000, "Undo Segment Recovery"
// *Cause:
// *Action:
a.spid
'.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
Or you can also Use DBMS_SESSION to set trace in your own session
The next method for setting trace in our own session also is done using a built in package, this time DBMS_SESSION. here it is:
SQL> -- in your own session using dbms_session
SQL> exec dbms_session.set_sql_trace(true);
PL/SQL procedure successfully completed.
SQL> -- execut some code
SQL> exec dbms_session.set_sql_trace(false);
PL/SQL procedure successfully completed.
** Remember trace files or tkprof files will reside in the server
To generate trace files for other sessions--------------------------------------------
To get session information query v$session view. Following query displays all the sessions
Select V.SID, V.SERIAL#, V.USER#, V.USERNAME, V.PROGRAM
From V$Session V
Where V.USERNAME Is Not Null
If you are having oracle 9i as database then you have to use dbms_support package to generate trace files for other sessions.
Dbms_Support package by default it will not be available to get that you need to execute the script which will be available
$ORACLE_HOME/rdbms/admin/dbmssupp.sql. The base install script is dbmssupp.sql script and is run as sysdba.
DBMS_SUPPORT package is preferred over DBMS_SYSTEM and is fully supported by Oracle.
To execute the Dbms_Support package in other schemas, should have the execute privileges on Dbms_Support.
Start Tracing -- equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing
SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false); .
Stop Tracing SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL);
For Example
----------
-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
New tracing methods in Oracle 10g - DBMS_MONITOR
Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.
The new functionality works in three levels. You can use the old SID / SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.
let's now take a look at some of the features of this package.
Setting trace with DBMS_MONITOR using SID / SERIAL#
Trace can be set for the current user session, for the current session or for another users session. First lets look at tracing another users session. First we need to get the SID and SERIAL# - we will use SCOTT connected through SQL*Plus as our sample session:
SQL> select s.sid,s.serial#,s.username
2 from v$session s, v$process p
3 where s.paddr=p.addr
SQL> /
...
SID SERIAL# USERNAME
---------- ---------- ------------------------------
248 153 SCOTT
258 61 DBSNMP
251 418 SYSMAN
255 961 SYS
249 215
27 rows selected.
SQL>
OK as with previous methods we can use a SID / SERIAL# pair of 248 and 153. lets set trace for this user session:
SQL> exec dbms_monitor.session_trace_enable(248,153,TRUE,FALSE);
PL/SQL procedure successfully completed.
SQL> -- execute some sql
SQL> -- in the other session
SQL> -- turn trace off
SQL> exec dbms_monitor.session_trace_disable(248,153);
PL/SQL procedure successfully completed.
SQL>
Setting trace at the session level using DBMS_MONITOR
The same procedures can be used to set trace for the session by omitting the serial#. This is demonstrated next:
SQL> exec dbms_monitor.session_trace_enable(248);
PL/SQL procedure successfully completed.
SQL> -- execute some sql in the other session
SQL> -- turn off trace
SQL> exec dbms_monitor.session_trace_disable(248);
PL/SQL procedure successfully completed.
SQL> -- or you can turn it on with
SQL> exec dbms_monitor.session_trace_enable(248,null);
PL/SQL procedure successfully completed.
SQL> -- turn off again with:
SQL> exec dbms_monitor.session_trace_disable(248,null);
PL/SQL procedure successfully completed.
SQL>
Setting trace for the current session using DBMS_MONITOR
Setting trace for the current user session is done by leaving out the SID and SERIAL# altogether by setting them to NULL. Here is an example:
SQL> -- trace the current session
SQL> exec dbms_monitor.session_trace_enable(null,null);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> -- turn it off again
SQL> exec dbms_monitor.session_trace_disable(null,null);
PL/SQL procedure successfully completed.
SQL> -- to get waits and binds do
SQL> exec dbms_monitor.session_trace_enable(null,null,true,true);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> -- then turn off tracec
SQL> exec dbms_monitor.session_trace_disable(null,null);
PL/SQL procedure successfully completed.
SQL> -- or turn it on like this
SQL> exec dbms_monitor.session_trace_enable();
PL/SQL procedure successfully completed.
SQL> -- execute some SQL and then turn off trace
SQL> exec dbms_monitor.session_trace_disable();
PL/SQL procedure successfully completed.
SQL>
That completes some of the ways to use DBMS_MONITOR to set trace using SID, SERIAL#, or at the session level or for the current session.
Set trace using a client identifier
Tracing using the client identifier allows trace to be set across multiple sessions as many Oracle shadow processes can work on behalf of one client. Also trace is persistent across all instances and restarts. First we need to see how the client identifier is set. This can be done using the DBMS_SESSION package as follows:
SQL> exec dbms_session.set_identifier('pete id');
PL/SQL procedure successfully completed.
SQL>
We can check now for a specific identifier in the V$SESSION view with the client_identifier column.
SQL> select s.username,s.client_identifier
2 from v$session s,v$process p
3 where s.paddr=p.addr
4 and client_identifier is not null;
USERNAME
------------------------------
CLIENT_IDENTIFIER
----------------------------------------------------------------
SCOTT
pete id
OK, now we can use this information to set trace for this client identifier as follows:
SQL> exec dbms_monitor.client_id_trace_enable('pete id',true,false);
PL/SQL procedure successfully completed.
SQL> -- wait for the client session to do something
SQL> -- turn off trace as follows:
SQL> exec dbms_monitor.client_id_trace_disable('pete id');
PL/SQL procedure successfully completed.
SQL>
That was quite easy!. next let's look at setting trace at the service, module action levels.
Setting trace for service/module/action with DBMS_MONITOR
This method of setting trace acts hierarchically. The first level is that trace is set globally for the whole database (all instances) You can override this by setting an instance name in the call to turn on trace. For this example as I am on a single instance database I will leave this parameter at its default. There are three levels to the hierarchy. If we set ACTION to NULL then all actions for the module and service are traced. The next level, if we set MODULE to NULL then all actions for all modules for the specified service name are traced. The trace will be collected into multiple trace files and the new tool trcsess must be used to collate all the trace files into one usable file.
The service name can be set using the package DBMS_SERVICE and the procedure CREATE_SERVICE. Here is an example:
SQL> exec dbms_service.create_service('Test Service','test network');
PL/SQL procedure successfully completed.
SQL> -- it can be deleted with
SQL> exec dbms_service.delete_service('Test Service');
PL/SQL procedure successfully completed.
SQL>
The service name can quite often be set already by the tool. It could be used to group together a set of programs / modules that perform some business task. Next let's see how the module and actions can be set.
SQL> -- set action
SQL> exec dbms_application_info.set_action('PAYMENT');
PL/SQL procedure successfully completed.
SQL> -- set the module
SQL> exec dbms_application_info.set_module('ACCOUNTS','PAYMENT');
PL/SQL procedure successfully completed.
SQL>
To view the relevant service names, modules and actions for sessions in the database you can use the v$SESSION view as follows:
SQL> col service_name for a15 wrapped
SQL> col username for a15 wrapped
SQL> col module for a15 wrapped
SQL> col action for a15 wrapped
SQL> select s.username,s.service_name,s.module,s.action
2 from v$session s,v$process p
3 where s.paddr=p.addr;
...
USERNAME SERVICE_NAME MODULE ACTION
--------------- --------------- --------------- ---------------
SYSMAN SANS
SYSMAN SANS OEM.SystemPool
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)
SYS$USERS
SYS SANS ACCOUNTS PAYMENT
SCOTT SANS SQL*Plus
...
29 rows selected.
SQL>
As we deleted our sample service name set up with DBMS_SERVICE.CREATE_SERVICE we will just use the default value SANS inserted by Oracle in our test case. Let's test some of the methods of setting trace with this functionality.
SQL> -- set trace for all modules and actions for SANS service name
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS',DBMS_MONITOR.ALL_MODULES,DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS');
PL/SQL procedure successfully completed.
SQL> -- now trace all actions for service SANS and module accounts
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS',DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- now turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS');
PL/SQL procedure successfully completed.
SQL> -- finally test service SANS, module ACCOUNTS and action PAYMENT
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS','PAYMENT',TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS','PAYMENT');
PL/SQL procedure successfully completed.
SQL> -- you can turn on or off binds and waits as well or use the waits=>true
SQL> -- syntax instead.
SQL>
OK, that wraps up the new procedures in 10g that can be used to turn on trace in different ways to capture true end to end trace for multi-tier applications. You should also be aware that DBMS_MONITOR also provides procedures to enable statistic gathering at the same levels of client identifier and service name/module/action. These statistics are stored and can then be accessed by selecting from V$SERV_MOD_ACT_STATS and V$CLIENT_STATS views. I will not detail those procedures here as this short paper is concentrating on trace only.
Oracle Event Tracing
Oracle provides various diagnostic tools for debugging the RDBMS. Certain events
can be turned on to dump diagnstic information to trace files. Next, some
INIT.ORA parameters are available that can be used while diagnosing memory and
disk corruptions. There parameters are not set during normal operation of the
database, due they affect the performance. In the following article, some useful
events will be closer explained.
Database hang problems
If you experience database hang problems during grants of roles or privileges a
state dump may help you to solve the problem. You can get a state dump by doing
the following:
Cleanup not needed TRACE Files
$ sqlplus /nolog
SQL> connect sys/manager as sysdba
SQL> alter session set events
immediate trace name systemstate level 10';
SQL> exit;
Log out, otherwise subsequent trace dumps overwrite the existing trace file.
Generally if a session is executing DBMS_PIPE, DBMS_SQL or
DBMS_SHARED_POOL at the time a grant is issued on any of these, a hang can
happen. By killing the session using these packages (or shutting down the process
if possible) the hang can be resolved.
Out of sync file headers and controlfiles
In some shutdown scenarios (such as a system crash) file headers and controlfiles
may get out of sync. If you get "ORA-01207: file is more recent than controlfile -
old controlfile " type errors, you can troubleshoot using the following events:
$ sqlplus /nolog
SQL> connect sys/manager as sysdba
SQL> startup mount
SQL> alter session set events
immediate trace name controlf level 10';
SQL> alter session set events
immediate trace name file_hdrs level 10';
SQL> alter session set events
immediate trace name redohdr level 10';
These commands will generate a single trace file located in your user_dump_dest
directory containing information about the controlfile and data/redolog files. In an
emergency, you can use the recover clause "using backup controlfile" to possibly
recover from this scenario. However, make sure you do a full cold backup before
attempting recovery.
Tracing ORA-60x Severity Errors
Oracle Event Tracing Page 1 of 2
http://www.akadia.com/services/ora_events.html 24/Mar/09
If you find any ORA-60x errors in you ALERT.ORA system file, you should check,
why the database dumps an errorstack to the trace file. The following example
dumps the error stack every time a process encounters the ORA-604 error.
SQL> alter session set events
604 trace name errorstack forever';
Useful Events set in INIT.ORA in case of an emergency
Oracle can use event setpoints in the initialization file for tracking and validation of
objects. The last event setting (10231) will allow export to extract all data, except
the corrupted block, from a table. You should only use these under guidance from
Oracle support.
# Sets integrity checking for tables
event = "10210 trace name context forever, level 10"
# Sets integrity checking for indexes
event = "10211 trace name context forever, level 10"
# Allows export to extract all data, except the corrupted blocks
event = "10231 trace name context forever, level 10"
# Identify corrupted rollback segment when database cannot be started
event = "10015 trace name context forever"
Where to find Event Numbers ?
Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg
cd $ORACLE_HOME/rdbms/mesg
vi oraus.msg
/10015
10015, 00000, "Undo Segment Recovery"
// *Cause:
// *Action:
No comments:
Post a Comment