1

I am trying to set up HADR to run on my DB2 10.5 DB2 servers. I believe I have followed the steps correctly, but I'm getting the following error:

[db2insh1@rslvm20 OneView]$ db2 restore database onevwhad
DB20000I  The RESTORE DATABASE command completed successfully.

[db2insh1@rslvm20 OneView]$ db2 -vtf update_config_HADR_Secondary.sql
UPDATE DB CFG FOR ONEVWHAD USING HADR_LOCAL_HOST rslvm20.dub.usoh.ibm.com HADR_LOCAL_SVC DB2_db2insh1_hadr HADR_SYNCMODE NEARSYNC HADR_REMOTE_HOST rslvm19.dub.usoh.ibm.com HADR_REMOTE_SVC DB2_db2insh1_hadr HADR_REMOTE_INST db2insh1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

[db2insh1@rslvm20 OneView]$ db2 start hadr on db onevwhad as standby
SQL1767N  Start HADR cannot complete. Reason code = "1".

The Knowledge Center says this about that error:

Start HADR cannot complete. The explanation corresponding to the reason code is:  
1: The database was not in roll forward-pending or roll forward-in-progress state
when the START HADR AS STANDBY command was issued.

The user response corresponding to the reason code is:
1: Initialize the standby database from a backup image or a split mirror of the 
primary database, then reissue the START HADR AS STANDBY command.

But I restored the database right before I updated the HADR config, and then tried to start HADR. There were no intervening steps, so I am confused. I'm fairly sure I have to update the config between restoring the DB and starting HADR. Any idea what I'm doing wrong?

==========================================================================================

The 'LOGARCHMETH1' option is set to 'OFF'.

Here are the applicable contents from db2diag.log. It doesn't tell me (with my limited knowledge) anything other than the -1767 error code.

2014-12-04-11.46.29.565620-300 I228560E692           LEVEL: Event
PID     : 32654                TID : 140560786515712 PROC : db2sysc 0
INSTANCE: db2insh1             NODE : 000            DB   : ONEVWHAD
APPHDL  : 0-200                APPID: *LOCAL.db2insh1.141204164629
AUTHID  : DB2INSH1             HOSTNAME: rslvm20.dub.usoh.ibm.com
EDUID   : 22                   EDUNAME: db2agent (ONEVWHAD) 0
FUNCTION: DB2 UDB, base sys utilities, sqleCalculateDbHeaps, probe:70
MESSAGE :  RLMS - DB Memory Set for Resident Member
DATA #1 : String, 10 bytes
totalBytes
DATA #2 : unsigned integer, 8 bytes
236519424
DATA #3 : String, 11 bytes
dbHeapBytes
DATA #4 : unsigned integer, 8 bytes
164298752

2014-12-04-11.46.29.594451-300 I229253E673           LEVEL: Error
PID     : 32654                TID : 140560786515712 PROC : db2sysc 0
INSTANCE: db2insh1             NODE : 000            DB   : ONEVWHAD
APPHDL  : 0-200                APPID: *LOCAL.db2insh1.141204164629
AUTHID  : DB2INSH1             HOSTNAME: rslvm20.dub.usoh.ibm.com
EDUID   : 22                   EDUNAME: db2agent (ONEVWHAD) 0
FUNCTION: DB2 UDB, data protection services, sqlpPrepareAndValidateEnvForHadr, probe:540
MESSAGE : ZRC=0x8010006D=-2146434963=SQLP_RC_CA_BUILT
          "SQLCA has been built and saved in component specific control block."
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
-1767

2014-12-04-11.46.29.594815-300 I229927E600           LEVEL: Error
PID     : 32654                TID : 140560786515712 PROC : db2sysc 0
INSTANCE: db2insh1             NODE : 000            DB   : ONEVWHAD
APPHDL  : 0-200                APPID: *LOCAL.db2insh1.141204164629
AUTHID  : DB2INSH1             HOSTNAME: rslvm20.dub.usoh.ibm.com
EDUID   : 22                   EDUNAME: db2agent (ONEVWHAD) 0
FUNCTION: DB2 UDB, data protection services, sqlpinit, probe:1050
MESSAGE : ZRC=0x8010006D=-2146434963=SQLP_RC_CA_BUILT
          "SQLCA has been built and saved in component specific control block."

2014-12-04-11.46.29.595098-300 I230528E936           LEVEL: Severe
PID     : 32654                TID : 140560786515712 PROC : db2sysc 0
INSTANCE: db2insh1             NODE : 000            DB   : ONEVWHAD
APPHDL  : 0-200                APPID: *LOCAL.db2insh1.141204164629
AUTHID  : DB2INSH1             HOSTNAME: rslvm20.dub.usoh.ibm.com
EDUID   : 22                   EDUNAME: db2agent (ONEVWHAD) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:9831
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -1767   sqlerrml: 1
 sqlerrmc: 1
 sqlerrp : sqlpPrep
 sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
           (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)        (11)
 sqlstate:
DATA #2 : Boolean, 1 bytes
false

Here are the config values for HADR on my standby, if they matter:

UPDATE DB CFG FOR ONEVWHAD USING
    HADR_LOCAL_HOST rslvm20.dub.usoh.ibm.com
    HADR_LOCAL_SVC DB2_db2insh1_hadr
    HADR_SYNCMODE NEARSYNC
    HADR_REMOTE_HOST rslvm19.dub.usoh.ibm.com
    HADR_REMOTE_SVC DB2_db2insh1_hadr
    HADR_REMOTE_INST db2insh1

And on the primary:

UPDATE DB CFG FOR ONEVWHAD USING
    HADR_LOCAL_HOST rslvm19.dub.usoh.ibm.com
    HADR_LOCAL_SVC DB2_db2insh1_hadr
    HADR_SYNCMODE NEARSYNC
    HADR_REMOTE_HOST rslvm20.dub.usoh.ibm.com
    HADR_REMOTE_SVC DB2_db2insh1_hadr
    HADR_REMOTE_INST db2insh1

BTW, I think the restore is working, because I can see the data from the primary in the tables in the standbay.

  • Usually when you restore you do not rollforward to end of backup or end of logs on the standby. You do not want to perform that step. What I'm wondering though....are you using archivable logging (ie, is LOGARCHMETH1 set to something other than OFF)? Wondering if that is affecting HADR? Because a circular logging database has no need to rollforward and that may be what is messing things up here? – Chris Aldrich Dec 4 '14 at 13:45
  • I'm sure you can find more diagnostic information in db2diag.log. – mustaccio Dec 4 '14 at 14:37
  • @ChrisAldrich -- Circular logging would produce SQL1768N instead, I think. – mustaccio Dec 4 '14 at 14:42
  • I edited the post to add info from the log, and the configuration parameters (since it was too long for a comment, and didn't make sense as an answer). – Westy Dec 4 '14 at 17:57
  • 1
    The database must be in the archive log mode to support HADR. And, if the standby were in the rollforward pending mode, as it should, you would not be able to connect to the standby to "see the data". So, go back to the manuals. – mustaccio Dec 4 '14 at 18:39
-1

It is logarchive problem pass this command in primary system1. 1. db2 update db cfg for using LOGARCHMETH1 LOGRETAIN

It will enable to database for log archiving and keep the logs in the same active log directory. This will also place the db in backup pending state.

  1. Take an offline backup. db2 "backup database "

  2. Setting up HADR cfg parameters on Primary database.

    db2 update db cfg for using HADR_LOCAL_HOST db2 update db cfg for using HADR_LOCAL_SVC db2 update db cfg for using HADR_REMOTE_HOST db2 update db cfg for using HADR_REMOTE_SVC db2 update db cfg for using HADR_REMOTE_INST db2 update db cfg for using LOGINDEXBUILD ON

  3. Take an offline backup to be used for setting HADR.

    db2 "backup database "

  • Be aware that LOGRETAIN will retain logs forever. It will not clean them up for you, even if you use AUTO_DEL_REC_OBJ. For this reason, I have avoided LOGRETAIN and gone with DISK. This allows me to specify where I want the archived logs to go to, and to enable auto-pruning through AUTO_DEL_REC_OBJ. – Chris Aldrich Feb 3 '16 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.