SHELL SCRIPT TO TAKE AUTOMATIC AWR SNAPSHOT IN ORACLE

SHELL SCRIPT TO TAKE AUTOMATIC AWR SNAPSHOT IN ORACLE

Below script will help to take AWR snapshots automatically in case, MMON did not work. There are situations where MMON process die and cannot restart itself during production hours. In this situation, DBA need to manually take snapshots to avoid any lack of performance report for diagnosis. In this case, modify this script and execute it. This script will take snapshots every hour. Modify the timer as per your snapshot needs. There are two scripts. One is for taking snapshots only and other is for both snapshot and details of the last snapshots taken

#!/bin/bash

trap 'rm -rf /home/oracle/oratab_new /home/oracle/obscura.sql' EXIT
#For logging purpose
_LOG_0()
{
echo "*************************************$1"
}

#Set the environment variables
_SET_ENV_1()
{
touch /home/oracle/obscura.sql
obscura=/home/oracle/obscura.sql
chmod -R 775 /home/oracle/obscura.sql
cat /etc/oratab|grep -v '#'|grep -v '^$' > /home/oracle/oratab_new
while read x
   do
     IFS=':' read -r -a array <<< $x
                ORACLE_SID="${array[0]}"
                ORACLE_HOME="${array[1]}"
                echo $ORACLE_SID
                echo $ORACLE_HOME
                export PATH=$PATH:$ORACLE_HOME/bin
   done < /home/oracle/oratab_new
}

#Timer
_TIMER()
{
sleep 3600
}

#Take Snap
_OBSCURA()
{
while _TIMER;
   do
     echo "EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();" > $obscura
     exit|$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' @$obscura
     cat /dev/null > $obscura
   done
}


_SET_ENV_1
_OBSCURA

[oracle@xhydra ~]$ cat obscura.sql
     EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
     select EXTRACT(HOUR FROM SNAP_INTERVAL) INTERVAL from wrm$_wr_control;

     select count(*) TOTAL_SNAPS_TODAY
     from wrm$_snapshot
     where to_char(BEGIN_INTERVAL_TIME,'DD-MON-RR')=trunc(sysdate)
     group by to_char(BEGIN_INTERVAL_TIME,'DD-MON-RR');

     select 24 - count(*) SNAPS_PEND_TODAY
     from wrm$_snapshot
     where to_char(BEGIN_INTERVAL_TIME,'DD-MON-RR')=trunc(sysdate)
     group by to_char(BEGIN_INTERVAL_TIME,'DD-MON-RR');


#!/bin/bash

trap 'rm -rf /home/oracle/oratab_new /home/oracle/obscura.sql' EXIT
#For logging purpose
_LOG_0()
{
echo "*************************************$1"
}

#Set the environment variables
_SET_ENV_1()
{
touch /home/oracle/obscura.sql
obscura=/home/oracle/obscura.sql
chmod -R 775 /home/oracle/obscura.sql
cat /etc/oratab|grep -v '#'|grep -v '^$' > /home/oracle/oratab_new
while read x
   do
     IFS=':' read -r -a array <<< $x
                ORACLE_SID="${array[0]}"
                ORACLE_HOME="${array[1]}"
                echo $ORACLE_SID
                echo $ORACLE_HOME
                export PATH=$PATH:$ORACLE_HOME/bin
   done < /home/oracle/oratab_new
}

#Timer
_TIMER()
{
sleep 3600
}

#Take snap
_OBSCURA()
{
while _TIMER;
   do
     exit|$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' @$obscura
   done
}




_SET_ENV_1
_OBSCURA