Configuring database role based Global services – 12c

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.