How to take system state dump when database is not allowing new connections

To diagnose the database hanging issues DBAs need to take the system state dumps and generally it is generated by connecting to the database as sysdba (or internal). There would be some situations where database will not be allowing to create a new connection (not allowing to connect as a SYSDBA/INTERNAL) in those cases use the following method to dump the system state dumps:

Thus, if following command does not work because of hanging issues -

sqlplus '/as sysdba'

1. Use below command in 10gR2 and above versions:

$sqlplus -prelim '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 17 17:32:10 2012

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

SQL> oradebug setmypid

Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> oradebug dump systemstate 266

Statement processed.

SQL> oradebug dump systemstate 266

Statement processed.

The system state dump will generate at user_dump_dest location.

Make sure that the trace file contains the statement like below (if not appearing  set - alter system set max_dump_file_size=unlimited  and re-run the above commands from a new session):

END OF SYSTEM STATE

2. Use  gdb or dbx for all database versions  to generate system state dump:

  - Find out a shadow process (do NOT use background process as it may kill the process):

$ ps -ef | grep sqlplus

oravis    7477   939  0 18:05 pts/16   00:00:00 sqlplus

oravis    7841 12008  0 18:06 pts/18   00:00:00 grep sqlplus

oravis    8252  8174  0 16:11 pts/20   00:00:00 sqlplus

oravis   12176 11925  0 14:28 pts/15   00:00:00 sqlplus

 $ ps -ef | grep 7477

oravis    7477   939  0 18:05 pts/16   00:00:00 sqlplus

oravis    7478  7477  0 18:05 ?        00:00:00 oracleVIS (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oravis    8063 12008  0 18:06 pts/18   00:00:00 grep 7477

 Run gdb / dbx command as below

$ gdb -pid 7478

(for dbx syntax: dbx –a 7478)

This GDB was configured as "x86_64-redhat-linux-gnu".

Attaching to process 7478

Reading symbols from /u08/app/oravis/db/tech_st/11.1.0/bin/oracle...(no debugging symbols found)...done.

Reading symbols from /u08/app/oravis/db/tech_st/11.1.0/lib/libskgxp11.so...(no debugging symbols found)...done.

Loaded symbols for /u08/app/oravis/db/tech_st/11.1.0/lib/libskgxp11.so

Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.

Etc ….

Loaded symbols for /u08/app/oravis/db/tech_st/11.1.0/lib/libnque11.so

0x0000003f9e60d290 in __read_nocancel () from /lib64/libpthread.so.0

(gdb) print ksudss(10) <<< once you get gdb prompt enter the command print ksudss(10)

$1 = 0

(gdb) print ksudss(10)  <<< to take 2^nd time in the same trace file.

$2 = 0

(gdb) detach

Detaching from program: /u01/app/oravis/db/tech_st/11.1.0/bin/oracle, process 7478

The trace file will be generated at user_dump_dest location and it can be uploaded to support