DBA from Crisis to Confidence

Now directly call the Oracle function that generates a system state dump:

(gdb) print ksudss(10)
[Switching to Thread -1218553600 (LWP 15965)]
$1 = 213658428
(gdb) detach
Detaching from program: /usr/app/oracle/product/10.2.0/db_1/bin/oracle, process 15965
(gdb) quit
 
数据挖掘研究院
 

In this particular case, the shadow process survived the experiment - which is not always the outcome in real-life scenarios:

dbhost|/home/oracle > ps -ef | grep 15965 | grep -v grep
oracle   15965 15964  0 09:20 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
 数据挖掘研究院 

The trace file, that contains the system state dump, can be found in the $ORACLE_BASE/admin/<dbname>/udump directory:

数据挖掘研究院

Dbhost|/usr/app/oracle/admin/myrac/udump > ls -ltr | tail -n 1
-rw-r-----    1 oracle   oinstall  2445565 Sep 23 09:29 myrac1_ora_15965.trc
 
数据挖掘研究院
 

Again, have a look inside to make sure the file contains what we need – see examples of required patterns in 2.2.2. 数据挖掘研究院

C. Process State and Error stack:

When: Hang & deadlock situations in a database or ASM – when you can narrow down your suspicions to a specific Oracle process.

Procedure: Run the procedure at least two times, with one to two minute intervals between the ′errorstack′ commands. 数据挖掘研究院

In the following demo, we obtain the error stack, call stack and process state info for the RECO background process. 数据挖掘研究院

Dbhost:/home/oracle > ps -ef | grep reco | grep ora_
oracle    7080     1  0 Sep22 ?        00:00:00 ora_reco_myrac1
 
数据挖掘研究院
 

After you have found the process′s OS PID, connect to the Oracle instance: 数据挖掘研究院

Dbhost:/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:14:44 2005

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> oradebug setospid 7080
Oracle pid: 13, Unix process pid: 7080, image: oracle@caosspc40.ca.oracle.com (RECO)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.
... wait a minute or two here...
SQL> oradebug dump errorstack 3
Statement processed.
SQL> oradebug tracefile_name
/usr/app/oracle/admin/myrac/bdump/myrac1_reco_7080.trc
SQL> exit