Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Session failover using Data Guard 11gR2

Session failover using Data Guard 11gR2

In the article we will see the session failover using an Oracle 11gR2 Data Guard. The feature is available since Oracle 11gr2 when the services become a CRS resource. The software used is Oracle RDBMS 11gR2. There are two nodes zeus running the PRA primary database and a STBY1 standby database and apollo running the STBY” standby instance. We will demonstrate a connection failover of a connection to a service on the primary while transitioning the STBY2 standby database open as an ADG as a new primary.

  1. Verify services and check available services in the listener. Note that initially the services are the db_unique_names.

    On Apollo we have:

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     STBY2

    SQL>

    [grid@apollo ~]$ lsnrctl services

    LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 28-JUL-2013 20:19:21

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

    Services Summary…

    Service “+ASM” has 1 instance(s).

    Instance “+ASM”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “STBY2” has 2 instance(s).

    Instance “STBY2”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    Instance “STBY2”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “STBY2_DGB” has 1 instance(s).

    Instance “STBY2”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “STBY2_DGMGRL” has 1 instance(s).

    Instance “STBY2”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    The command completed successfully

    [grid@apollo ~]$

    On zeus we have:

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     PRA

    SQL>

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     STBY1

    SQL>

    [grid@zeus ~]$ lsnrctl services

    LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 28-JUL-2013 20:32:21

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

    Services Summary…

    Service “+ASM” has 1 instance(s).

    Instance “+ASM”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “PRA” has 2 instance(s).

    Instance “PRA”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:6 refused:0

    LOCAL SERVER

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:7 refused:0 state:ready

    LOCAL SERVER

    Service “PRAXDB” has 1 instance(s).

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: zeus.gj.com, pid: 5173>

    (ADDRESS=(PROTOCOL=tcp)(HOST=zeus.gj.com)(PORT=26319))

    Service “PRA_DGB” has 1 instance(s).

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:7 refused:0 state:ready

    LOCAL SERVER

    Service “PRA_DGMGRL” has 1 instance(s).

    Instance “PRA”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    Service “RMAN” has 1 instance(s).

    Instance “RMAN”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “RMANXDB” has 1 instance(s).

    Instance “RMAN”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: zeus.gj.com, pid: 5203>

    (ADDRESS=(PROTOCOL=tcp)(HOST=zeus.gj.com)(PORT=28379))

    Service “STBY1” has 2 instance(s).

    Instance “STBY1”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:1 refused:0

    LOCAL SERVER

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:6 refused:0 state:ready

    LOCAL SERVER

    Service “STBY1XDB” has 1 instance(s).

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: zeus.gj.com, pid: 5184>

    (ADDRESS=(PROTOCOL=tcp)(HOST=zeus.gj.com)(PORT=41719))

    Service “STBY1_DGB” has 1 instance(s).

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:6 refused:0 state:ready

    LOCAL SERVER

    Service “STBY1_DGMGRL” has 1 instance(s).

    Instance “STBY1”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    The command completed successfully

    [grid@zeus ~]$

  2. Create services. Open the STBY2 is read only apply. Create services PROD.

    On zeus

    srvctl add service -d PRA -s PROD -l primary -y AUTOMATIC -m basic -e select -w 1 -z 250

    srvctl add service -d STBY1 -s PROD -l primary -y AUTOMATIC -m basic -e select -w 1 -z 250

    on apollo

    srvctl add service -d STBY2 -s PROD -l primary -y AUTOMATIC -m basic -e select -w 1 -z 250

  3. Start the services

    On zeus.

    srvctl start service -d pra -s prod

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     PROD

    SQL>

    Verify the services registration.

    [grid@zeus ~]$ lsnrctl services | grep PROD

    Service “PROD” has 2 instance(s).

    [grid@zeus ~]$ lsnrctl services

    LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 28-JUL-2013 20:56:22

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

    Services Summary…

    Service “+ASM” has 1 instance(s).

    Instance “+ASM”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “PRA” has 2 instance(s).

    Instance “PRA”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:6 refused:0

    LOCAL SERVER

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “PRAXDB” has 1 instance(s).

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: zeus.gj.com, pid: 5173>

    (ADDRESS=(PROTOCOL=tcp)(HOST=zeus.gj.com)(PORT=26319))

    Service “PRA_DGB” has 1 instance(s).

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “PRA_DGMGRL” has 1 instance(s).

    Instance “PRA”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    Service “PROD” has 2 instance(s).

    Instance “PRA”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “RMAN” has 1 instance(s).

    Instance “RMAN”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “RMANXDB” has 1 instance(s).

    Instance “RMAN”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: zeus.gj.com, pid: 5203>

    (ADDRESS=(PROTOCOL=tcp)(HOST=zeus.gj.com)(PORT=28379))

    Service “STBY1” has 2 instance(s).

    Instance “STBY1”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:1 refused:0

    LOCAL SERVER

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “STBY1XDB” has 1 instance(s).

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: zeus.gj.com, pid: 5184>

    (ADDRESS=(PROTOCOL=tcp)(HOST=zeus.gj.com)(PORT=41719))

    Service “STBY1_DGB” has 1 instance(s).

    Instance “STBY1”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “STBY1_DGMGRL” has 1 instance(s).

    Instance “STBY1”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    The command completed successfully

    [grid@zeus ~]$

  4. Verify the services running on the sites where the databases qre up and running are configured

    On zeus PRA database we have:

    srvctl config service -d pra -s prod

    Service name: PROD

    Service is enabled

    Cardinality: SINGLETON

    Disconnect: false

    Service role: PRIMARY

    Management policy: AUTOMATIC

    DTP transaction: false

    AQ HA notifications: false

    Failover type: SELECT

    Failover method: BASIC

    TAF failover retries: 250

    TAF failover delay: 1

    Connection Load Balancing Goal: LONG

    Runtime Load Balancing Goal: NONE

    TAF policy specification: NONE

    Edition:

    On Apollo STBY2 we have. Note PROD service is not started.

    srvctl config service -d stby2 -s prod

    Service name: PROD

    Service is enabled

    Cardinality: SINGLETON

    Disconnect: false

    Service role: PRIMARY

    Management policy: AUTOMATIC

    DTP transaction: false

    AQ HA notifications: false

    Failover type: SELECT

    Failover method: BASIC

    TAF failover retries: 250

    TAF failover delay: 1

    Connection Load Balancing Goal: LONG

    Runtime Load Balancing Goal: NONE

    TAF policy specification: NONE

    Edition:

    SQL>

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     STBY2

    SQL>

    [grid@apollo ~]$ lsnrctl services | grep PROD

    [grid@apollo ~]$

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     STBY2

    SQL> select open_mode from v$database;

    OPEN_MODE

    ——————–

    MOUNTED

    SQL> select database_role from v$database;

    DATABASE_ROLE

    —————-

    PHYSICAL STANDBY

    SQL>

    On zeus STBY1 wee have

    srvctl config service -d stby1 -s prod

    Service name: PROD

    Service is enabled

    Cardinality: SINGLETON

    Disconnect: false

    Service role: PRIMARY

    Management policy: AUTOMATIC

    DTP transaction: false

    AQ HA notifications: false

    Failover type: SELECT

    Failover method: BASIC

    TAF failover retries: 250

    TAF failover delay: 1

    Connection Load Balancing Goal: LONG

    Runtime Load Balancing Goal: NONE

    TAF policy specification: NONE

    Edition:

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     PROD

    SQL>

    SQL> select open_mode from v$database;

    OPEN_MODE

    ——————–

    READ ONLY WITH APPLY

    SQL> select database_role from v$database;

    DATABASE_ROLE

    —————-

    PHYSICAL STANDBY

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     PROD

    SQL>

  5. Prepare a network tnsnames.ora alias

    PROD =

    (DESCRIPTION =

    (ADDRESS_LIST =

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

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

    )

    (CONNECT_DATA =

    (SERVICE_NAME = prod)

    )

    )

  6. Create a user on the primary database PRA

    SQL> create user dgtest identified by dgtest;

    User created.

    SQL> grant create session to dgtest;

    Grant succeeded.

    SQL>

    SQL> grant select_catalog_role to dgtest;

    Grant succeeded.

    SQL>

  7. Connect to the primary and check the failover attributes.

    SQL> connect dgtest/dgtest@prod

    Connected.

    SQL> select instance_name from v$instance;

    INSTANCE_NAME

    —————-

    PRA

    SQL>

    SQL> select failover_method, failover_type, failed_over from v$session where username=’DGTEST’;

    FAILOVER_M FAILOVER_TYPE FAI

    ———- ————- —

    BASIC     SELECT     NO

  8. Perform a database switchover to STBY2 and monitor the session failover

    DGMGRL> connect sys/sys1

    Connected.

    DGMGRL> switchover to stby2

    Performing switchover NOW, please wait…

    New primary database “stby2” is opening…

    Operation requires shutdown of instance “PRA” on database “pra”

    Shutting down instance “PRA”…

    ORACLE instance shut down.

    Operation requires startup of instance “PRA” on database “pra”

    Starting instance “PRA”…

    ORACLE instance started.

    Database mounted.

    Switchover succeeded, new primary is “stby2”

    DGMGRL>

  9. Verify the session running on the new primary.

    On Apollo we have:

    SQL> select failover_method, failover_type, failed_over from v$session where username=’DGTEST’

    2 ;

    FAILOVER_M FAILOVER_TYPE FAI

    ———- ————- —

    BASIC     SELECT     YES

    SQL> select instance_name from v$instance;

    INSTANCE_NAME

    —————-

    STBY2

    SQL>

    SQL> select open_mode from v$database;

    OPEN_MODE

    ——————–

    READ WRITE

    SQL> select database_role from v$database;

    DATABASE_ROLE

    —————-

    PRIMARY

    SQL> select instance_name from v$instance;

    INSTANCE_NAME

    —————-

    STBY2

    SQL>

    SQL> show parameter service

    NAME                 TYPE     VALUE

    ———————————— ———– ——————————

    service_names             string     PROD

    SQL>

    [grid@apollo ~]$ lsnrctl services | grep PROD

    Service “PROD” has 1 instance(s).

    [grid@apollo ~]$ lsnrctl serices

    LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 28-JUL-2013 21:43:20

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    NL-00853: undefined command “serices”. Try “help”

    [grid@apollo ~]$ lsnrctl service

    LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 28-JUL-2013 21:43:29

    Copyright (c) 1991, 2011, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

    Services Summary…

    Service “+ASM” has 1 instance(s).

    Instance “+ASM”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0 state:ready

    LOCAL SERVER

    Service “PROD” has 1 instance(s).

    Instance “STBY2”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:1 refused:0 state:ready

    LOCAL SERVER

    Service “STBY2” has 2 instance(s).

    Instance “STBY2”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:94 refused:0

    LOCAL SERVER

    Instance “STBY2”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:1 refused:0 state:ready

    LOCAL SERVER

    Service “STBY2XDB” has 1 instance(s).

    Instance “STBY2”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “D000” established:0 refused:0 current:0 max:1022 state:ready

    DISPATCHER <machine: apollo.gj.com, pid: 5725>

    (ADDRESS=(PROTOCOL=tcp)(HOST=apollo.gj.com)(PORT=10107))

    Service “STBY2_DGB” has 1 instance(s).

    Instance “STBY2”, status READY, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:1 refused:0 state:ready

    LOCAL SERVER

    Service “STBY2_DGMGRL” has 1 instance(s).

    Instance “STBY2”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

    “DEDICATED” established:0 refused:0

    LOCAL SERVER

    The command completed successfully

    [grid@apollo ~]$

    On zeus we have:

    [grid@zeus ~]$ lsnrctl services | grep PROD

    [grid@zeus ~]$

  10. Switchover back to PRA and check the services

    DGMGRL> switchover to pra

    Performing switchover NOW, please wait…

    New primary database “pra” is opening…

    Operation requires shutdown of instance “STBY2” on database “stby2”

    Shutting down instance “STBY2″…

    ORACLE instance shut down.

    Operation requires startup of instance “STBY2” on database “stby2”

    Starting instance “STBY2″…

    ORACLE instance started.

    Database mounted.

    Switchover succeeded, new primary is “pra”

    DGMGRL>

    [grid@zeus ~]$ lsnrctl services | grep PROD

    Service “PROD” has 1 instance(s).

    [grid@zeus ~]$

    [grid@apollo ~]$ lsnrctl services | grep PROD

    [grid@apollo ~]$

Summary:

Oracle 11gR2 enable us to implement a session failover while performing a DG switchover.

March 2, 2016 - Posted by | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: