JDBC and dot Net clients are the recommended way to automatically subscribe to high availability events in an Oracle clustered environment, AKA, a RAC.<\/p>\n
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.<\/p>\n
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.<\/p>\n
As SYS…<\/p>\n
\r\nbegin\r\n dbms_aqadm.grant_queue_privilege (privilege =>'DEQUEUE',\r\n queue_name => 'sys.SYS$SERVICE_METRICS',\r\n grantee => 'HOWARDS',\r\n grant_option=>FALSE);\r\nend;\r\n\/\r\n<\/pre>\nWe 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.<\/p>\n
\r\ngrant execute on dbms_aq to howards;\r\nGRANT EXECUTE ON SYS.SYS$RLBTYP to HOWARDS;\r\n<\/pre>\nWe must then add ourselves as a subscriber of this queue…<\/p>\n
\r\ndeclare\r\n subscriber sys.aq$_agent;\r\nbegin\r\n subscriber:=sys.aq$_agent('HOWARDS',null,null);\r\n dbms_aqadm.add_subscriber(queue_name => 'sys.SYS$SERVICE_METRICS', subscriber=>subscriber);\r\nend;\r\n\/\r\n<\/pre>\nNext, we use a PL\/SQL procedure to dequeue our events. This is created in the HOWARDS schema.<\/p>\n
\r\nCREATE OR REPLACE procedure get_rac_events (p_srv out varchar2,\r\n p_payload out varchar2) is\r\n l_dequeue_options DBMS_AQ.dequeue_options_t;\r\n l_message_properties DBMS_AQ.message_properties_t;\r\n l_message_handle RAW(16);\r\n l_event_msg SYS.SYS$RLBTYP;\r\nBEGIN\r\n l_dequeue_options.consumer_name := 'HOWARDS';\r\n l_dequeue_options.visibility := DBMS_AQ.IMMEDIATE;\r\n l_dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;\r\n DBMS_AQ.dequeue(queue_name => 'sys.SYS$SERVICE_METRICS',\r\n dequeue_options => l_dequeue_options,\r\n message_properties => l_message_properties,\r\n payload => l_event_msg,\r\n msgid => l_message_handle);\r\n dbms_output.put_line(l_event_msg.srv || ' ' || l_event_msg.payload);\r\n p_srv := l_event_msg.srv;\r\n p_payload := l_event_msg.payload;\r\n COMMIT;\r\nEND;\r\n\/\r\n\r\nSHOW ERRORS\r\n<\/pre>\nEnsure that your service is configured for high availability events…<\/p>\n
\r\nbegin\r\n dbms_service.modify_service('reqman',AQ_HA_NOTIFICATIONS=>true,GOAL=>1,clb_goal=>1);\r\nend;\r\n\/\r\n<\/pre>\nLastly, we use a short python script to run in a loop and print events as we dequeue them with our procedure created above…<\/p>\n
\r\n#!\/usr\/local\/bin\/python\r\n\r\nimport cx_Oracle\r\nimport string\r\nimport time\r\n\r\nora = cx_Oracle.Connection('howards\/****@perfrac')\r\ncurs = ora.cursor()\r\nn = curs.var(cx_Oracle.STRING)\r\no = curs.var(cx_Oracle.STRING)\r\nmin=0\r\nwhile True:\r\n curs.callproc('get_rac_events',[n,o])\r\n service=string.split(string.split(str(o),\" \")[5],\"=\")[1]\r\n instances=string.split(string.split(str(o),\"{ \")[1],\" }\")[0]\r\n found=True\r\n while found == True:\r\n thisone=string.replace(string.split(instances,\"}\")[0],\"{\",\"\")\r\n if thisone == \"\":\r\n found = False\r\n else:\r\n instance=string.split(string.split(str(thisone),\" \")[0],\"=\")[1]\r\n pct=string.split(string.split(str(thisone),\" \")[1],\"=\")[1]\r\n print \"For service \" + service + \", send \" + str(pct) + \"% of traffic to instance \" + instance\r\n instances=string.replace(instances[instances.find(\"}\") + 1:],\"{\",\"\")\r\n<\/pre>\nWhen you run this, you should see output similar to what is below…<\/p>\n
\r\nC:\\>type.py\r\nFor service perfcnx, send 50% of traffic to instance perfrac1\r\nFor service perfcnx, send 50% of traffic to instance perfrac2\r\nFor service perfrac.dev.oclc.org, send 54% of traffic to instance perfrac1\r\nFor service perfrac.dev.oclc.org, send 46% of traffic to instance perfrac2\r\nFor service reqman, send 100% of traffic to instance perfrac2\r\nFor service perfcnx, send 50% of traffic to instance perfrac1\r\nFor service perfcnx, send 50% of traffic to instance perfrac2\r\nFor service perfrac.dev.oclc.org, send 69% of traffic to instance perfrac1\r\nFor service perfrac.dev.oclc.org, send 31% of traffic to instance perfrac2\r\nFor service reqman, send 100% of traffic to instance perfrac2\r\nFor service perfcnx, send 50% of traffic to instance perfrac1\r\nFor service perfcnx, send 50% of traffic to instance perfrac2\r\nFor service perfrac.dev.oclc.org, send 60% of traffic to instance perfrac1\r\nFor service perfrac.dev.oclc.org, send 40% of traffic to instance perfrac2\r\nFor service reqman, send 100% of traffic to instance perfrac2\r\nFor service perfcnx, send 50% of traffic to instance perfrac1\r\nFor service perfcnx, send 50% of traffic to instance perfrac2\r\nFor service perfrac.dev.oclc.org, send 55% of traffic to instance perfrac1\r\nFor service perfrac.dev.oclc.org, send 45% of traffic to instance perfrac2\r\nFor service reqman, send 100% of traffic to instance perfrac2\r\nFor service perfcnx, send 50% of traffic to instance perfrac1\r\nFor service perfcnx, send 50% of traffic to instance perfrac2\r\nFor service perfrac.dev.oclc.org, send 76% of traffic to instance perfrac1\r\nFor service perfrac.dev.oclc.org, send 24% of traffic to instance perfrac2\r\nFor service reqman, send 100% of traffic to instance perfrac2\r\nFor service perfcnx, send 50% of traffic to instance perfrac1\r\nFor service perfcnx, send 50% of traffic to instance perfrac2\r\nFor service perfrac.dev.oclc.org, send 87% of traffic to instance perfrac1\r\nFor service perfrac.dev.oclc.org, send 13% of traffic to instance perfrac2\r\n<\/pre>\nObviously, you can make this much more sophisticated. <\/p>\n
Next time, we will look at a far<\/b> more efficient way of doing this, similar to subscribing to the ONS setup which we configure when using an OracleDataSource in java.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n