Oracle alerted us recently of a non public bug #6886239 "DBMS_SERVICE parameters are not added using srvctl add service. It affects 10g and 11gR1 databases, this is fixed in release 11.2 onwards. In Oracle RAC after setting up TAF although it does not fails and everything looks good, it does not configure correctly.
When you check the service configuration you find no values for failover method, type and retries, those values are needed for TAF to happen.
Creating a service
srvctl add service -d rac -s server_taf -r "rac1,rac2" -P BASIC
Start the service
srvctl start service -d rac -s server_taf
Checking that the services are running.
srvctl config service -d rac
ractest PREF: rac1 rac2 AVAIL:
server_taf PREF: rac1 rac2 AVAIL:
Getting the service ID value
sqlplus /nolog
Connect / as sysdba
SQL> select name,service_id from dba_services where name = 'server_taf';
NAME SERVICE_ID
---------------------------------------------------------------- ----------
server_taf 6
Checking the setup
SQL>col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'
SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 6
NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf LONG NO
Adding the server side failover parameter to the service
Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time. It is not possible to pre-establish a backup connection. (which is to say, PRECONNECT is not supported)
SQL> execute dbms_service.modify_service (service_name => 'server_taf' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries => 180 -
, failover_delay => 5 -
, clb_goal => dbms_service.clb_goal_long);
PL/SQL procedure successfully completed.
Check that the service can now see the values for methods, types and retries
SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 6
NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf BASIC SELECT 180 NONE LONG YES
Check that the listener has the service registered
lsnrctl services
Create a net service name. Here we have client load balancing between the two nodes.
SERVERTAF =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = dell01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dell02)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = server_taf.za.oracle.com)
You are all set now :-
Subscribe to:
Post Comments (Atom)
Posts
2 comments:
if we change aq_ha_notifications to true will it be any problem on normal db execution or how to revoke the action.
Praba, I am not sure I am following what you mean. You need to change it to true if you are using advanced queueing and you want your queue to continue working after failover, even if you don't use AQ I don't see any harm in having it set to true other than the additional chatter about AQ information across the nodes. You can always change the service to make this option false.
DBMS_SERVICE.MODIFY_SERVICE(
service_name IN VARCHAR2,
goal IN NUMBER DEFAULT NULL,
dtp IN BOOLEAN DEFAULT NULL,
aq_ha_notifications IN BOOLEAN DEFAULT NULL,
failover_method IN VARCHAR2 DEFAULT NULL,
failover_type IN VARCHAR2 DEFAULT NULL,
failover_retries IN NUMBER DEFAULT NULL,
failover_delay IN NUMBER DEFAULT NULL,
clb_goal IN NUMBER DEFAULT NULL);
I hope this helps :)
Post a Comment