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.

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 ~]$
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
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 ~]$
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>
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)

)

)
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>
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
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>
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 ~]$
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 gjilevski | oracle

No comments yet.

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

Guenadi Jilevski is Oracle OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and Oracle OCM9i , OCM 11g, Teradata Certified DBA, Informix Certified DBA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.

Archives
Archives
Blog Stats
- 1,135,013 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

March 2016

M T W T F S S

« Dec May »

1 2 3 4 5 6

7 8 9 10 11 12 13

14 15 16 17 18 19 20

21 22 23 24 25 26 27

28 29 30 31
Search

Search for:
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,113 other followers
OraNA

Guenadi N Jilevski's Oracle BLOG

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