Automated Stats Pack Generation Script

Automated Stats Pack Generation Script

In day to day monitoring frame work of Oracle Database Cluster, administrator have to monitor many important & critical files and values and one of them is Stats Pack report. 

Stats Pack report will give the clear picture of what activities performed by your oracle, hom many locks and various type of different scenarios which give you clear picture to find problems and take further steps.

Now its a Administrator's task to generate Stats Pack report on daily basis.And this daily report generation can take you few critical time band & its a daily consumption time band. You have yo manually login into the system, find the exact snap id & generate Stats pack report within those snap id.

As a Administrator, I also faced same problem & each day I was investing my 20-30 minutes in generating Stats Pack report. And this condition brings me to develop a script will automatically generate Stats Pack report. I scheduled script to run on every 9AM and it give me a fully populated Stats Pack report in hand. So I saved my 30 minutes every day. Check out Script below: -

#!/usr/bin/ksh
##############################  SCRIPT START ##################
#
##
# This code is Copyright(c) 2013 by Yuvam Jain
# All rights reserved.
#
# You may use this program without charge, you may copy it for
# backup purposes, you may even give it away to anyone you like.
# You may not charge a fee for it.  You may charge a fee for
# a distribution mechanism (such as a CD-ROM containing it), but
# such fee must not exceed the amount required to recoup costs.
# You may modify it for your own purposes, but this notice must
# remain attached to all copies and derivitive works.
#
# If you make modifications that you think others would like, please
# send diffs of your changes to the author at
# <yuvamjain@gmail.com>.
#
#
#Version- 2.7
#Created by:- Yuvam Jain
# IMPORTANT
# Run script as Oracle User.
# Its a kshell script. Make sure you have kshell executable available at path "/usr/bin/ksh"
############################################ 

################################
#Section 0
#change the ORACLE_SID as per req.
#################################
export ORACLE_SID=<NIDE_ID> #like NODE11
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1 #Oracle home path
export PATH=$ORACLE_HOME/bin:/bin #Exporting ORACLE_HOME Path

#################################
#Section 1
#Table name to fetch the latest snap id from which script will fetch the snap id.
#BEsure the table name should be correct
#################################
 table_name='stats$snapshottable'

###############################
#Section 2
#Reading snap_id.txt to read the BEGIN_ID. This file will get update with latest snap id available at the end of script.
#Make sure to run script with root permission & read write permission for snap_id.txt file.
###############################
cat /public/scripts/snap_id.txt | while read LINE
do
    BEGIN_ID=$LINE
done


###############################
#Section 3
#Fetching latest snap id available from table 'table_name'.
#Change INSTANCE_NUMBER=<Value>. Here value means on which node you are running this script.
#For example: If you have two nodes of 1 oracle cluster NODE1. lets says two nodes are NODE11 & NODE12.
#Now if u are running this script on NODE11, then INSTANCE_NUMBER=1 & for NODE12, INSTANCE_NUMBER=2.
#
# This script is running on NODE11 i.e., INSTANCE_NUMBER=1
# DO NOT EDIT ANYTHING ELSE SECTION.
###############################
 snap_id=`sqlplus -s perfstat/ctadmin  << EOF
        set pagesize 0
                set feedback off
                set verify off
                set heading off
        select max(snap_id) from $table_name where INSTANCE_NUMBER=1;
               exit;
EOF`

for get_id in $snap_id
do
    END_ID=$get_id
done


# Echo of Start & End snap id.
# You can take its log in a log file if required.
echo " BEGIN =$BEGIN_ID= and  END =$END_ID="

#################################
#Section 4
#Name and location of the output report. Change as per your requirement.
#Make sure scirpt user should have permission to read write on that path.
#################################
 REPORT="/public/StatsPackReport/SPReport_ARCH011-$BEGIN_ID-$END_ID"

#################################
#Section 5
#calling sql statment with BEGIN_ID and END_ID.
# DO NOT EDIT THIS SECTION
#################################
##Generation of stats pack report
$ORACLE_HOME/bin/sqlplus -s perfstat/ctadmin \
@$ORACLE_HOME/rdbms/admin/spreport.sql <<EOF
${BEGIN_ID}
${END_ID}
${REPORT}
EOF


##########################
#Section 6
#Updating the new BEGIN id in snap_id.txt
#Script will again use this file & use available ID as Start ID.
##########################
 echo $END_ID > /public/scripts/snap_id.txt;

##################### END OF SCRIPT ###########################
Limitation of scripts1- If there is any change in oracle service or if service got restarted or any blank value in stats pack table, then script will not able to generate Stats Pack for that period. But from next day, it will continue to work perfectly.

***************************************************************

Like the post and choose Follow and stay tuned for more updates.
JUST BELIEVE IN TECHNOLOGY..... Enjoy....