High Availability Disaster Recovery (HADR)

April 8, 2005

High Availability Disaster Recovery (HADR)

1. How to Set Up and Initialize HADR

Consider two test instances:
Primary Site:
Host Name: ABC.COM
UDB V8.2 Instance: db2inst1
Database: HADRTEST
Standby Site:
Host Name XYZ.COM
UDB V8.2 Instance; db2inst1
Database: HADRTEST

Step by step:

1. Make sure that file systems, table spaces and the containers are identical in Primary and Standby databases.

2. Create Standby database on Standby server:
Create db hadrtest on path

3. Make backup on Primary database.
backup db hadrtest to path

4. Restore the backup from step3 into Standby database.
Restore db hadrtest from path replace history file

Do not issue ROLLFORWARD. (though it is an offline backup, it will be in rollforward pending state)

5. Issue update db cfg to set HADR configuration parameters in Primary databases:

o HADR_LOCAL_SVC: local_port_number
o HADR_REMOTE_SVC: remote_port_number
o HADR_REMOTE_INST: db2inst1

in Standby database:
o HADR_LOCAL_SVC: local_port_number
o HADR_REMOTE_SVC: remote_port_number
o HADR_REMOTE_INST: db2inst1

6. Set the DB2_LOAD_COPY_NO_OVERRIDE registry variable on the primary database.

7. Set ALTERNATE SERVER on both Primary and Standby instances:

On Primary instance abc.com:
Update alternate server for database HADRTEST using hostname xyz.com port remote_port_number

On Standby instance xyz.com:
Update alternate server for database HADRTEST using hostname abc.com port remote_port_number

2. How to Fail Over
Perform fail over when current primary database fails or is not available.
1. Stop DB2 on failed Primary site completely.
2. Issue the TAKEOVER HADR (with FORCE option, if necessary) command on the Standby database
Now, the Standby database becomes Primary database.
Client will automatically reroute to new primary database. Application server may need to restart to establish connection to new server.
After Primary database is fixed and udb instance is up, issue START HADR to make it a standby server
If you want to take back the primary role, issue TAKEOVER HADR command.
Switching Database Roles
This is when both Primary and Standby are active. After the switching, Primary DB becomes Standby, and Standby becomes Primary.

1. Issue the TAKEOVER HADR command without the BY FORCE option on the standby database:

Automatic client reroute
There are some limitations with use of the automatic client reroute feature:
Automatic client reroute is only supported when the communications protocol used for connecting to the DB2(R) Universal Database (DB2 UDB) server, or to the DB2 Connect(TM) server, is TCP/IP. This means that if the connection is using a different protocol other than TCP/IP, the automatic client reroute feature will not be enabled. Even if DB2 UDB is setup for a loopback, TCP/IP communications protocol must be used in order accommodate the automatic client reroute feature.
If the connection is reestablished to the alternate server location, any new connection to the same database alias will be connected to the alternate server location. If you want any new connection to be established, to the original location in case the problem on the original location is fixed, there are a couple of options from which to choose:

You need to take the alternate server offline and allow the connections to fail back over to the original server. (This assumes that the original server has been cataloged using the UPDATE ALTERNATE SERVER command such that it is set to be the alternate location for the alternate server.)
You could catalog a new database alias to be used by the new connections.
You could uncatalog the database entry and re-catalog it again

Hardware and Operating System Requirements

· Use identical host computers for the HADR primary and standby databases. That is, they should be from the same vendor and have the same architecture.
· OS version should be same, including patches. You can violate this rule for a short time during a rolling upgrade, but take extreme caution.

DB2 UDB Requirement

· Primary and Standby databases must be identical.
· During rolling upgrades, the database version of the standby database may be later than the primary database for a short time. The DB2 UDB version of the primary database can never be later than the standby database version.
· Table spaces and their containers must be identical on the primary and standby databases.
· Primary and standby databases must have the same amount of memory.


· On the standby database there is always a connection to the database (replay Application). We cannot force this connection at any time. Also because of this connection, the database on both primary and secondary is always online. If we need to do any operation for which no one should be connected, we need to deactivate the standby database.
· The stored procedure created in the primary is also copied on to the secondary and will be ready to execute after takeover without compiling/promoting again.
· Changes to database parameters on primary server are not reflected on the standby.
· During the failover, the client connections and session information is lost.

How to use load in HADR

· We have to do load with copy yes option
· If an NFS mount is possible between primary and standby server, choose that file system in the copy yes option and do the load
· If an NFS mount is not possible, the following steps have to be done in the same sequence
o Deactivate the standby database
o Stop HADR on standby database
o Perform load on the primary database with copy yes option. A file will be created with name of the database and a timestamp.
o FTP the file to the standby server to same location as mentioned in the previous load operation
o Start HADR on standby database as standby (In db2diag.log, it is immediately noted that the replay has happened)
o Take a snapshot of database and make sure that the HADR state is set to peer. Once it is set to peer, the file can be deleted.

How to use BLOB/CLOB in HADR

· Insert, update, delete operations performed on a table with CLOBS/BLOBS is not replicated on the other server, unless the BLOB/CLOB is logged. The table has to be defined such that the DML operations performed on these columns is always logged.


HADR is NOT supported on multiple database partitions.
Reads on the standby database are not supported. Clients cannot connect to the standby database.
Log archiving can only be performed by the current primary database.
Backup operations are not supported on the standby database.
Non-logged operations, such as changes to database configuration parameters and to the recovery history file, are not replicated to the standby database.
Load operations with the COPY NO option specified are not supported. (COPY NO is default on databases with archival logging). If we choose COPY NO or don’t choose anything then the tablespace on the primary will be in the backup pending state and tablespace on the standby database will be in restore pending and HADR doesn’t permit restoring a tablespace on either primary or standby databases.
Tablespace level restore is not supported on both primary and standby
Use of Data Links is not supported.


posted in DB2 by Visu

Follow comments via the RSS Feed | Leave a comment | Trackback URL

2 Comments to "High Availability Disaster Recovery (HADR)"

  1. Daniel wrote:


    From time to time I find a blog worth reading, and this is one of them, I really enjoy what you have been posting lately.

    I’m working on a data backup site. It is not easy to find good content for data backup websites but I try to do my best…I also have a blog there

  2. Webincome wrote:

    Good work I liked your posting about mysql
    You can find a fantastic database backup tool at http://www.databasebackup.co.uk
    backup your database before it is to late……

Leave Your Comment

Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org