(RPM) DB2 HADR

Transfer from http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0508luojunkai/

Original: http://blog.csdn.net/deanza/article/details/8469447

Standby is one of the DB2 high availability (Ha,high availability) technologies (and snapshot and mirror). It is similar in principle to Oracle's data guard, providing a standby mode that allows another server to receive logs from the "Master" system and update it to the standby database for data synchronization purposes.

The DB2 provides two switching options to the user, i.e.

• Idle standby (I translate it into passive standby mode), that is, the main system and the standby system has no master-slave relationship, the standby system is only activated in a standby state until it is manual activation;
• Mutual takeover (mutual backup mode), that is, the main system and the standby system has a master-slave relationship, one switch to the main system, the other side automatically switch to the standby system, and vice versa

In the way that logs are transferred, DB2 provides three modes:

• Sync mode (sync), this is the highest protection mode, the main system must wait until the standby system completes the log write (to write to the disk) and the data update and back to the completion signal to respond to the next operation;
• Near-synchronization (Nearsync), this is a tradeoff, the main system as long as the log to the main memory of the standby system and received the signal of the standby system to respond to the next operation (if the network conditions are good, you can consider this);
• Asynchronous (Async), this is relatively simple, the main system completes the log to write to the disk and send the log to the main system TCP layer returned (if the network conditions are not good, only use this)

DB2 Hadr is somewhat similar to Oracle Dataguard, where the experiment uses manual switching of the main standby, while log shipping takes the Nearsync mode.

DB2 Hadr Overview

The High Availability Disaster Recovery (HADR) is a database-level, highly available data replication mechanism that was originally applied to the Informix database system, known as higher availability data Replication (HDR). After IBM acquired Informix, the technology was applied to the new DB2 release. An HADR environment requires two database servers: the primary database server (primary) and the standby database server (standby). When a transactional operation occurs in the primary database, the log files are routed through the TCP/IP protocol to the standby database server, and the standby database is replayed (Replay) by the log files that are received to maintain consistency with the primary database. In the event of a failure of the primary database, the standby database server can take over the transaction processing of the primary database server. At this point, the standby database server reads and writes the database as the new primary database server, and the client application's database connection can be transferred to the new primary server through the automatic client reroute (Automatic client Reroute) mechanism. When the original primary database server is repaired, you can join HADR as a new standby database server. Through this mechanism, DB2 UDB realizes the disaster recovery and high availability of the database, and avoids the data loss to a minimum. Working schematics for DB2 Hadr:

Note: The database in the standby role cannot be accessed.

Let's start with a configuration example to understand the basic configuration process of the DB2 HADR environment, and then discuss some of the technical points that are involved in the HADR environment.

Get started with quick examples

To perform this instance configuration process, you must have DB2 UDB Enterprise Server Edition (ESE), which I am using DB2 ese v8.2.2 for Linux 32bit (v8.2 based on fixpack9a). If you do not have this version, you can download the trial version on the official IBM website (it may take some time to fill in some information), download the link: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=db2udbdl.

In addition, the author uses two Dell PowerEdge 2850 as a database server, installing Redhat Linux Enterprise Server v4.0. The host names and IP addresses of the two machines are: DBSERV1 (192.168.1.162) and DBSERV2 (192.168.1.163) respectively. In the following configuration process we will DBSERV1 as the primary database server, in fact, after the HADR configuration, the roles of these two servers can be converted. For the sake of simplicity, we use the DB2 sample database sample as the configuration object.

The configuration process (the following commands are performed in the DB2 CLP):

1. Install the DB2 on DBSERV1 and DBSERV2 and create the default instance Db2inst1, service port: 50000, we use the default instance owner user Db2inst1, password: db2inst1

2. Create a sample database on DBSERV1 using the Db2sampl command sample

3. Modify the sample database configuration parameter Logretain to on so that the database logging mode changes to archive log.

UPDATE db CFG for sample using Logretain ONUPDATE DB cfg for sample using Trackmod on

4. Modifying index logging parameters

UPDATE db CFG for sample using Logindexbuild ONUPDATE DB cfg for sample using Indexrec RESTART

Note: This step is not required.

5. Back up the Database sample

BACKUP DB SAMPLE To/database/dbbak

where "/database/dbbak" is the directory that the author uses to store the database backup files, you can specify any other directory where Db2inst1 has write permission.

After the backup is complete, we will see the database backup image file in the/database/dbbak directory:

sample.0.db2inst1.node0000.catn0000.20050726122125.001

Note: You get the file name of the time of the logo section is certainly not the same as mine, in the following recovery database command to pay attention to make the corresponding changes.

6. Copy the resulting database image files to the db2serv2 corresponding directory (/database/dbbak).

7. Resume the Database sample on DBSERV2:

RESTORE DATABASE SAMPLE from '/database/dbbak ' taken at 20050726122125 REPLACE FILE without prompting

8. Configure automatic Client Reroute:

On the primary database server (DBSERV1):

UPDATE ALTERNATE SERVER for DATABASE SAMPLE USING HOSTNAME 192.168.1.163 PORT 50000

On the Standby database server (DBSERV2):

UPDATE ALTERNATE SERVER for DATABASE SAMPLE USING HOSTNAME 192.168.1.162 PORT 50000

9. Configuring the HADR service and listening port

Edit the/etc/services file with VI (need to switch to root user), add the following two lines:

db2_hadr_1 55001/tcpdb2_hadr_2 55002/tcp

For Windows, edit%systemroot%\system32\drivers\etc\services.

Note: This step is not required because you can use the port number instead of 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.162UPDATE DB cfg for sample using Hadr_local_svc db2_hadr_1update DB CFG for sample using Hadr_remote_host 192.168.1.163UPDATE db CFG for sample using HADR_REMOTE_SVC Db2_hadr_2update db CFG for sample using Hadr_remote_inst db2inst1update db cfg for SAMPLE using Hadr_syncmode nearsyncupdate db cfg for Sampl E USING hadr_timeout 120CONNECT to samplequiesce DATABASE IMMEDIATE Force connectionsunquiesce databaseconnect RESET

11. Modify the configuration parameters of the standby database (dbserv2-sample):

UPDATE db CFG for sample using Hadr_local_host 192.168.1.163UPDATE DB cfg for sample using Hadr_local_svc db2_hadr_2update DB CFG for sample using Hadr_remote_host 192.168.1.162UPDATE db CFG for sample using HADR_REMOTE_SVC Db2_hadr_1update db CFG for sample using Hadr_remote_inst db2inst1update db cfg for SAMPLE using Hadr_syncmode nearsyncupdate db cfg for Sampl E USING Hadr_timeout 120

12. Start Hadr:

Start Hadr on the standby database server first:

DEACTIVATE database Samplestart HADR on DATABASE SAMPLE as STANDBY

Then start HADR on the primary database server:

DEACTIVATE database Samplestart HADR on DATABASE SAMPLE as PRIMARY

Note: If you start the primary database server Hadr First, you must ensure that the standby database server Hadr is started within the time specified by the Hadr_timeout parameter (in seconds). Otherwise, the startup fails.

OK, so far we have successfully configured and started DB2 HADR. In the next section we'll test this configured HADR environment to see if it works the way we expect it to.

Hadr test

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

CONNECT to the SAMPLE USER db2inst1 USING db2inst1create TABLE hadrtest (ID INTEGER not NULL with Default,name VARCHAR), PRIM ARY KEY (ID)) insert into hadrtest (id,name) VALUES (1, ' Zhang San ') inserts into Hadrtest (Id,name) VALUES (2, ' John Doe ')

2. Using a backup database to take over the primary database

Takeover HADR on DATABASE SAMPLE USER db2inst1 USING db2inst1

Observe the status of the database master database and the standby database:

GET SNAPSHOT for DB on SAMPLE

New primary Database (original standby database):

Standby database (primary database):

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

Obviously, our HADR environment is ready to work properly. The reader can do some testing on the data modification, deletion and so on. The automatic client reroute (Automatic client Reroute) feature is also left to the reader to test itself.

