TAF (Transparent Application Failover) for Data Guard

TAF (Transparent Application Failover) for Data Guard:

Below implementation will be continued after DG broker configuration.

To know DG broker configuration, go to below link:

http://oracledbahub.com/2013/02/dg-broker-configuration/

 

–In my environment

Primary: Cobra

Standby: Cobradup

And I want that once switchover or failover occurs application automatically will go to newly up primary.So, to achieve this we need to apply TAF by making some steps on Clinet and Server both.

Step1:   On Client (if using tnsnames.ora)

Add following connection string in tnsnames.ora file with SERVICE_NAME.

cobra.com =

(DESCRIPTION =

   (ADDRESS_LIST =

      (LOAD_BALANCE = YES)

         (ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.2.211)(PORT = 1521))

         (ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.2.212)(PORT = 1521))

       )

   (CONNECT_DATA =

      (SERVICE_NAME = cobra.com)

      (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC)(RETRIES = 20)(DELAY = 15))

   )

)

 

NOTE: We need to take care that PMON service for both databases should talk to Local Listeners (which should be added in initialization parameter as “LOCAL_LISTENER”) on both servers.

Step2: On Server Side

On Primary and Standby both: Check active services using below command:

On Primary(Cobra):

SQL> select NAME, BLOCKED from gv$active_services;

NAME                                                             BLO

—————————————————————- —

cobraXDB                                                         NO

cobra                                                            NO

SYS$BACKGROUND                                                   NO

SYS$USERS                                                        NO

 

On Standby(Cobradup):

SQL> select NAME,BLOCKED from gv$active_services;

NAME                                                             BLO

—————————————————————- —

cobradupXDB                                                      NO

cobradup                                                         NO

SYS$BACKGROUND                                                   NO

SYS$USERS                                                        NO

 

–Perform below on Primary

–Create service which will be used on client side for switchover and failover:

exec dbms_service.create_service( –

service_name => 'cobra.com', –

network_name => 'cobra.com', –

aq_ha_notifications => true, –

failover_method => 'BASIC', –

failover_retries => 180, –

failover_delay => 1);

/

 

— Create the role_change trigger

CREATE OR REPLACE TRIGGER set_rc_svc AFTER DB_ROLE_CHANGE ON DATABASE DECLARE

role VARCHAR(30);

BEGIN

SELECT DATABASE_ROLE INTO role FROM V$DATABASE;

IF role = 'PRIMARY' THEN

DBMS_SERVICE.START_SERVICE('cobra.com');

ELSE

DBMS_SERVICE.STOP_SERVICE('cobra.com');

END IF;

END;

/

— Create the database startup trigger

CREATE OR REPLACE TRIGGER "SYS"."DBSTARTSRVC" AFTER

STARTUP ON DATABASE declare

role VARCHAR(30);

BEGIN

SELECT DATABASE_ROLE INTO role FROM V$DATABASE;

IF role = 'PRIMARY' THEN

DBMS_SERVICE.START_SERVICE('cobra.com');

ELSE

DBMS_SERVICE.STOP_SERVICE('cobra.com');

END IF;

END;

/

Step3: On Server Side (now start and crosscheck the services)

–Start the created service (cobra.com)

exec dbms_service.START_SERVICE('cobra.com');

— Now check the service through “gv$active_services” dictionary views as below:

 

On Primary(Cobra):

SQL> select NAME,BLOCKED from gv$active_services;

NAME                                                             BLO

—————————————————————- —

cobra.com                                                        NO

cobraXDB                                                         NO

cobra                                                            NO

SYS$BACKGROUND                                                   NO

SYS$USERS                                                        NO

 

On Standby(Cobradup):

SQL> select NAME,BLOCKED from gv$active_services;

NAME                                                             BLO

—————————————————————- —

cobradupXDB                                                      NO

cobradup                                                         NO

SYS$BACKGROUND                                                   NO

SYS$USERS                                                        NO

 

NOTE: Now cobra.com service will be seen only on Primary side as we can see above.

           And after switchover cobra.com service will be seen on new Primary cobradup.

          We can see cobra.com service in lsnrctl status.

 

–After switchover cobra.com service will be on new primary cobradup as below:

On Primary(Cobradup):

SQL>  select NAME,BLOCKED from gv$active_services;

NAME                                                             BLO

—————————————————————- —

cobra.com                                                        NO

cobradupXDB                                                      NO

cobradup                                                         NO

SYS$BACKGROUND                                                   NO

SYS$USERS                                                        NO

 

On Standby(Cobra):

SQL> select NAME,BLOCKED from gv$active_services;

NAME                                                             BLO

—————————————————————- —

cobraXDB                                                         NO

cobra                                                            NO

SYS$BACKGROUND                                                   NO

SYS$USERS                                                        NO

 

–Use the following dictionary views to monitor services:

dba_services – All defined services

gv$active_services – All active (started) services

–can also check service:

SQL> show parameter service

–To see what service a session is connected to:

SELECT username, program, machine, service_name FROM gv$session;

–To start service:

SQL> exec dbms_service.START_SERVICE('cobra.com')

–TO stop service:

SQL> exec dbms_service.STOP_SERVICE('cobra.com')

–To delete service:

SQL> exec dbms_service.DELETE_SERVICE('cobra.com')

 

Request you all to put your valuable comments on  posts of  this blog, will motivate me to write more good post.smiley

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply