Auto StatsPack Snapshots and Reports

We run Standard Edition, and in 10.0.2.4 it's a license violation to pull AWRs. Most people don't know or don't care, but we do. So we implemented STATSPACK in all our SE databases some time ago. Lucky thing because AWRs don't quite work anymore in 10.2.0.5 SE.

Now back to the original issue, to avoid the time consuming routine of pulling a statspack report for a certain time frame we modified the statspack code to automatically take a snapshot every hour, and run the report. The mods are easy, go in and code out the ACCEPT PROMPT statements in the sp*.sql and then create a query to SELECT MAX() snapshot and MAX()-1.

[ email me if you want the code ].


Works great, the reports pile up, and are easy to find because we put the date and time and host name in the file name.

Having accomplished this the last item how to easily mail them out.

For instance, today I was asked for 30 days worth of snap shot reports. ( are you sure?)

I mean, that's 24 x 30 = 720 files.

Well they wanted them (now!)

So this little piece of korn shell script made that chore easy.

Our file format is

oracle_monitor_snapshot.YYYYMMDD_HHMM.log
#!/bin/ksh
files="/fs0/oracle/admin/log/oracle_monitor_snapshot.*.log"
for f in $files; do
if [ ! -f $f ]
  then
    #echo "$f file missing!"
  fi
  echo "$f"
  cat "$f"  | mailx -s "Customer STATSPACK - $f" appowner05@zzcorp.com
done

Cheers