Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 13, 2006

DBA from Crisis to Confidence

By Sreeram Surapaneni

Every DBA needs to know how to do their work under pressure, while facing crisis after crisis. Our most important contribution is to keep the database running. It's an on-call function; you never know what might happen. Half the branch was up all night last night restoring a database because of a failure. Data recovery is very important, and so is performance tuning and problem solving. IT tends to start at the back end and work out to see where the problem lies, so generally, we're one of the first areas that will be contacted when a problem occurs.

There are hundreds of possible sources of downtime hiding in the crooks and crannies of your company. Many outage situations cascade into each other, where an attempt to resolve a problem creates a new outage and quickly spirals further and further out of control. DBAs should always try to obtain critical diagnostics even when under pressure to bring the database up or press before system reboot.

This article describes some of the steps for a thorough post-mortem that a DBA in crisis mode should follow, especially in crises that happened unknowingly, caused database outage or company dollars. It is very important that some of these tasks listed below be performed before you resort to such drastic measures as restarting the database or server it self. I classify these tasks into three categories, tested on 10g and Linux environments. Please ensure you can run the tasks on your specific environment before you use them.

  1. hanganalyze;
  2. system state dump;
  3. Errorstack if a specific process is at fault.

After the system has been stabilized and when there is time, open a Service Request with Oracle. Don't forget to collect necessary trace and log files listed in the end of this article and upload them for the Oracle to work on your TAR

A. Hanganalyze:

HANGANALYZE helps you to determine if a performance analysis will be required to diagnose the real problem when a HANG is taking place

When: Hang & deadlock situations.

Scope: Always perform cluster-wide 'hanganalyze' if it is RAC database.

Procedure: Run at least two times with one to two minute intervals between 'hanganalyze' commands, waiting for couple of minutes to allow time to identify process state changes.

dbhost:/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:14:44 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
... wait a minute or two here...
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
SQL> exit

Make sure the following pattern is present in the trace file – in my demo case, it is two identical entries in 'myrac1_diag_7056.trc' file since I have done 'hanganalyze' twice:

*** 2005-09-23 10:50:46.051
====================
END OF HANG ANALYSIS
END OF HANG ANALYSIS

The trace file for the DIAG background process on the local node contains the 'hanganalyze' results – upload the file to Metalink.

Another easy method of Hanganalyze :

If you cannot connect to database/ASM instances on any cluster node with SQL*Plus, then you may want to try the '-prelim' option as follows. Note the difference in the SQL*Plus banner – it is a 'lightweight' session, no SQL is allowed in the connected session:

dbhost:/home/oracle > sqlplus -prelim / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:20:38 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
...wait a minute or two here...
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
SQL> exit

If you cannot connect to the database using either one of the above described methods, or if hanganalyze doesn't come back and doesn't produce any traces, then abandon 'hanganalyze' attempts and proceed to the 'systemstate' dump part of the emergency diagnostics routine.

The most important thing in the hanganalyze trace file is the 'wait-for-graph' information. It will show the blocking/locking resources. If you want to dump information specific to an instance, oradebug setinst can be used. HANGANALYZE run at levels higher than 3 may generate a huge number of trace files for large systems.

B. System state dump

When: Hang & deadlock situations in a RAC database or ASM instance.

Scope: Always obtain results on all cluster nodes. Do it this way even if it is a single node that experiences problems. In a RAC environment resources are globally managed, hence a blocker process that runs on a different cluster node may be causing a problem. If you do not obtain cluster-wide systemstate information then you will never be able to discover this.

Procedure: Run this at least two times, with one to two minute intervals between 'systemstate' commands, which will provide evidence showing whether a resource is still being held from one time to the next. It is important to ensure max_dump_file size has been set, if you are handling large databases, to avoid filling up your filesystem.

