Recently, I discovered a bug in EM. If we create a database service using EM, it updates TAF settings (Failover type, failover method, retries and delay) only in tnsnames.ora on all the nodes . Data dictionary and OCR are not updated.

Current scenario:

Name of RAC database : orcl
No. of Nodes  : 2
Names of nodes : host01, host02

— Create database service orcl_em using EM --

Name of service orcl_em

Attributes :

Start service after creation : Yes
Update local naming parameter (tnsnames.ora) file  : Yes
preferred instance : orcl1
Available instance : orcl2
TAF policy         : Basic

– After the service has been created, check that service is running on preferred instance orcl1

[oracle@host01 ~]$ srvctl status service -s orcl_em -d orcl

Service orcl_em is running on instance(s) orcl1

– check that the tnsnames.ora entry contains TAF settings as specified :

orcl_em =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = cluster01-scan.cluster01.example.com)
(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_em)
  (FAILOVER_MODE =
         (TYPE = SELECT)
         (METHOD = BASIC)
         (RETRIES = 180)
         (DELAY = 5))))

– check that data dictionary view is not aware of TAF settings

SQL> col name for a15
col failover_type for a15
col failover_method for a15

select name, FAILOVER_METHOD, FAILOVER_TYPE, FAILOVER_RETRIES, FAILOVER_DELAY
from dba_services
where upper(name) = 'ORCL_EM';

NAME            FAILOVER_TYPE   FAILOVER_METHOD FAILOVER_RETRIES FAILOVER_DELAY
--------------- --------------- --------------- ---------------- --------------
orcl_em

– check that the resource profile as stored in OCR also does not contain any information about TAF settings

[oracle@host01 admin]$ srvctl config service -s orcl_em -d orcl
Service name: orcl_em
Service is enabled
Server pool: orcl_orcl_em
Cardinality: 1
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: orcl1
Available instances: orcl2

It can be clearly seen that TAF attributes are updated only in tnsnames.ora . They have not been updated in data dictionary or OCR.

– Now let us create and start another database service orcl_manual using srvctl

[oracle@host01 admin]$ srvctl add service -s orcl_manual -r orcl1 -a orcl2 -m BASIC -e SELECT -w 100 -z 5

[oracle@host01 admin]$ srvctl start service -s orcl_manual -d orcl

– Check that TAF attributes have been updated in OCR

[oracle@host01 admin]$ srvctl config service -s orcl_manual -d orclService name: orcl_manual
Service is enabled
Server pool: orcl_orcl_manual
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 100
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: orcl1
Available instances: orcl2

– Check that TAF settings have been updated in data dictionary too

SQL> select name, failover_type, failover_method, failover_retries, failover_delay
from dba_services
where upper(name) = 'ORCL_MANUAL';

NAME            FAILOVER_TYPE   FAILOVER_METHOD FAILOVER_RETRIES FAILOVER_DELAY
--------------- --------------- --------------- ---------------- --------------
orcl_manual     SELECT          BASIC                          5            100

Hence, database services for RAC database should always be created using srvctl and tnsnames.ora entry should be  added manually.

——————————————————————————————

Related Links:

Home

11g R2 RAC Index

11g R2 RAC :Services
Tracing Services in RAC