<< Flushing the SGA | Journals | Fixing Watermark Error in Replication >>

Jobs are automatically re-tried when it fails. However, after 16 re-tries, the job is marked as BROKEN and will not be tried again. Here is a query that will monitor the Job Status so that it can send a SMS to the DBA when broken jobs are detected. 

DECLARE
     BROKEN_JOBS_DETECTED EXCEPTION;
     V_COUNT NUMBER;
BEGIN
     SELECT COUNT(*) INTO V_COUNT FROM DBA_JOBS WHERE BROKEN='Y';
     IF (V_COUNT &gt; 0) THEN
          RAISE BROKEN_JOBS_DETECTED;
     END IF;
EXCEPTION
    WHEN BROKEN_JOBS_DETECTED THEN
           RAISE_APPLICATION_ERROR (-20199,'Broken Jobs Detected');
END;
/
EXIT

So in the command file: 

sqlplus user/password@instance @broken_job.sql
if %ERRORLEVEL% = 20199 sendmail email@address "Broken Job Detected" "Additional Text"

The sendmail is a 4NT command. You can replace it by any command line e-mail. In 4NT.INI, you need an entry for:

  • MailServer=pop.address.of.email
  • MailAddress=email@address

Contents

Play Area

Contacts

Wiki Guides

Private

Shortcuts

Interests

Controls

edit SideBar
Login