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 2nd
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
