16.2 RAC diagnostics | Oracle Real Application Clusters

< Day Day Up >

RAC is a multi-instance clustered configuration. As we have discussed in the previous chapter, apart from the single-instance-related issues that could be encountered, RAC could have issues across multiple instances. Critical issues such as an instance hang or very bad performance may lead to no or little information, even after the regular investigation performed by the database administrator. Oracle provides a good utility that helps gather statistics across all instances. The data gathered through this utility will help diagnose where the potential problem could be.

RACDIAG.SQL

RACDIAG.SQL is a script provided and downloadable from Metalink note# 135714.1. This script contains queries that will help diagnose various areas of the RAC instance and the cluster. The script is origi nally intended to diagnose hung sessions; however, its potential is much greater.

Among these scripts and outputs generated by the RACDIAG.SQL utility, the section that contains information regarding WAITING SESSIONS is very important in providing details of any serious issues.

Oracle also provides another diagnostic utility specifically for OPS environments called OPSDIAG.SQL. The script can be obtained from Metalink note# 205809.1. Apart from the RAC diagnostic scripts which help capture performance- related data,

Oracle also provides another set of diagnostic steps for troubleshooting interinstance performance. Though Metalink note# 18489.1 is titled ''Tuning inter-instance performance in RAC and OPS'' it has a good amount of detail that helps troubleshoot interconnect issues.

Daemon processes

The various daemon processes such as the GSD and SRVCTL processes are critical to the cluster, providing visibility to the RAC database and the existence of the other instances. If there is trouble with any of these processes, tracing the activities of these processes could be enabled to diagnose details of the problem.

Tracing the daemon processes Metalink note# 178683.1 contains steps for tracing GSD, SRVCTL, GSDCTL,and SRVCONFIG. Configuring the environment to trace these areas requires modifications to some of the Oracle-provided scripts. The following illustrates the tracing of the GSDCTL procedure where the status of the GSD is obtained. The first illustration is the actual extract from the file gsdctl located in the $ORACLE_HOME/bin directory:

# External Directory Variables set by the Installer  JREDIR =/apps/oracle/product/9.2.0/JRE JLIBDIR =/apps/oracle/product/9.2.0/jlib  MY_ORACLE_HOME =/apps/oracle/product/9.2.0 # jar files SRVMJAR =$JLIBDIR/srvm.jar #NetCa config jar NETCFGJAR =$JLIBDIR/netcfg.jar # JRE Executable and Class File Variables  JRE =$JREDIR/bin/jre JRECLASSES =$JREDIR/lib/classes.zip  JREJAR =$JREDIR/lib/rt.jar CLASSPATH =$NETCFGJAR:$JREJAR:$SRVMJAR # Set the shared library path for JNI shared libraries  # A few platforms use an environment variable other than   LD_LIBRARY_PATH  SET_DEFAULT =YES  PLATFORM ='uname'  case $PLATFORM in "HP-UX"|"HI-UX")   SET_DEFAULT =NO    SHLIB_PATH =$MY_ORACLE_HOME/lib32:$MY_ORACLE_HOME/   srvm/lib:$SHLIB_PATH   export SHLIB_PATH esac case $SET_DEFAULT in "YES") LD_LIBRARY_PATH =$MY_ORACLE_HOME/lib32:$MY_ORACLE_HOME/   srvm/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH esac # Run gsd control utility  -DTRACING.ENABLED =true -DTRACING.LEVEL =2  $JRE -DTRACING.ENABLED =true -DTRACING.LEVEL =2   -classpath $CLASSPATH oracle.ops.mgmt.daemon.GSDCTLDriver    "$@" $MY_ORACLE_HOME exit $?

The highlighted area contains the actual changes to the gsdctl file that enables tracing. The following is the trace generated to the terminal when the status of the GSD is checked:

ora-db2:RAC2:oracle # gsdctl stat /apps/oracle/product/9.2.0/bin/gsdctl: -DTRACING.ENABLED=true: not found [main] [16:11:10:285] [GetActiveNodes.create:Compile]   Going into GetActiveNodes constructor... [main] [16:11:10:310] [sQueryCluster.<init>:Compile]   Detected Cluster [main] [16:11:10:311] [sQueryCluster.isCluster:Compile]   Cluster existence = true [main] [16:11:10:312] [NativeSystem.<init>:Compile]   Going to load SRVM library [main] [16:11:10:368] [NativeSystem.<init>:Compile]   loaded libraries [main] [16:11:10:374]   [GetActiveNodes.initializeCluster:Compile] Inside   GetActiveNodes.initializeCluster [main] [16:11:13:512] [NativeResult.<init>:Compile]   NativeResult: The String obtained is1|Everything ok So Far [main] [16:11:13:513] [NativeResult.<init>:Compile]   The status string is: 1 [main] [16:11:13:515] [NativeResult.<init>:Compile]   The result string is: Everything ok So Far 1 [main] [16:11:13:615] [NativeResult.<init>:Compile]   NativeResult: The String obtained is1|^ora-db1.   summerskyus.com^ora-db2.summerskyus.com|oradb2.   summerskyus.com|Everything ok So Far [main] [16:11:13:616] [NativeResult.<init>:Compile]   The status string is: 1 [main] [16:11:13:617] [NativeResult.<init>:Compile]   The result string is: ^ora-db1.summerskyus.com^ora-db2.   summerskyus.com 3 [main] [16:11:13:619] [GetActiveNodes.<init>:Compile]   Started clusterware daemon thread [main] [16:11:13:620] [GetActiveNodes.create:Compile]   Out of GetActiveNodes constructor. [GetActiveNodes thread] [16:11:13:621]   [GetActiveNodes.initializeClusterWare:Compile]   Inside GetActiveNodes.initializeClusterWare [GetActiveNodes thread] [16:11:13:624]   [sQueryCluster.<init>:Compile] full:Acquire called by thread   GetActiveNodes thread count=0 [main] [16:11:13:625] [sQueryCluster.<init>:Compile]   Detected Cluster [main] [16:11:13:626] [sQueryCluster.isCluster:Compile]   Cluster existence = true [main] [16:11:13:627] [NativeSystem.<init>:Compile]   loaded libraries [main] [16:11:13:629] [Semaphore.acquire:Compile]   empty:Acquire called by thread main count=1 [main] [16:11:13:630] [Semaphore.release:Compile]   full:Release called by thread main count=1 [GetActiveNodes thread] [16:11:13:632]   [Semaphore.release:Compile] empty:Release called by thread   GetActiveNodes thread count=1 [GetActiveNodes thread] [16:11:13:633]   [GetLiveDaemonCommand.execute:Compile]   In Execute function of GetLiveDaemonCommand [GetActiveNodes thread] [16:11:13:914]   [GetLiveDaemonCommand.execute:Compile]   GetLiveDaemonCommand: String returned from native   1|2|!ora-db1.summerskyus.com!ora-db2.summerskyus.com [GetActiveNodes thread] [16:11:13:916]   [NativeResult.<init>:Compile] NativeResult:   The String obtained is1|2|!ora-db1.summerskyus.com!   ora-db2.summerskyus.com [GetActiveNodes thread] [16:11:13:917]   [NativeResult.<init>:Compile] The status string is: 1 [GetActiveNodes thread] [16:11:13:918]   [NativeResult.<init>:Compile] The result string is: 2 2 [GetActiveNodes thread] [16:11:13:919] [line# N/A]   full:Acquire called by thread GetActiveNodes thread   count=0 [main] [16:11:13:919] [NativeResult.<init>:Compile]   NativeResult: The String obtained is1|2|!ora-db1.   summerskyus.com!ora-db2.summerskyus.com [main] [16:11:13:921] [NativeResult.<init>:Compile]   The status string is: 1 [main] [16:11:13:922] [NativeResult.<init>:Compile]   The result string is: 2 2 [main] [16:11:13:923] [ClusterResult.getDaemon:Compile]   Number of dead daemons 2 GSD is running on the local node ora-db2:RAC2:oracle #

It is to be noted that the trace file indicates the validation of the cluster (highlighted) and checks and confirms the accessibility of the clusterware.

Tracing Oracle Net The performance reports indicate that there are server performance issues with the network traffic. The SQL*Net message from the client and SQL*Net message to the client show a large number of waits. While these waits also include the idle time, there is a potential that there could be an Oracle Net issue. A quick and easy method to identify the problem is to enable tracing of the Oracle Net traffic.

The listener and tnsnames files could be traced to look at the network traffic. For example the following is an output of the listener trace file.

In this situation the tracing was enabled at level 6 by adding TRACE_LEVEL_LISTENER = 6 to the listener.ora file:

ora-db2:RAC2:oracle # more listener.trc TNSLSNR for Solaris: Version 9.2.0.1.0 - Production on   08-NOV-2002 15:57:17 Copyright (c) 1991, 2002, Oracle Corporation. All rights   reserved. [08-NOV-2002 15:57:17:171] --- TRACE CONFIGURATION INFORMATION FOLLOWS --- [08-NOV-2002 15:57:17:171] New trace stream is /apps/   oracle/product/9.2.0/network/trace/listener.trc [08-NOV-2002 15:57:17:171] New trace level is 6 [08-NOV-2002 15:57:17:171] --- TRACE CONFIGURATION   INFORMATION ENDS --- [08-NOV-2002 15:57:17:172] --- PARAMETER SOURCE INFORMATION FOLLOWS --- [08-NOV-2002 15:57:17:172] Attempted load of system   pfile source/apps/oracle/product/9.2.0/network/   admin/listener.ora [08-NOV-2002 15:57:17:172] Parameter source loaded   successfully [08-NOV-2002 15:57:17:172] [08-NOV-2002 15:57:17:172] Attempted load of command line source [08-NOV-2002 15:57:17:172] Parameter source was not loaded [08-NOV-2002 15:57:17:172] [08-NOV-2002 15:57:17:172] -> PARAMETER TABLE LOAD RESULTS   FOLLOW <- [08-NOV-2002 15:57:17:173] Successful parameter table load [08-NOV-2002 15:57:17:173] -> PARAMETER TABLE HAS THE FOLLOWING   CONTENTS <- [08-NOV-2002 15:57:17:173] TRACE_LEVEL_LISTENER = 6 [08-NOV-2002 15:57:17:173] LISTENER = (DESCRIPTION_LIST =    (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =    IPC)(KEY = E XTPROC))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)   (HOST = ora-db2.summerskyus.com)(PORT = 1521))))) [08-NOV-2002 15:57:17:173] SID_LIST_LISTENER = (SID_LIST =    (SID_DESC = (SID_NAME = PLSExtProc)   (ORACLE_HOME = /apps/oracle/product/9.2.0)   (PROGRAM = extproc)) (SID_DESC = (ORACLE_HOME =    /apps/oracle/product/9.2.0) (SID_NAME = RAC2))   (SID_DESC = (ORACLE_HOME=/apps/oracle/product/9.2.0)   (SID_NAME = PRODDB)) [08-NOV-2002 15:57:17:173] --- PARAMETER SOURCE   INFORMATION ENDS --- [08-NOV-2002 15:57:17:173] --- LOG CONFIGURATION INFORMATION FOLLOWS --- [08-NOV-2002 15:57:17:174] Log stream will be "/apps/   oracle/product/9.2.0/network/log/listener.log" [08-NOV-2002 15:57:17:174] Log stream validation   requested [08-NOV-2002 15:57:17:174] Log stream is valid [08-NOV-2002 15:57:17:174] --- LOG CONFIGURATION   INFORMATION ENDS --- [08-NOV-2002 15:57:17:191] nsinherit: doing connect   handshake... [08-NOV-2002 15:57:17:192] nlpcaini: entry [08-NOV-2002 15:57:17:192] nlpcaini: No process   parameters set [08-NOV-2002 15:57:17:192] nlpcaini: exit [08-NOV-2002 15:57:17:192] nsinherit: inheriting the   connection... [08-NOV-2002 15:57:17:193] nsmal: 776 bytes at 0x1003cb0d0 [08-NOV-2002 15:57:17:193] nsmal: 2592 bytes at 0x1003cb3f0 [08-NOV-2002 15:57:17:193] nsopen: opening transport... [08-NOV-2002 15:57:17:193] ntpcon: entry [08-NOV-2002 15:57:17:193] ntpcon: toc = 4 . . . [08-NOV-2002 15:57:17:275] nspsend: 32 bytes   to transport [08-NOV-2002 15:57:17:275] nscon: sending 439 bytes   connect data [08-NOV-2002 15:57:17:275] nsdo: cid=0, opcode=67,   *bl=439, *what=1, uflgs=0x4002, cflgs=0x0 [08-NOV-2002 15:57:17:275] nsdo: nsctx: state=2,   flg=0x4204, mvd=0 [08-NOV-2002 15:57:17:275] nsdo: gtn=185, gtc=185,   ptn=10, ptc=2011 [08-NOV-2002 15:57:17:276] nsdo: 439 bytes to NS buffer [08-NOV-2002 15:57:17:276] nsdofls: DATA flags: 0x0 [08-NOV-2002 15:57:17:276] nsdofls: sending NSPTDA packet [08-NOV-2002 15:57:17:276] nspsend: plen=449, type=6 [08-NOV-2002 15:57:17:276] ntpwr: entry [08-NOV-2002 15:57:17:276] ntpwr: exit [08-NOV-2002 15:57:17:276] nspsend: 449 bytes to   transport [08-NOV-2002 15:57:17:277] nsdoacts: flushing   transport [08-NOV-2002 15:57:17:277] ntpctl: entry [08-NOV-2002 15:57:17:277] ntpctl: exit [08-NOV-2002 15:57:17:277] nscon: doing connect   handshake...

Looking through the above listener trace file indicates the various connections to the database and the steps taken by the connection request. During this process, bytes are transferred.

< Day Day Up >