JDBC and dot Net clients are the recommended way to automatically subscribe to high availability events in an Oracle clustered environment, AKA, a RAC.
I was curious as to how this could be implemented using your own code behind the scenes. To show this, we will use a combination of PL/SQL and python.
First, we should note the queue used by Oracle to store high availability events is a member of the SYS schema and is named SYS$SERVICE_METRICS. As such, we first need to allow our user who will will be monitoring the service health of our cluster to dequeue events from this queue.
As SYS…
begin
dbms_aqadm.grant_queue_privilege (privilege =>'DEQUEUE',
queue_name => 'sys.SYS$SERVICE_METRICS',
grantee => 'HOWARDS',
grant_option=>FALSE);
end;
/
We also need to allow our user to perform operations using the PAYLOAD_TYPE used by the queue. This is also a member of the SYS schema and is named SYS$RLBTYP.
grant execute on dbms_aq to howards;
GRANT EXECUTE ON SYS.SYS$RLBTYP to HOWARDS;
We must then add ourselves as a subscriber of this queue…
declare
subscriber sys.aq$_agent;
begin
subscriber:=sys.aq$_agent('HOWARDS',null,null);
dbms_aqadm.add_subscriber(queue_name => 'sys.SYS$SERVICE_METRICS', subscriber=>subscriber);
end;
/
Next, we use a PL/SQL procedure to dequeue our events. This is created in the HOWARDS schema.
CREATE OR REPLACE procedure get_rac_events (p_srv out varchar2,
p_payload 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.SYS$RLBTYP;
BEGIN
l_dequeue_options.consumer_name := 'HOWARDS';
l_dequeue_options.visibility := DBMS_AQ.IMMEDIATE;
l_dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;
DBMS_AQ.dequeue(queue_name => 'sys.SYS$SERVICE_METRICS',
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.srv || ' ' || l_event_msg.payload);
p_srv := l_event_msg.srv;
p_payload := l_event_msg.payload;
COMMIT;
END;
/
SHOW ERRORS
Ensure that your service is configured for high availability events…
begin
dbms_service.modify_service('reqman',AQ_HA_NOTIFICATIONS=>true,GOAL=>1,clb_goal=>1);
end;
/
Lastly, we use a short python script to run in a loop and print events as we dequeue them with our procedure created above…
#!/usr/local/bin/python
import cx_Oracle
import string
import time
ora = cx_Oracle.Connection('howards/****@perfrac')
curs = ora.cursor()
n = curs.var(cx_Oracle.STRING)
o = curs.var(cx_Oracle.STRING)
min=0
while True:
curs.callproc('get_rac_events',[n,o])
service=string.split(string.split(str(o)," ")[5],"=")[1]
instances=string.split(string.split(str(o),"{ ")[1]," }")[0]
found=True
while found == True:
thisone=string.replace(string.split(instances,"}")[0],"{","")
if thisone == "":
found = False
else:
instance=string.split(string.split(str(thisone)," ")[0],"=")[1]
pct=string.split(string.split(str(thisone)," ")[1],"=")[1]
print "For service " + service + ", send " + str(pct) + "% of traffic to instance " + instance
instances=string.replace(instances[instances.find("}") + 1:],"{","")
When you run this, you should see output similar to what is below…
C:\>type.py
For service perfcnx, send 50% of traffic to instance perfrac1
For service perfcnx, send 50% of traffic to instance perfrac2
For service perfrac.dev.oclc.org, send 54% of traffic to instance perfrac1
For service perfrac.dev.oclc.org, send 46% of traffic to instance perfrac2
For service reqman, send 100% of traffic to instance perfrac2
For service perfcnx, send 50% of traffic to instance perfrac1
For service perfcnx, send 50% of traffic to instance perfrac2
For service perfrac.dev.oclc.org, send 69% of traffic to instance perfrac1
For service perfrac.dev.oclc.org, send 31% of traffic to instance perfrac2
For service reqman, send 100% of traffic to instance perfrac2
For service perfcnx, send 50% of traffic to instance perfrac1
For service perfcnx, send 50% of traffic to instance perfrac2
For service perfrac.dev.oclc.org, send 60% of traffic to instance perfrac1
For service perfrac.dev.oclc.org, send 40% of traffic to instance perfrac2
For service reqman, send 100% of traffic to instance perfrac2
For service perfcnx, send 50% of traffic to instance perfrac1
For service perfcnx, send 50% of traffic to instance perfrac2
For service perfrac.dev.oclc.org, send 55% of traffic to instance perfrac1
For service perfrac.dev.oclc.org, send 45% of traffic to instance perfrac2
For service reqman, send 100% of traffic to instance perfrac2
For service perfcnx, send 50% of traffic to instance perfrac1
For service perfcnx, send 50% of traffic to instance perfrac2
For service perfrac.dev.oclc.org, send 76% of traffic to instance perfrac1
For service perfrac.dev.oclc.org, send 24% of traffic to instance perfrac2
For service reqman, send 100% of traffic to instance perfrac2
For service perfcnx, send 50% of traffic to instance perfrac1
For service perfcnx, send 50% of traffic to instance perfrac2
For service perfrac.dev.oclc.org, send 87% of traffic to instance perfrac1
For service perfrac.dev.oclc.org, send 13% of traffic to instance perfrac2
Obviously, you can make this much more sophisticated.
Next time, we will look at a far more efficient way of doing this, similar to subscribing to the ONS setup which we configure when using an OracleDataSource in java.