본문 바로가기

Oracle Wait Interface

Systemstate dumps


Systemstate dumps

A system state dump is trace file containing a snapshot of all processes running in a database. It is used to diagnose hang issues and has more information compared to a hanganalyze dump.There are various ways to dump the systemstate.


If you can connect to the database you can dump it using

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit  this is important so that the file is not truncated.
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
c:\oracle\product\10.2.0\admin\usyd\udump\usyd_ora_17452.trc

A complete file should have the following string at the end
END OF SYSTEM STATE

If you want only the short stack of each process in the instance
you need to use

SQL> oradebug dump systemstate 256
Statement processed.

Alternatively you can dump the system state using

SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10'; 

Session altered.

If you cannot connect to the database instance you can use a session less connection in 10g using
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

Prior to 10g you can use a unix debugger like gdb, adb or dbx to attach to a shadow process and dump the system state using the function ksudss which is the subroutine in the Oracle source code which does the same.

eiger-> ps -ef | grep HRDEV | grep j00 | grep –v grep


oracle 14022 1 0 Dec10 ? 00:00:21 ora_j000_HRDEV

eiger-> gdb
GNU gdb Red Hat Linux (6.3.0.0-1.132.EL4rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.Type "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
(gdb) attach
Argument required (process-id to attach).
(gdb) attach 14022
Attaching to process 14022
Reading symbols 
…… (output deleted for readability purposes)
0x000000324f7c7c0c in semtimedop () from /lib64/tls/libc.so.6
(gdb) print ksudss(10)
[Switching to Thread 182922098368 (LWP 14022)]
$1 = -1073747016
(gdb) detach
Detaching from program: /usr/opt/oracle/u00/app/oracle/product/10.2.0/bin/oracle, process 14022
(gdb) quit

Since in the above case we have attached to a job process the trace file containing the dump will be in bdump else the trace file would be in udump.

For RAC databases if you need to dump the systemstate on all instances

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u00/app/oracle/admin/F8902UAT/udump/f8902uat1_ora_2513.trc

The trace file will have only the following contents

PORADEBUG REMOTE COMMAND BEGIN SERIAL:0 MYPID:2513
PORADEBUG REMOTE COMMAND END SERIAL:0 MYPID:2513 [SUCCESS]

The systemstate dump will be written to by the DIAG process on each node.


Please use these commands with caution. On busy systems with a large number of processes these commands can take quite a while. Also a systemstate dump need not always portray the correct information of a system especially when the time to 
dump all the processes is long.

Labels: 


Comments:
Try oradebug -g systemstate 10
This should dump in a single file the systemstate of all the instances.
Thanks but the command doesn't seem to work 

SQL> oradebug setmypid
Statement processed.
SQL> oradebug -g systemstate 10
ORA-00089: invalid instance number in ORADEBUG command
Ok...sorry.. i missed something i guess..

oradebug setmypid
oradebug -g all systemstate 10

Does it work?
No worries but that is the same syntax as in my post albeit at a higher level
there is a difference ..."you have a "dump" specified! and this is without that.
Post a Comment

Subscribe to Post Comments [Atom]

http://el-caro.blogspot.com/2006/12/systemstate-dumps.html 

'Oracle Wait Interface' 카테고리의 다른 글

buffer is pinned count  (0) 2014.02.07
조동욱 님의 OWI (Oracle Wait Interface)  (0) 2011.08.04
HangAnalyze 3  (1) 2011.07.31
Dump SGA level 2  (0) 2011.07.31