Hi,
had to review service creation with DBMS_SERVICE package for a good-feeling load balancing in Oracle RAC 10gR2 several times – now I have to write it down for myself. 🙂 I needed it for a web service using shared server today, but the generic syntax is useful for all other purposes as well.
Basic service creation in RAC is rather simple, just use
alter system set service_name='service1','service2' scope=both sid='*'; alter system register;
But for the configuration of active RAC load balancing and for setting a special focus on a balancing behaviour, you need the DBMS_SERVICE package, as described in Oracle documentation “95 DBMS_SERVICE“.
My example syntax for a modification of a service created by init parameter service_name (as described above):
begin
dbms_service.modify_service(service_name => 'SERVICE2', goal => DBMS_SERVICE.GOAL_THROUGHPUT, aq_ha_notifications => TRUE, failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC, failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, failover_retries => 120, failover_delay => 2, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);
end;
A quick explanation:
GOAL_THROUGHPUT: Load Balancing Advisory is based on the rate that work is completed in the service plus available bandwidth to the service. Other option: GOAL_SERVICE_TIME, it does not take the real work into account, just the time you are “sitting on the node”.
aq_ha_notifications: Determines whether HA events are sent via AQ for this service. I considered Active Queuing being a good solution for me.
FAILOVER_METHOD_BASIC: You don’t have another chance than BASIC, that’s the switch for TAF on or TAF off.
FAILOVER_TYPE_SELECT: Failover works on statement basis, not session based.
RETRIES and DELAY should explain themselves.
CLB_GOAL_SHORT: Connection load balancing uses Load Balancing Advisory, when Load Balancing Advisory is enabled (either goal_service_time or goal_throughput). When GOAL=NONE (no load balancing advisory), connection load balancing uses an abridged advice based on CPU utilization.
EDIT: Just a hint: Check your results with
select * from gv$services;
Have a well balanced day now,
Usn