Rolling your own Grid Control with AQ

DBControl can do the same thing, but if you have more than one database on a server, this is where something like this will come in handy.

PLSQL setup, you should only have to change the SHOWARD part…

begin
  dbms_aqadm.grant_queue_privilege (privilege =>'DEQUEUE',
                                    queue_name => 'sys.alert_que',
                                    grantee => 'HOWARDS',
                                    grant_option=>FALSE);
end;
/
 
grant execute on dbms_aq to howards;
grant execute on sys.alert_type to howards;
 
begin
  dbms_aqadm.create_aq_agent('SHOWARD');
  dbms_aqadm.enable_db_access(agent_name  => 'HOWARDS',db_username => 'HOWARDS');
end;
/
 
declare
  subscriber sys.aq$_agent;
begin
  subscriber:=sys.aq$_agent('HOWARDS',null,null);
  dbms_aqadm.add_subscriber(queue_name => 'sys.alert_que', subscriber=>subscriber);
end;
/
 
create or replace procedure howards.get_alerts (p_timestamp_originating out timestamp,
                                                p_reason_argument_1 out varchar2,
                                                p_reason_argument_2 out varchar2,
                                                p_reason_argument_3 out varchar2,
                                                p_reason_argument_4 out varchar2,
                                                p_reason_argument_5 out varchar2,
                                                p_instance_name out varchar2) is
  l_dequeue_options     dbms_aq.dequeue_options_t;
  l_message_properties  dbms_aq.message_properties_t;
  l_message_handle      raw(16);
  l_event_msg           sys.alert_type;
begin
    l_dequeue_options.consumer_name := 'HOWARDS';
    l_dequeue_options.visibility := dbms_aq.immediate;
    l_dequeue_options.dequeue_mode := dbms_aq.remove;
    dbms_aq.dequeue(queue_name          => 'SYS.ALERT_QUE',
                    dequeue_options     => l_dequeue_options,
                    message_properties  => l_message_properties,
                    payload             => l_event_msg,
                    msgid               => l_message_handle);
    --dbms_output.put_line(l_event_msg.timestamp_originating || ' ' || l_event_msg.instance_name);
    p_timestamp_originating := l_event_msg.timestamp_originating;
    p_reason_argument_1 := l_event_msg.reason_argument_1;
    p_reason_argument_2 := l_event_msg.reason_argument_2;
    p_reason_argument_3 := l_event_msg.reason_argument_3;
    p_reason_argument_4 := l_event_msg.reason_argument_4;
    p_reason_argument_5 := l_event_msg.reason_argument_5;
    p_instance_name := l_event_msg.instance_name;
    commit;
end;
/

You can then register the procedure above as a callback with the queue, or for testing, run the python code below (still being fleshed out). You could also just run the get_alerts procedure (which will hang) and fill up a tablespace. You should see the alert output on the SQL*PLUS session.

#!/home/oracle/local/bin/python
 
import cx_Oracle
import string
import time
 
ora = cx_Oracle.Connection('howards/******@emprod1')
curs = ora.cursor()
n = curs.var(cx_Oracle.TIMESTAMP)
o1 = curs.var(cx_Oracle.STRING)
o2 = curs.var(cx_Oracle.STRING)
o3 = curs.var(cx_Oracle.STRING)
o4 = curs.var(cx_Oracle.STRING)
o5 = curs.var(cx_Oracle.STRING)
p = curs.var(cx_Oracle.STRING)
while True:
  curs.callproc('get_alerts',[n,o1,o2,o3,o4,o5,p])
  print str(n.getvalue()) + " " + str(o1.getvalue()) + " " + str(o2.getvalue()) + " " + str(o3.getvalue()) + " " + str(o4.getvalue()) + " " + str(o5.getvalue()) + " " + str(p.getvalue())

…which outputs stuff similar to…

 
12:08:21 oracle@emgrid01 ~ >./alerts.py
2010-01-19 12:11:58.848506 FOO 95 percent  None None None emprod1
2010-01-19 12:21:58.917836 FOO 0 percent  None None None emprod1
2010-01-19 12:41:59.033629 FOO 95 percent  None None None emprod1

The PLSQL callback registration with the queue is much cleaner, as you would have to daemonize the python stuff above. When I get that completely working, I will post it.