Basic Oracle Maintenance Jobs

There are many processes that need to be in place in order to maintain Oracle databases' environments.

There are, also, many 3rd-party tools that are available to assist the DBA as well as many differing methods in which to approach performing these tasks; however, traditionally, shell scripts called from an O/S scheduler - such as cron - have been used to perform - at a bare minimum - the following tasks:

• Rotate Listener Log


• Rotate Alert Log


• Romove Old Files - Trace, etc.


• Backup Database and Archive Logs, including Deleting Old Logs and Crosschecking Recovery Catalogs/Controlfiles, etc.


• Gather Object - Table, Index, etc. - Statistics

This example includes a tar file which contains all of the scripts necessary to perform the above 5 tasks on the Linux platform for any Oracle 9i/10g database - these scripts can also be modified to support 11g:

oracron.tar

Simply untar the files to the desired location, edit as appropriate and schedule via your preferred mechanism; below are the entries from my oracle user's crontab file:

49 18 * * * sh -c '. ~/.bash_profile ; ~/scripts/rotate_alert.bsh ldg' >> ~/logs/rotate_alert.log 2>&1
50 18 * * * sh -c '. ~/.bash_profile ; ~/scripts/remove_old_files.bsh ldg 2' >> ~/logs/remove_old_files.log 2>&1
13 20 * * * sh -c '. ~/.bash_profile ; ~/scripts/rman_full_nc.bsh ldg' >> ~/logs/rman_full_nc.log 2>&1
12 21 * * * sh -c '. ~/.bash_profile ; ~/scripts/analyze_db.bsh ldg' >> ~/logs/analyze_db.log 2>&1
23 22 * * * sh -c '. ~/.bash_profile ; ~/scripts/rotate_listener.bsh ldg ldg' >> ~/logs/rotate_listener.log 2>&1

Make sure that oraenv is in the oracle user's path and that each database has a /etc/oratab entry. You will want to adjust the crontab entries above to use the $ORACLE_SID of your choosing. Additionally, my database statistics gathering job requires that you first compile the DBA_STATS_MAINT_PKG in the SYS schema. You will find the ddl for this package within the tar file.

analyze_db.bsh

   #!/bin/bash

 # Wrapper Script that calls PL/SQL function to analyze your
 # database to suite your particular needs

 function quit {
   echo "${0} ORACLE_SID";
   exit;
 }

 # Make Sure Oracle instance name is passed
 if [ "" == "${1}" ]; then
  quit;
 fi;

 # Set the Environment
 export ORACLE_SID=${1};
 export ORAENV_ASK=NO;
 . oraenv;

 date ;
 sqlplus -s /nolog <&ltINP   connect / as sysdba ;   exec dba_stats_maint_pkg.disable_auto_job('GATHER_STATS_JOB');   exec dba_stats_maint_pkg.gather_stats;   exec dba_stats_maint_pkg.gather_data_dict_stats; INP   date ;

remove_old_files.bsh

   #!/bin/bash

 # Works for Oracle databases pre-11g
 # 11g introduces universal diagnostic dest

 function quit {
   echo "${0} ORACLE_SID DAYS_OLD";
   exit;
 }

 # Make Sure Oracle instance name is passed
 if [ "" == "${1}" ]; then
  quit;
 fi;

 # Make Sure the Days Old argument is passed
 if [ "" == "${2}" ]; then
  quit;
 fi;

 # Set the Environment
 export ORACLE_SID=${1};
 export DAYS_OLD=${2};
 export ORAENV_ASK=NO;
 . oraenv;

 # Get the location for the bdump
 bdump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <&ltEOF    connect / as sysdba ;    set head off;    set feedback off;    set verify off;    select value from v\\$parameter where name='background_dump_dest';    exit;    EOF `    # Get the location for the udump   udump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <&ltEOF    connect / as sysdba ;    set head off;    set feedback off;    set verify off;    select value from v\\$parameter where name='user_dump_dest';    exit;    EOF `     # Make sure the directories are valid and cleanup the files   if [ -d ${bdump} ] && [ -d ${udump} ] ;  then     echo "Removing old files at `date +%Y%m%d%H%M` for ${ORACLE_SID}" ;     echo "Background Dump Before Cleanup `ls -l ${bdump} | wc -l`";     echo "User Dump Before Cleanup `ls -l ${udump} | wc -l`";     echo "Listener Log Dump Before Cleanup `ls -l ${ORACLE_HOME}/network/log | wc -l` ";     find ${bdump} -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;     find ${udump} -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;     find ${ORACLE_HOME}/network/log -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;     echo "Background Dump After Cleanup `ls -l ${bdump} | wc -l`";     echo "User Dump After Cleanup `ls -l ${udump} | wc -l`";     echo "Listener Log Dump After Cleanup `ls -l ${ORACLE_HOME}/network/log | wc -l` ";   fi;

rman_full_nc.bsh

   #!/bin/bash

 # Backup/Catalog Crosscheck Delete using RMAN
 # This is for Controlfile backups w/o RMAN Catalog

 function quit {
   echo "${0} ORACLE_SID";
   exit;
 }

 # Make Sure Oracle instance name is passed
 if [ "" == "${1}" ]; then
  quit;
 fi;

 # Set the Environment
 export ORACLE_SID=${1};
 export BACKUP_TAG=FHB_${ORACLE_SID}`date +%Y%m%d%H%M`
 export ARCH_TAG=ARCH_${ORACLE_SID}`date +%Y%m%d%H%M`

 export ORAENV_ASK=NO;
 . oraenv;

 date ;
 rman<&ltEOF   connect target / ;   show all ;   crosscheck backup ;   crosscheck archivelog all ;   delete noprompt obsolete ;   backup full database tag ${BACKUP_TAG} plus archivelog tag ${ARCH_TAG} ;   exit ; EOF   date ;

rotate_alert.bsh

   #!/bin/bash

 # Works for Oracle databases pre-11g
 # 11g introduces universal diagnostic dest

 function quit {
   echo "${0} ORACLE_SID";
   exit;
 }

 # Make Sure Oracle instance name is passed
 if [ "" == "${1}" ]; then
  quit;
 fi;

 # Set the Environment
 export ORACLE_SID=${1};
 export ORAENV_ASK=NO;
 . oraenv;

 date ;
 # Get the location for the alert log
 bdump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <&ltEOF    connect / as sysdba ;    set head off;    set feedback off;    set verify off;    select value from v\\$parameter where name='background_dump_dest';    exit;    EOF    `     logfile=${bdump}/alert_${ORACLE_SID}.log ;     # Ensure that the background dump dest exists     # If so, rotate the log    if [ -d ${bdump} ]; then      if [ -f ${logfile} ]; then        echo "${logfile} rotated at `date +%Y%m%d%H%M`" ;        mv ${logfile} ${bdump}/alert_${ORACLE_SID}.`date +%Y%m%d%H%M`.log          if [ $? == 0 ]; then            touch ${logfile};          fi;      else        echo "Alert Log ${logfile} does not exist...";        quit;      fi;    else     echo "Directory does not exist...";     quit;    fi;     date ;

rotate_listener.bsh

   #!/bin/bash

 # Works for Oracle databases pre-11g
 # 11g introduces universal diagnostic dest

 function quit {
   echo "${0} ORACLE_SID LSNR";
   exit;
 }

 # Make Sure Oracle instance name is passed
 if [ "" == "${1}" ]; then
  quit;
 fi;

 # Make Sure Listener name is passed
 if [ "" == "${2}" ]; then
  quit;
 fi;

 # Set the Environment
 export NOW=`/bin/date +%m%d%y%H%M%S`;
 export ORACLE_SID=${1};
 export LSNR=${2};
 export ORAENV_ASK=NO;
 . oraenv;

 date ;

 if [ -d ${ORACLE_HOME}/network/log ]; then
   if [ -f ${ORACLE_HOME}/network/log/listener_${LSNR}.log ]; then
    cat ${ORACLE_HOME}/network/log/listener_${LSNR}.log >> ${ORACLE_HOME}/network/log/listener_${LSNR}.log.$NOW ;  
    ${ORACLE_HOME}/bin/lsnrctl <<>> $ORACLE_HOME/network/log/listener_${LSNR}.log.${NOW} ;
    rm ${ORACLE_HOME}/network/log/temp.log ;
  fi ;
 else
    echo "Directory not found...";
    quit ;
 fi ;
 date ;

Edit the above mentioned in any way you see fit. Although these are written using bash, you could easily adapt these scripts to your preferred shell.