High availability and disaster recovery

-

 

High availability and disaster recovery (HADR) is a Db2 database replication feature that provides a high availability solution for both partial and complete site failures. HADR protects against data loss by replicating data changes from a source database, called the primary database, to one or more target databases, called the multiple standby databases.

In an HADR environment, one database instance acts as the current primary database that is used by the applications. Synchronization with the standby databases occurs by rolling forward transaction log data generated on the primary database which is then shipped to the standby databases.

With HADR, you can choose different levels of protection for potential data loss by specifying one of the following four synchronization modes:

SYNC: This mode (synchronous) provides the greatest protection against transaction loss. This option, however, results in the longest transaction response time among the four modes. In this mode, log writes are considered successful only when logs have been written to log files on the primary database and when the primary database has received acknowledgement from the standby database that the logs have also been written to log files on the standby database. The log data is guaranteed to be stored at both sites.

NEARSYNC: While this mode (near synchronous) has a shorter transaction response time than synchronous mode, it also provides slightly less protection against transaction loss. In this mode, log writes are only considered successful when the log records have been written to the log files on the primary database and when the primary database has received acknowledgement from the standby system that the logs have also been written to main memory on the standby system. Loss of data only occurs if both sites fail simultaneously and if the target site has not transferred all the log data that it has received to volatile storage.

ASYNC: Compared with the SYNC and NEARSYNC modes, the ASYNC mode (asynchronous) results in shorter transaction response times, but greater transaction losses are possible if the primary database fails. In the ASYNC mode, log writes are only considered successful when the log records have been written to the log files on the primary database and have been delivered to the TCP layer of the primary system's host machine. Because the primary system does not wait for acknowledgement from the standby system, transactions might be considered committed when they are still on their way to the standby database.

SUPERASYNC: This mode (super asynchronous) has the shortest transaction response time but also has the highest probability of transaction losses in the event that the primary system fails. This mode is useful when you do not want transactions to be blocked or experience elongated response times due to network interruptions or congestion. In this mode, the HADR pair can never be in peer state or disconnected peer state. The log writes are considered successful as soon as the log records have been written to the log files on the primary database. Because the primary database does not wait for acknowledgement from the standby database, transactions are considered committed, irrespective of the state of the replication of the transaction.

Setting up an HADR multiple standby environment:

 

The process of setting up an HADR environment is straightforward. After ensuring the systems you intend to use as the primary and standby servers are identical and that a TCP/IP connection exists between them, you simply perform the following tasks in order:

 Determine the host name, host IP address, and the service name or port number for both the primary and the standby database servers.

Create the principal standby and auxiliary standby databases by restoring a backup image or initializing a split mirror copy of the database that is to serve as the primary database.

Set the HADR configuration parameters on both the primary, principal standby, and auxiliary standby databases.

After the standby databases have been created, you must set the HADR configuration parameters which are shown in the HADR-specific parameters section.

Start configuring the standby databases and then the primary database by using the following commands. On the principal standby, the HADR_TARGET_LIST command's first entry is always the primary server and associated port number:

 

UPDATE DB CFG FOR sample USING

   HADR_LOCAL_HOST   HADR2.ibm.com

   HADR_LOCAL_SVC    55002

   HADR_REMOTE_HOST  HADR1.ibm.com

   HADR_REMOTE_SVC   55001

   HADR_REMOTE_INST  db2inst1

   HADR_TIMEOUT      120

   HADR_SYNCMODE     NEARSYNC

   HADR_PEER_WINDOW  0

   HADR_TARGET_LIST  HADR1.ibm.com:55001|HADR3.ibm.com:55003|HADR4.ibm.com:55004;

On the first auxiliary standby, the HADR_TARGET_LIST command's first entry is always the principal standby, then the primary, and then the other auxiliary standby (if there is one):

 

UPDATE DB CFG FOR sample USING

   HADR_LOCAL_HOST HADR3.ibm.com

   HADR_LOCAL_SVC 55003

   HADR_REMOTE_HOST HADR2.ibm.com

   HADR_REMOTE_SVC 55002

   HADR_REMOTE_INST db2inst2

   HADR_TIMEOUT 120

   HADR_SYNCMODE SUPERASYNC

   HADR_PEER_WINDOW 0

   HADR_TARGET_LIST HADR2.ibm.com:55002|HADR1.ibm.com:55001|HADR4.ibm.com:55004;

 

