rsdiag.ksh


Print this article  Send the URL to a friend

#!/bin/ksh # ========================================================================== # # Copyright (C) RoughSea Ltd, 2004 # http://www.roughsea.com # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. # # ========================================================================== # # Quick diagnostic tool. # # Assumes than one can connect as $CONNECT (and see the V$ stuff) # # Basically useful in situations such as : # "Users complain about performance on server xxx" # ... where of course you hardly have any idea about what xxx is # used for nor which of the 42 Oracle instances running on xxx has # any problem, if any. The script tries to identify all the instances # on the server, and to run a (relatively quick) health-check script # against each of them. # typeset CONNECT=’DBSNMP/DBSNMP’ # Change if required (usually works :( ) typeset TNS_ADMIN typeset PORTLIST=’1521 1526’ typeset -i PORT typeset -i POS typeset VAL typeset SIDVER typeset SID typeset -i VER typeset SIDLIST typeset LASTFILE typeset FULLLIST=’’ typeset REFDATE typeset -i SUCCESS=0 # export TNS_ADMIN=. # if [ $# -eq 0 ] then echo "Usage : $0 [ports]" >&2 exit 1 fi if [ $# -gt 1 ] then PORTLIST=’’ POS=2 while [ $POS -le $# ] do VAL="echo \$$POS" PORTLIST="$PORTLIST $(eval $VAL)" POS=$(( $POS + 1 )) done fi echo "# Generated by $0" > tnsnames_$1.ora for PORT in $PORTLIST do echo ’LISTENER=’ > listener.ora echo ’ (DESCRIPTION_LIST=’ >> listener.ora echo ’ (DESCRIPTION=’ >> listener.ora echo ’ (ADDRESS_LIST=’ >> listener.ora echo " (ADDRESS=(PROTOCOL=TCP)(Host=$1)(PORT=$PORT))" >> listener.ora echo ’ )’ >> listener.ora echo ’ )’ >> listener.ora echo ’ )’ >> listener.ora SIDLIST=$(lsnrctl stat 2>&1 | awk ’BEGIN ok=0;ver=8;\ if (ok == 0)\ if ($0 == "Services Summary...")\ ok = 1;\ else \ if ($1 == "Version")\ for (i=2; i < NF; i++)\ if ($i == $1)\ n = split($(i+1),a,".");\ if (n > 0)\ ver = a[1];\ \ \ \ \ \ else \ if ($0 == "The command completed successfully")\ ok = 0;\ else \ if (($1 != "extproc") && ($1 != "PLSExtProc")) \ printf("%s:%d\n", $1, ver);\ \

’ | sort -u) if [ "$SIDLIST" != ’’ ] then SUCCESS=1 FULLLIST="$SIDLIST $FULLLIST" for SIDVER in $SIDLIST do SID=$(echo $SIDVER | cut -f1 -d:) echo "$SID=" >> tnsnames_$1.ora echo ’ (DESCRIPTION=’ >> tnsnames_$1.ora echo ’ (ADDRESS_LIST=’ >> tnsnames_$1.ora echo " (ADDRESS=(PROTOCOL=TCP)(HOST=$1)(PORT=$PORT))" >> tnsnames_$1.ora echo ’ )’ >> tnsnames_$1.ora echo ’ (CONNECT_DATA =’ >> tnsnames_$1.ora echo " (ORACLE_SID=$SID)" >> tnsnames_$1.ora echo ’ )’ >> tnsnames_$1.ora echo ’)’ >> tnsnames_$1.ora done else ping $1 3 >/dev/null 2>&1 if [ $? -ne 0 ] then echo "WARNING : failed to ping $1" exit 1 fi fi done cp tnsnames_$1.ora tnsnames.ora REFDATE=$(date +"%Y%m%d") if [ "$FULLLIST" != ’’ ] then for SIDVER in $FULLLIST do SID=$(echo $SIDVER | cut -f1 -d:) VER=$(echo $SIDVER | cut -f2 -d:) LASTFILE=$(ls -C1 -t $SID_$REFDATE*.lst 2>/dev/null | head -n 1) if [ ! "$LASTFILE" -nt tnsnames.ora ] then echo "Diagnosing $SID" if [ $VER -lt 9 ] then echo "set linesize 200 set trimspool on set pagesize 999 set recsep off col pct format 990.00 set verify off col id noprint new_value peep_id select name || ’_’ || to_char(sysdate, ’YYYYMMDDHH24MI’) ID from v\$database; spool DATA/&&peep_id..lst select ’&&peep_id’ identifier from dual; prompt Wait statistics select a.class, a.count, a.time, round(100*a.time/b.tot,2) PCT from v\$waitstat a, (select decode(sum(time),0,1,sum(time)) tot from v\$waitstat where count > 0) b where a.count > 0 order by a.time desc, a.count desc; prompt Non-idle waits select e.event, e.time_waited, e.total_waits, decode(x.tot, 0, to_number(NULL), round(100 * e.time_waited / x.tot, 2)) PCT from v\$system_event e, (select sum(time_waited) tot from v\$system_event where event not in (’SQL*Net message from client’, ’PX Idle Wait’, ’pmon timer’, ’rdbms ipc message’, ’smon timer’)) x where e.event not in (’SQL*Net message from client’, ’PX Idle Wait’, ’pmon timer’, ’rdbms ipc message’, ’smon timer’) and decode(x.tot, 0, e.total_waits, round(100 * e.time_waited / x.tot, 2)) > 0 order by 2 desc, 3 desc; break on pct on buffer_gets on disk_reads on executions prompt Top queries select round(100 * s.buffer_gets / c.totcost, 2) PCT, s.buffer_gets, s.disk_reads, s.executions, t.sql_text from v\$sql s, v\$sqltext_with_newlines t, (select sum(buffer_gets) totcost from v\$sql) c where t.address = s.address and t.hash_value = s.hash_value and round(100 * s.buffer_gets / c.totcost, 2) > 2 order by s.buffer_gets desc, s.address, s.hash_value, t.piece; spool off exit" | sqlplus -s $CONNECT@$SID > /dev/null else echo "set linesize 200 set trimspool on set pagesize 999 set recsep off col pct format 990.00 set verify off col id noprint new_value peep_id select name || ’_’ || to_char(sysdate, ’YYYYMMDDHH24MI’) ID from v\$database; spool &&peep_id..lst select ’&&peep_id’ identifier from dual; prompt Wait statistics select a.class, a.count, a.time, round(100*a.time/b.tot,2) PCT from v\$waitstat a, (select sum(time) tot from v\$waitstat where count > 0) b where a.count > 0 order by a.time desc; prompt Non-idle waits select e.event, e.time_waited, e.total_waits, decode(x.tot, 0, to_number(NULL), round(100 * e.time_waited / x.tot, 2)) PCT from v\$system_event e, (select sum(time_waited) tot from v\$system_event where event not in (’SQL*Net message from client’, ’PX Idle Wait’, ’pmon timer’, ’rdbms ipc message’, ’smon timer’)) x where e.event not in (’SQL*Net message from client’, ’PX Idle Wait’, ’pmon timer’, ’rdbms ipc message’, ’smon timer’) and decode(x.tot, 0, e.total_waits, round(100 * e.time_waited / x.tot, 2)) > 0 order by 2 desc, 3 desc; break on pct on elapsed_time on cpu_time on buffer_gets - on disk_reads on executions prompt Top queries select round(100 * s.buffer_gets / c.totcost, 2) PCT, s.elapsed_time, s.cpu_time, s.buffer_gets, s.disk_reads, s.executions, t.sql_text from v\$sql s, v\$sqltext_with_newlines t, (select sum(buffer_gets) totcost from v\$sql) c where t.address = s.address and t.hash_value = s.hash_value and round(100 * s.buffer_gets / c.totcost, 2) > 2 order by s.elapsed_time desc, s.buffer_gets desc, s.address, s.hash_value, t.piece; spool off exit" | sqlplus -s $CONNECT@$SID > /dev/null fi fi done fi if [ $SUCCESS -eq 0 ] then echo "WARNING : No listener found answering" fi