Linux Shell Scripting For Automatic Oracle Statspack report generation

 

This is the Linux Shell Scripting For Oracle Staspack report generation

statspack report  is  the picture of database between two points of time.thats why this is divided into two steps

 

These Steps Are:

 

1.            Create a shell script named

      first_step.sh at location from whereever you want it to run

 

      conn

sqlplus Perfstat/perfstat@db_name

 

                   exec statspack.snap;

           

                   and assign it in cronjob at time when you want to start taking the picture of of your database

 

2.      Create another shell script named

second_step.sh  at location from whereever you want it to run

                       

                        set head off

                        set timing off

                        sqlplus Perfstat/perfstat@db_name  <<EOF

                        exec statspack.snap;

                        spool /address/first_snap.lst;

                        select max(snap_id) from stats\$snapshot where snap_id not in (select max(snap_id) from        stats\$snapshot);

                        spool off;

                        spool /address/Second_snap.lst;

                         select max(snap_id) from stats\$snapshot;

                        spool off;

                        exit;

                        !

                        EOF

                        START_SNAP=$(cat /address/first_snap.lst  | tail +5 | head -1)

                        END_SNAP=$(cat /address/Second_snap.lst  | tail +5 | head -1)

                        REP_NAME=/address/sp'date'_ac

                        sqlplus Perfstat/perfstat@db_name  <<EOF

                        define start_snap=$ START_SNAP

                        define end_snap=$ END_SNAP

                        define rep_name=$ REP_NAME

                        @?/rdbms/admin/spreport.sql;

                        EOF

                        and also assign it in cronjob at time when you want to end the picture of of your database.

           

                        This two shell scripts assign in cronjob at two point of time whenever you want to view the                         picture of your DB .