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
..

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

– backup control file in binary format to a file named /tmp/x.ctl, it will preserve the backup information of the database
SQL> alter database backup controlfile to ‘/tmp/x.ctl’;

Database altered.

– backup control file in readable format in /tmp/x.trc, the script created doesn’t contain information about backups so if you backup in the binary format it will keep the backup information
SQL> alter database backup controlfile to trace as ‘/tmp/x.trc’;

Database altered.

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.

To turn on trace for a specific event.
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 8, '');
Example: Enable trace at level 8 for session id 10046
exec dbms_system.set_ev(12345, 543211, 10046, 8, '');

– To turn off the tracing:
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 0, '');
Example: exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:
– ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK LEVEL 3′;

It will write to the alert.log
Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off one would run the following SQL:
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
SQL> alter system set event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on event for.

Using strace (linux)/truss (AIX) one can trace the system calls as program executes. It can be useful in identifying where a program identify an issue depending on the situation.

Example below shows output when strace is called for echo
$ strace echo “Hello”
execve(“/bin/echo”, ["echo", "Hello"], [/* 42 vars */]) = 0 – shows process being executed
uname({sys=”Linux”, node=”localhost.localdomain”, …}) = 0
brk(0) = 0×505000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95556000
access(“/etc/ld.so.preload”, R_OK) = -1 ENOENT (No such file or directory)
open(“/etc/ld.so.cache”, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=101242, …}) = 0
mmap(NULL, 101242, PROT_READ, MAP_PRIVATE, 3, 0) = 0x2a95557000
close(3) = 0
open(“/lib64/tls/libc.so.6″, O_RDONLY) = 3 – libraries being loaded
read(3, “\177ELF\2\1\1\3>\1\240\304\241\f=”…, 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=1622288, …}) = 0
mmap(0x3d0ca00000, 2314184, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3d0ca00000
mprotect(0x3d0cb2c000, 1085384, PROT_NONE) = 0
mmap(0x3d0cc2c000, 20480, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x12c000) = 0x3d0cc2c000
mmap(0x3d0cc31000, 16328, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x3d0cc31000
close(3) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95570000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a95571000
mprotect(0x3d0cc2c000, 12288, PROT_READ) = 0
mprotect(0x3d0c914000, 4096, PROT_READ) = 0
arch_prctl(ARCH_SET_FS, 0x2a95570b00) = 0
munmap(0x2a95557000, 101242) = 0
brk(0) = 0×505000
brk(0×526000) = 0×526000
open(“/usr/lib/locale/locale-archive”, O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=48509536, …}) = 0
mmap(NULL, 48509536, PROT_READ, MAP_PRIVATE, 3, 0) = 0x2a95572000
close(3) = 0
fstat(1, {st_mode=S_IFCHR|0600, st_rdev=makedev(136, 2), …}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a983b6000
write(1, “Hello\n”, 6Hello — message being written to stdout (1)
) = 6
exit_group(0) = ?

– One can use the following query to find the name of the trace filename

– To find the name of the current session
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename
from v$process p, v$session s, v$instance
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum = 1);

FILENAME
——————————
orcl_ora_1360.trc

– To find the filename for another known SID
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename
from v$process p, v$session s, v$instance
where p.addr = s.paddr
and s.sid = 170;

FILENAME
——————————
orcl_ora_2552.trc

To make trace files visible to all users, set the parameter _TRACE_FILES_PUBLIC=TRUE, this is undocumented parameter. This parameter requires a restart of instance to take into effect.

View the following link to find if parameter can be modified without restart or requires a restart.

http://oraclespin.wordpress.com/2008/05/14/internal-parameters/