Sunday, October 11, 2015

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.

No comments:

Post a Comment