10g & 11g :Configuration of TAF(Transparent Application Failover) and Load Balancing [ID 453293.1]

Script started on Thu 01 Mar 2012 10:43:38 AM EST
[oracle@dbrac1 scripts]$ srvctl add service -d dbrac -s dbrac_taf -r "dbrac1,dbrac2" -P BASIC
[oracle@dbrac1 scripts]$ srvctl start service -d dbrac -s dbrac_taf
[oracle@dbrac1 scripts]$ srvctl config service -d dbrac
Service name: dbrac_taf
Service is enabled
Server pool: dbrac_dbrac_taf
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: dbrac1,dbrac2
Available instances:
[oracle@dbrac1 scripts]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 10:45:20 2012


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> select name, service_id from dba_services where name='dbrac_taf';


NAME                                                             SERVICE_ID
---------------------------------------------------------------- ----------
dbrac_taf                                                                 3


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 headiSQL> SQL> SQL> SQL> SQL> SQL> ng 'AQNOT'
SQL>
SQL>
SQL>
SQL> select name,failover_method, failover_type, failover_retries,goal, clb_goal, aq_ha_notifications from dba_services where service_id=3;


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       NONE        NONE              0 NONE       LONG     NO


Note: no need to do following step in SQL, in 11gR2, it can be done by using "srvctl" command
SQL> execute dbms_service.modify_service (service_name => 'dbrac_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.


SQL> select name,failover_method, failover_type, failover_retries,goal, clb_goal, aq_ha_notifications from dba_services where service_id=3;


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       BASIC       SELECT          180 NONE       LONG     YES


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options




[oracle@dbrac1 scripts]$ lsnrctl services


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-MAR-2012 10:49:38


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "dbrac" has 1 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "dbracXDB" has 1 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=dbrac1.wealthengine.com)(PORT=25774))
Service "dbrac_taf" has 1 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[oracle@dbrac1 scripts]$ exit
Script done on Thu 01 Mar 2012 11:01:13 AM EST



Note: =============================
I don't know why when I re-queired the "dba_service" table later after a few re-start of instance 2 at node 2, it shows me following:

[oracle@dbrac1 admin]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 14:36:18 2012


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


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 headiSQL> SQL> SQL> SQL> SQL> SQL> ng 'AQNOT'
SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications from dba_services where service_id =3;


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       NONE        NONE              0 NONE       LONG     NO


SQL> exit


====================================================


That's why I modified the service settings with "srvctl" command rather than the pre-11gR2  method by running "execute dbms_service.modify_service ..."



[oracle@dbrac1 scripts]$ srvctl modify service -d dbrac -s dbrac_taf -r "dbrac1,dbrac2" -P basic -e select -m basic -z 10 -w 5 -B throughput
PRKO-2002 : Invalid command line option: dbrac1,dbrac2
[oracle@dbrac1 scripts]$ srvctl modify service -d dbrac -s dbrac_taf -P basic -e select -m basic -z 10 -w 5 -B throughput
[oracle@dbrac1 scripts]$
[oracle@dbrac1 scripts]$
[oracle@dbrac1 scripts]$ srvctl config service -d dbrac -s dbrac_taf
Service name: dbrac_taf
Service is enabled
Server pool: dbrac_dbrac_taf
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 10
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: THROUGHPUT
TAF policy specification: BASIC
Edition:
Preferred instances: dbrac1,dbrac2
Available instances:
[oracle@dbrac1 scripts]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 1 14:52:46 2012


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


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 headiSQL> SQL> SQL> SQL> SQL> SQL> ng 'AQNOT'
SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id =  2  3
  3  /


NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
dbrac_taf       BASIC       SELECT           10 THROUGHPUT LONG     NO


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Oracle Metalink Notes:

• 10g & 11g :Configuration of TAF(Transparent Application Failover) and Load Balancing [ID 453293.1]
• Note 460982.1 How To Configure Server Side Transparent Application 
• NOTE 263599.1 Understanding and Troubleshooting Instance Load Balancing
• NOTE 395525.1How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager
• NOTE:226880.1 - Configuration of Load Balancing and Transparent Application Failover
• NOTE:342419.1 - ORA-12520: Listeners Running on VIP Address in 10g or Newer RAC
• Failover Issues and Limitations [Connect-time failover and TAF] [ID 97926.1]
• Connect time failover (ADDRESS_LISTS) Note:67136.1
• Configuring Transparent Application Failover Note:69906.1
• Oracle8i Dynamic Instance Registration Note:69546.1

Oracle Books:

• Pro Oracle Database 11g RAC - page 572-577 (Balancing the Workload)