Redirecting cron Output

Whenever you run a Linux shell command, by default the standard output (of the command) will be displayed on your screen. Also, if any error messages are generated, they will, by default, be displayed on your screen. You can use either > or 1> (they are synonymous) to redirect any standard output to an OS file. Additionally, you can use 2> to redirect any error messages to a file. The notation 2>&1 instructs the shell to send any error messages to the same location as standard output.

When you create a cron job, you can use these redirection features to send the output of a shell script to a log file. For example, in the following cron table entry, any standard output and error messages generated by the b ackup.bsh shell script are captured in a file named bck.log:

11 12 * * * /home/oracle/bin/backup.bsh 1>/home/oracle/bin/log/bck.log 2>&1

If you don’t redirect the cron job output, then any output will be e-mailed to the user that owns the cron table. You can override this behavior by specifying the M AILTO variable directly within the cron table. With the next few lines of code, the cron output will go to the root user:

MAILTO=root
11 12 * * * /home/oracle/bin/backup.bsh

If you don’t want the output to go anywhere, then redirect it to the proverbial bit bucket. The following entry sends the standard output and standard error to the dev/null device:

11 12 * * * /home/oracle/bin/backup.bsh 1>/dev/null 2>&1

Troubleshooting cron

If you have a cron job that isn’t running correctly, follow these steps to troubleshoot the issue:

1. Copy your cron entry, paste it into the OS command line, and manually run the command. Frequently, a slight typo in a directory or file name can be the source of the problem. Manually running the command will highlight errors such as this.
2. If the script runs Oracle utilities, make sure you source (set) the required OS variables within the script, such as ORACLE_HOME and ORACLE_SID. Oftentimes, these variables are set by startup scripts (such as HOME/.bashrc) when you log in. Because cron doesn’t run a user’s startup scripts, any required variables must be set explicitly within the script.
3. Ensure that the first line of any shell scripts invoked from cron specify the name of the program that will be used to interpret the commands within the script. For instance, #!/bin/bash should be the first entry in a Bash shell script. Because cron doesn’t run a user’s startup scripts (such as HOME/.bashrc), you can’t assume that your OS user’s default shell will be used to run a command or script evoked from cron.
4. Make certain that the cron background process is running. Issue the following command from the OS to verify:

   $ ps -ef | grep cron

   If the cron daemon (background process) is running, you should see something similar to this:

   root      2969     1  0 Mar23 ?        00:00:00 crond

5. Check your e-mail on the server. The cron utility will usually send an e-mail to the OS account when there are issues with a misbehaving cron job.
6. Inspect the contents of the /var/log/cron file for any errors. Sometimes, this file has relevant information regarding a cron job that has failed to run.

Starting and Stopping the Database and Listener

In many environments it’s desirable to have the Oracle database and listener automatically shut down and start up when the server reboots. If you have that requirement, then follow the next several steps to automate your database and listener shutdown and startup.

• 1.  Edit the /etc/oratab file, and place a Y at the end of the entry for the databases you want to restart automatically when the system reboots. You may need root privileges to edit the file:

  # vi /etc/oratab

• 2.  Place within the file a line similar to this, for your environment:

  o12c:/u01/app/oracle/product/12.1.0.1/db_1:Y

• 3.  In the previous line, o12c is the database name, and /u01/app/oracle/product/12.1.0.1/db_1 specifies the directory ORACLE_HOME. The Y at the end of the string signifies that the database can be started and stopped by the ORACLE_HOME/bin/dbstart and ORACLE_HOME/bin/dbshut scripts. You can replace the Y with an N if you do not want the database automatically stopped and restarted.

• 4.  As root, navigate to the /etc/init.d directory, and create a file named dbora:

  # cd /etc/init.d
  # vi dbora

• 5.  Place the following lines in the dbora file. Make sure you change the values of the variables ORA_HOME and ORA_OWNER to match your environment. This is a bare-bones script of what you minimally would need to stop and start a database and listener:

  #!/bin/bash
  # chkconfig: 35 99 10
  # description: Starts and stops Oracle processes
  ORA_HOME=/u01/app/oracle/product/12.1.0.1/db_1
  ORA_OWNER=oracle
  case "$1" in
    'start')
      su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
      su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
     ;;
    'stop')
      su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
      su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
     ;;
  esac

These lines look like comments in the d bora file but are actually mandatory:

# chkconfig: 35 99 10
# description: Starts and stops Oracle processes

The prior lines describe the service characteristics of the script. The 35 means that the service will be started in runlevels 3 and 5. The 99 indicates that the service will be started near the end of the init processing. The 10 signifies that the service will be stopped near the beginning of the init processing. A description is also required providing textual information about the service.

• 6.  Change the group of the d bora file to match the group assigned to the OS owner of the Oracle software (usually oinstall or dba):

  # chgrp dba dbora

• 7.  Change the permissions on the d bora file to 750:

  # chmod 750 dbora

• 8.  Run the following c hkconfig command:

  # /sbin/chkconfig --add dbora

Here, the chkconfig command registers the service script. This also creates the appropriate symbolic links to files beneath the /etc/rc.d directory. Use the --list option to display whether a service is on or off for each runlevel:

# chkconfig --list | grep dbora
dbora           0:off   1:off   2:off   3:on    4:off   5:on    6:off

This output indicates that the dbora service is on for runlevels 3 and 5. If you need to delete a service, use the -del option of c hkconfig.

Automating the shutdown and startup of your Oracle database will vary, depending on whether you’re using tools such as cluster software or ASM. The prior code in this section demonstrates the typical steps for implementing the shutdown and startup of your database in scenarios in which you don’t have other software that manages this task.

To test whether the d bora script is working, as root, run the following command to stop your database and listener:

# /etc/init.d/dbora stop

To test the startup of your database and listener, as root, issue the following command:

# /etc/init.d/dbora start

As of this writing, you may need to make a slight modification to the Oracle-supplied ORACLE_HOME/bin/dbstart and ORACLE_HOME/bin/dbshut scripts. If you inspect these scripts with an OS editor (such as vi), you’ll notice the following line:

ORACLE_HOME_LISTNER=$1

I would recommend that you change it to this:

ORACLE_HOME_LISTNER=${1:-$ORACLE_HOME}

This line instructs the scripts to accept a parameter if one is passed in. If a parameter is not passed in, then set ORACLE_HOME_LISTNER to the value contained in the variable $ORACLE_HOME. This preserves the functionality of dbstart and dbshut and also makes these scripts work when called from d bora.

If you have the opportunity to reboot your system, I recommend that you do so to ensure that the database stops and restarts correctly. When you invoke dbstart and dbshut, these utilities create log files in the ORACLE_HOME directory, named startup.log and shutdown.log. You can inspect the contents of these to verify that the shutdown and startup are working as expected.

Checking for Archive Redo Destination Fullness

Sometimes DBAs and SAs don’t adequately plan and implement a location for storing archive redo log files on disk. In these scenarios it’s sometimes convenient to have a script that checks for space in the primary location and that sends out warnings before the archive redo destination becomes full. Additionally, you may want to implement within the script that the archive redo log location automatically switch to an alternate location that has adequate disk space.

I’ve only used scripts such as this in chaotic environments that have issues with the archive redo log destination’s filling up at unpredictable frequencies. If the archive redo log destination fills up, the database will hang. In some environments this is highly unacceptable. You could argue that you should never let yourself get into this type of situation. Therefore, if you’re brought in to maintain an unpredictable environment, and you’re the one getting the phone calls at 2:00 am, you may want to consider implementing a script such as the one provided in this section.

Before using the following script, change the variables within the script to match your environment. For instance, SWITCH_DIR should point to an alternate location on disk that you can safely switch to the archive redo log destination, should the primary destination become full. The script will send warning e-mails when the threshold goes below the amount of space specified by the T HRESH_GET_WORRIED variable. If the archive redo log space falls below the value specified in the T HRESH_SPACE_CRIT variable, then the destination will automatically be switched to the directory contained in the S WITCH_DIR variable.

#!/bin/bash
PRG='basename $0'
DB=$1
USAGE="Usage: ${PRG} <sid>"
if [ -z "$DB" ]; then
  echo "${USAGE}"
  exit 1
fi
# source OS variables
. /var/opt/oracle/oraset ${DB}
# Set an alternative location, make sure it exists and has space.
SWITCH_DIR=/oradump01/${DB}/archivelog
# Set thresholds for getting concerned and switching.
THRESH_GET_WORRIED=2000000 # 2Gig from df -k
THRESH_SPACE_CRIT=1000000  # 1Gig from df -k
MAILX="/bin/mailx"
MAIL_LIST="[email protected] "
BOX='uname -a | awk '{print$2}''
#
loc='sqlplus -s <<EOF
CONNECT / AS sysdba
SET HEAD OFF FEEDBACK OFF
SELECT SUBSTR(destination,1,INSTR(destination,'/',1,2)-1)
FROM v\$archive_dest WHERE dest_name='LOG_ARCHIVE_DEST_1';
EOF'
# The output of df depends on your version of Linux/Unix,
# you may need to tweak the next line based on that output.
free_space='df -k | grep ${loc} | awk '{print $4}''
echo box = ${BOX}, sid = ${DB}, Arch Log Mnt Pnt = ${loc}
echo "free_space        = ${free_space} K"
echo "THRESH_GET_WORRIED= ${THRESH_GET_WORRIED} K"
echo "THRESH_SPACE_CRIT = ${THRESH_SPACE_CRIT} K"
#
if [ $free_space -le $THRESH_GET_WORRIED ]; then
$MAILX -s "Arch Redo Space Low ${DB} on $BOX" $MAIL_LIST <<EOF
Archive log dest space low, box: $BOX, sid: ${DB}, free space: $free_space
EOF
fi
#
if [ $free_space -le $THRESH_SPACE_CRIT ]; then
sqlplus -s << EOF
CONNECT / AS sysdba
ALTER SYSTEM SET log_archive_dest_1='location=${SWITCH_DIR}';
ALTER SYSTEM SWITCH LOGFILE;
EOF
$MAILX -s "Archive Switch ${DB} on $BOX" $MAIL_LIST <<EOF
Archive log dest, box: $BOX, sid: ${DB} has switched.
Then ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=<Normal Location>';
EOF
else
  echo no need to switch, ${free_space} KB free on ${loc}
fi
#
exit 0

The prior script assumes that the LOG_ARCHIVE_DEST_1 initialization parameter has been set to your archive redo location. If you’re using an FRA for the location of your archive redo log files, you can derive the archive location from the V$ARCHIVED_LOG view; for example,

select
  substr(name,1,instr(name,'/',1,2)-1)
from v$archived_log
where first_time =
 (select max(first_time) from v$archived_log);

Typically, I’ll run a script to check the archive redo log destination once an hour. Here is a typical cron entry (this entry should actually be a single line of code but has been placed on two lines in order to fit on the page):

38 * * * * /u01/oracle/bin/arch_check.bsh DWREP
  1>/u01/oracle/bin/log/arch_check.log 2>&1

Truncating Large Log Files

Sometimes log files can grow very large and cause issues by filling up critical mount points. The listener.log will record information about incoming connections to the database. With active systems this file can quickly grow to several gigabytes. For many of my environments, the information in the listener.log file does not need to be retained. If there are Oracle Net connectivity issues, then the file can be inspected to help troubleshoot issues.

The listener.log file is actively written to, so you shouldn’t just delete it. If you remove the file, the listener process won’t re-create the file and start writing to it again; you have to stop and restart the listener to restart its writing to the listener.log file. You can, however, null out the listener.log file or truncate it. In Linux/Unix environments this is done via the following technique:

$ cat /dev/null >listener.log

The previous command replaces the contents of the listener.log file with the contents of /dev/null (a default file on Linux/Unix systems that contains nothing). The result of this command is that the listener.log file is truncated, and the listener can continue to actively write to it.

Listed next is a shell script that truncates the default listener.log file. This script is dependent on setting the OS variable ORACLE_BASE. If you don’t set that variable in your environment, you’ll have to hard-code the directory path within this script:

#!/bin/bash
#
if [ $# -ne 1 ]; then
  echo "Usage: $0 SID"
  exit 1
fi
# See Chapter 2 for details on setting OS variables
# Source oracle OS variables with oraset script
. /etc/oraset $1
#
MAILX='/bin/mailx'
MAIL_LIST='[email protected]’
BOX=$(uname -a | awk '{print $2}' | cut -f 1 -d'.')
#
if [ -f $ORACLE_BASE/diag/tnslsnr/$BOX/listener/trace/listener.log ]; then
  cat /dev/null > $ORACLE_BASE/diag/tnslsnr/$BOX/listener/trace/listener.log
fi
if [ $? -ne 0 ]; then
  echo "trunc list. problem" | $MAILX -s "trunc list. problem $1" $MAIL_LIST
else
  echo "no problem..."
fi
exit 0

The following cron entry runs the prior script on a monthly basis (this entry should all be on one line but has been placed on two lines in order to fit on the page):

30 6 1 * * /orahome/oracle/bin/trunc_log.bsh DWREP
  1>/orahome/oracle/bin/log/trunc_log.log 2>&1

Checking for Locked Production Accounts

Usually I have a database profile in place that specifies that a database account become locked after a designated number of failed login attempts. For example, I’ll set the DEFAULT profile FAILED_LOGIN_ATTEMPTS to 5. Sometimes, however, a rogue user or developer will attempt to guess the production account password, and after five attempts, locks the production account. When this happens, I need to know about it as soon as possible so that I can investigate the issue and then unlock the account.

The following shell script checks the LOCK_DATE value in DBA_USERS for a list of production database accounts:

#!/bin/bash
if [ $# -ne 1 ]; then
  echo "Usage: $0 SID"
  exit 1
fi
# source oracle OS variables
. /etc/oraset $1
#
crit_var=$(sqlplus -s <<EOF
/ as sysdba
SET HEAD OFF FEED OFF
SELECT count(*)
FROM dba_users
WHERE lock_date IS NOT NULL
AND username in ('CIAP','REPV','CIAL','STARPROD'),
EOF)
#
if [ $crit_var -ne 0 ]; then
  echo $crit_var
  echo "locked acct. issue with $1" | mailx -s "locked acct. issue" [email protected]
else
  echo $crit_var
  echo "no locked accounts"
fi
exit 0

This shell script is called from a scheduling tool, such as cron. For example, this cron entry instructs the job to run every 10 minutes (this entry should actually be a single line of code but has been placed on two lines in order to fit on the page):

0,10,20,30,40,50 * * * * /home/oracle/bin/lock.bsh DWREP
   1>/home/oracle/bin/log/lock.log 2>&1

In this way, I am notified when one of the production database accounts becomes locked.

Checking for Files Older Than a Certain Age

For some jobs that I run from shell scripts (such as backups), I’ll first check to see if another backup job is already running. The check involves looking for a lock file. If the lock file exists, then the shell script exits; if the lock file doesn’t exist, then one is created. At the end of the job, the lock file is removed.

Sometimes, there’s a problem with the job, and it aborts abnormally before the lock file can be removed. In these situations, I want to know if a lock file that is older than 1 day exists the a server. The existence of an old lock file indicates that there has been an issue and that I need to investigate. The following shell script checks for any lock files older than 1 day in the tmp directory.

#!/bin/bash
BOX=$(uname -a | awk '{print $2}')
# find all lock files gt 1 day old.
# Find all lock files in /tmp, if found, find any older than one day
ls /tmp/*.lock 2>/dev/null && 
filevar=$(find /tmp/*lock -type f -mtime +1 | wc -l) || filevar=0
if [ $filevar -gt 0 ]; then
  echo "$BOX, lockfile issue: $filevar" | 
  mailx -s "$BOX lockfile problem" [email protected]
else
  echo "Lock file ok: $filevar"
fi
exit 0

I usually check for the existence of a lock file on a daily basis. Here is a typical cron entry for running the prior script, named lock_chk.bsh (this entry should all be on one line but has been placed on two lines in order to fit on the page):

33 5 * * * /orahome/oracle/bin/lock_chk.bsh
   1>/orahome/oracle/bin/log/lock_chk.log 2>&1

Checking for Too Many Processes

On some database servers, you may have many background SQL*Plus jobs. These batch jobs may perform tasks such as copying data from remote databases and large daily update jobs. In these environments it’s useful to know if, at any given time, there are an abnormal number of shell scripts or SQL*Plus processes running on the database server. An abnormal number of jobs could be an indication that something is broken or hung.

The next shell script has two checks in it: one  to determine the number of shell scripts that are named with the extension of bsh and one to determine the number of processes that contain the string of sqlplus:

#!/bin/bash
#
if [ $# -ne 0 ]; then
  echo "Usage: $0"
  exit 1
fi
#
crit_var=$(ps -ef | grep -v grep | grep bsh | wc -l)
if [ $crit_var -lt 20 ]; then
  echo $crit_var
  echo "processes running normal"
else
  echo "too many processes"
  echo $crit_var | mailx -s "too many bsh procs: $1" [email protected]
fi
#
crit_var=$(ps -ef | grep -v grep | grep sqlplus | wc -l)
if [ $crit_var -lt 30 ]; then
  echo $crit_var
  echo "processes running normal"
else
  echo "too many processes"
  echo $crit_var | mailx -s "too many sqlplus procs: $1" [email protected]
fi
#
exit 0

The prior shell script, named proc_count.bsh, is run once an hour from a cron job (this entry should actually be a single line of code but is placed on two lines in order to fit on the page):

33 * * * * /home/oracle/bin/proc_count.bsh
  1>/home/oracle/bin/log/proc_count.log 2>&1

Verifying the Integrity of RMAN Backups

As part of your backup-and-recovery strategy, you should periodically validate the integrity of the backup files. RMAN provides a RESTORE...VALIDATE command that checks for physical corruption within the backup files. The following script starts RMAN and spools a log file. The log file is subsequently searched for the keyword error. If there are any errors in the log file, an e-mail is sent:

#!/bin/bash
#
if [ $# -ne 1 ]; then
  echo "Usage: $0 SID"
  exit 1
fi
# source oracle OS variables
. /etc/oraset $1
#
date
BOX='uname -a | awk '{print$2}''
rman nocatalog <<EOF
connect target /
spool log to $HOME/bin/log/rman_val.log
set echo on;
restore database validate;
EOF
grep -i error $HOME/bin/log/rman_val.log
if [ $? -eq 0 ]; then
  echo "RMAN verify issue $BOX, $1" | 
  mailx -s "RMAN verify issue $BOX, $1" [email protected]
else
  echo "no problem..."
fi
#
date
exit 0

The RESTORE...VALIDATE doesn’t actually restore any files; it only validates that the files required to restore the database are available and checks for physical corruption.

If you need to check for logical corruption as well, specify the CHECK LOGICAL clause. For example, to check for logical corruption, the prior shell script would have this line in it:

restore database validate check logical;

For large databases the validation process can take a great deal of time (because the script checks each block in the backup file for corruption). If you only want to verify that the backup files exist, specify the V ALIDATE HEADER clause, like so:

restore database validate header;

This command only checks for valid information in the header of each file that would be required for a restore and recovery.