Solved: DB2 9.7 Disaster Recovery Set-up

Dear Experts,

One of our client wants to setup DB2 DR for production system.

System Configuration :

ERP Release : ERP6.0 SR3

OS : Suse Linux 11

DATABASE : DB6 9.07.0006

Method : Implementation of configuring log shipping using db2 database

RAM : 16 GB

we have decide will going to following method :

Install separate system and configure cron jobs for log shipping from Primary PRD to standby server and then apply it to database with cron jobs only.

Please suggest step by step process of above method.

If your answer is beneficial for me i will definitely give you points.

Thanks,

Rahul

SAP Managed Tags:
IBM Db2 for Linux, UNIX, and Windows

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Hello Rahul,

We recommend to use DB HADR that is much easier ,

Let me know you need any help, and how to do

Thanks

Sadiq

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Dear Sadiq,

Thanks for your reply. Actually our scenario is like Primary server (i.e., PRD) is at one location and standby server is other location, So it is possible to implement DB HADR above scenario.

If possible Kindly tell me the prerequisite of DB HADR.

Regards,

Rahul

Hi Rahul ,

HADR systems will be maintained in always two or more separate locations , As during a disaster ( Like earthquake , volcano, flood etc ) on a particular site entire data is not lost . The main prerequisite would be network connection between the two Disaster Recovery Sites

Thanks ,

Manu

Hello Rahul

Do the bellow steps you HADR done

Check and let us know if any issue

Description of Activity
Description of Activity
Full Database backup of Primary Database before the activity
Set the required database configuration parameters. Turn on archive logging and update the LOGRETAIN parameter: db2 update database configuration for <SID> using LOGRETAIN recovery Set the LOGINDEXBUILD parameter, so that index creation,re-creation, or reorganization operations are logged: db2 update database configuration for <SID> using LOGINDEXBUILD ON
Full Database backup of Primary Database after the parameter changes
Update the following fields in the services file on the primary system(ho-dbpr1)for HADR communication: – Service name: DB2_HADR_1 – Port number: 55001 – Service name: DB2_HADR_2 – Port number: 55002
Update the following fields in the services file on the standby system (dr-dbpr) for HADR communication: – Service name: DB2_HADR_1 – Port number: 55001 – Service name: DB2_HADR_2 – Port number: 55002
Restoration of Database on Standby Server by backup taken on step 3
Update the HADR database configuration parameters on the primary database(<SID>) ; db2 update db cfg for <SID> using HADR_LOCAL_HOST HO-DBPR1 db2 update db cfg for <SID> using HADR_LOCAL_SVC DB2_HADR_1 db2 update db cfg for <SID> using HADR_REMOTE_HOST DR-DBPR db2 update db cfg for <SID> using HADR_REMOTE_SVC DB2_HADR_2 db2 update db cfg for <SID> using HADR_REMOTE_INST DB2<SID> db2 update db cfg for <SID> using HADR_SYNCMODE NEARSYNC db2 update db cfg for <SID> using HADR_TIMEOUT 120 db2 update db cfg for <SID> using HADR_PEER_WINDOW 120 db2 connect to <SID> db2 quiesce database immediate force connections db2 unquiesce database db2 connect reset
Update the HADR database configuration parameters on the standby database(<SID>); db2 update db cfg for <SID> using HADR_LOCAL_HOST DR-DBPR db2 update db cfg for <SID> using HADR_LOCAL_SVC DB2_HADR_2 db2 update db cfg for <SID> using HADR_REMOTE_HOST HO-DBPR1 db2 update db cfg for <SID> using HADR_REMOTE_SVC DB2_HADR_1 db2 update db cfg for <SID> using HADR_REMOTE_INST DB2<SID> db2 update db cfg for <SID> using HADR_SYNCMODE NEARSYNC db2 update db cfg for <SID> using HADR_TIMEOUT 120 db2 update db cfg for <SID> using HADR_PEER_WINDOW 120
Start HADR on the standby database on dr-dbpr db2 deactivate database <SID> db2 start hadr on database <SID> as standby
Start HADR on the primary database on ho-dbpr1 db2 deactivate database <SID> db2 start hadr on database <SID> as primary

1 -> On the PRIMARY system (ho-dbpr1)

(a) Check that the alternate server has been set up.
db2 list db directory
(b) Check that the database configuration values.
db2 get db cfg for <SID>
(c) Get the snapshot report.
>db2 get snapshot for db on <SID>
(d) Check the entries in the services file.

2 --> On the STANDBY system (dr-dbpr)
(a) Check that the alternate server has been set up.
db2 list db directory
(b) Check that the database configuration values
db2 get db cfg for <SID>
(c) Get the snapshot report.
>db2 get snapshot for db on <SID>
(d) Check the entries in the services file.

Test Applications

Backup Plan


Switch-Over Testing

1 ---> On the STANDBY(dr-dbpr)
>db2 takeover hadr on db <SID> >db2 get snapshot for db on <SID> (the output should show dr-dbpr running as primary server)

2 ----> On the ho-dbpr1
>db2 get snapshot for db on <SID> (the output should show ho-dbpr1 running as standby server)
Thanks Sadiq

Dear Manu,

The Network connection between two system has been done using static IP, as per my understanding its need NAS Storage on clustering technology for implementing the DB-HADR. And customer don't have NAS storage device. So thats why customer has been decided to making the online DR using log shipping from primary server to standby server.

If any step by step documents related to making online DR kindly share or guide me how to start.

Regards,

Rahul

Dear Sadiq,

Thanks for reply, Actually we have set all database parameter and services on primary and standby server as well. Expect LOGRETAIN parameter, instead of LOGRETAIN we have set LOGARCHMETH1 parameter to enable the archive logging and also set the path.

After update all parameter we can't understand the following lines :

Start HADR on the standby database on dr-dbpr db2 deactivate database <SID> db2 start hadr on database <SID> as standby

Start HADR on the primary database on ho-dbpr1 db2 deactivate database <SID> db2 start hadr on database <SID> as primary

When I excute Start HADR command following output show me :

> start HADR

CORRECT>startx HADR (y|n|e|a)? yes

HADR: Command not found.

xauth: creating new authority file /db2/db2rdv/.serverauth.7635

xauth: creating new authority file /db2/db2rdv/.Xauthority

Fatal server error:

Server is already active for display 0

If this server is no longer running, remove /tmp/.X0-lock

and start again.

Please consult the The X.Org Foundation support

at http://wiki.x.org

for help.

Invalid MIT-MAGIC-COOKIE-1 keygiving up.

xinit: Resource temporarily unavailable (errno 11): unable to connect to X server

xinit: No such process (errno 3): Server error.

I think you doing wrong command

Start HADR on the standby database on hostname

db2 deactivate database SID

db2 start hadr on database SID as standby

Start HADR on the primary database on Hostname

db2 deactivate database SID

db2 start hadr on database SID as primary

Thanks

Sadiq

Answers (2)

Dear Sadiq,

As per your instruction we have executed the command but still this error occurs For your reference kindly check the output of that command :

RPLRDVHO:db2rdv 52> start HADR on standby database on RPLRDVHO

CORRECT>startx HADR on standby database on RPLRDVHO (y|n|e|a)? yes

HADR: Command not found.

xauth: creating new authority file /db2/db2rdv/.serverauth.7027

Fatal server error:

Server is already active for display 0

If this server is no longer running, remove /tmp/.X0-lock

and start again.

Please consult the The X.Org Foundation support

at http://wiki.x.org

for help.

Invalid MIT-MAGIC-COOKIE-1 keygiving up.

xinit: Resource temporarily unavailable (errno 11): unable to connect to X server

xinit: No such process (errno 3): Server error.

Regards,

Rahul

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Again wrong only

command is only below 2 line,

db2 deactivate database SID

db2 start hadr on database SID as standby

Thanks

Sadiq

Dear Sadiq,

we have to executed this command but last one gives me error for your reference we will send you the output of that commands

RPLRDVHO:db2rdv 61> db2 deactivate database RDV

SQL1496W Deactivate database is successful, but the database was not activated.

RPLRDVHO:db2rdv 62> db2 start hadr on database RDV as standby

SQL1767N Start HADR cannot complete. Reason code = "1".

Regards,

Rahul

Hello Rahul

Below links explans your problem.

please check and also take help from DB2 team if you have,

IBM Knowledge Center

How to Start DB2 10.5 HADR on the Standby Database - Database Administrators Stack Exchange

Thanks

Sadiq

Dear Sadiq,

Thanks for your reply, I don't have such knowledge about db2 database but I solved this problem. Now on both primary and standby system database HADR command execute successfully.

I have execute following commands on primary and standby systems.

db2 list db directory

db2 get db cfg for <SID>

db2 get snapshot for db on <SID>

On primary system all those commands execute successfully and On standby systems Last one command gives the error, Kindly see the output of that command.

RPLRDVHO:db2rdv 87> db2 get snapshot for db on RDV

SQL1611W No data was returned by Database System Monitor.

Regards,

Rahul

db2 rollforward database SID query status

Please send the output

Thanks

Sadiq

Dear Sadiq,

Output :

>db2 rollforward db <SID> query status

SQL1776N The command is not supported on an HADR standby database or on an

HADR standby database with the current configuration or state. Reason code =

"1".

Regards,

Rahul

hello Rahul

Could you provide the activity you have done in standby node as of now?

i think something you missed

Thanks

Sadiq

Dear Sadiq,

Standby activity done :

Log retain for recovery enabled (LOGRETAIN) = RECOVERY

User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDBY

HADR local host name (HADR_LOCAL_HOST) = RPLRDVHO

HADR local service name (HADR_LOCAL_SVC) = DB2_HADR_2

HADR remote host name (HADR_REMOTE_HOST) = RUSANDEVHO

HADR remote service name (HADR_REMOTE_SVC) = DB2_HADR_1

HADR instance name of remote server (HADR_REMOTE_INST) = DB2RDV

HADR timeout value (HADR_TIMEOUT) = 120

HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 120

First log archive method (LOGARCHMETH1) = LOGRETAIN

Dear Sadiq,

etc/services file entry :

# Reserved for Disaster Recovery servers

DB2_HADR_1 55001/tcp # communication for HADR1 server

DB2_HADR_2 55002/tcp # communication for HADR2 server

etc/hosts file entry :

#standby server Host

192.168.1.249 RPLRDVHO.site RPLRDVHO

#Primary server host

192.168.1.251 RUSANDEVHO.site RUSANDEVHO

After updation of all database parameter I have restore the database through following command:

>db2 restore db <sid> from <dbbackuppath>

After restoration complete successfully. I have to start HADR on standby without rollforward the logs using following command.

>db2 start hadr on database <sid> as standby

DB20000I The START HADR ON DATABASE command completed successfully.

After that i have execute the following commands see the output:

> db2 list db directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias = RDV

Database name = RDV

Local database directory = /db2/RDV

Database release level = d.00

Comment = SAP database RDV

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

> db2 get db cfg for RDV

Database Configuration for Database RDV

Database configuration release level = 0x0d00

Database release level = 0x0d00

Database territory = en_US

Database code page = 1208

Database code set = UTF-8

Database country/region code = 1

Database collating sequence = IDENTITY_16BIT

Alternate collating sequence (ALT_COLLATE) =

Number compatibility = OFF

Varchar2 compatibility = OFF

Date compatibility = OFF

Database page size = 16384

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Statement concentrator (STMT_CONC) = OFF

Discovery support for this database (DISCOVER_DB) = ENABLE

Restrict access = NO

Default query optimization class (DFT_QUERYOPT) = 5

Degree of parallelism (DFT_DEGREE) = 1

Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO

Default refresh age (DFT_REFRESH_AGE) = 0

Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM

Number of frequent values retained (NUM_FREQVALUES) = 10

Number of quantiles retained (NUM_QUANTILES) = 20

Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

Backup pending = NO

All committed transactions have been written to disk = NO

Rollforward pending = NO

Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = RECOVERY

User exit for logging status = NO

Self tuning memory (SELF_TUNING_MEM) = ON

Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(626624)

Database memory threshold (DB_MEM_THRESH) = 10

Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(4096)

Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)

Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(27390)

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(3208)

Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(507)

Database heap (4KB) (DBHEAP) = AUTOMATIC(2557)

Catalog cache size (4KB) (CATALOGCACHE_SZ) = 2560

Log buffer size (4KB) (LOGBUFSZ) = 1024

Utilities heap size (4KB) (UTIL_HEAP_SZ) = 50000

Buffer pool size (pages) (BUFFPAGE) = 10000

SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(8192)

Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)

Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)

Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

Interval for checking deadlock (ms) (DLCHKTIME) = 10000

Lock timeout (sec) (LOCKTIMEOUT) = 3600

Changed pages threshold (CHNGPGS_THRESH) = 20

Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(3)

Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(4)

Index sort flag (INDEXSORT) = YES

Sequential detect flag (SEQDETECT) = YES

Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

Track modified pages (TRACKMOD) = YES

Default number of containers = 1

Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 2

Max number of active applications (MAXAPPLS) = AUTOMATIC(40)

Average number of active applications (AVG_APPLS) = AUTOMATIC(3)

Max DB files open per application (MAXFILOP) = 61440

Log file size (4KB) (LOGFILSIZ) = 16380

Number of primary log files (LOGPRIMARY) = 60

Number of secondary log files (LOGSECOND) = 0

Changed path to log files (NEWLOGPATH) =

Path to log files = /db2/RDV/log_dir/NODE0000/

Overflow log path (OVERFLOWLOGPATH) =

Mirror log path (MIRRORLOGPATH) =

First active log file = S0000537.LOG

Block log on disk full (BLK_LOG_DSK_FUL) = YES

Block non logged operations (BLOCKNONLOGGED) = NO

Percent max primary log space by transaction (MAX_LOG) = 0

Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1

Percent log file reclaimed before soft chckpt (SOFTMAX) = 300

Log retain for recovery enabled (LOGRETAIN) = RECOVERY

User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDBY

HADR local host name (HADR_LOCAL_HOST) = RPLRDVHO

HADR local service name (HADR_LOCAL_SVC) = DB2_HADR_2

HADR remote host name (HADR_REMOTE_HOST) = RUSANDEVHO

HADR remote service name (HADR_REMOTE_SVC) = DB2_HADR_1

HADR instance name of remote server (HADR_REMOTE_INST) = DB2RDV

HADR timeout value (HADR_TIMEOUT) = 120

HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 120

First log archive method (LOGARCHMETH1) = LOGRETAIN

Options for logarchmeth1 (LOGARCHOPT1) =

Second log archive method (LOGARCHMETH2) = OFF

Options for logarchmeth2 (LOGARCHOPT2) =

Failover log archive path (FAILARCHPATH) =

Number of log archive retries on error (NUMARCHRETRY) = 5

Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20

Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON

Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)

Log pages during index build (LOGINDEXBUILD) = ON

Default number of loadrec sessions (DFT_LOADREC_SES) = 1

Number of database backups to retain (NUM_DB_BACKUPS) = 12

Recovery history retention (days) (REC_HIS_RETENTN) = 60

Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

TSM management class (TSM_MGMTCLASS) =

TSM node name (TSM_NODENAME) =

TSM owner (TSM_OWNER) =

TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = ON

Automatic database backup (AUTO_DB_BACKUP) = OFF

Automatic table maintenance (AUTO_TBL_MAINT) = ON

Automatic runstats (AUTO_RUNSTATS) = ON

Automatic statement statistics (AUTO_STMT_STATS) = ON

Automatic statistics profiling (AUTO_STATS_PROF) = OFF

Automatic profile updates (AUTO_PROF_UPD) = OFF

Automatic reorganization (AUTO_REORG) = OFF

Auto-Revalidation (AUTO_REVAL) = DEFERRED

Currently Committed (CUR_COMMIT) = DISABLED

CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW

Enable XML Character operations (ENABLE_XMLCHAR) = YES

WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0

Monitor Collect Settings

Request metrics (MON_REQ_METRICS) = BASE

Activity metrics (MON_ACT_METRICS) = BASE

Object metrics (MON_OBJ_METRICS) = BASE

Unit of work events (MON_UOW_DATA) = NONE

Lock timeout events (MON_LOCKTIMEOUT) = WITHOUT_HIST

Deadlock events (MON_DEADLOCK) = WITHOUT_HIST

Lock wait events (MON_LOCKWAIT) = NONE

Lock wait event threshold (MON_LW_THRESH) = 5000000

Number of package list entries (MON_PKGLIST_SZ) = 32

Lock event notification level (MON_LCK_MSG_LVL) = 1

SMTP Server (SMTP_SERVER) =

SQL conditional compilation flags (SQL_CCFLAGS) =

Section actuals setting (SECTION_ACTUALS) = NONE

Connect procedure (CONNECT_PROC) =

> db2 get snapshot for db on RDV

SQL1611W No data was returned by Database System Monitor.

Regards,

Rahul

Hi Rahul

Give me a test mail. i will fwd the doc you can cross check with it,

while you can try

db2 activate db <SID>

Thanks

Sadiq

Dear Sadiq,

My mail id : rahulpinate@gmail.com

Dear Sadiq,

My mail Id is : rahulpinate@gmail.com

Regards,

Rahul

HI Rahul

i shared the doc with you please check

Thanks

Sadiq

Dear Sadiq,

Thanks for sharing me doc, as per your doc i have done all step again and all step execute successfully. Expect the following while performing takeover activity on primary system :

I have execute following command <sid>adm user :

>saposcol -k

saposcol: command not found

I have share you on your mail my docs which is prepared for me. Kindly look at ones for your reference.

Regards,

Rahul

Hi Rahul

Is other things are working fine?

saposcol is now part of hostagent

this is not a big problem , just read the host agent concept .

Thanks

Sadiq

Dear Sadiq,

Thanks for guiding me to implement the HADR of DB2 database on primary and standby system and also all other things are working fine expect saposcol.

Now HADR is completed successfully it is going to live.

Regards,

Rahul

Hi Rahul

I would rather make use of DB2 HADR functionality. It would be much easier than creating your own scripts to roll the log forward. See the following Note:

1612105 - DB6: FAQ for DB2 High Availability Disaster Recovery (HADR)

Regards

Robert

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Ask a Question

Top Q&A Solution Author

User

Count

DB2 9.7 Disaster Recovery Set-up

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

Re: SAP Build Apps - How to do Data Refresh for Ma...

Re: DRC e-Cockpit Add a New Button

Re: BTP Build Apps- Error while integration of BTP...

SAP MAXDB : Content Server Administration - Reposi...

Re: "Failed to update setup engine executables. Pr...