Understanding and managing client connectivity

While managing Data Guard Standby databases in our environment one of the key responsibility of DBA is End-user connectivity to a proper database or we can say clients connect to the correct database even in case of failover.

To know Oracle DataGuard

Your client connectivity should be managed in such a way that if failover or switchover happens with your databases your client connect to the correct database.

Database role we can manage using Client Connectivity are the following :

1. Primary database
2. Physical Standby [Read only with apply]
3. Logical standby
4. Snapshot Standby

The client should be connected to the correct database. If you have offloaded your reporting task to Physical Standby [Active Dataguard] or testing is being performed on Snapshot Standby or if the user wants to connect to your logical standby database, Clients must be connected to their desired database.

If a client has sent the request to the wrong host it results in connection to wrong database or error may occur.

We can Manage Client connectivity with  :

1. Local Naming
2. Using Services

1.Understanding Client Connectivity with Local Naming :

Configure a PRIMARY role service on the Primary and Standby and modify the Client connect descriptor to include both Primary and the Standby.

Example :

PRIM = (DESCRIPTION =
          (ADDRESS=(PROTOCOL = TCP)
              (HOST = DBPRM)(PORT = 1521))
          (ADDRESS=(PROTOCOL = TCP)
              (HOST = DBSTD)(PORT = 1521))
          (CONNECT_DATA = (SERVICE_NAME = PRIM)))

This TNS entry will direct connection to the server which supports PRIM service.

Preventing Clients from Connecting to the Wrong Database :

We can create database services to prevent a client from connecting to the wrong database in data guard environment. When we use database services it works as the abstract layer between your client and database instances.

As with this, your clients will be connected to Database Services instead of directly connected to the specific database instance.

Services you have created will be registered to Listeners. A listener will determine which service supports which database at the particular time. It can check and can redirect service to particular instance otherwise particular error will be returned. But your client will not be connected to the wrong instance.

You may get the error like

ORA-01033: ORACLE initialization or shutdown in progress
You can prevent clients from connect

Managing Service :

When Oracle Restart is not enabled, we can manage database service using DBMS_SERVICE package. This package is used for the creation, deletion, starting, and stopping of services for a single database instance.

If you use Oracle Real Application Cluster or single instance database which includes Oracle Restart, you can manage service using SRVCTL, DBMS_SERVICE package is deprecated in 11.2.

Attributes for DBMS_SERVICE package :

1. Service Name: For administration of service.
2. Network Name: For external clients
3. TAF Attributes: For TAF enabled clients

In the next Article, we will see creating After Startup trigger which manages Database Service for different roles of database

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp