High Availability Series: Part 1 - DB2 HADR Setup

-
I am planning to post a set of articles covering various DR options provided by Oracle, DB2 and Progress OpenEdge database. This will include setup guides for Oracle DataGuard, DB2 HADR and OpenEdge Fathom Replication.

Series will be started with basic setup of each of these technologies, and then these will be reconfigured, changed as per the requirements to demonstrate different recovery scenarios.

First I will setup a simple HADR system using Virtualbox. At the start only basic setup will be configured and will be changed/ reconfigured when testing different DR situations.

Virtualbox Setup

Install OS and db2 (I am using Oracle linux 5.6 (32) and db2 10.1 (32)) into single VM.

Clone or manually create another duplicate server. 

Make sure to configure network connectivity between two machines. Use different hosts names. In this example my primary would be "db21" and standby is "db22"

DB2 Setup

step 1 – Create two db2 instances
Primary – 
 db2icrt –u db2fenc 1 db2inst1
Standby – 
 db2icrt –u db2fenc2 db2inst2

Step 2 - Create sample database on primary server
I have created SAMPLE database by just running - ./db2sampl

Step 3 –Setup database for remote connectivity,
set DB2COMM registry variable to tcpip.
 db2set DB2COMM=tcpip
Update database manager configuration “SVCENAME” using the service defined in /etc/services.
 db2 "update dbm cfg using SVCENAME DB2_db2inst1"
After setting these OS and DB parameter my primary and secondary databases are configured as follows.
Primary
Secondary
IP address
192.168.0.184
192.168.0.106
Hostname
db21
db22
Port/service
DB2_db2inst1/60004
DB2_db2inst2/60008
Instance name/user
db2inst1
db2inst2
Database
Sample
-

Step 4 - Enable primary database for archive logging.
Set LOGRETAIN to on.
 db2 "update dbm cgg using logretain on"
Step 5 -  Enable logged index rebuild and rebuild to happen at HADR log apply.
Set LOGINDEXBUILD to on.
 db2 "update db cfg for SAMPLE using LOGINDEXBUILD ON"
Set INDEXREC to RESTART.
 db2 "update db cfg for SAMPLE using INDEXREC RESTART"
Step 6 - Backup primary database and restore it in standby server
You can use either online or offline backup. I ll be using an online backup. As db2inst1 user on Primary server,
 db2 "backup database sample online to /home/db2inst1"
Since I have created standby server as a clone of the primary my restoration is straight forward as well.
SCP the backup file from 0.184:/home/db2inst1 to 0.106:/home/db2inst2. On Standby as db2inst2 run,
 db2 "restore database sample from /home/db2inst2"
If there are any difference between the servers it is required to address them during the restoration (redirected restore - example http://dbtechno.blogspot.com/2012/12/part1-db2-restorerecover-online-backup.html ). Also it should only be the restore, no roll-forwarding is done for standby db.

Step 7 - Add HADR configuration on Pimary and Standby

On primary add below parameters,
   db2 update alternate server for database sample using hostname db22 port 60008  
   db2 update db cfg for SAMPLE using HADR_LOCAL_HOST 192.168.0.184  
   db2 update db cfg for SAMPLE using HADR_LOCAL_SVC 60001  
   db2 update db cfg for SAMPLE using HADR_REMOTE_HOST 192.168.0.106  
   db2 update db cfg for SAMPLE using HADR_REMOTE_SVC 60002  
   db2 update db cfg for SAMPLE using HADR_REMOTE_INST db2inst2

Here HADR remote/local ports must be different from instance ports. Also there are many more HADR related parameters which are defaulted for this basic setup guide.

On Standby add below parameters, 
   db2 update alternate server for database sample using hostname db21 port 60004  
   db2 update db cfg for SAMPLE using HADR_LOCAL_HOST 192.168.0.106  
   db2 update db cfg for SAMPLE using HADR_LOCAL_SVC 60002  
   db2 update db cfg for SAMPLE using HADR_REMOTE_HOST 192.168.0.184  
   db2 update db cfg for SAMPLE using HADR_REMOTE_SVC 60001  
   db2 update db cfg for SAMPLE using HADR_REMOTE_INST db2inst1

That is it, Now your HADR setup is complete, and when you start HADR on both sides standby database should get synced from the production database.

Step 8 - Start/Monitor HADR on both sides.

First start HADR on standby side, as db2inst2 user on standby 
 db2 "start hadr on database sample as standby"
Then as db2inst1 user on primary, 
 db2 "start hadr on database sample as primary"
To monitor the HADR status use, 
 db2pd -d sample -hadr

Ok. So our basic HADR setup is completed. We will discuss more options, configurations and different failover scenarios later by using this base setup.


Comments

Post a Comment

Popular posts from this blog

ORA-16433: The database or pluggable database must be opened in read/write

-
Image
ORA-16433 is an error returned when a database cannot be opened. This can be a result of multiple conditions including, The inconsistency of the database Physical corruption Loss of dependent files or access issues etc.. More often than not the solution is to recover from available backups, but there can be situations where backups are not available, specially on UAT or Test setups. Although not the ideal scenario, in this type of situation you might want to open the databases even with inconsistency.  In Comes --> _ALLOW_RESETLOGS_CORRUPTION First, if you are in a position to use _ALLOW_RESETLOGS_CORRUPTION=TRUE, that means it has to be a critical moment, and when you come across more ora- errors while you are trying to reopen the databsae, that can be annoying. Note - _ALLOW_RESETLOGS_CORRUPTION=TRUE must only be used after Oracle support confirmation as a last resort. Also make sure to take cold backup before proceeding. Basic steps to be followed to resolve ORA 16433 af...
Read more

Oracle Multitenant - Create new service for PDB using DBMS_SERVICE

-
Image
When an Oracle PDB is created it starts up a default service with the PDB name. For direct connections to    PDB this service can be used. But there are situations where new service name is required in PDB level, common example is after a PDB restoration/clone, in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup. How to create a new PDB service on Oracle 12C Check the current services, here only the default service is running, SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NFACTORPDB READ WRITE NO SQL> select service_id, name, pdb from dba_Services; SERVICE_ID NAME PDB ---------- ---------------------------------------- ---------- 7 nfactorpdb NFACTORPDB SQL> ...
Read more

Wait for unread message on broadcast channel - Blocking Sessions

-
Image
What is Wait for unread message on broadcast channel wait event? Wait for unread message on broadcast channel is a wait event where source destination communication is involved and the database is waiting for a reply from remote party. Mostly this wait is seen related to data pump export (expdp) or import (impdp) operations. Although categorized in idle wait class "Wait for unread message on broadcast channel" can become the blocking session for some other sessions. Common causes for stuck sessions waiting on this wait event includes, Broken databases links, where remote database is not responding. Abnormally killed remote processes causing the sessions to wait. When a session is waiting on "Wait for unread message on broadcast channel" it can become a blocking session for dependent sessions. Most probably these sessions will be waiting on "library cache pin" or related concurrency wait events. The blocking_session column of (g)v$session view...
Read more