Managing Global Data Services and Failover Test – GDS 12c

This article explains how to create service, managing services and close view of service failover to the preferred databases and various other options related to services, The GDS introduction and the installation part we have already completed and this will be the 3^rd series related to GMS/GDS -12c.

Prerequisites

In order to play with the services, we must have the setup ready and functioning, they are

1. Catalog creation
2. Adding GSM
3. Start GSM
4. Creating Regions
5. Creating Pools
6. Adding regions to GSM
7. Adding Broker configuration
8. Assigning pools to the primary, standby databases or Golden Gate replicated databases.

If we have the above all, then we are ready to create services to the specific pools.

Creating Services

We’ve already seen the creating service part but again adding few steps, so that we will not miss anything related to the services management.  Here we will have two services, one service runs only in production database and the other service (read only) runs on the standby database and the service can failover to the primary database. Before that ensure we have connected to the GSM and the connection established to catalog.

1)      Read Write service to use for Production

GREP net service connects to the created catalog database and we will set GSM environment to created GSM i.e. SOUTHGSM

GDSCTL>connect gsmadm/oracle@grep
Catalog connection is established
GDSCTL>set gsm -gsm southgsm
GDSCTL>add service -gdspool psfin -service cobol_process -preferred uk
GDSCTL>
2) Adding Read-Only service to run on standby and it can failover to the primary database if the standby is unreachable.
GDSCTL>add service -service psfin_nvision -gdspool psfin -preferred_all -role PHYSICAL_STANDBY -FAILOVER_PRIMARY
GDSCTL>

Starting the services and overview of the services

After adding services to the GDS pool, they will not be started until we start manually, So we will start the both created services and we will check the complete status of each service.

GDSCTL>start service -service psfin_nvision -gdspool psfin
GDSCTL>start service -service cobol_process -gdspool psfin
GDSCTL>

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.

GDSCTL>config service -service psfin_nvision
Name: psfin_nvision
Network name: psfin_nvision.psfin.oradbcloud
Pool: psfin
Started: Yes
Preferred all: Yes
Locality: ANYWHERE
Region Failover: No
Role: PHYSICAL_STANDBY
Primary Failover: Yes
Lag: ANY
Runtime Balance: SERVICE_TIME
Connection Balance: LONG
Notification: Yes
TAF Policy: NONE
Policy: AUTOMATIC
DTP: No
Failover Method: NONE
Failover Type: NONE
Failover Retries:
Failover Delay:
Edition:
PDB:
Commit Outcome:
Retention Timeout:
Replay Initiation Timeout:
Session State Consistency:
SQL Translation Profile:
Databases
————————
Database Preferred Status
——– ——— ——
uk Yes Enabled
india Yes Enabled
GDSCTL>
GDSCTL>services
Service "cobol_process.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%1", name: "ORC1", db: "UK", region: "europe", status: ready.
Service "psfin_nvision.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%11", name: "ORC1", db: "INDIA", region: "apac", status: ready.
GDSCTL>databases
Database: "uk" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: europe
Service: "cobol_process" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Service: "psfin_nvision" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Registered instances:
psfin%1
Database: "india" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: apac
Service: "psfin_nvision" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Registered instances:
psfin%11

GDSCTL>

Now both the services were started and enabled on the preferred databases. We can check these details from the database level using the DBA_SERVICES

SQL> select name,global_service from dba_services;

NAME GLO
——————- —
SYS$BACKGROUND NO
SYS$USERS NO
UK NO
UK_DGB NO
ORC1XDB NO
ORC1 NO
INDIA_DGB NO
INDIA NO
HR NO
NVISION NO
HR NO
psfin_nvision YES
cobol_process YES

13 rows selected.

[oracle@ORA-C1 ~]$ lsnrctl status |grep cobol
Service "cobol_process.psfin.oradbcloud" has 1 instance(s).
[oracle@ORA-C1 ~]$
[oracle@ORA-C2 ~]$ lsnrctl status |grep nvision
Service "psfin_nvision.psfin.oradbcloud" has 1 instance(s).
[oracle@ORA-C2 ~]$

Prepare the TNS entries for the services to connect

After enabling the services, we can provide the service details to the Cobol process job holders or for the reporting (nvision) users, so that the database connectivity will be through with the GDService.  If we see below the Read only job NVISION service can be failover and that can be enabled again in service name and here we will not use any server IP or hostnames, we will specify ONLY GSM server, because service will be created by default in “GLOBAL”, service can be accessed from anywhere. Hence when we use the specific service name and Oracle/GDS knows that where the service connection should be established.

PSFIN_NVISION =
(DESCRIPTION =
(FAILOVER=ON)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA-C2.localdomain)(PORT = 1555))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = psfin_nvision.psfin.oradbcloud)
)
)

COBOL_PROCESS=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA-C2.localdomain)(PORT = 1555))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cobol_process.psfin.oradbcloud)
)
)

Here the GSM is on ORA-C2 hence the connectivity should point to GSM server and the port but not the database server. 

Connectivity Test

We can test the connectivity using the above TNS service which should connect to the primary database, even though we connect from GSM server.

