How to handle HANG Databases

Source: Internet
Author: User
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


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


  • 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)


    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

    Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.