Oracle Point, Oracle Life.

In versions before Oracle 10g, statspack is useful utility to monitor database performance and workload issues. I just came across a shell script which can automate snapshot report create and mail to DBA.

The script is re-posted as below without change.

[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!

BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!

mail -s “perfstat report” ddd@eee.fff < /home/oracle/sql/report/sp`date +%m%d`_ac.lst

[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >> /home/oracle/sql/backup/perf.lst 2>&1

Note: The snapshot report is created from 8:00 AM to 8:00 PM daily and the job of crontab is called at 9:00 AM.

Popularity: 23% [?]