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.
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply