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 <<INP
   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 <<EOF
    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 <<EOF
    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<<EOF
   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 <<EOF
    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 << EOF
      set current_listener LISTENER_${LSNR}
      set log_file temp.log
EOF

      rm ${ORACLE_HOME}/network/log/listener_${LSNR}.log ;

      ${ORACLE_HOME}/bin/lsnrctl << EOF
      set current_listener LISTENER_${LSNR}
      set log_file listener_${LSNR}.log
EOF

      cat ${ORACLE_HOME}/network/log/temp.log >> $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.

Labels: Maintenance