How to Setup and Monitor Oracle Job Coordinator Background Process

Information about Setting up and Monitor Oracle Job Coordinator Background

This document will describe the process to setup and run the Oracle Job Coordinator Background Process  Monitoring Script called check_ora_jobq_proc.

These processes must be active in order for a scheduled database jobs to run (database version of crontab). The process is named ora_cjq0_<instance name> or ora_cjq0_PWAKDB2 on srv0dbx02. The background process is started when the instance is started.

If the process is down  jobs that are scheduled in the database are affected and usually the instance will have to be restarted. OEM has its own job queue outside of the database. OEM uses the cjq0 process to run its jobs .

Document Creator Todd Walters  

Requirements

• Only run on Oracle RAC servers
• The check_ora_jobq_proc script is used to Monitor the ora_cjq0_(InstanceName) process.
• The check_ora_jobq_proc script is located locally in /scripts/bin
• A copy of check_ora_jobq_proc script is located in /home/admins/cci/sbin
• The check_ora_jobq_proc script is run by root.
• The script is run hourly via root's cron job.
• Must have root access to run this command.
• If the ora_cjq0_(InstanceName) is not running, then Email Notification is sent to OracleAdmin@mysite.com and LinuxTeam@mysite.com Distrubution-Lists.
• Always check with DBA if you recieve email on this.

How to Setup check_ora_jobq_proc Script

1. Login to each Oracle RAC Node Server
2. Switch user to root and edit check_ora_jobq_proc if necessary
• [twalters@srv0smx00 ~]$ su -Password:
  [root@srv0smx00 ~]# vi  /scripts/bin/check_ora_jobq_proc
3. The Script is written as follow:

[root@srv0dbx01 ~]# cat /scripts/bin/check_ora_jobq_proc
#!/bin/bash
#
# This script will monitor process and email if they are not running.
# Todd Walters -  5/28/2009
#
#
DB_SID=`ps -ef |grep pmon |grep DB | awk -F_ '{print $NF}'`
if [ -z "$DB_SID" ]; then
          exit
  fi
PROC_NAME=ora_cjq0_$DB_SID
# must run as root
if [ `whoami` != "root" ]; then
  echo "You don't have privileged to run this program."
  exit 1
fi
# check if process is running....
RUNNING=`ps -ef | grep "$PROC_NAME" | grep -v grep | wc -l`
if [ $RUNNING -ne 1 ]; then
  echo "$PROC_NAME is not running..."
  echo "From: \" IT Oracle Admin\" <OracleAdmin@mysite.com>
To: todd.walters@mysite.com
Subject: WARNING $PROC_NAME is not running - `date`
The Job Coordinator Background process $PROC_NAME is not running on `hostname`
`ps -ef | grep $PROC_NAME `
A DBA should please check on this process $PROC_NAME on `hostname` and restart if necessary
================================================================================
This report was generated by $0 from `hostname` " | /usr/sbin/sendmail -t
  exit 1
fi

1. Add Script to crontab on ALL 3 Oracle RAC Nodes

• [root@srv0dbx02 ~]# crontab -e
• Add this line:

1. 5 * * * * /scripts/bin/check_ora_jobq_proc 2>&1
1. Verifiy cront is correct with:
• [root@srv0dbx02 ~]# crontab -l
2. Example Email Message:
   
    -----Original Message-----

From: CCI SAN - IT Oracle Admin [mailto: ITOracleAdmin@mydomain.com]
Sent: Thursday, May 28, 2009 11:02 AM
To: Walters, Todd

Subject: WARNING ora_cjq0_PWAKDB2 is not running - Thu May 28 11:01:34 PDT 2009

The Job Coordinator Background process ora_cjq0_PWAKDB2 is not running on srv0dbx02.mysite.com

oracle 1123 1 0 May06 ? 00:04:12 ora_cjq0_PWAKDB2

root 16981 16979 0 11:01 pts/0 00:00:00 grep ora_cjq0_PWAKDB2

A DBA should please check on this process ora_cjq0_PWAKDB2 on srv0dbx02.corp.mysite.com and restart if necessary.
================================================================================
This report was generated by /scripts/bin/check_ora_jobq_proc from srv0dbx02.mysite.com