Oracle DBA Daily Experiences

Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area

Example:
– Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc

Information in the trace file:
…
*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3′ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?

..

Using “oradebug dump events 1″ one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

In another session login as an account as sysdba
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321

The following example shows how to set event on a oracle session

To turn on event, needs to be run as sysdba
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.

To turn off event
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.

List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

To find the trace file one using oradebug one can find it using the example below:

SQL> oradebug mysetpid
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_9964.trc

If you try to run the above command without attaching to a session it will return ORA-00074
SQL> oradebug tracefile_name
ORA-00074: no process has been specified

To use oradebug one first needs to connect to a session using the process id or ora pid.

One can do so by doing one of the following, the following example shows how to connect using OS process id or oracle PID or current session.

SELECT a.username, a.sid, a.serial#, b.spid, b.pid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username = #&39;SCOTT#&39;;

USERNAME SID SERIAL# SPID PID
—————————— ———- ———- ———— ———-
SCOTT 25 920 7106 14

SQL> !ps -ef | grep 7106
oracle 7106 6778 0 21:05 ? 00:00:00 oracleTEST (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Connect using operating system (OS) process id:
SQL> oradebug setospid 7106
Oracle pid: 14, Unix process pid: 7106, image: oracle@localhost.localdomain (TNS V1-V3)

Connecting using oracle pid:
SQL> oradebug setorapid 14
Unix process pid: 7106, image: oracle@localhost.localdomain (TNS V1-V3)

Connect to current session
SQL> oradebug setmypid
Statement processed.

Using oradebug unlimit one can set to unlimit the size of the trace file.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug unlimit
Statement processed.

Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query by program, username, machine, terminal.

To start trace:
SQL> exec dbms_support.start_trace_in_session(<SID>, <Serial#>);

To stop trace:
SQL> exec dbms_support.stop_trace_in_session(<SID>, <Serial#>);

To start trace with Wait Event data with SQL trace
exec sys.dbms_support.start_trace(<SID>, <Serial#>,waits => TRUE, binds=> TRUE);

If one needs to turn timed statistics on for another session, one can execute it through the package dbms_system.

SQL> exec sys.dbms_system.set_bool_param_in_session(sid => <sid>, serial# => <seral#>, parnam => ‘TIMED_STATISTICS’, bval => true);

To install the DBMS_SUPPORT package if it doesn’t exists in the database run the following script to create/install the package and setup access for other users other than sysdba.
SQL> connect / AS SYSDBA
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> GRANT execute ON dbms_support TO SCOTT_DBA;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

In 10g there is a new utility DBMS_MONITOR which you can use to achieve the, eg:
exec dbms_monitor.session_trace_enable (session_id=>139, serial_num=>53, waits=>true, binds=>false); For additional information you can the following article by Oracle http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html

One can also turn trace on an already running session using oradebug, once session id (SID) has been identified.
SQL> select p.PID, p.SPID, s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = 62;

PID SPID SID
———- ———— ———-
68 5243378 62

1 row selected.

SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.