How to handle HANG Databases

How to generate systemstatedump for DB1. in HANG: SQLgt; sqlplus-prelimassysdbaSQLgt; altersessionsetmax_dump_file_si

How to deal with the database in HANG 1. Generate systemstate dump: SQLgt; sqlplus-prelim/as sysdbaSQLgt; alter session set max_dump_file_si

Homepage → Database Technology

Background:

Read News

How to handle HANG Databases

[Date: 2013-12-12] Source: Linux community Author: zhou1862324 [Font:]

How to handle HANG Databases

1. Generate systemstate dump:
SQL> sqlplus-prelim/as sysdba
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events 'immediate trace name systemstate level 10 ';
Wait for some 30 seconds
SQL> alter session set events 'immediate trace name systemstate level 10 ';
Wait for some 30 seconds
SQL> alter session set events 'immediate trace name systemstate level 10;

2. Start a new SESSION:
SQL> sqlplus-prelim/as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug dump hanganalyze 3
Wait for 30 seconds
SQL> oradebug dump hanganalyze 3
Wait for 30 seconds
SQL> oradebug dump hanganalyze 3

Analyze the generated file.

Appendix: system state dump when connection to the instance is not possible
There are several ways of getting more information when connection to an instance is not possible by sqlplus. It is useful to collect as much information about a hang before clearing the instance.

1) Using OS debuggers like dbx or gdb.
For example:
$ Ps-ef | grep $ Oracle_SID # Use PID of any User Process instead of BG Process
$ Gdb $ ORACLE_HOME/bin/oracle
Print ksudss (10)
The systemstate dump will be in the bdump or udump depending on the PID chosen
2.) Using the new 10.1 sqlplus-prelim option.
For example:
Export ORACLE_SID = PROD # Replace PROD with the SID you want to trace
Sqlplus-prelim/as sysdba
Oradebug setmypid
Oradebug unlimit;
Oradebug dump systemstate 10
3.) Finally if either option (1) or (2) is not doable, please remember to take stacks of all the Oracle Background process using pstack or gdb
For example:
Script stack. log
Pstack
Pstack
4.) Finally you may also want to collect some truss, tusc or strace of sqlplus
Note: The recommended systemstate level for 10g is 266

0

VIP cannot be started properly, error CRS-1006

GoldenGate suspected BUG: info all cannot view extract

HANG

Drop user hang solution (January 23)

Batch user names and passwords error caused by business users (January 1, January 09)

Oracle Database shutdown immediate (January 19)

Image Information

View All comments in this article (0)

Comments

Latest Information

Popular this week

Introduction to Linux community-advertisement service-website map-help Info-contact us
The articles published on this site (LinuxIDC) do not mean that they agree with the statement or description. They only provide more information and do not constitute any suggestions.


Copyright©2006-2011 Linux community All rights reserved Zhejiang ICP backup No. 06018118

,