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.
- hanganalyze;
- system state dump;
-
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.
- 10gR1: collect log files from
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.
