Configure TAF (Transparent Application Failover) in Oracle Databae 12c R1

1. Oracle RAC Installation and Configuration

Refer to Oracle Base for details of Oracle RAC 12c Installation and Configuration.

2. Configuration Environment DB Version

SQL> SET LINESIZE 250

COLUMN DESCRIPTION FORMAT A60

SELECT patch_id, version, flags, action, status, description

  FROM dba_registry_sqlpatch

 ORDER BY patch_id;

SQL> SQL>   2    3

PATCH_ID VERSION    FLAGS  ACTION   STATUS   DESCRIPTION

--------- ---------- ------ -------- -------- ----------------------------------------------------

19769480 12.1.0.2   NB     APPLY    SUCCESS  Database Patch Set Update : 12.1.0.2.2 (19769480)

19877336 12.1.0.2   NJJ    APPLY    SUCCESS  Database PSU 12.1.0.2.2, Oracle JavaVM Component (

SQL>

3. Configure auto start in PDB level

PDBs will start autmatically on CDB restart if PDB level auto start is configured.

--Specify instance name while opening PDBs.
ALTER pluggable DATABASE TAF_PDB OPEN instances=('ORCL1','ORCL2'); 

--Open PDB in all the available instances
ALTER pluggable DATABASE TAF_PDB OPEN instances=ALL;

4. Save the state of PDBs

--Specify instance name while saving PDBs state.

ALTER pluggable DATABASE TAF_PDB SAVE state instances=('ORCL1','ORCL2');

--Save PDBs state in all the available instances

ALTER pluggable DATABASE TAF_PDB SAVE state instances=ALL;

--Specify instance name while discarding PDBs state.

ALTER PLUGGABLE DATABASE TAF_PDB DISCARD STATE instances=ALL;

--Discard PDBs state in all the available instances

ALTER PLUGGABLE DATABASE TAF_PDB DISCARD STATE instances = ('ORCL1','ORCL2');

5. Add a service as oracle user

srvctl add service -d ORCL -pdb TAF_PDB -s taf_pdb_svc -r ORCL1 -a ORCL2 -P BASIC -y AUTOMATIC -q TRUE -j LONG -z 180 -w 5 -e SELECT -m BASIC

6. Start the service

srvctl start service -d ORCL -s taf_pdb_svc

7. Check the status of the service

[oracle@cornswjagp001 ~]$ srvctl config service -d ORCL

Service name: taf_pdb_svc

Server pool:

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: MANUAL

DTP transaction: false

AQ HA notifications: true

Global: false

Commit Outcome: false

Failover type: SELECT

Failover method: BASIC

TAF failover retries: 180

TAF failover delay: 5

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Edition:

Pluggable database name: TAF_PDB

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

GSM Flags: 0

Service is enabled

Preferred instances: ORCL1

Available instances: ORCL2

[oracle@cornswjagp001 ~]$

8. Check the service values in dba_services

Login to CDB and check the status of service created above.

[oracle@cornswjagp001 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 2 14:17:04 2015

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL>

Session altered.

SQL> column name format a30

select name, service_id from dba_services where name = 'taf_pdb_svc';

SQL>

NAME                         SERVICE_ID

---------------------------- ----------

taf_pdb_svc                           2

SQL>

SQL> col name format a15

SQL> col failover_method format a11 heading 'METHOD'

SQL> col failover_type format a10 heading 'TYPE'

SQL> col failover_retries format 9999999 heading 'RETRIES'

SQL> col goal format a10

SQL> col clb_goal format a8

SQL> col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'

SQL> SQL> SELECT name, failover_method, failover_type, failover_retries,goal, clb_goal, aq_ha_notifications

2    FROM dba_services

3   WHERE service_id = 2;

      

NAME         METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT

------------ ----------- ---------- -------- ---------- -------- -----

taf_pdb_svc  BASIC       SELECT          180 NONE       LONG     YES

      

SQL>


9. Create TNS entry in tnsnames.ora file

TAFPDB_SVC =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ractest-scan)(PORT = 1521))

    (LOAD_BALANCE = yes)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = taf_pdb_svc)

      (FAILOVER_MODE =

        (TYPE = SELECT)

        (METHOD = BASIC)

        (RETRIES = 180)

        (DELAY = 5)

      )

    )

  )

In case of RAC environments TNS entry has to be created in all the nodes participating in RAC environment.

10. Test TAF (Transparent Application Failover)

Connect to the database and check the instance of connected session.

[oracle@cornswjagp001 ~]$ sqlplus system@taf_pdb_svc

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 2 11:34:52 2015

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

Enter password:

Last Successful login time: Fri Oct 02 2015 11:23:41 +10:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

ORCL1

11. Find pid of pmon of connected instance

The PID of ORCL1 instance is 569 in host racsrv001.

[oracle@racsrv001 admin]$ ps -ef|grep pmon

oracle     569     1  0 Sep24 ?        00:02:08 ora_pmon_ORCL1

oracle    4706 16879  0 11:35 pts/3    00:00:00 grep pmon

grid     17236     1  0 10:47 ?        00:00:00 mdb_pmon_-MGMTDB

grid     32326     1  0 Sep24 ?        00:01:36 asm_pmon_+ASM1

[oracle@racsrv001 admin]$

Remember the pid could be different in different systems. It does not remain the same.

12. Kill the process of pmon

Kill the pmon process in racsrv001 for instance ORCL1.

[oracle@racsrv001 admin]$ kill -9 569

13. Check the instance name again in the previous connection.

SQL> select instance_name from v$instance;

     INSTANCE_NAME

     ----------------

     ORCL2

SQL>

The connection automatically failed over to second surviving node. Testing successful.

14. The pmon process starts again

After some time the pmon process of the killed database instance starts automatically

[oracle@cornswjagp001 admin]$ ps -ef|grep pmon

oracle    4842     1  0 11:35 ?        00:00:00 ora_pmon_ORCL1

oracle    5363 16879  0 11:36 pts/3    00:00:00 grep pmon

grid     17236     1  0 10:47 ?        00:00:00 mdb_pmon_-MGMTDB

grid     32326     1  0 Sep24 ?        00:01:36 asm_pmon_+ASM1

[oracle@cornswjagp001 admin]$

15.  Trick in DB 12c for TAF

The trick in Oracle Database 12c is that if you issue SHUTDOWN ABORT command in SQL*plus CRS detects through the agent that it was the user that issued the SHUTDOWN ABORT command, so the state changes to PLANNED_OFFLINE and the label to "Instance Shutdown, Stable". If it was detected as a real crash, it would say OFFLINE and "Abnormal Termination,Cleaning" respectively.

If the user wants to test the failover in a real case they should set the database policy to NORESTART and kill the instance's PMON:

$ srvctl modify database -d ORCL -policy NORESTART

accept the prompt and then kill PMON

If you set the database policy to NORESTART the pmon process will not restart automatically. For the pmon to restart automatically you have to set database policy back to AUTOMATIC.

$ srvctl modify database -d ORCL -policy AUTOMATIC

Point no 15 was copied and edited from service request raised from Oracle Support.

Hope this helps.