Client Connect

In today’s article, I will explain how the Clients are Automatically Connected to the New Primary in Role Changes(SINGLE NODE).

In case of Switchover or Failover, it is very important that the clients try to connect to the correct database.

Clients connected to the Primary database should no longer try to connect to the Primary after Role Reversal operations, and try to connect to the old Physical Standby new Primary database.

This is provided by database services managed by the DBMS_SERVICE package in Single Node and by SRVCTL in RAC structure.

Database services act as abstraction layer between Clients and Database instance. They register to the Listener.

Since the database services are registered to the Listener, the clients continue to perform their duties over the other instance defined in Failover or Switchover states, by enabling the clients to connect to the database services instead of the database instances.

AFTER STARTUP ON DATABASE Trigger is used so that clients can automatically connect to the new Primary database as a result of the role change process.

Trigger is created in the Primary database and automatically replicated to the entire Standby database.

There are database services defined in Default. They do not need to be recreated. They only need to be started after a role change.

DG_PROD: Primary Database
DG_RTQ: Active Data Guard
DG_SNAP: Snapshot Standby Database
DG_LSBY: Logical Standby Database

The created services must be saved in the TNSNAMES.ORA file.

Let’s Test

1. After the role change, the service name to be created is added to the TNSNAMES.ORA file so that the clients can connect to the physical standby database. The creation will be done from NETMGR as recommended by Oracle.

a. By connecting to the server from terminal, switch to Oracle user and run netmgr.

b. In the window that opens, while the Service Naming tab is active, click the Create button with + sign.

c. We write “prmy” in the Net Service Naming field.

d. We choose “TCP/IP” as the network protocol.

e. We write “primary.tivibulab.local”, which is the Hostname of the primary database, as the Hostname, and “1521” as the Port.

f. We choose “prmy” as Service Name and “Dedicated Server” as Connection Type.

g With Finish, the service creation process is completed.

h. The service is configured. Click the Advanced button.

ı. Enter “32767” in the Session Data Unit field. This value increases the network throughput. Normally, this value is 64KB for 12cR1, but 32767 is entered because NETMGR does not support this value. It will then be edited manually.

j. The database that will be primary in the role change is added to the Address Configuration field. This is done with the + sign.

k. Switchover and Failover’s Target Standby database information is written as hostname. We write “physical.tivibulab.local” as the Hostname and “1521” as the Port.

m. We set Send and Receive Buffer Sizes for network throughput. This adjustment is made accordingly.

This value is compared to 10MB. Whichever is greater is entered.

Let’s calculate.

[This value can be seen by pinging the Primary Far SYNC instance where it sends the Redos from the Primary]

Therefore, the value of 10MB=10*1024*1024=10485760 bytes is written to the “Show Advanced” section of each 2 Adress Configuration section.

n. Settings are saved by clicking “Save” in the “File” section.

o. The TNSNAMES.ORA file is checked to see if the changes have been made.

p. The SDU value is changed to “65535”. For this, the TNSNAMES.ORA file is opened with the vi text editor and the following command is run.

q. File wq! Save with and check if the change has occurred.

e. The session opened from NETMGR and Terminal is exited.

2. The service is created in the primary database.

3. The service is started.

4. In Listener, we check whether the service has started.

While in SQLPLUS ! The flag enables operating system commands to be run.

5. The functionality of the service is tested by trying to connect with the created service.

6. AFTER STARTUP ON DATABASE Trigger is created to start the service automatically as a result of the role change operations.

7. We are checking whether the trigger occurs automatically in the Physical Standby database.

8. After the role change, the switchover process is performed to see if the service starts automatically and the clients can go to the new Primary.

For this, first of all, the compatibility of the databases with Switchover is checked.

9. Switchover operation is performed.

10. It is checked whether the switchover process is successful or not.

11. By trying to connect with the service, we check whether it connects to the old Physical Standby (New Primary) database.

12. We check that the service is running by querying the Listener in the Physical Standby database.

13. It is checked that the service is no longer running in the primary database.

Notice that the “prmy” service is not running in the primary database.

14. When there is no trigger, the service will not start in both databases after switchover. Trigger is DROP to see it.

15. We check the compatibility of databases for Switchover.

16. Switchover operation is performed.

17. We check if the switchover was successful.

18. It is checked whether the services are started in Listener.

Notice that the “prmy” service is not running in both instances.