HADR Management Operations Summary

1. Start and stop Hadr

Use the start Hadr command to start Hadr on the primary and standby databases. Start the primary database using the AS primary clause and start the standby database using the AS STANDBY clause. If you want to start Hadr with another user, you can specify a user name and password by using the user-name using password clause:

Example:

START HADR on DATABASE SAMPLE using db2inst1 using Db2inst1 as STANDBY

When you start HADR for the primary database, the startup fails if you fail to establish a connection to the standby database Hadr within the time specified by the database hadr_timeout. At this point, you can wait to troubleshoot and successfully start the standby database Hadr before starting the main database Hadr, or you can forcibly start 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 HADR for the primary database and standby database.

If this command is issued on the active primary database, all database connections are disconnected, the database reverts to the standard database (we call the database without HADR enabled as the standard database), and remains online.

If this command is issued on an active standby database, the failure will stop. You must first deactivate using the Deactivate Database command and then stop Hadr.

2. View hard configuration and operating status

Hadr Connection Status:

When HADR for the standby database is started, it first enters the local synchronous update state. The log files on the local system are retrieved and replayed based on the local log path configuration parameters and the settings of the log archive method. When the local log file is replayed, the standby database enters the remote sync pending state. When a connection is established to the primary database, the standby database enters the remote synchronization update state. That is, the primary database sends its own log files through the TCPIP protocol to the standby database, the standby database receives the log files and replays them until all the log files are replayed, and the standby and primary databases enter the peer state. See:

Observe the connection state of the primary and standby databases with the Get snapshot command.

The Get DB CFG command allows you to view the configuration of Hadr, which is the values of several database parameters related to Hadr.

3. Take over/fail over

In the event of a primary database failure, the standby database can take over the service of the primary database and become the new primary database (known as failover). When the primary database is repaired, the Hadr pair can be joined as a standby database. Even if the primary database server is not faulted, we switch the roles of the primary and standby databases by taking over commands (takeover). The takeover command can only be used on the standby database.

HADR offers two ways to take over:

Emergency takeover:

When the primary database fails, an emergency takeover can be used on the standby database to make the standby database the new primary database. Emergency takeover You must specify the by force clause of the takeover command, for example:

Takeover HADR on DATABASE SAMPLE by force

Normal takeover:

A normal takeover is a takeover that does not use the by force clause, for example:

Takeover HADR on DATABASE SAMPLE

This takeover must be used in the event that both the primary and standby databases are functioning properly. If the primary database fails, the normal takeover fails, and the above emergency takeover must be used.

4. Synchronization mode

In the above configuration example, we set the Hadr_syncmode parameter value of the primary and standby databases to Nearsync, and Hadr manages log writes with Nearsync (Close synchronization) synchronously when the primary and standby databases are in peer state. The DB2 provides three ways to log synchronization:

Sync (synchronous):

When the sync mode is used, log writes are considered successful only if the primary database log is successfully written and the standby database is answered, ensuring that the log for the standby database is also successfully written.

Transaction response time is the longest in this way, but the maximum ensures that no transaction loss occurs.

Nearsync (near Sync):

When the Nearsync method is used, the log writes are considered successful when the primary database log is successfully written and the standby database is answered to determine that the standby database has received the log. In other words, the log received by the standby database does not necessarily successfully write to the log file on the persistent storage device.

The transaction response time in this way is shorter than the sync mode, and transaction loss occurs only if two servers fail at the same time.

Async (Async):

In async mode, log writes are considered successful when the primary database log is successfully written and the log is sent out. This approach does not guarantee that the standby database will receive logs, depending on the TCP/IP network situation.

Transaction response time is the shortest in this way, but the likelihood of transaction loss is also the most

5. Automatic client Reroute (Automatic Reroute)

To configure automatic client reroute, use the Update ALTERNATE Server command to set up the standby database information (using the method reference above for the configuration instance), which will be stored in the system directory of the database. Note: You must use this command to set up the standby database instead of the Hadr_remote_host and hadr_remote_svc database configuration parameters, which are not used by automatic client rerouting.

When the client establishes a connection to the database, the configuration information (host/IP and port number) of the standby database is also sent to the DB2 client. When the client's connection to the primary database is interrupted, the client uses this information to connect to the standby database, minimizing the impact of a database failure. It should be emphasized that this process is done automatically by the DB2 client and does not require the user to interfere with the program. See:

The list DB direcotry command allows you to view the configuration of automatic client rerouting in the system database directory.

6. Using the control center to manage Hadr

In the above discussion we created and managed DB2 HADR primarily through the DB2 CLP command. In fact, DB2 's control center also provides a graphical interface for creating and managing HADR, such as the tool-〉 Wizard, which sets up a high-availability disaster recovery (HADR) database. These features are very simple to use and are not discussed in detail here. However, I strongly recommend that you use DB2 CLP commands as much as possible to manage DB2 (not just for hadr), not to rely too much on DB2 control center, because many server environments do not have a control center installed, when you do not have the DB2 CLP command, it will be a big hassle.

7. About Index Logging

The creation, rebuilding, and reassembly of indexes is also an aspect to be considered in the HADR environment, DB2 through the database configuration parameters Logindexbuild and the log INDEX in the CREATE TABLE or ALTER TABLE statement Build option to control whether the related operations of the index are logged in detail. We configured the Logindexbuild database parameter to on in the Hadr configuration instance above, which means that DB2 records the full log of index creation, rebuild, and reassembly. This obviously reduces the efficiency of the primary database and consumes more log space. However, because the standby database can re-build the index by replaying the log, the index of the standby database is still available when the primary database fails.

You can set the index logging level for a single table by using the Log Index build option of the CREATE TABLE or ALTER TABLE statement. The LOG INDEX build option has three optional parameters:

• NULL: This is the default value, and when this parameter is used, the index logging level of the table is determined by the value of the database configuration parameter logindexbuild.
• On: Using this parameter, the value of the database configuration parameter logindexbuild will be ignored, and DB2 will record a detailed log of all index maintenance on this table.
• OFF: When using this parameter, the value of the database configuration parameter logindexbuild will be ignored, DB2 will not log the index maintenance on this table.

If the table option Log index build is set to OFF, or the log index build is set to NULL but the database configuration parameter logindexbuild is set to off,db2 the index maintenance log for these tables will not be logged, the standby database will not be able to replay index maintenance operations , causing these indexes to become invalid on 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 uses the database configuration parameter Indexrec to specify when to check for and rebuild invalid indexes. The INDEXREC parameter has three optional values:

• RESTART:DB2 will check and rebuild the invalid index when explicitly or implicitly restarting the database (RESTART).
• ACCESS:DB2 will be rebuilt when the invalid index is accessed for the first time.
• SYSTEM: Use the Database Manager to configure the value of the Indexrec parameter (DB Manager configuration).

In the above configuration instance, we set the value of Indexrec to restart, and the standby database checks and re-constructs all invalid indexes when it takes over as the new primary database.

Restrictions on the use of DB2 Hadr

• Only DB2 UDB Enterprise Server Edition (ESE) supports HADR, but Hadr does not support partitioned databases (database partitioning FEATURE,DPF).
• The primary and standby databases must be running on the same operating system version, and the version of DB2 UDB must be the same, unless the software upgrade process is short-lived.
• The bit size of the primary and standby databases must be the same (32-bit or 64-bit).
• Backup operation cannot be performed on the standby database
• The standby database cannot be accessed and the client program cannot connect to the standby database.
• The day-to-archive operation 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 circular logs
• HADR does not replicate database configuration parameters, shared libraries, DLLs, UDFs, or stored procedures

Conclusion

You can also use specialized HA software to achieve high availability and disaster recovery, but these HA software is generally expensive, and DB2 Hadr is included in DB2 ESE, no additional cost, and DB2 HADR configuration and management is simple, and the function is very good, So I always like to recommend it to customers.

(RPM) DB2 HADR