SYSTEMSTATE DUMPs: An example of how to take and upload them [ID 125568.1]

Applies to:

Enterprise Manager for Oracle Database - Version: 8.1.7.4 and later [Release: and later ]
Oracle Server - Enterprise Edition - Version: 6.0.0.0 and later [Release: 6.0 and later]
Oracle Database Configuration Assistant - Version: 8.1.7 and later [Release: 8.1.7 and later]
Information in this document applies to any platform.

Purpose

To provide step by step instructions for taking system state dumps to assist database hanging/locking issues and to further provide instructions for
delivering the output for examination

Scope and Application

scope

SYSTEMSTATE DUMPs: An example of how to take and upload them

1) Get the process/procedure/query/command/database hung

2) Start SQLPLUS and connect as SYSDBA or SYSOPER

Prior to 11g

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266';

11g and later

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 258';


If you cannot connect to the database to take the dump the following note may assist you in taking the dump

Note:121779.1 Taking Systemstate dumps when you cannot connect to Oracle

3) Exit SQLPLUS

4) Wait 5 minutes

5) Repeat steps 2-4 ... 2 more times

6) At this point you can do what is necessary to unhang the process

7) Locate the trace files (3) in the user_dump_dest

The user_dump_dest is defined in your parameter file or can be viewed by SHOW PARAMETER USER_DUMP_DEST

8) Upload the 3 trace files via My Oracle Support

NOTE :

If REAL APPLICATION CLUSTER (RAC) is being used ... it is strongly suggested that systemstates be done from each node ... after which ... they should be renamed to clearly indicate which node the dump originated from if it is not already clear by the file name

EXAMPLE:

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 12 18:21:40 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Session altered.

SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /ora/ora_11.2.0.2/diag/rdbms/ora11202/ora11202/trace

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

> cd /ora/ora_11.2.0.2/diag/rdbms/ora11202/ora11202/trace

> ls -alt

-rw-r----- 1 oracle oinstall 1461273 Oct 12 18:19 ora11202_ora_3915.trc
-rw-r----- 1 oracle oinstall 202940 Oct 12 18:19 ora11202_ora_3915.trm
drwxr-x--- 3 oracle oinstall 20480 Oct 12 18:19 .
-rw-r----- 1 oracle oinstall 194243 Oct 12 18:19 alert_ora11202.log
drwxr-xr-x 2 oracle oinstall 4096 Oct 11 16:06 cdmp_20101011160628
drwxr-x--- 15 oracle oinstall 4096 Sep 14 15:45 ..

> more ora11202_ora_3915.trc

Trace file /ora/ora_11.2.0.2/diag/rdbms/ora11202/ora11202/trace/ora11202_ora_391
5.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /ora/ora_11.2.0.2/11202
System name: Linux
Node name: filnx10
Release: 2.6.9-42.0.3.EL
Version: #1 Mon Sep 25 17:14:19 EDT 2006
Machine: i686
Instance name: ora11202
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 3915, image: oracle@filnx10 (TNS V1-V3)


*** 2010-10-12 18:19:39.705
*** SESSION ID:(1.7) 2010-10-12 18:19:39.705
*** CLIENT ID:() 2010-10-12 18:19:39.705
*** SERVICE NAME:(SYS$USERS) 2010-10-12 18:19:39.705
*** MODULE NAME:(sqlplus@filnx10 (TNS V1-V3)) 2010-10-12 18:19:39.705
*** ACTION NAME:() 2010-10-12 18:19:39.705
*** ACTION NAME:() 2010-10-12 18:19:39.705

===================================================
SYSTEM STATE (level=10, with short stacks)
------------
System global information:
processes: base 0x3f3bac6c, size 150, cleanup 0x3f3caa8c
allocation: free sessions 0x3e87927c, 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: 49
system statistics:
0 OS CPU Qt wait time
40 logons cumulative
20 logons current
5281 opened cursors cumulative
29 opened cursors current
13 user commits
0 user rollbacks
114 user calls
31001 recursive calls
904 recursive cpu usage
10 pinned cursors current
....

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22308399/viewspace-750809/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22308399/viewspace-750809/