Oracle Technology Blog

OraTech Blog provides tips,tricks,scripts and how-to type answers for Oracle related technologies. I use these commands very often, so I thought it would be useful to share with everybody. Please feel free to include your comments/corrections/questions.

Thursday, November 30, 2006

Gather Database Information systemstate and hanganalyze

#--------------------------------------------
# -- To capture Database information
#--------------------------------------------

# ---------------------------------------------------------------------------
# Put output in .out. Change as desired.
# Note: output directory requires write permission.
# ---------------------------------------------------------------------------

OUTF=${0}.out

# ---------------------------------------------------------------------------
# You may want to delete the output file so that backup information does
# not accumulate. If not, delete the following lines.
# ---------------------------------------------------------------------------

if [ -f "$OUTF" ]
then
rm -f "$OUTF"
fi

{ # output block

echo "`date` ----------------Beginning of Script------------"
echo "Script name: $0"
prstat 3 3
vmstat 1 3
ORACLE_SID=pnet11
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/9.2.0
PATH=./:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/usr/sbin:/usr/local/bin
LD_LIBRARY_PATH=$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:/usr/openwin/lib:/usr/dt/lib
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH
umask 022
STAMP=`date "+%Y%m%d"`
INFO1="Started at `date`"
x=`dekr /opt/oracle/scripts/dba/.Schedulejobs/perf.dat`
sqlplus perfstat/$x << eof
spool utllockt.log
@/opt/oracle/product/9.2.0/rdbms/admin/utllockt.sql
set lines 130 pages 500
select * from gv\$lock;
col machine format a30
select machine,username,count(*) from gv\$session where status='ACTIVE' group by machine,username;
spool off
exit;
eof
echo "`date` ----------------Beginning of systemstate------------"
sqlplus "/ as sysdba" << eof
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10
exec dbms_lock.sleep(90);
oradebug dump systemstate 10
exec dbms_lock.sleep(90);
oradebug dump systemstate 10
exit;
eof
echo "`date` ----------------Beginning of hanganalyze------------"
sqlplus "/ as sysdba" << eof
oradebug setmypid
oradebug setinst all
oradebug -g def hanganalyze 10
exec dbms_lock.sleep(120);
oradebug -g def hanganalyze 10
exec dbms_lock.sleep(120);
oradebug -g def hanganalyze 10
exit;
eof
echo "`date` ----------------Beginning of RACDIAG------------"
sqlplus perfstat/$x << eof
@racdiag.sql
@tfsmlock.sql
@tfsclock.sql
@tfslkill.sql
@v_views.sql
exit;
eof
prstat
vmstat 1 3
echo "`date` ----------------End of Script------------"
} >> $OUTF

0 Comments:

Post a Comment

<< Home