(Systemstate dump may not be possible in all the instances, especially when the SGA is in Gigs. I have seen a couple of cases where systemstate dumps take hours, filling up gigs of storage. In addition, analysis of Systemstate dump requires experienced support personnel.

cluster-wide systemstate dump:

On any one cluster node, run the following:

caosspc40|/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:14:44 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all dump systemstate 10
Statement processed.
... wait a minute or two here...
SQL> oradebug -g all dump systemstate 10
Statement processed.
SQL> exit

The above forces the DIAG background process on each node to generate systemstate dump on a local node. Hence this time the results won't be in ' user_dump_dest' directory - you should look for systemstate dump info in ' background_dump_dest', trace file of DIAG process - like 'myrac1_diag_7056.trc' in the example. Make sure you have it generated on all nodes – collect DIAG trace files from all nodes. If the above procedure does not work for any reason, then get systemstate dump individually on every node – see below.

Individual systemstate dumps:

If you have to generate systemstate information individually on each cluster node, use following the procedure:

dbhost:/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:14:44 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter session set tracefile_identifier='sysstatedump';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
... wait a minute or two here...
SQL> oradebug dump systemstate 10
Statement processed.
SQL> exit

The dump files are generated in the $ORACLE_BASE/admin/<DB_name>/udump directory. It's easy to locate the trace file as it will have 'systemstate' suffix in its name:

dbhost:/usr/home/oracle/admin/myrac/udump > ls -l | grep systemstate
-rw-r-----    1 oracle   oinstall  2352795 Sep 22 16:57 myrac1_ora_16571_sysstatedump.trc

Have a look inside the trace file to make sure the file contains what we need. You should see the following somewhere at the beginning of the file:

*** SESSION ID:(93.7828) 2005-09-23 09:29:35.901
===================================================
SYSTEM STATE
------------
System global information:

In addition, the file should end with the following line:

END OF SYSTEM STATE

Actually, because the system state information has been dumped twice into the same trace file, the above patterns should appear twice.

Still not a rocket science... - individual system state dumps using OS debugger

You are experiencing a severe database hang and can't even login to the database on a particular node. Then you have to use Linux 'gdb' debugger to generate a system state dump on every node where you can't connect via SQL*Plus.

Following is the how-to steps.

Pick a user (a.k.a. shadow) process. Do not attach with gdb to a background process - you can crash the entire instance if the background process dumps the core during the procedure. The example assumes that the database name is 'myrac':

dbhost|/home/oracle > ps -ef | grep oraclemyrac | grep -v grep
oracle    8136     1  0 Sep22 ?        00:00:01 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    8425     1  0 Sep22 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    8433     1  0 Sep22 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    8439     1  0 Sep22 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   15965 15964  0 09:20 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Let's choose the shadow process with PID=15965 as a victim for gdb. Invoke the debugger and attach to the above process. Do not forget you have to tell gdb which executable file the process is running:

Dbhost:/home/oracle > which gdb
/usr/bin/gdb
Dbhost://home/oracle > gdb $ORACLE_HOME/bin/oracle 15965
GNU gdb Red Hat Linux (6.1post-1.20040607.52rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i386-redhat-linux-gnu"...(no debugging symbols found)...Using host libthread_db library "/lib/tls/libthread_db.so.1".
Attaching to program: /usr/app/oracle/product/10.2.0/db_1/bin/oracle, process 15965
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libskgxp10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libhasgen10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libhasgen10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libskgxn2.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libskgxn2.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libocr10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libocr10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libocrb10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libocrb10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libocrutl10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libocrutl10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libjox10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libjox10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libclsra10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libclsra10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libdbcfg10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libdbcfg10.so
Reading symbols from /usr/app/oracle/product/10.2.0/db_1/lib/libnnz10.so...done.
Loaded symbols for /usr/app/oracle/product/10.2.0/db_1/lib/libnnz10.so
Reading symbols from /usr/lib/libaio.so.1...done.
Loaded symbols for /usr/lib/libaio.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/tls/libm.so.6...done.
Loaded symbols for /lib/tls/libm.so.6
Reading symbols from /lib/tls/libpthread.so.0...done.
[Thread debugging using libthread_db enabled]
[New Thread -1218553600 (LWP 15965)]
Loaded symbols for /lib/tls/libpthread.so.0
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/tls/libc.so.6...done.
Loaded symbols for /lib/tls/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...done.
Loaded symbols for /lib/libnss_files.so.2
0x0011ca4e in __read_nocancel () from /lib/tls/libpthread.so.0
(gdb)

Now directly call the Oracle function that generates a system state dump:

(gdb) print ksudss(10)
[Switching to Thread -1218553600 (LWP 15965)]
$1 = 213658428
(gdb) detach
Detaching from program: /usr/app/oracle/product/10.2.0/db_1/bin/oracle, process 15965
(gdb) quit

In this particular case, the shadow process survived the experiment - which is not always the outcome in real-life scenarios:

dbhost|/home/oracle > ps -ef | grep 15965 | grep -v grep
oracle   15965 15964  0 09:20 ?        00:00:00 oraclemyrac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The trace file, that contains the system state dump, can be found in the $ORACLE_BASE/admin/<dbname>/udump directory:

Dbhost|/usr/app/oracle/admin/myrac/udump > ls -ltr | tail -n 1
-rw-r-----    1 oracle   oinstall  2445565 Sep 23 09:29 myrac1_ora_15965.trc

Again, have a look inside to make sure the file contains what we need – see examples of required patterns in 2.2.2.

C. Process State and Error stack:

When: Hang & deadlock situations in a database or ASM – when you can narrow down your suspicions to a specific Oracle process.

Procedure: Run the procedure at least two times, with one to two minute intervals between the 'errorstack' commands.

In the following demo, we obtain the error stack, call stack and process state info for the RECO background process.

Dbhost:/home/oracle > ps -ef | grep reco | grep ora_
oracle    7080     1  0 Sep22 ?        00:00:00 ora_reco_myrac1

After you have found the process's OS PID, connect to the Oracle instance:

Dbhost:/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:14:44 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> oradebug setospid 7080
Oracle pid: 13, Unix process pid: 7080, image: oracle@caosspc40.ca.oracle.com (RECO)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.
... wait a minute or two here...
SQL> oradebug dump errorstack 3
Statement processed.
SQL> oradebug tracefile_name
/usr/app/oracle/admin/myrac/bdump/myrac1_reco_7080.trc
SQL> exit

Make sure the following patterns exist in the trace file:

- call stack
*** 2005-09-23 14:28:40.028
ksedmp: internal or fatal error
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27          call     ksedst1()            1 ? 1 ?
ksedmp()+557         call     ksedst()             1 ? 1 ? 5 ? F4166F ? F4E518 ?
....
- process state dump
===================================================
PROCESS STATE
-------------
Process global information:
Since it is 10g, the ASH should be discussed. It will have the past active waits.
...

d. Collecting log files in RAC environment:

When you have to log a Service Request for RAC Oracle environment, there are a few more log files you have to collect. Following is a list of files you may want to upload every time you log a Service Request.

  • RDA output from all cluster nodes. An alternative is to always keep the recent RDA in proactive SR. Remember to renew RDA stuff each time there is a change in the environment – for instance, when a database or ASM persistent configuration parameter is changed.
  • Database instances and ASM alert log files from all cluster nodes. The important part here is not to forget ASM logs – it well may be an ASM instance that is responsible for problems with the database proper.
  • CRS log files from all cluster nodes. The collection procedure varies a bit depending on whether you are in 10gR1 or 10gR2 environment.
    • 10gR1: collect log files from $CRS_HOME/crs/log, $CRS_HOME/css/log, $CRS_HOME/evm/log, $CRS_HOME/crs/racg/dump.
    • 10gR2: collect log files from $CRS_HOME/log/`hostname` directory – you may want to 'tar' the entire directory altogether rather than go through every subdirectory in there.

Conclusion:

If you, as a DBA, need to call in Oracle Support they are going to treat you in much the same way as you have treated the user who reported the problem to you. They are going to ask for a very precise definition of the problem, and if they cannot solve it at once, they are going to ask for hard evidence. It is also helpful if another DBA is called in to deal with a related problem later. It may be possible for them to save a vast amount of time if they have full information as to what you have done already. Indeed, you yourself may need this written record if the problem recurs in a few months time or if it becomes so complex that it is hard to remember everything that has happened.

Indeed, it is the responsibility of DBA to have the full story carefully documented if there is a management post-mortem or if you need to argue the case for some contentious change in order to prevent a recurrence of the problem. Therefore, what you do during a crisis is very important for building confidence in you to your manager and company.

» See All Articles by Columnist Sreeram Surapaneni



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM