Recently we migrated from db2 v9.5 to v9.7. We had to take an offline backup from v9.5 and restore that on the v9.7. But now we cannot seem to take an online backup.

On v9.5:
> db2 backup db mydb

On v9.7
> db2 drop db mydb
> rm -rf (my active and archive logs)
> db2 restore db mydb
> db2 backup db online include logs
SQL2428N The BACKUP did not complete because one or more of the requested log
files could not be retrieved.

How can I take an online backup?

May be you need to rollforward the database after the restore?

if any error messages - please post details so we can understand what is going on

Sorry I did a rollforward but forgot to include it in the post. Here it is again.

On v9.5:
> db2 backup db mydb

On v9.7
> db2 drop db mydb
> rm -rf (my active and archive logs)
> db2 restore db mydb
> db2 rollforward db mydb to end of logs and complete
> db2 backup db online include logs
SQL2428N The BACKUP did not complete because one or more of the requested log
files could not be retrieved.

Here is the output from db2diag.log for the backup command:

2011-10-03-18.14.00.929621-240 E21765384E450 LEVEL: Info
PID : 2251 TID : 140212583786240PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5241 EDUNAME: db2agent (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubSetupJobControl, probe:1533
MESSAGE : Starting an online db backup.

2011-10-03-18.15.19.698001-240 E21765835E439 LEVEL: Warning
PID : 2264 TID : 140515667232512PROC : db2acd
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::update, probe:500
MESSAGE : ADM10502W Health indicator "Table Space Operational State"
("ts.ts_op_status") is in state "Backup in progress" on "table space"
"db2inst1.MYDB .USERSPACE1".

2011-10-03-18.15.28.592060-240 I21766275E419 LEVEL: Warning
PID : 2251 TID : 140213187766016PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
EDUID : 5810 EDUNAME: db2lfr (MYDB)
FUNCTION: DB2 UDB, recovery manager, sqlpValidateLogCID, probe:30
DATA #1 : <preformatted>
extentNum 23209 extentCID 1317680032 truncArray[2] has extentNumber 1246986726 curCID 2024997888

2011-10-03-18.15.28.592124-240 I21766695E376 LEVEL: Error
PID : 2251 TID : 140213187766016PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
EDUID : 5810 EDUNAME: db2lfr (MYDB)
FUNCTION: DB2 UDB, recovery manager, sqlplfrIsLogFromValidChain, probe:5160
DATA #1 : <preformatted>
Log extent 23209 not in chain, rc -2045771753

2011-10-03-18.15.28.596463-240 I21767072E419 LEVEL: Warning
PID : 2251 TID : 140213187766016PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
EDUID : 5810 EDUNAME: db2lfr (MYDB)
FUNCTION: DB2 UDB, recovery manager, sqlpValidateLogCID, probe:30
DATA #1 : <preformatted>
extentNum 23209 extentCID 1317680032 truncArray[2] has extentNumber 1246986726 curCID 2024997888

2011-10-03-18.15.28.596502-240 I21767492E376 LEVEL: Error
PID : 2251 TID : 140213187766016PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
EDUID : 5810 EDUNAME: db2lfr (MYDB)
FUNCTION: DB2 UDB, recovery manager, sqlplfrIsLogFromValidChain, probe:5160
DATA #1 : <preformatted>
Log extent 23209 not in chain, rc -2045771753

2011-10-03-18.15.28.596541-240 I21767869E566 LEVEL: Error
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubProcessLogExtent, probe:1769
MESSAGE : Backup error while copying log file extent:
DATA #1 : Hexdump, 4 bytes
0x00007F85DCBFD01C : A95A 0000 .Z..

2011-10-03-18.15.28.601560-240 I21768436E539 LEVEL: Error
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0
MESSAGE : zrc:
DATA #1 : Hexdump, 4 bytes
0x00007F85DCBFD018 : 0900 0F87 ....

2011-10-03-18.15.28.601597-240 I21768976E550 LEVEL: Error
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0
MESSAGE : Current offset:
DATA #1 : Hexdump, 4 bytes
0x00007F85DCBFCECC : 0000 0000 ....

2011-10-03-18.15.28.601633-240 I21769527E549 LEVEL: Error
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0
MESSAGE : Pages to read:
DATA #1 : Hexdump, 4 bytes
0x00007F85DCBFCFDC : 0300 0000 ....

2011-10-03-18.15.28.601666-240 E21770077E572 LEVEL: Severe
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubProcessLogExtent, probe:1775
MESSAGE : ADM8010E Backup was unable to copy requested log file "S0023209.LOG"
for inclusion in the backup image. The backup has been aborted.

2011-10-03-18.15.28.601825-240 E21770650E563 LEVEL: Severe
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubProcessLogExtent, probe:1858
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
-2428
DATA #2 : Hexdump, 4 bytes
0x00007F85ADBD0998 : 84F6 FFFF ....

2011-10-03-18.15.28.601883-240 E21771214E966 LEVEL: Severe
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubProcessLogExtent, probe:1858
MESSAGE : SQL2428N The BACKUP did not complete because one or more of the
requested log files could not be retrieved.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2428 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubPro
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:

2011-10-03-18.15.28.605775-240 E21772181E553 LEVEL: Severe
PID : 2251 TID : 140212910941952PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5823 EDUNAME: db2bm.5241.0 (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubbuf, probe:340
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
-2044
DATA #2 : Hexdump, 8 bytes
0x00007F85ADBD0998 : 04F8 FFFF 0200 0000 ........

2011-10-03-18.15.29.223636-240 I21772735E364 LEVEL: Warning
PID : 2251 TID : 140213141628672PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
EDUID : 5807 EDUNAME: db2logmgr (MYDB)
FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3108
MESSAGE : Started archive for log file S0023209.LOG.

2011-10-03-18.15.29.763025-240 I21773100E516 LEVEL: Warning
PID : 2251 TID : 140213141628672PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
EDUID : 5807 EDUNAME: db2logmgr (MYDB)
FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3180
MESSAGE : Completed archive for log file S0023209.LOG to
/home/db2inst1/database/MYDB/logs/archive/db2inst1/MYDB/NODE0000/C00
00010/ from /home/db2inst1/database/MYDB/logs/active/.

2011-10-03-18.15.40.247334-240 E21773617E428 LEVEL: Severe
PID : 2251 TID : 140212583786240PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-30936 APPID: *LOCAL.db2inst1.111003221359
AUTHID : DB2INST1
EDUID : 5241 EDUNAME: db2agent (MYDB)
FUNCTION: DB2 UDB, database utilities, sqlubcka, probe:843
MESSAGE : Backup terminated.

-------------------------------------------------------------------
Please let me know if you need more output. Additional note: our original database was setup for online backup using logarchmeth1: disk.

Thanks for your help.

I'd try activating the database before the backup to initialize the new log chain.

Thanks, n_i, I tried activating the database before doing online backup, but it still fails.

This is due to:
https://www-304.ibm.com/support/docv...id=swg1IC61886

It can happen when you migrate to v9.7 GA. Upgrading to a higher fixpack won't resolve this error. Open a pmr with DB2 Support, they would have to patch the log control file mentioned in the APAR.

The workaround would be to exclude the logs during backup.

Thanks for the info db2girl. I am using DB2 v9.7 Express-C. Do you know if I would need to pay for the support, i.e. to submit a pmr?

Yes, you would have to pay unless you purchased some license.

This problem only exists when you migrate to v9.7 GA (no fixpack).

What's your db2level? If it's still FP0, download the latest Express-C version (FP ?), drop this v9.7 db and them restore it from v9.5 image again.

db2girl, I tried installing v9.7.4 on our testing server and restoring a v9.5 backup image and was able to get the online backup working. However it doesn't work with a v9.7 offline backup, which is what I need to restore from our live database now. Are there any other options?

Thanks!

I'm not sure I understand the second part.

You mean an online backup fails with sql2428n after restoring it from a v9.7 offline image? Is this a db that was originally migrated to v9.7 FP0? If so, the options are - patch the LFH, redo the migration, rebuild the db manually or exclude the logs during backup.

db2girl, you understood correctly. My turn to ask for clarification. 1) When you say 'redo the migration', do you mean restore the v9.5 backup onto the v9.7.4 database? We probably can't do this because there is new data since the v9.5 backup. 2) What do you mean by 'rebuild the db manually'?

Thanks again.

Yes.


By rebuild, I mean dropping and recreating the database. You would need to get the DDL, export data, drop/re-create db, reload data, etc...