UPDATE DB CFG FOR sample USING

   HADR_LOCAL_HOST HADR4.ibm.com

   HADR_LOCAL_SVC 55004

   HADR_REMOTE_HOST HADR2.ibm.com

   HADR_REMOTE_SVC 55002

   HADR_REMOTE_INST db2inst2

   HADR_TIMEOUT 120

   HADR_SYNCMODE SUPERASYNC

   HADR_PEER_WINDOW 0

   HADR_TARGET_LIST HADR2.ibm.com:55002|HADR1.ibm.com:55001|HADR3.ibm.com:55003;

On the primary database, the HADR_TARGET_LIST command's first entry is always the principal standby and then the auxiliary standbys:

 

UPDATE DB CFG FOR sample USING

   HADR_LOCAL_HOST   HADR1.ibm.com

   HADR_LOCAL_SVC    55001

   HADR_REMOTE_HOST  HADR2.ibm.com

   HADR_REMOTE_SVC   55002

   HADR_REMOTE_INST  db2inst2

   HADR_TIMEOUT      120

   HADR_SYNCMODE     NEARSYNC

   HADR_PEER_WINDOW  0

   HADR_TARGET_LIST  HADR2.ibm.com:55002|HADR3.ibm.com:55003|HADR4.ibm.com:55004;

Connect to the standby instances and start HADR on the principal and auxiliary standby databases. To start HADR, you must execute the START HADR command as follows:

START HADR ON [DATABASE | DB] [DatabaseAlias]

<USER [UserName] <USING [Password]>>

 

AS [PRIMARY <BY FORCE> | SECONDARY]

Let's explore the following commands from the preceding code:

 DatabaseAlias identifies the alias assigned to the database to start HADR

UserName identifies the name assigned to the user who is starting HADR

Password identifies the password of the user who is starting HADR

For example, if you want to start HADR on a database named SAMPLE and indicate that it is to act as a standby database, you could do so by executing a START HADR command that looks as follows:

 

START HADR ON DATABASE sample AS STANDBY;

Connect to the primary instance and start HADR on the primary database. Execute a START HADR command as follows:

START HADR ON DATABASE sample AS PRIMARY;

SUPERASYNC is the only supported synchronization mode for auxiliary HADR standby databases. You can use the TAKEOVER HADR ON DATABASE command to perform a role switch between primary and standby HADR databases. After a successful role switch, the HADR_STATE command should be PEER. Multiple standby setup is not supported in the pureScale environment.

You can update the value of the hadr_target_list parameter online. However, there are restrictions on modification when HADR is active. You cannot change the principal standby of the primary without first stopping HADR on the primary. You cannot remove a standby from the list if it is connected to the primary. To disconnect a standby, deactivate it. Then, you can remove it from the primary's target list.

You cannot dynamically update the hadr_target_list configuration parameter for a standby unless you enabled the HADR reads on the standby feature. You cannot remove the primary database from the target list of a standby if the standby is connected to the primary. The target list must contain IP addresses that are either IPv4 or IPv6, but not a combination of the two. You cannot dynamically update the hadr_target_list configuration parameter in a Db2 pureScale environment.


Comments

Post a Comment

Popular posts from this blog

Db2 export command example using file format (del , ixf)

-
You want to transfer data from a db2 database table to another db2 database or within the same database, one of the way is to use db2 export / import command. Using db2 export / import you can move your data to excel also.   DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of  del - delimited , wsf - worksheet format , ixf - Integration Exchange Format,asc.   The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data ...
Read more

How to fix DB2 Tablespace OFFLINE state issue?

-
Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...
Read more

Phases of a load operation

-
A complete load operation consists of five distinct phases; they are as follows: Analyze phase Load phase Build phase Delete phase Index copy phase Analyze phase:  This phase of the Load process is only utilized when a column-organized table is being loaded and the column compression dictionaries need to be built. This happens during a LOAD REPLACE operation, a LOAD REPLACE RESETDICTIONARY operation, a LOAD REPLACE RESETDICTIONARYONLY operation, or a LOAD INSERT operation (if the column-organized table is empty). For column-organized tables, this phase is followed by the load, build, and delete phases. The following diagram showcases the steps involved in the analyze phase. The analyze phase is invoked if you are loading data for the first time into a column-organized BLU acceleration table using LOAD INSERT, or running a LOAD REPLACE with RESETDICTIONARY or RESETDICTIONARYONLY clause. Load phase:  During this phase, data is loaded into the table, and index keys and table stat...
Read more