by In this blog post, I will talk about how you can perform workload routing across the standby databases based on Database role with GDS. GDS provides this functionality without requiring Oracle Clusterware to be installed. But if clusterware is installed then GDS automatically register the global service to GI home. When role transition happens in Data Guard Broker configuration, GDS relocates the global services automatically as per the role attribute. There is no need to write custom logic or database startup triggers to achieve this behavior.
Prerequisites
In order to play with the GDS with active data guard, you must have the setup ready and functioning. Read more about GDS set up Here. And read here for Data Guard environment with dg broker.
My data guard and GDS environment set up is as below.
|
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 |
# Data Guard DGMGRL> show configuration Configuration - DGTST_HA Protection Mode: MaxAvailability Members: DGTST_PRIMY - Primary database DGTST_STBY - (*) Physical standby database Fast-Start Failover: ENABLED Threshold: 30 seconds Target: DGTST_STBY Observer: DGTST03 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: SUCCESS # GDS GSM:/oracle:>gdsctl GDSCTL: Version 12.1.0.2.0 - Production on Mon Nov 27 12:48:40 NZDT 2017 Copyright (c) 2011, 2014, Oracle. All rights reserved. Welcome to GDSCTL, type "help" for information. Current GSM is set to GSM1 GDSCTL>config gsm Name Region ENDPOINT ---- ------ -------- gsm1 APAC (ADDRESS=(HOST=gsm01)) gsm2 APAC (ADDRESS=(HOST=gsm02)) |
Add GDS Pool:
Now add the GDS pool.
|
1 2 |
GDSCTL>add gdspool -gdspool DGTST_pool Catalog connection is established |
Add TNS Entry for Primary/standby database at GSM Home:
We need to add below net services related with DG environment to GDS catalog server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DGTST_PRIMY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DGTST01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTST) ) ) DGTST_STBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DGTST02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTST) ) ) |
Add DG Broker configuration in GDS pool
Add the databases to GSM environment.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# Unlock and Reset the GSMUSER password AT CDB DB level. CDB$ROOT@DGTST> alter user gsmuser identified by gsmuser account unlock; User altered. # AT GSM level GDSCTL>add brokerconfig -connect DGTST_PRIMY -region APAC -gdspool DGTST_pool -pwd gsmuser Catalog connection is established DB Unique Name: DGTST_PRIMY GDSCTL>config database Catalog connection is established Name Pool Status Region ---- ---- ------ ------ dgtst_stby dgtst_pool Ok APAC dgtst_primy dgtst_pool Ok APAC |
Add GDS service:
Now add the services to GDS for data guard environment. After adding services to the GDS pool, they will not be started until we start manually.After starting the services , we will check the configuration of each service in detail which explains services was created under which GSM, connection balance details, preferred instances and much more.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# Add HA servive at PDB level GDSCTL>add service -service DGTST_HA -gdspool DGTST_pool -preferred_all -failovermethod BASIC -failovertype SELECT -notification TRUE -role PRIMARY -pdbname DGTSTPDB # Start the Service GDSCTL>start service -service DGTST_HA -gdspool DGTST_pool # Check Services GDSCTL>services Service "dgtst_ha.dgtst_pool.apac_cloud" has 1 instance(s). Affinity: ANYWHERE Instance "dgtst_pool%401", name: "DGTST", db: "DGTST_PRIMY", region: "apac_region", status: ready. # Check service at Database PDB Level DGTSTPDB@DGTST> select name from v$active_services; NAME ---------------------------------------------------------------- dgtst_ha # If using GI, then this service should be automatically register. +ASM:/oracle/product/grid12.2/bin:>./crsctl status resource -t -------------------------------------------------------------------------------- Name Target State Server State details Local Resources ------------------------------------------------------------------------------- ora.dgtst_primy.dgtst_ha.svc 1 ONLINE ONLINE DGTST01 STABLE |
Client connection details for application
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
#TNS Entry DGTST_HA= (DESCRIPTION_LIST= (LOAD_BALANCE=off) (FAILOVER=on) (DESCRIPTION = (CONNECT_TIMEOUT=10)(RETRY_COUNT=3) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=gsm01)(PORT=1522)) (ADDRESS=(PROTOCOL=tcp)(HOST=gsm02)(PORT=1522))) (CONNECT_DATA=(SERVICE_NAME = dgtst_ha.dgtst_pool.apac_cloud)) ) ) #JDBC jdbc:oracle:thin:@(DESCRIPTION_LIST=(FAILOVER=on)(DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=gsm01)(PORT=1522))(ADDRESS=(PROTOCOL=tcp)(HOST=gsm02)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME = dgtst_ha.dgtst_pool.apac_cloud))) |
Testing:
For maintenance work, sometimes we need to switch the database role to DR site. So I explained both switchover/failover.
|
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 |
# DG broker configuration DGMGRL> show configuration Configuration - DGTST_HA Protection Mode: MaxAvailability Members: DGTST_PRIMY - Primary database DGTST_STBY - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 11 seconds ago) #Switchover DGMGRL> switchover to 'DGTST_STBY' Performing switchover NOW, please wait... Operation requires a connection to instance "DGTST" on database "DGTST_STBY" Connecting to instance "DGTST"... Connected as SYSDBA. New primary database "DGTST_STBY" is opening... Oracle Clusterware is restarting database "DGTST_PRIMY" ... Switchover succeeded, new primary is "DGTST_STBY" # check database for role change and service switchover DGTSTPDB@DGTST> select name, DB_UNIQUE_NAME,database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------------------------------------------------------- DGTST DGTST_STBY PRIMARY DGTSTPDB@DGTST> select name from v$active_Services; NAME ---------------------------------------------------------------- dgtst_ha |
Failover
To test the failover with GDS, please test shutdown abort Primary database. Before/After shutdown, please check the connection from GSM and database. Fast start failover is enabled.
We can test the connectivity using “DGTST_HA” net service which should connect to the primary database, even though we connect from GSM server and even after db role change after failover.
|
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 32 33 34 35 36 37 38 39 |
DGTSTPDB@DGTST> select name, DB_UNIQUE_NAME,database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------------------------------------------------------- DGTST DGTST_STBY PRIMARY DGTSTPDB@DGTST> select username, sid from v$session where username like 'SYSTEM'; USERNAME SID ------------------------------ ---------- SYSTEM 269 # connect to CDB CDB$ROOT@DGTST> shutdown abort ORACLE instance shut down. # DG broker DGMGRL> show configuration Configuration - DGTST_HA Protection Mode: MaxAvailability Members: DGTST_PRIMY - Primary database DGTST_STBY - (*) Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 121 seconds ago) # Connect to new primary db DGTSTPDB@DGTST> select name, DB_UNIQUE_NAME,database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------------------------------------------------------- DGTST DGTST_PRIMY PRIMARY DGTSTPDB@DGTST> select name from v$active_Services; NAME ---------------------------------------------------------------- dgtst_ha DGTSTPDB@DGTST> select username, sid from v$session where username like 'SYSTEM'; USERNAME SID ------------------------------ ---------- SYSTEM 189 # GSM GDSCTL>services Service "dgtst_ha.dgtst_pool.apac_cloud" has 1 instance(s). Affinity: ANYWHERE Instance "dgtst_pool%411", name: "DGTST", db: "DGTST_PRIMY", region: "apac_region", status: ready |
Conclusion:
We’ve seen the overview of the Global data services(services) in dataguard environment. Services can be added on standby database service for read only queries.
Hi, Thanks for the post. Do you know the client JDK and jdbc requirments for 19c GDS?
Hi Alexandre, Sorry I didn’t get chance to look at latest version of GDS. Will have a look in coming month or so if you still need help.