SERVICE CREATED USING EM DOES NOT UPDATE DATA DICTIONARY OR OCR

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

 

 

 

 

2 thoughts on “SERVICE CREATED USING EM DOES NOT UPDATE DATA DICTIONARY OR OCR

  1. Thanks Anuj, for sharing this. This is very interesting. May I also please request, what is the version of EM. I’m asking this because recently I have created a services for 11gR2 database with EM 12cr2 and it works fine. Let’s explore more.

    1. Hi Jay

      Let me clarify that my name is Anju and not Anuj. I was using EM for 11g only. In your case services might be working fine because:
      – bug might have been resolved in EmM 12c r2′
      or
      – The bug might still be there but you are accessing database service using tnsnames.ora created by EM which contains TAF settings. The point is that you can configure TAF settings in tnsnames.ora on clilent side or in OCR/data dictionary on server side or both. They will work fine. But if TAF settings are missing from server side and you resolve connect string using some means other than tnsnames.ora, it will not work as expected.

      Hope it helps

      Regards
      Anju Garg

Your comments and suggestions are welcome!