How to generate systemstate dump & hanganalyze dump ?

nohup sqlplus sysman/oem10g @create_table.sql >create_table.log &

1- For errorstack, you can use:

*SQL*Plus (9i+ generic)

$ sqlplus

connect / as sysdba

oradebug setospid

oradebug unlimit

oradebug dump errorstack 3

$ sqlplus /nolog

connect username/password

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever, level 12';

[[ Run the query ]]

alter session set events '10046 trace name context off';

quit

How to generate a systemstate dump ?

####################################

sqlplus "/ as sysdba"

alter session set max_dump_file_size = unlimited;

alter session set events 'immediate trace name systemstate level 266';

alter session set events 'immediate trace name systemstate level 266';

alter session set events 'immediate trace name systemstate level 266';

How to generate a hanganalyze ?

################################

HANGANALYZE may be executed using the following syntax:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level 10';

Or

when logged in with the “SYSDBA” role,

ORADEBUG hanganalyze 10

To perform cluster wide HANGANALYZE use the following syntax:

ORADEBUG setmypid

ORADEBUG setinst all

ORADEBUG -g def hanganalyze 10

The sets the amount of additional information that will be extracted from the processes found by HANGANALYZE (ERROSTACK dump) based on the “STATE” of the node. See further detail in the “STATE DESCRIPTION” section of this document.

The levels are defined as follows:

10 Dump all processes (IGN state)

5 Level 4 + Dump all processes involved in wait chains (NLEAF state)

4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

1-2 Only HANGANALYZE output, no process dump at all

Example:

@@@@@@@

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

SQL> alter session set events 'immediate trace name systemstate level 266';

Session altered.

SQL> alter session set events 'immediate trace name systemstate level 266';

Session altered.

SQL> alter session set events 'immediate trace name systemstate level 266';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level 10';

Session altered.

SQL> ORADEBUG setmypid

Statement processed.

SQL> ORADEBUG setinst all

Statement processed.

SQL> ORADEBUG -g def hanganalyze 10

Hang Analysis in /oracle/admin/loypt060/bdump/loypt601_diag_647218.trc

SQL>

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 18 15:42:26 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> set time on

15:42:26 SQL> CREATE TABLESPACE "TXS_TEST" LOGGING DATAFILE '/oratxp/060/data/data04/txs_test.dbf' SIZE 32000M REUSE AUTOEXTEND ON NEXT 2000M MAXSIZE 3276

7M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

Tablespace created.

16:31:09 SQL> Error 45 initializing SQL*Plus

Internal error