不能連線資料庫收集Systemstate Dumps資訊

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. $ sqlplus 
      connect sys/passwd as sysdba
      oradebug setospid 
      oradebug unlimit
      oradebug dump systemstate 10

(Note, do not use any semi colons in oradebug.  If using a version older than 9, you
will need to use svrmgrl; connect internal) 

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_.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 and higher:
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