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

In RAC, if a node crashes, a jobs running on that node can automatically failover to another node provided the job has been mapped to a service by means of job class.

Let’s demonstrate this concept.

Current scenario:

Name of the cluster : cluster01.example.com
No. of nodes : 2 (host01, host02)
Name of the database : orcl

– Create  demo table hr.test 

 SQL> create table hr.test(dt date);

– Create a  database service job_serv with

. preferred instance : orcl1
. available instance : orcl2

[oracle@host01 ~]$ srvctl add service -s job_srv -d orcl -a orcl2 -r orcl1
[oracle@host01 ~]$ srvctl status service -d orcl
Service job_serv is running on instance(s) orcl1
Service job_srv is not running.

preferred node : host01, available node host02

– Create tnsnames.ora entry for service job_serv

job_srv = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = cluster01-scan.cluster01.example.com)(PORT = 1521))(LOAD_BALANCE = YES)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = job_srv))

–  Start the service and check that service is running on host01 (preferred instance)

[oracle@host01 ~]$ srvctl start service -d orcl
CRS-5702: Resource 'ora.orcl.job_serv.svc' is already running on 'host01'

[oracle@host01 ~]$ srvctl status service -d orcl
Service job_srv is running on instance(s) orcl1

– Create job class JC1 which maps to the service job_serv

SQL>BEGIN
sys.dbms_scheduler.create_job_class(
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
service => 'job_srv',
job_class_name => '"JC1"');
END;
/

– Create a job JOB1 belonging to job class JC1 which inserts a row in table hr.test every 3  seconds

SQL>BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."JOB1"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into hr.test values (sysdate);
end;',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=3',
start_date => systimestamp at time zone 'Asia/Calcutta',
job_class => '"JC1"',
auto_drop => FALSE,
enabled => TRUE);
END;
/

– Check that  job is running i.e. rows are getting inserted  in table hr.test 

SQL> select to_char(dt, 'dd-mon-yyyy hh24:mi:ss') from hr.test;

TO_CHAR(DT,'DD-MON-YYYYHH24:M
-----------------------------
11-jan-2014 04:12:57
11-jan-2014 04:12:47
11-jan-2014 04:12:49

...

11-jan-2014 04:13:27
11-jan-2014 04:13:30
11-jan-2014 04:13:33

– Check that job is running  the on same node as the  service job_serv i.e. host01 

SQL> col job_name for a7
 col status for a15 
select JOB_NAME, status , INSTANCE_ID from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name = 'JOB1';
 JOB_NAM STATUS          INSTANCE_ID 
------- --------------- ----------- 
JOB1    SUCCEEDED                 1 
JOB1    SUCCEEDED                 1 
JOB1    SUCCEEDED                 1
..

– To simulate the crash of host01, kill the database instance on host01

[root@host01 ~]# ps -ef |grep pmon_
grid      5493     1  0 03:43 ?        00:00:00 asm_pmon_+ASM1
oracle    6755     1  0 03:47 ?        00:00:00 ora_pmon_orcl1
root     25051  6336  0 04:17 pts/1    00:00:00 grep pmon_
[root@host01 ~]# kill -9 6755

– Check that job has failed over to host02

[oracle@host01 ~]$ srvctl status service -d orcl
Service job_srv is not running.
[oracle@host01 ~]$ srvctl status service -d orcl
Service job_srv is running on instance(s) orcl2

select JOB_NAME, status , INSTANCE_ID from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name = 'JOB1' ;

JOB_NAM STATUS          INSTANCE_ID
------- --------------- -----------
JOB1    SUCCEEDED                 1
JOB1    SUCCEEDED                 1
JOB1    SUCCEEDED                 1
...

JOB1    SUCCEEDED                 2
JOB1    SUCCEEDED                 2
JOB1    SUCCEEDED                 2

– Check that the job is still running i.e. records are still getting inserted in the table hr.test

SQL> select to_char(dt, 'dd-mon-yyyy hh24:mi:ss') from hr.test;

TO_CHAR(DT,'DD-MON-YYYYHH24:M
-----------------------------
11-jan-2014 04:12:57
11-jan-2014 04:12:47
11-jan-2014 04:12:49

...

11-jan-2014 04:15:27
11-jan-2014 04:15:30
11-jan-2014 04:15:33

Home
11G R2 RAC Index

11g R2 RAC : Services

—————————-