We have seen managing Global data services in Data Guard configuring by failover test and so on, if any situation occur like the role transitions of the Data Guard configuration between Primary database and Physical standby database then in normal service we have to reconfigure the services based on the nature of the database role, but in case of global services the GDS will take care of everything even though after the role transitions.
Role Based Global Services
As we know Global data services is suitable for replica aware databases and it provides great flexibility in load balancing, service failover and so on. Let’s suppose we have added the Data Guard broker to the GDS configuration and created few services to connect read write services to the primary database and the read only services to physical standby database and now consider there is server maintenance on primary database and to escape the down time on production then it is good practice to perform the role transitions/switch over and then switch back. But here the question is how the read write services and read only services work?
Where in case of local services we have to reconfigure the services and we need some maintenance time to do this. In case of RAC systems then we have to modify the services using srvctl command line reference. Now with Global data services the job is so simple and you will basically nothing to do except the configuration of the services in preferred manner.
Demo on creating services
We will see some demo on this role based services, in order to do that we will create one service for primary usage and one for standby service. Here there will be configuration steps included only the configuration of services. Below we have created service cobol_process with primary database and the nvision_report for the physical standby database.
GDSCTL>add service -service cobol_process -gdspool psfin -preferred_all -role PRIMARY
GDSCTL>add service -service nvision_report -gdspool psfin -preferred_all -role PHYSICAL_STANDBY
Start the created global data services.
GDSCTL>start service -service cobol_process -gdspool psfin
GDSCTL>start service -service nvision_report -gdspool psfin
Check status of services and databases
GDSCTL>services
Service "cobol_process.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%1", name: "ORC1", db: "CANADA", region: "westcan", status: ready.
Service "nvision_report.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%11", name: "ORC1", db: "INDIA", region: "apac", status: ready.
GDSCTL>databases
Database: "canada" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: westcan
Service: "cobol_process" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Service: "nvision_report" Globally started: Y Started: N
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: "cobol_process" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Service: "nvision_report" Globally started: Y Started: Y
Scan: Y Enabled: Y Preferred: Y
Registered instances:
psfin%11
GDSCTL>
If we see the service cobol_process is running with the preferred instance CANADA and the database role is primary database, the nvision_report is running on Instance INDIA and the database role is physical standby database.
Connectivity and the Listener status of Primary database
[oracle@ORA-C2 ~]$ sqlplus sys/oracle@cobol_process as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 1 04:27:41 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 database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY
SQL>
[oracle@ORA-C1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 01-JUN-2016 05:09:39
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C1.localdomain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 28-MAY-2016 12:56:23
Uptime 3 days 16 hr. 13 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ORA-C1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA-C1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service "CANADA" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "CANADA_DGB" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "CANADA_DGMGRL" has 1 instance(s).
Instance "ORC1", status UNKNOWN, has 1 handler(s) for this service…
Service "ORC1XDB" has 1 instance(s).
Instance "ORC1", status READY, has 0 handler(s) for this service…
Service "cobol_process.psfin.oradbcloud" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@ORA-C1 ~]$
Connectivity to the standby database and the Listener status
[oracle@ORA-C2 ~]$ sqlplus sys/oracle@nvision_report as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 1 04:27:55 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 database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
SQL>
[oracle@ORA-C2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 01-JUN-2016 05:10:10
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C2.localdomain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 28-MAY-2016 12:59:40
Uptime 3 days 16 hr. 10 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ORA-C2/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA-C2.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ORA-C2.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/GCAT/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "GCAT" has 1 instance(s).
Instance "GCAT", status READY, has 1 handler(s) for this service…
Service "GCATXDB" has 1 instance(s).
Instance "GCAT", status READY, has 1 handler(s) for this service…
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
Instance "GCAT", status READY, has 1 handler(s) for this service…
Service "INDIA" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "INDIA_DGB" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "INDIA_DGMGRL" has 1 instance(s).
Instance "ORC1", status UNKNOWN, has 1 handler(s) for this service…
Service "nvision_report.psfin.oradbcloud" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@ORA-C2 ~]$
Broker configuration and Switchover Test
[oracle@ORA-C1 ~]$ dgmgrl nassyam/oracle@canada
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration
Configuration – hadg
Protection Mode: MaxPerformance
Members:
canada – Primary database
india – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 22 seconds ago)
DGMGRL>
DGMGRL> switchover to india
Performing switchover NOW, please wait…
Operation requires a connection to instance "ORC1" on database "india"
Connecting to instance "ORC1"…
Connected as SYSDBA.
New primary database "india" is opening…
Operation requires start up of instance "ORC1" on database "canada"
Starting instance "ORC1"…
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "india"
DGMGRL>
The configuration status is in SUCCESS state and the switchover is successful and now the new primary database is INDIA.
Review of Databases and Services
GDSCTL>databases
Database: "canada" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: westcan
Service: "cobol_process" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Service: "nvision_report" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Registered instances:
psfin%1
Database: "india" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: apac
Service: "cobol_process" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Service: "nvision_report" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Registered instances:
psfin%11
GDSCTL>services
Service "cobol_process.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%11", name: "ORC1", db: "INDIA", region: "apac", status: ready.
Service "nvision_report.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%1", name: "ORC1", db: "CANADA", region: "westcan", status: ready.
GDSCTL>
After the successful switchover and above from the output of command “databases” we can see the Canada is physical standby database where it was the primary database prior to switchover and the india is now the primary database. When it comes to the switchover the cobol_process global service now is running on the India instance and the nvision_report global services is running on the Canada which is physical standby database.
So we can see that the services role was changed automatically without human interaction, which is the great flexibility of the global data services if we configure role based.
Crosschecking the services nature
We have seen how the services role changed, now we will confirm by doing the manual connectivity to the databases using the global data services configured. Now we will connect to the nvision_report read only service, it was running earlier on the INDIA Instance and now it should run on CANADA instance after the role transitions.
[oracle@ORA-C2 ~]$ sqlplus sys/oracle@nvision_report as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 1 05:28:16 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 database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
SQL>
[oracle@ORA-C1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 01-JUN-2016 05:29:20
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C1.localdomain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 28-MAY-2016 12:56:23
Uptime 3 days 16 hr. 32 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ORA-C1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA-C1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service "CANADA" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "CANADA_DGB" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "CANADA_DGMGRL" has 1 instance(s).
Instance "ORC1", status UNKNOWN, has 1 handler(s) for this service…
Service "ORC1XDB" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "nvision_report.psfin.oradbcloud" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@ORA-C1 ~]$
Now we will test connectivity of the read write cobol_process and also we can see that service is registered with the new primary database.
[oracle@ORA-C2 ~]$ sqlplus sys/oracle@cobol_process as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 1 05:28:44 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 database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY
SQL>
[oracle@ORA-C2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 01-JUN-2016 05:29:07
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C2.localdomain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 28-MAY-2016 12:59:40
Uptime 3 days 16 hr. 29 min. 27 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ORA-C2/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA-C2.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ORA-C2.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/GCAT/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "GCAT" has 1 instance(s).
Instance "GCAT", status READY, has 1 handler(s) for this service…
Service "GCATXDB" has 1 instance(s).
Instance "GCAT", status READY, has 1 handler(s) for this service…
Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
Instance "GCAT", status READY, has 1 handler(s) for this service…
Service "INDIA" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "INDIA_DGB" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
Service "INDIA_DGMGRL" has 1 instance(s).
Instance "ORC1", status UNKNOWN, has 1 handler(s) for this service…
Service "cobol_process.psfin.oradbcloud" has 1 instance(s).
Instance "ORC1", status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@ORA-C2 ~]$
Conclusion
This article basically gives idea how to configure the role based global services so that the services will work without any maintenance after the role transitions between the Data Guard configurations. In case of local data services additional DBA should be available to manage the services or to relocate or reconfigure but here everything will take care of by GDS.
Start the discussion at forums.toadworld.com