Automatic Client switching in the Data Guard Environment

When Data guard is used as the HA solution, one problem to be solved is that the master-slave switchover occurs in the background database. How can the client connection be automatically switched to the new primary database?
 
For general solutions, the client needs to provide automatic reconnection, which is implemented in most java occi connection pools.
 
However, most of these existing implementations are related to the same connection configuration. Therefore, you need to consider providing a transparent connection method for the application, so that the application will not see multiple ip addresses and service names of the specific data guard, this requires additional configuration work.
 
In one way, the ip address actually forwarded is only associated with the ip address of the valid database. After this switchover occurs, the application initiates dml on the disconnected old connection and gets the ORA-3113 "end of file on communication channel" error, in this case, the application can try the reconnection mechanism and establish a connection with the new primary.
 
On f5, you can set heartbeat SQL and expected returned results to obtain the remote Database Availability Using ping-like methods to determine whether the ip address should be forwarded to the physical ip address.
 
Another method is to set the service name of the tns and database to access the database. By setting the service name reasonably, you can even block the select Operation for a while in the case of a switchover, the database has completed the master-slave switchover.
 
The procedure is as follows:
 
1. Configure tns
 
MYAPP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = HostA) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = HostB) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = myapp)
)
)
 
 
 
2. Run in the primary database
 
Begin
Dbms_service.create_service ("myapp", "myapp ");
End;
/
Begin
DBMS_SERVICE.START_SERVICE ("myapp ");
End;
/
 
3. Create a trigger in the primary database:
 
Create trigger myapptrigg after startup on database
Declare
V_role varchar (30 );
Begin
Select database_role into v_role from v $ database;
If v_role = "PRIMARY" then
DBMS_SERVICE.START_SERVICE ("myapp ");
Else
DBMS_SERVICE.STOP_SERVICE ("myapp ");
End if;
End;
/
 
The solution is to set the service names of databases on both sides to "myapp". When switching, when the trigger starts the database, the instance of primary is displayed as "myapp", and the service name of standby "myapp" is stopped, in this way, only the service named "myapp" is displayed on the master node at any time.
 
Note that plsql here runs on primary and does not need to be set on standby, because data guard automatically synchronizes the changes to the standby database.
 
By running the following program in the primary database, the client may not even be aware of database switching during the select Operation:
 
Begin
Dbms_service.modify_service
("Myapp ",
FAILOVER_METHOD => "BASIC ",
FAILOVER_TYPE => "SELECT ",
FAILOVER_RETRIES = & gt; 200,
FAILOVER_DELAY => 1 );
End;
/
 
Note that if a comit commit transaction occurs during the switchover, The commit may fail and rollback is required.
 
 
 
The following tns is another configuration method (similar to rac's failover configuration idea). In this way, you do not need to run any plsql script on the Oracle server, the two databases in DESCRIPTION_LIST do not even need to be in data guard. They can be any two databases. The driver will traverse the database in the list in sequence until it can be connected.
 
MYAPP =
(DESCRIPTION_LIST =
(LOAD_BALANCE = off)
(FAILOVER = on)
(DESCRIPTION = (CONNECT_TIMEOUT = 5) (TRANSPORT_CONNECT_TIMEOUT = 3) (RETRY_COUNT = 10)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = myapp1)
)
)
(DESCRIPTION = (CONNECT_TIMEOUT = 5) (TRANSPORT_CONNECT_TIMEOUT = 3) (RETRY_COUNT = 10)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = otherIP) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = myapp2)
)
)
)
 
 
 
Note the following:
 
1. jdbc must be in the oci mode. If it is in the jdbc: thin + tns mode
 
Exception in thread "main" java. lang. ArrayIndexOutOfBoundsException: 545
At oracle.net. nl. NVTokens. parseTokens (Unknown Source)
At oracle.net. nl. NVFactory. createNVPair (Unknown Source)
 
The reason is that the jdbc driver itself cannot identify the tns content in this format.
 
Even if you access other normal tns through jdbc: thin + tns, this error is thrown because jdbc cannot parse the entire tnsnames. ora file correctly.
 
While jdbc: oci is actually responsible for parsing tnsnames. ora and processing communication is dependent on oci. lib, so this problem does not exist.
 
2. This configuration applies to any client that relies on oci communications, including oci, occi, some of their-based wrap libraries, and tool software such as pl/SQL developer.
 
3. note: If the connected database group belongs to the manually switch mode, instead of the switchover caused by fail down, for example, database a in tns is in the mount state, and database B is in the primary state, while the List order of tns is first a and then B, it will appear even when the client connects to a, throwing a ORA-0133 error, but will not try to connect B in order.
 
The reason is that when processing this link, the oci client will try to establish a connection through listener and service.
 
If the listener is disabled, or the client can connect to the listener but cannot find the corresponding service, it will try to connect to the second B, but if the opposite service is found through the listener, however, if you cannot establish a connection (for example, if the database is in the mount state), you will not try to connect to B, but will directly throw
 
ORA-0133: ORACLE initialization or shutdown in progress
 
Terminate the connection attempt.
 
Therefore, when using this tns, make sure that all the databases that can be accessed through the tns list are not always in the mount state. Otherwise, the connection will interrupt the attempt to connect to the subsequent normal open database.
 
This is also the reason why the client cannot perform automatic failover Based on the tns configuration method, because the manually switched mongouard database status must be an open mount, if mount is in front of the List of tns, A ORA-0133 exception is thrown when it fails to connect to the database that is normally open.

Recommended reading:

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption