# Fung's DBA World

## DBA knowledge,standing on the shoulders of giants.

March 09, 2016

HADR is an abbrevation of High Avaiablity Disaster Recovery, as it’s names implies, HADR is a software solution providing high avaiablity and disaster recovery for databases, expecially for 24*7 mission critical systems. The technology can be classified as a replication solution. Basically, it replicates data by sending and replaying logs from a source database called primary database to a target database called standby database. HADR is currently avaliable for single-partitioned databases only.

### 1. Introduction

#### 1.1 Requirements

• OS should be the same version including patches. If rolling update happening, you can violate this rules for a short time
• DB2 version and the level must be identical on both servers, including the bit size. (32bit or 64bit)
• Nerwork interface must be available on both server
• Bufferpool size should be the same
• The database name should be identical, that means they must be in different instances if they reside in a same server
• Tablespace must be identical
• The log file space also should be the same on both server
• The system clock must be sychronized on both servers
• #### 1.2 Drawback of HADR

• Backup operations are not supported on standby databases
• Not support infinite logging
• Not support no logging transaction
• Not support for DPF
• Not support in DB2 pureScale environments (v10.5 support)
• #### 1.3 What HADR can do

• Read on standby (supported db2 v9.7.1 and above)
• Read on standby can enable you to run read-only operations on standby.
• Rolling update without any downtime for running applications
• Enable you rolling update the database with minimal downtime or zero downtime when updating a fix pack version, but not supported upgrade from major version to a high major version, for example, it’s supported update from 9.7.5 to 9.7.10, but not supported upgrade from 9.7 to 10.1. The main reason that HADR rolling update cannot cross major version boundary is that DB2 transaction logs from the new release may not be compatible with the old release, but HADR requires log compatibility on primary and standby.
• Delayed reply, new feature in 10.1
• This feature helps prevent data loss due to errant transactions. When standby server set the hadr_replay_delay database configuration parameter,it will keep standby database at a point in time that is earlier than primary database, if someone deleted some database by accidentally in primary, because standby didn’t replay this errant transaction, so you can only copy the deleted data from standby to primary, or just take over in standby as primary.
• Log Spooling, new feature in 10.1
• This feature allows transactions on primary to make progress without having to wait for the log replay on the standby. When this feature is enabled, log data sent by the primary is spooled, or written, to disk on the standby, and that log data is later read by log replay.
• Mutilple standby, new feature in 10.1
• Before 10.1, HADR only supported one standby database, but from 10.1 and above, it’s supported multiple standby database. For example, you can deploy a standby for delayed replying, and another standby for normal purpose.

### 2. Setup HADR with command line

Summary of testing information:

#### 2.1 Prepare for the environment

If it’s a totally new build env, you can install and create DB2 database as Install DB2 in Linux. As in standby server, only install software and create instance, no need to create database. Also you need an identical env as primary, so do not forget to create necessery directories in standby server, set permissions on the new directories.

#### 2.2 Setup the requirement of primary

• turn on the archival log mode
• Set the parameter LOGINDEXBUILD to ON so that index creation, recreation, or reorgnization operations are logged. If the parameter is set to OFF, then there’s not enough logging information for building the indexes on the standby, therefore, the new indexes created or rebuilt are marked as invalid on the standby. Also will increase time consume when standby activate because of rebuilding the invalid indexes.

• configure INDEXREC parameater
• This parameter controls the rebuild of invalid indexes on database startup, in HADR, this parameter should be set to RESTART on both servers.

#### 2.3 Take an offline backup of primary

Take an offline backup and send the backup image to the standby, restore to rollforward pending status, DO NOT issue rollforward command. Because standby database needs in rollforward pending state for replaying logs.

#### 2.5 Configure database parameters of HADR on standby

Be noticed, the value for hadr_local_svc on the primary or standby database systems cannot be the same as the value of svcename or svcename +1 on their corresponding hosts. For instance,my instance SVCENAME is 50000, then you cannot use 50000 or 50001 for the HADR service port.

#### 2.6 Start HADR on primary and standby

Recommend startup standby first.

If maintenance tasks hanppened, such as OS upgrade, fix pack need to restart the HADR, do not issue any db2 stop hadr on db command at any host.

• ### 3. RoS in HADR

From DB2 version v9.7.1, standby database is supported enable read on standby feature, this feature is enable query statements with UR isolation level in standby while log reply is happening simultaneously.

Let’s start HADR and have a test.

In primary, we create a new table, and insert some records into the table, see if it can be queried or not in standby.

Finding the result of standby server.

There are till some restrictions on RoS, standby database only support UR isolation level, Automatic Client Reroute not supported in RoS, when DDL replaying on standby, user cannot access to the standby in RoS, also with some maintenance tasks such as runstats, reorg, so, it’s recommended when doing maintenance tasks, choose a maintenance window to accomplish it.

### 4. Planned takeover

Takeover also call switch roles, when planning some maintenance tasks in primary server, to minimize the downtime, standby can takeover as a primary role. Switching roles only be avaliable when databases are in peer state. And do not forget to rerouter the clients either manually or by ACR after takeover.

If the state is in peer, we can takeover now.

When the maintenance jobs are done, you can switch over the roles.
Besides switch over, there’s a takeover called takeover by force, it means failover, if the primary not functional, you should use takeover by force. When failover happened, it means the HADR not functional anymore, you need re-build the HADR by backup image.

You can monitor HADR status by db2pd or db2 get snapshot command.

### 6. ACR

Automatic Client Reroute is a feature that enables a DB2 client to recover from a loss of connection to the DB2 server by rerouting the connection to an alternate server.This automatic connection rerouting occurs automatically.

#### 6.3 Running query in client while takeover occur

Let the standby takeover as primary, do not disconnect client’s connection by manually.

You cannot use the automatic client reroute (ACR) if you enable the read on standby feature.

### 7. New feature on HADR examples

#### 7.1 Delayed reply and log spooling

The database configuration parameter HADR_REPLAY_DELAY define the amount time of delayed replay in seconds (this parameter should only be configured in standby), value zero means turn off this feature, which is the default value. Log delayed replay is depend on the following rules, it’s important to sychronize the system clock between primary and standby system.

It’s recommended to configure log spooling feature when enabling delayed replay. For the convience of query on the standby, I also enable the RoS on the standby.
Restrictions on delayed relay:

• Only support superasync synchronization mode
• Not support take over while enable this feature
• #### 7.2 examples for delayed reply and log spooling

Modify sychronization mode, the database configuration parameter HADR_SYNCMODE is not dynamic, every change to the sychronization mode requires a database restart for both primary and standby databases.

Enable RoS on standby:

Enable delayed replay feature:

Now the HADR status should be catchup:

Create a test table and insert one rows in primary:

Even if we enabled RoS on standby now, but because of RoS restrictions on DDL, when replaying DDL happened on standby, cannot access to the standby,

Trying to connect in standby 10 mins later:

Now we insert another rows and delete first rows in primary, and query the table from standby:

What happened? The deleted data still in the standby with inserted data? If delayed replay works, we should only see the first rows in the table. Actually, in the DB2, not all operations in the log recorded the timestamp, the automatic committed operation contains 2 operations: insert and commit, when insert operation is transferred to standby, this insert operation is replayed first,but not committed.So you can query this row by UR isolation. Next, I stop the HADR on the standby, and rollforward it as complete:

The query result is what we expects, insert and delete operations are all delayed replay, so the only data we can see in the table is the original rows. but remember, if you issue rollforward command to the standby database, you should rebuild the HADR via backup image. Never do that in a production environment.

### 8. Conclusions

The mechanism of HADR is primary database sends the log contents to be replayed on standby. There are 2 special euds processes to handle the log transmission,db2hadrp on primary and db2hadrs on standby.

There are 2 important parameters:
HADR_TIMEOUT: Specified the amount of time (in seconds) to wait before HADR considers the communication lost between database pairs.If an HADR database does not receive any communication from its partner database for longer than the length of time specified by the hadr_timeout database configuration parameter, then the database concludes that the connection with the partner database is lost.
HADR_PEER_WINDOW: Specified the amount of time (in seconds) in which the primary database suspends a transaction after the database pairs have entered disconnect state.

Next topic will be how to rolling update (fix pack) in HADR.

EOF