Introduction
IBM DB2 comes and integrates a multitude of high-availability solutions, that can employ and increase the availability of databases. There are software high availability solutions, such as SQL Replication, Q Replication, HADR, DB2 ACS, and IBM TSA, and hardware-based solutions, such as IBM PPRC, HACMP, FlashCopy, or hybrid such as the new clustering solution provided by DB2 pureScale technology, covered in Chapter 14, IBM pureScale Technology and DB2. Obviously, we can choose to implement one solution or use a virtually unlimited number of combinations to ensure that we are highly protected from any type of disaster. In the following recipes, we will cover how to set up and DB2 fault monitor as high availability solutions as a high availability solution.
HADR is a high availability software solution provided by IBM for DB2 database protection in the case of disaster or critical database failure. HADR is an abbreviation for High Availability Disaster Recovery. The technology itself can be classified as a replication solution. Basically, this technology replicates data by sending and replaying logs from a source database to a destination database. The source database, by convention, is called the primary database; the destination database is called the standby database
Some important benefits of HADR:
- Transparent takeover (switchover) or takeover by force (failover) for clients connected
- Automatic client reroute capabilities
- It is a very fast method in terms of recoverability
- It has a negligible impact on transaction performance
- The cost is low compared with a hardware replication solution
Some restrictions with using HADR:
- Backup operations are not supported on standby databases/li>
- HADR cannot be implemented with multipartitioned databases
- The primary and standby database must be run on the same operating system (same bit size) and the same version of the DB2 database system
- The system clock must be synchronized on both primary and standby servers
Operations replicated using HADR:
- Data definition language (DDL)
- Data manipulation language (DML)
- Buffer pool operations
- Table space operations
- Online reorganization
- Offline reorganization
- Metadata for stored procedures and user-defined functions
Operations that do not replicate using HADR:
- Tables created with the NOT LOGGED INITIALLY option
- Non-logged LOB columns are not replicated
- Updates to database configuration
- Database configuration and database manager configuration parameters
- Objects external to the database-related objects and library files
- The recovery history file (db2rhist.asc) and changes made to it
Setting up HADR by using the command line
Setting up HADR is straightforward. You can use a variety of methods to set up HADR, using the command line or Control Center, and IBM Optim Database Administrator HADR setup wizards. In the following recipe, we will set up HADR using the command line.
Getting ready
In this recipe, nodedb21 will be used for the initial primary database, and nodedb22 for the initial standby database. We use the term initially, because in the following recipes, we will initiate takeover and takeover by force operations, and the databases will exchange and change their roles. All operations will be conducted on the non-partitioned NAV database, under instance db2inst1 on nodedb21, and db2inst1 on nodedb22.
How to do it…
To set up a HADR configuration, we will use the following steps:
- Install IBM DB2 9.7 ESE in location /opt/ibm/db2/ V9.7_01, on nodedb22
- Creating additional directories for log archiving, backup, and mirror log locations, on both nodes
- Setting proper permissions on new directories
- Configuring log archiving and log mirroring
- Configuring the LOGINDEXBUILD and INDEXREC parameters
- Backing up the primary database
- Copying primary database backup to nodedb22
- Setting up HADR communication ports
- Configuring HADR parameters on both databases
- Initiating HADR on the standby database
- Initiating HADR on the primary database
Install IBM DB2 ESE on nodedb22
Install IBM DB2 9.7 Enterprise Server Edition in location /opt/ibm/db2/V9.7_01, on nodedb22; create users db2inst1 and db2fenc1, and instance db2inst1, during installation.
Creating additional directories for table space containers, archive logs, backup, and mirror logs
- Create one directory for table space containers of the NAV application on nodedb22:
[root@nodedb22 ~]# mkdir -p /data/db2/db2inst1/nav
[root@nodedb22 ~]# - Create directories for the archive logs location on both servers:
[root@nodedb22 ~]# mkdir -p /data/db2/db2inst1/logarchives
[root@nodedb22 ~]#
[root@nodedb21 ~]# mkdir -p /data/db2/db2inst1/logarchives
[root@nodedb22 ~]# - Create directories for the database backup location on both servers:
[root@nodedb21 ~]# mkdir -p /data/db2/db2inst1/backup
[root@nodedb21 ~]#
[root@nodedb21 ~]# mkdir -p /data/db2/db2inst1/backup
[root@nodedb21 ~]# - Create directories for the mirror log location on both servers:
[root@nodedb21 ~]# mkdir -p /data/db2/db2inst1/mirrorlogs
[root@nodedb21 ~]#
[root@nodedb22 ~]# mkdir -p /data/db2/db2inst1/mirrorlogs
[root@nodedb22~]#
This is just an example; usually, the mirror logs should be stored in a safe location. If it is possible use an NFS mount exported from another server.
Setting permissions on the new directories
- Set db2inst1 as owner for the directories where we will configure archive log and log mirrors, and restore the NAV application table space containers on both servers:
[root@nodedb21 ~]# chown –R db2inst1:db2iadm1 /data/db2/db2inst1
[root@nodedb21 ~]#
[root@nodedb22 ~]# chown –R db2inst1:db2iadm1 /data/db2/db2inst1
[root@nodedb22 ~]#
Configuring archive log and mirror log locations
- Connect to the NAV database:
[db2inst1@nodedb21 ~]$ db2 “CONNECT TO NAV”
Database Connection Information
Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = DB2INST1
Local database alias = NAV - Quiesce the NAV database:
[db2inst1@nodedb21 ~]$ db2 “QUIESCE DATABASE IMMEDIATE”
DB20000I The QUIESCE DATABASE command completed successfully.
[db2inst1@nodedb21 ~]$ - Set the log archive location:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DB CFG FOR NAV USING
logarchmeth1 ‘DISK:/data/db2/db2inst1/logarchives'”
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 ~]$ - Set the number of primary logs; usually, in a HADR configuration, it should be set to a greater value than in a normal database:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DB CFG FOR NAV USING
LOGPRIMARY 20″
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 ~]$ - Set the number of secondary logs:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DB CFG FOR NAV USING
LOGSECOND 5″
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 ~]$ - Set log file size; it is also recommended to be bigger than in a normal database:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DB CFG FOR NAV USING
LOGFILSIZ 2048 “
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 - Set a mirror log location in the case where the primary log’s host fails (these logs will be needed for replaying on the standby database):
[db2inst1@nodedb21 ~]$ db2 “UPDATE DATABASE CONFIGURATION USING
MIRRORLOGPATH /data/db2/db2inst1/mirrorlogfiles”
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 ~]$ - Set log buffer size. Use a log buffer size on both the primary and standby databases, bigger than in a normal database, to overcome log buffer full events:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DB CFG FOR NAV USING
LOGBUFSZ 1024 “
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
db2inst1@nodedb21 ~]$ - Set log buffer size. Use a log buffer size on both the primary and standby databases, bigger than in a normal database, to overcome log buffer full events:
[db2inst1@nodedb21 ~]$ db2 “UNQUIESCE DATABASE”
DB20000I The UNQUIESCE DATABASE command completed successfully.
[db2inst1@nodedb21 ~]$ - Unquiesce the NAV database:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DATABASE CONFIGURATION FOR NAV
USING LOGINDEXBUILD ON “
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 ~]$LOGBUFSZ should be correlated with network tuning; try to set TCP tunables (receive and send buffer) to appropriate values.
Configuring LOGINDEXBUILD and INDEXREC parameters
- The LOGINDEXBUILD parameter specifies if operations as create index, rebuild index, and reorganize table generates log when it has a value of ON or not if it is OFF, rebuild or table reorganization; usually, this parameter in a HADR configuration should be configured to ON. If you plan to use the standby database for reporting, then it is mandatory to set the parameter to ON.
- If it is set to OFF, then there is not enough log information for building the indexes on the standby database. Therefore, the new indexes created or rebuilt in a HADR configuration are marked as invalid on the standby database.
- In case you have slow network bandwidth, you can set it to OFF, but the amount of time needed to activate the standby database will increase considerably, because the invalid indexes have to be rebuilt. You can also control index logging at table level by setting the table option LOG INDEX BUILD to ON or OFF.
[db2inst1@nodedb21 ~]$ db2 “UPDATE DATABASE CONFIGURATION FOR NAV
USING INDEXREC RESTART”
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
[db2inst1@nodedb21 ~]$ - In case you have slow network bandwidth, you can set it to OFF, but the amount of time needed to activate the standby database will increase considerably, because the invalid indexes have to be rebuilt. You can also control index logging at table level by setting the table option LOG INDEX BUILD to ON or OFF.
[db2inst1@nodedb21 ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@nodedb21 ~]$ db2 “BACKUP DATABASE NAV TO “/data/backup”
COMPRESS”
Backup successful. The timestamp for this backup image is :
20110707150659
[db2inst1@nodedb21 ~]$ - The parameter INDEXREC control s the rebuild of invalid indexes on database startup. In HADR configurations, it should be set to RESTART, on both databases.
[db2inst1@nodedb21 ~]$ scp /data/db2/db2inst1/backup/
NAV.0.db2inst1.NODE0000.CATN0000.
20110707150659.001 nodedb22:/data/db2/db2inst1/backup
db2inst1@nodedb22’s password:
NAV.0.db2inst1.NODE0000.CATN0000.20110707150659.001
[db2inst1@nodedb21 ~]$
Backing up the primary database
- Back up the database with the compress option, to save space; it is useful to compress the backup piece, especially when you have a very large database:
[db2inst1@nodedb22 ~]$ db2 “RESTORE DATABASE NAV FROM /data/db2/
db2inst1/backup TAKEN AT 20110707150659 REPLACE
HISTORY FILE”
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@nodedb22 ~]$
Copying the database backup to nodedb22
- Copy the database backup to location /data/db2/db2inst1/backup on nodedb22:
DB2_HADR_NAV1 55006/tcp
DB2_HADR_NAV2 55007/tcp
Restoring the database NAV on nodedb22
- Restore the database on the standby location:
[db2inst1@nodedb21 ~]$ db2 “UPDATE DATABASE CONFIGURATION
FOR NAV USING HADR_LOCAL_HOST nodedb21″
DB20000I The UPDATE DATABASE CONFIGURATION command
completed successfully.
[db2inst1@nodedb21 ~]$
Setting up HADR communication ports
- Add the following two entries to /etc/services, on both locations:
DB2_HADR_NAV1 55006/tcp
DB2_HADR_NAV2 55007/tcp
