DB2 High Availability disaster recovery

 

Through this article, you will learn about DB2
Hadr functions and basic operating principles, you can quickly configure an hadr environment through an instance and perform some simple functional tests. Next we will discuss some technical points of hadr.
Discuss in detail and give some suggestions. If you are considering the HA issue, I sincerely hope this article will help you.

DB2 hadr Overview

High Availability disaster recovery
(Hadr) is a database-level high-availability data replication mechanism. It was initially applied to INFORMIX database systems and is called High Availability Data.
Replication (HDR ). After IBM acquired Informix, this technology was applied to the new DB2 release. A hadr environment requires two database servers: Master
Database Server (primary) and backup database server (standby ). When a transaction occurs in the primary database, the log files are also transmitted to the standby number through the TCP/IP protocol.
The slave database is connected to the log file for replay (replay) to maintain consistency with the master database. When the primary database fails, the backup database server can take over
The transaction processing of the master database server. In this case, the slave database server acts as the new master database server to read and write the database, and the database connection of the client application can be re-routed through the automatic client.
By
Reroute) to the new master server. When the original primary database server is repaired, it can be added to hadr as a new backup database server. Through this mechanism, DB2
UDB achieves disaster recovery and high availability for databases, and minimizes data loss. Working principle diagram for DB2 hadr:

Note: databases in standby roles cannot be accessed.

Next we will first start with a configuration instance to understand the basic configuration process of the DB2 hadr environment, and then discuss some technical points involved in the hadr environment.

 

 

Quick Start

To configure this instance, you must have DB2 UDB Enterprise Server Edition (ESE ).
Ese v8.2.2 for Linux
32bit (fixpack9a is created based on v8.2 ). If you do not have this version, you can download the trial version from the official IBM website (it may take some time to enter some information ).
Link: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do? Source = db2udbdl
.

In addition, I use two Dell poweredge 2850 database servers and install RedHat Linux Enterprise
Server
V4.0. The host names and IP addresses of these two machines are respectively dbserv1 (192.168.1.162) and dbserv2 (192.168.1.163 ). Below
In the configuration process, we use dbserv1 as the master database server. In fact, After configuring hadr, the roles of the two servers can be converted. For simplicity, we use the number of DB2 samples.
The database sample is used as the configuration object.

Configuration process (the following commands are executed in DB2 CLP ):

1. Install DB2 on dbserv1 and dbserv2, and create the default instance db2inst1 and service port: 50000. We use the default instance owner user db2inst1 and password: db2inst1.

2. Use the db2sampl command to create a sample database sample on dbserv1

3. modify the configuration parameter logretain of the sample database to on to change the log record mode of the database to archive logs.

 

UPDATE DB CFG FOR SAMPLE USING LOGRETAIN ON UPDATE DB CFG FOR SAMPLE USING TRACKMOD ON

 

4. Modify index logging parameters

 

UPDATE DB CFG FOR SAMPLE USING LOGINDEXBUILD ON UPDATE DB CFG FOR SAMPLE USING INDEXREC RESTART

 

Note: This step is not required.

5. Backup database sample

 

BACKUP DB SAMPLE TO /database/dbbak

"/Database/dbbak" is the directory used by the author to store database backup files. You can specify any other directory in which db2inst1 has write permission.

After the backup is complete, the database backup image file is displayed in the/database/dbbak directory:

 

SAMPLE.0.db2inst1.NODE0000.CATN0000.20050726122125.001

 

Note: The time mark of the file name you get must be different from mine. In the following database recovery command, make changes accordingly.

6. Copy the obtained database image file to the directory corresponding to db2serv2 (/database/dbbak ).

7. Restore the database sample on dbserv2:

 

RESTORE DATABASE SAMPLE FROM "/database/dbbak" TAKEN AT 20050726122125 REPLACE HISTORY FILE WITHOUT PROMPTING

 

8. Configure Automatic client re-routing:

On the master database server (dbserv1:

 

UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USING HOSTNAME 192.168.1.163 PORT 50000

 

On the backup database server (dbserv2 ):

 

UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USING HOSTNAME 192.168.1.162 PORT 50000

 

9. Configure the hadr service and listening port

Use VI to edit the/etc/services file (you need to switch to the root user) and add the following two lines:

 

DB2_HADR_1 55001/tcp DB2_HADR_2 55002/tcp

 

For Windows, edit % SystemRoot %/system32/Drivers/etc/services.

Note: This step is not required, because you can directly use the port number to replace the service name When configuring the hadr_local_svc and hadr_remote_svc database parameters below.

10. modify the configuration parameters of the primary database (dbser1-sample:

 

UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.1.162 UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC DB2_HADR_1 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.1.163 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC DB2_HADR_2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST db2inst1 UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120 CONNECT TO SAMPLE QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS UNQUIESCE DATABASE CONNECT RESET

 

11. modify the configuration parameters of the backup database (dbserv2-sample:

 

UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.1.163 UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC DB2_HADR_2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.1.162 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC DB2_HADR_1 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST db2inst1 UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120

 

12. Start hadr:

First, start the hadr of the backup database server:

 

DEACTIVATE DATABASE SAMPLE START HADR ON DATABASE SAMPLE AS STANDBY

 

Then start hadr of the master database server:

 

DEACTIVATE DATABASE SAMPLE START HADR ON DATABASE SAMPLE AS PRIMARY

 

Note: If you start the primary database server hadr, you must ensure that the backup database server hadr is started within the time specified by the hadr_timeout parameter (in seconds. Otherwise, startup will fail.

OK. So far, we have successfully configured and started DB2 hadr. In the next section, we will perform some tests on the configured hadr environment to verify whether it can work as expected.

 

 

Hadr Testing

1. Connect to the primary database, create the test table hadrtest, and insert several test data records:

 

Connect to sample user db2inst1 using db2inst1 Create Table hadrtest (ID integer not null with default, name varchar (10), primary key (ID )) Insert into hadrtest (ID, name) values (1, 'zhang san ') Insert into hadrtest (ID, name) values (2, 'lily ')

 

2. Use the backup database to take over the master database

 

TAKEOVER HADR ON DATABASE SAMPLE USER db2inst1 USING db2inst1

 

Observe the status of the primary and standby databases:

 

GET SNAPSHOT FOR DB ON SAMPLE

 

New primary database (original standby database ):

Backup database (original primary database ):

3. Connect to the new primary database and query the hadrtest table:

Obviously, our hadr environment is ready to work normally. You can perform some tests on data modification and deletion. The automatic client reroute function is left for the reader to test.

 

 

Hadr management operation summary

1. Start and Stop hadr

Use the start hadr command to start the hadr of the primary and standby databases. Start the master database using the as primary clause and start the slave database using the
Standby clause. If you want to start hadr with another user, you can use the user-name using password clause to specify the user name and password:

Example:

 

START HADR ON DATABASE SAMPLE USING db2inst1 USING db2inst1 AS STANDBY

 

When you start hadr of the primary database, if you fail to establish a connection with the standby database hadr within the time specified by hadr_timeout, the startup will fail. At this time, you can start the primary database hadr after troubleshooting and successfully starting the standby database hadr, or forcibly starting the primary database by specifying the by force clause.

For example:

 

START HADR ON DATABASE SAMPLE AS PRIMARY BY FORCE

 

Use stop hadr to stop the hadr of the primary and standby databases.

If this command is issued on the active primary database, all database connections are disconnected and the database is restored to a standard database (we call a database without hadr enabled as a standard database ), and stay online.

If this command is issued on the active standby database, the Operation will stop and fail. You must first use the Deactivate database command to cancel the activation and then stop hadr.

2. View hard configuration and running status

Hadr connection status:

When the hadr of the standby database is started, it first enters the local synchronous update status. Search and replay the log files in the Local System Based on the local Log Path configuration parameters and the log archiving method settings. When
After the local log file is replayed, the Standby database enters the remote synchronization pending state. After establishing a connection with the primary database, the Standby database enters the remote synchronous update status. That is, the primary database uses its own log files
The TCPIP protocol is sent to the standby database. The standby database receives and replays log files until all log files are replayed. The standby database and the primary database enter the peer state. See:

Run the get snapshot command to check the connection status between the master database and the backup database.

You can run the get dB CFG command to view the configuration of hadr, that is, several database parameter values related to hadr.

3. Take over/failover

When the primary database fails, the Standby database can take over the services of the primary database and become a new primary database (referred to as failover ). After the original primary database is repaired, it can be added as a backup database.
Hadr pair. Even if the primary database server is not faulty, we use the takeover command to switch the role of the primary database and the standby database. The takeover command can only be used on the standby database.

Hadr provides the following two methods:

Emergency Takeover:

When the primary database fails, the Standby database can be taken over urgently to make the standby database a new primary database. For emergency take-over, the by force clause of the takeover command must be specified. For example:

 

TAKEOVER HADR ON DATABASE SAMPLE BY FORCE

 

Common Takeover:

A common takeover is a takeover without the by force clause. For example:

 

TAKEOVER HADR ON DATABASE SAMPLE

 

This type of takeover must be used when both the primary database and the standby database are running normally. If the primary database fails, the normal take-over will fail. In this case, the above emergency take-over must be used.

4. synchronization mode

In the above configuration instance, we set the hadr_syncmode parameter value of the primary database and the standby database to nearsync. When the primary database and the standby database are in a peering state, hadr uses nearsync (close to synchronization) manage log writing in synchronous mode. DB2 provides three log Synchronization Methods:

Sync ):

When sync is used, the log is considered to be successfully written only when the primary database log is successfully written and the standby database response is received to ensure that the logs of the standby database are also successfully written.

In this mode, the transaction response time is the longest, but the maximum guarantee is that no transaction is lost.

Nearsync (near synchronization ):

When the nearsync method is used, when the primary database logs are successfully written and the standby database responds, it is deemed that the logs are successfully written when the standby database has received the logs. That is to say, the logs received by the standby database may not be successfully written into the log files on the persistent storage device.

In this mode, the transaction response time is shorter than that in sync mode, and the transaction is lost only when both servers fail at the same time.

Async (asynchronous ):

In async mode, when the primary database logs are successfully written and the logs are sent out, the logs are considered to have been written successfully. This method does not ensure that the standby database can receive logs, which depends on TCP/IP network conditions.

In this mode, the transaction response time is the shortest, but the loss of the transaction is also the most likely.

5. Automatic client re-routing (automatic client reroute)

To configure an automatic client to reroute, use update Alternate
Server command to set the backup database information (for usage, refer to the configuration instance above), the information will be stored in the database system directory. Note: You must use this command to set the backup database,
Instead of configuring the hadr_remote_host and hadr_remote_svc database parameters, the automatic client re-routing does not use these two parameters.

When the client establishes a connection with the database, the backup database configuration information (host/IP and
Port Number) is also sent to the DB2 client. When the connection between the client and the primary database is interrupted, the client uses this information to connect to the standby database, minimizing the impact of database faults.
. It should be emphasized that this process is automatically completed by the DB2 client without user intervention. See:

You can use the list dB direcotry command to view the configuration of automatic client route re-routing in the system database directory.

6. Use the control center to manage hadr

In the above discussion, we mainly use the DB2 CLP command to create and manage DB2
Hadr. In fact, the DB2 control center also provides a graphical interface for creating and managing hadr, such as: Tools-> wizard-> setting high availability disaster recovery (hadr) databases. Use these functions
It is very simple, so we will not discuss it in detail here. However, I strongly recommend that you use DB2 as much as possible.
CLP commands are used to manage DB2 (not just for hadr). Do not rely too much on the DB2 control center, because many server environments do not install the Control Center. If you do not know the DB2
The CLP command is troublesome.

7. About Index log records

Index creation, reconstruction, and reorganization are also considerations in the hadr environment. DB2 uses the database configuration parameters logindexbuild and create
Log index in table or alter table statements
Build option to control whether detailed log records are performed on index-related operations. In the above hadr configuration instance, we set the logindexbuild database parameter to on, meaning
Let DB2 record the complete logs of index creation, reconstruction, and reorganization. This will obviously reduce the operational efficiency of the primary database and occupy more log space. However, because the standby database can rebuild indexes by replaying logs,
Therefore, when the primary database fails, the indexes of the standby database are still available.

You can use the create table or alter table statement's log index build option to set the index log level for a single table. The log index build option has three optional parameters:

• Null: This is the default value. When this parameter is used, the index log record level of the table is determined by the value of the logindexbuild parameter configured in the database.
• On: With this parameter, the value of the Database Configuration Parameter logindexbuild will be ignored, and DB2 will record the detailed logs maintained by all indexes on this table.
• Off: When this parameter is used, the value of the Database Configuration Parameter logindexbuild will be ignored, and DB2 will not record the logs maintained by the index on this table.

If the table option log index build is set to off, or log Index
Build is set to null, but the Database Configuration Parameter logindexbuild is set to off. DB2 will not record the index maintenance logs of these tables, and the standby database will not be able to replay the index dimension.
Protection Operation, causing these indexes to become invalid in the standby database. When the primary database fails and the standby database switches to the new primary database, these invalid indexes must be rebuilt before they can be used. DB2 pass count
The Database Configuration Parameter indexrec specifies when to check and recreate invalid indexes. The indexrec parameter has three optional values:

• Restart: DB2 checks and reconstructs invalid indexes when it explicitly or implicitly restarts the database.
• Access: DB2 reconstructs an invalid index only when it is accessed for the first time.
• System: use the database manager to configure the value of indexrec.

In the preceding configuration instance, we set the value of indexrec to restart. The slave database checks and reconstructs all invalid indexes when taking over the new master database.

 

 

DB2 hadr restrictions

• Only DB2 UDB Enterprise Server Edition (ESE) supports hadr, but hadr does not support database partitioning feature (DPF ).
• The master database and the slave database must run on the same operating system version, and the DB2 UDB version must also be consistent, unless a short software upgrade process occurs.
• The size of the base database and the backup database must be the same (32-bit or 64-bit ).
• Backup operations cannot be performed on the backup database
• The standby database cannot be accessed, and the client program cannot connect to the standby database.
• Day-to-day archiving can only be performed on the primary database.
• The load command with the copy no option is not supported.
• The primary and standby databases must be one-to-one.
• Hadr cannot use cyclic logs.
• Hadr does not copy database configuration parameters, shared libraries, DLLs, udfs, or stored procedures