Quickpoll Monitor Alert

Procedure Call

Quickpoll_Monitor_Alert(#DBID#, 30, #ALERTVALUE#, #ALERTSTRING#)

Description

This alert will notify you when:

1. A QuickPoll process is no longer running for the selected databases.
2. A QuickPoll process is running but has not collected data for over 30 minutes (the 30 in the above call).

PL/SQL Code For Quickpoll_Monitor_Alert Procedure

CREATE OR REPLACE PROCEDURE Quickpoll_Monitor_Alert (

                pDatabase               in number,

                pMinutes  in number,

                pAlertLevel              out varchar2,

        pError                              out varchar2)

AS

        sDatabaseName              varchar2(100);

                bMonitorUp              varchar2(10);

                iIntervals integer;

        OracleSID        integer;

                sSQL                       varchar2(100);

BEGIN

   -- initialize the alert values to FALSE, i.e. nothing wrong

   pAlertLevel := 'FALSE';

   pError := '';

   -- get the database name

   SELECT name INTO sDatabaseName FROM cond WHERE id = pDatabase;

   -- see if the QuickPoll monitor is running

   SELECT decode(count(1),0,'FALSE','TRUE')

   INTO bMonitorUp

   FROM gv$session

   WHERE module = SUBSTR(TRIM(sDatabaseName) || '-QuickPollProc',1,48);

   -- is the monitor running for this database?

   IF bMonitorUp = 'TRUE' THEN

      -- monitor is running so let's make sure data has been collected in the last "x" minutes

      -- should use something more than 10 minutes here because the summary poll may not be quite done

      sSQL := 'select count(1) from contt_' || trim(pDatabase) || '

               where iedx > sysdate - ' || trim(pMinutes) || '/1440';

      EXECUTE IMMEDIATE sSQL INTO iIntervals;

      -- if nothing has been collected then warn someone

      IF iIntervals = 0 THEN

         pAlertLevel := 'TRUE';

         pError := 'Ignite QuickPoll for ' || sDatabaseName || ' is still running but has not collected data in the last ' || pMinutes || ' minutes.';

      END IF;

   ELSE

      -- the monitor is not up so also warn about this

      pAlertLevel := 'TRUE';

      pError := 'Ignite QuickPoll for ' || sDatabaseName || ' is no longer running.';

   END IF;  -- Monitor Up

END Quickpoll_Monitor_Alert;

/

For more information about Writing Custom Alerts, see PG110.