PURPOSE
-------
The purpose of this note is to provide a method for taking system state dumps
on the very rare occasions when connect internal is not possible.
SCOPE & APPLICATION
-------------------
This document is intended for Oracle database administrators and support
analysts.
Taking System State Dumps without Connecting to Oracle
------------------------------------------------------
If you are encountering a database hanging situation, you need to take system
state dumps so that Oracle Support can begin to diagnose the cause of the problem.
Whenever you take such dumps for a hang it is important to take at least 3 of
them a few minutes apart, on all instances of your database. That way there is
evidence showing whether a resource is still being held from one time to the
next.
Ordinarily, you can take a systemstate dump in the case of a hanging situation
in one of two ways:
1. alter session set events 'immediate trace name SYSTEMSTATE level 10';
or
2. $ svrmgrl
connect internal
oradebug setospid <process ID>
oradebug unlimit
oradebug dump systemstate 10
(Note, do not use any semi colons in oradebug. Also, starting in 8.1.6 this should
be possible in sqlplus.)
When using one of these two methods, be sure to disconnect and connect internal
again in between dumps. That way each dump will be in a separate ora_<pid>.trc
trace file in your user_dump_dest.
On very rare occasions it is not possible to make the svrmgrl or
sqlplus connection necessary for issuing these commands. In that event, there
is still a back door method of getting the systemstate dump using a debugger
like dbx, if that is available on your system. The process you attach to will
most likely dump core and be killed, so do not attach to an Oracle background
process. Here is the syntax:
dbx -a PID (where PID = any oracle shadow process)
dbx() print ksudss(10)
...return value printed here
dbx() detach
First, you will need to find a shadow process:
(saki) % ps -ef |grep sqlplus
osupport 78526 154096 0 12:11:05 pts/1 0:00 sqlplus scott/tiger
osupport 94130 84332 1 12:11:20 pts/3 0:00 grep sqlplus
(saki) % ps -ef |grep 78526
osupport 28348 78526 0 12:11:05 - 0:00 oracles734 (DESCRIPTION=(LOCAL
osupport 78526 154096 0 12:11:05 pts/1 0:00 sqlplus scott/tiger
osupport 94132 84332 1 12:11:38 pts/3 0:00 grep 78526
You will attach to shadow PID 28348, enter the print ksudss (10) command
when the prompt comes back, and detach:
(saki) % dbx -a 28348
Waiting to attach to process 28348 ...
Successfully attached to oracle.
warning: Directory containing oracle could not be determined.
Apply 'use' command to initialize source path.
Type 'help' for help.
reading symbolic information ...
stopped in read at 0xd016fdf0
0xd016fdf0 (read+0x114) 80410014 lwz r2,0x14(r1)
(dbx) print ksudss(10)
2
(dbx) detach
You will find a systemstate in the user_dump_dest directory under the PID
trace (and probably a useless core file as well).
(saki) % ls -lrt *28348*
-rw-r----- 1 osupport dba 46922 Oct 10 12:12 ora_28348.trc
core_28348:
total 72
-rw-r--r-- 1 osupport dba 16567 Oct 10 12:12 core
drwxr-xr-x 7 osupport dba 12288 Oct 10 12:12 ../
drwxr-x--- 2 osupport dba 512 Oct 10 12:12 ./
You will find the usual header information in the trace file. On 7.3.4 OPS
systems this will be followed by some lock information. Then the system state
begins. On Oracle8 OPS and non-OPS systems, and on 7.3.4 non-OPS systems the
system state immediately follows the header information.
Here is the beginning of our dump:
Dump file /oracle/mpp/734/rdbms/log/ora_28348.trc
Oracle7 Server Release 7.3.4.4.1 - Production
With the distributed, replication, parallel query, Parallel Server
and Spatial Data options
PL/SQL Release 2.3.4.4.1 - Production
ORACLE_HOME = /oracle/mpp/734
System name: AIX
Node name: saki
Release: 3
Version: 4
Machine: 000089914C00
Instance name: s734
Redo thread mounted by this instance: 2
Oracle process number: 0
Unix process pid: 28348, image:
ksinfy: nfytype = 0x5
ksinfy: calling scggra(&se)
scggra: SCG_PROCESS_LOCKING not defined
scggra: calling lk_group_attach()
ksinfy: returning
*** SESSION ID:(12.15) 2000.10.10.12.11.06.000
ksqcmi: get or convert
ksqcmi: get or convert
*** 2000.10.10.12.12.08.000
===================================================
SYSTEM STATE
.....
Make sure there is an END OF SYSTEM STATE in the file. You can grep for
it or do a search in vi. If it is not there then the dump is not complete,
possibly because max_dump_file size is too small in the init.ora.
Update for 10g:
In some cases, no connections are allowed on the instance (in some ORA-20 situations for example).
As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to
generate traces.
sqlplus -prelim / as sysdba
For example
export ORACLE_SID=PROD ## Replace PROD with the SID you want to trace
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10
Related Postsoracle@ www.ads1st.com $sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 17 22:12:32 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0www.ads1st.com oradebug setmypid
Statement processed.
www.ads1st.com oradebug unlimit
Statement processed.
www.ads1st.com oradebug dump systemstate 1;
Statement processed.
www.ads1st.com oradebug tracefile_name;
/oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2691.trc
www.ads1st.com ho more /oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2691.trc
Trace file /oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2691.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/11g
System name: Linux
Node name: www.ads1st.com
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 02:16:47 EDT 2009
Machine: i686
Instance name: r11g
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 2691, image: oracle@www.ads1st.com*** 2010-08-17 22:12:38.371
Processing Oradebug command ’setmypid’*** 2010-08-17 22:12:38.371
Oradebug command ’setmypid’ console output: <none>*** 2010-08-17 22:12:43.885
Processing Oradebug command ‘unlimit’*** 2010-08-17 22:12:43.885
Oradebug command ‘unlimit’ console output: <none>*** 2010-08-17 22:12:57.134
Processing Oradebug command ‘dump systemstate 1′
===================================================
SYSTEM STATE (level=1)
————
System global information:
processes: base 0×3c1a62e0, size 100, cleanup 0×3c1abb10
allocation: free sessions 0×3c1d9654, free calls (nil)
control alloc errors: 0 (process), 0 (session), 0 (call)
PMON latch cleanup depth: 0
seconds since PMON’s last scan for dead processes: 43
system statistics:
0 OS CPU Qt wait time
57 logons cumulative
26 logons current
11470 opened cursors cumulative
23 opened cursors current
66 user commits
3 user rollbacks
151 user calls
180258 recursive calls
648 recursive cpu usage
1 pinned cursors current
99020 session logical reads
–More–(2%)………
SO: 0×3c1ce514, type: 2, owner: (nil), flag: INIT/-/-/0×00 if: 0×3 c: 0×3
proc=0×3c1ce514, name=process, file=ksu.h LINE:11459, pg=0
(process) Oracle pid:0, ser:0, calls cur/top: (nil)/(nil)
flags : (0×20) PSEUDO
flags2: (0×0), flags3: (0×0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 0 0 0
last post received-location: No post
last process to post me: none
last post sent: 0 0 0
last post sent-location: No post
last process posted by me: none
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0×3c1ce514
O/S info: user: , term: , ospid: (DEAD)
OSD pid info: Unix process pid: 0, image: PSEUDO
Dump of memory from 0×3C1A6470 to 0×3C1A65F4
3C1A6470 00000000 00000000 00000000 00000000 [................]
Repeat 23 times
3C1A65F0 00000000 [....]
BEGIN DUMP RESERVED PROCESSES
END DUMP RESERVED PROCESSES
KSS PSEUDO PARENT:
—————————————-
SO: 0×20007a10, type: 5, owner: 0×3bd6c55c, flag: INIT/-/-/0×00 if: 0×3 c: 0×3
proc=(nil), name=kss parent, file=kss2.h LINE:125, pg=0
END OF SYSTEM STATE*** 2010-08-17 22:12:57.151
Oradebug command ‘dump systemstate 1′ console output: <none>*** 2010-08-17 22:13:02.029
Processing Oradebug command ‘tracefile_name’*** 2010-08-17 22:13:02.030
Oradebug command ‘tracefile_name’ console output:
/oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2691.trc
www.ads1st.com select * from v$version;BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – ProductionElapsed: 00:00:00.36