[oracle@ORA-C2 admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/gsmhome_1
[oracle@ORA-C2 admin]$ tnsping cobol_process
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 18-APR-2016 06:08:31
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORA-C2.localdomain)(PORT = 1555))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cobol_process.psfin.oradbcloud)))
OK (10 msec)
[oracle@ORA-C2 admin]$ sqlplus sys@cobol_process as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 18 06:08:40 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select database_role,db_unique_name from v$database;
DATABASE_ROLE DB_UNIQUE_NAME
—————- ——————————
PRIMARY UK                       <--- Connected to desired database
SQL> set pages 100
SQL> col name for a20
SQL> select name,global_service from dba_services;

NAME GLO
——————– —
SYS$BACKGROUND NO
SYS$USERS NO
UK NO
UK_DGB NO
ORC1XDB NO
ORC1 NO
INDIA_DGB NO
INDIA NO
HR NO
NVISION NO
HR NO
psfin_nvision YES
cobol_process YES

13 rows selected.

SQL> col network_name for a40
SQL> select name,network_name,global from v$active_services;

NAME NETWORK_NAME GLO
——————– —————————————- —
cobol_process cobol_process.psfin.oradbcloud YES
HR CONHR NO
UK_DGB UK_DGB NO
ORC1XDB ORC1XDB NO
UK UK NO
SYS$BACKGROUND NO
SYS$USERS NO

7 rows selected.

Enabling and Disabling Services

If the service is no more in use or disabling for some time for any maintenance to avoid connections and we can enable again after the maintenance.

GDSCTL>disable service -gdspool psfin -service psfin_nvision -database india
GDSCTL>enable service -gdspool psfin -service psfin_nvision -database india
GDSCTL>
GDSCTL>status service -service psfin_nvision
Service "psfin_nvision.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%11", name: "ORC1", db: "india", region: "apac", status: ready.
GDSCTL>

Changes in services

If in case of any maintenance on server and we have to either disable if downtime applicable if not then we must relocate to run on another database, but in order to relocate the service to run from one database to another database, then ensure the service is configured the target database is under the preferred list.  Now we will change the runtime balance for the service and we can see below the changes after the modifying the service.

GDSCTL>modify service -gdspool psfin -service psfin_nvision -rlbgoal throughput
GDSCTL>
GDSCTL>config service -service psfin_nvision
Name: psfin_nvision
Network name: psfin_nvision.psfin.oradbcloud
Pool: psfin
Started: Yes
Preferred all: Yes
Locality: ANYWHERE
Region Failover: No
Role: PHYSICAL_STANDBY
Primary Failover: Yes
Lag: ANY
Runtime Balance: THROUGHPUT
Connection Balance: LONG
Notification: Yes
TAF Policy: NONE
Policy: AUTOMATIC
DTP: No
Failover Method: NONE
Failover Type: NONE
Failover Retries:
Failover Delay:
Edition:
PDB:
Commit Outcome:
Retention Timeout:
Replay Initiation Timeout:
Session State Consistency:
SQL Translation Profile:

Databases
————————
Database Preferred Status
——– ——— ——
uk Yes Enabled
india Yes Enabled

GDSCTL>

Service Failover Test

We have covered almost all tasks of managing services and now we will see how the services will be failover to the available nodes. In this example we will take the Read only service which runs on standby database and we will see how it relocates to the primary database after the standby database failure. As we said earlier in order to relocate service the primary database should be in preferred list of service configuration.

[oracle@ORA-C2 ~]$ ps -ef|grep pmon
oracle 4611 1 0 18:19 ? 00:00:02 ora_pmon_GREP
oracle 20553 1 0 Apr16 ? 00:00:09 ora_pmon_ORC1
oracle 27890 27847 0 22:59 pts/2 00:00:00 grep pmon
[oracle@ORA-C2 ~]$ kill -9 20553
[oracle@ORA-C2 ~]$
We have killed the mandatory background process of the database on standby server ORA-C2, Now we will see the latest status of the database and services
GDSCTL>databases
Database: "uk" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: europe
Service: "psfin_nvision" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
psfin%1
Database: "india" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: apac
Service: "psfin_nvision" Globally started: Y Started: N
Scan: Y Enabled: Y Preferred: Y
GDSCTL>services
Service "psfin_nvision.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%1", name: "ORC1", db: "uk", region: "europe", status: ready.
GDSCTL>

We can see above the service is now running on primary database UK and we will test manually as well.

[oracle@ORA-C2 ~]$ sqlplus sys/oracle@PSFIN_NVISION as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 17 23:00:33 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME DATABASE_ROLE
—————————— —————-
uk PRIMARY
SQL>

The service will run on the primary forever until the standby is up and running, now we will start the standby database and we can check whether the service is running or not.

SQL> startup mount
ORACLE instance started.

Total System Global Area 473956352 bytes
Fixed Size 2925744 bytes
Variable Size 197135184 bytes
Database Buffers 268435456 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL>

GDSCTL>databases
Database: "uk" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: europe
Service: "psfin_nvision" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
psfin%1
Database: "india" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: apac
Service: "psfin_nvision" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
psfin%11

GDSCTL>services
Service "psfin_nvision.psfin.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
Instance "psfin%1", name: "ORC1", db: "uk", region: "europe", status: ready.
Instance "psfin%11", name: "ORC1", db: "india", region: "apac", status: ready.

GDSCTL>

Conclusion

We’ve seen 360 degrees overview of the Global data services(services)

1. Adding service
2. Starting service
3. Stopping service
4. Enable service
5. Disable service

Apart from that we have seen the demo on how the service was failed over to the available instance when there is no availability of database.

References

https://docs.oracle.com/database/121/GSMUG/cloud.htm#GSMUG141