作为一个IT人士，写小说之余还是要干点本职工作。接下来跟大家分享一个曾经遇到过的DB2数据库重定向恢复的问题。
以前做过好几次用到redirected restore的数据库迁移，都很顺利，可是这一次却栽了个跟头。不能说“大意了”，只能怪自己“懂得太少”。
环境：9.7 fp9 + zlinux
从prod环境上将备份文件transfer到DEV环境，由于DEV上已经有了同名的数据库SMMNF，所以需要用到redirected restore，将备份文件restore到一个新建的SMMNFP数据库里。
自己懒得写脚本，就直接生成了：
db2 restore database smmnf into smmnfp redirect generate script ./rr.sql
看了下，因为db_path是一样的，所以没改脚本，直接就开炮了！
前边一切顺利，到最后db2 rollforward db smmnfp query status查看处于rollforward pending,突然发现原来备份文件是online的，还是从TSM上取下来的。
跑一下rollfoward db to end of log and complete,出错：
SQL1268N Roll-forward recovery stopped due to error "SQL2033" while
retrieving log file "S0055756.LOG" for database "SMMNFP" on node "0".
原因是restore的时候没有指定LOGTARGET，导致rollforward需要的日志文件没能extract出来。
于是修改生成的rr.sql文件，指定logtarget:
RESTORE DATABASE SMMNF FROM '/db/backups/prod_backup' TAKEN AT 20140425114322 INTO SMMNFP LOGTARGET /db2/apply/logtarget REDIRECT
前期一切顺利，可是到rollforward的时候又出错了：
baccdsdata:/db2/apply$ db2 rollforward database smmnfp query status
Rollforward Status
Input database alias = smmnfp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB pending
Next log file to be read = S0055756.LOG
Log files processed = -
Last committed transaction = 2014-04-25-12.23.22.000000 UTC
baccdsdata:/db2/apply$ db2 "rollforward db smmnfp to end of logs and complete overflow log path (/db2/apply/logtarget)"
SQL1268N Roll-forward recovery stopped due to error "SQL2033" while
retrieving log file "S0055757.LOG" for database "SMMNFP" on node "0".
这次肿么又冒出一个S0055757.LOG(⊙o⊙)？
看db2diag.log（记住，日志是你解决问题第一个要看的东西）：
2014-05-01-06.38.26.838436+000 E90419A432 LEVEL: Error
PID : 21430 TID : 4392547772688PROC : db2sysc 0
INSTANCE: smartmfg NODE : 000
EDUID : 38651 EDUNAME: db2lfr (SMMNFP) 0
FUNCTION: DB2 UDB, data protection services, sqlpSearchForLogArchiveOnDisk, probe:4000
MESSAGE : ZRC=0x860F000A=-2045837302=SQLO_FNEX "File not found."
DIA8411C A file "" could not be found.
2014-05-01-06.38.26.897621+000 E90852A361 LEVEL: Error
PID : 34158 TID : 4397873211152PROC : db2vend (db2logmgr.meth1 - 38648
INSTANCE: smartmfg NODE : 000
FUNCTION: DB2 UDB, database utilities, sqluvint, probe:292
DATA #1 : TSM RC, PD_DB2_TYPE_TSM_RC, 4 bytes
TSM RC=0x00000196=406 -- see TSM API Reference for meaning.
2014-05-01-06.38.26.898602+000 E91214A865 LEVEL: Error
PID : 21430 TID : 4392681990416PROC : db2sysc 0
INSTANCE: smartmfg NODE : 000
EDUID : 38648 EDUNAME: db2logmgr (SMMNFP) 0
FUNCTION: DB2 UDB, data protection services, sqlpInitVendorDevice, probe:1030
MESSAGE : ZRC=0x86100025=-2045771739=SQLP_MEDIA_VENDOR_DEV_ERR
"A vendor device reported a media error."
DATA #1 : String, 29 bytes
Init failed! Vendor rc info:
DATA #2 : Vendor RC, PD_DB2_TYPE_VENDOR_RC, 4 bytes
Vendor RC=0x0000000B=11 -- see DB2 API Guide for meaning.
DATA #3 : Hexdump, 48 bytes
0x000003FED157F090 : 0000 0196 3239 3220 3430 3600 0000 0000 ....292 406.....
0x000003FED157F0A0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x000003FED157F0B0 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
2014-05-01-06.38.26.898998+000 I92080A438 LEVEL: Error
PID : 21430 TID : 4392681990416PROC : db2sysc 0
INSTANCE: smartmfg NODE : 000
EDUID : 38648 EDUNAME: db2logmgr (SMMNFP) 0
FUNCTION: DB2 UDB, data protection services, sqlpSearchForLogArchiveOnVendor, probe:8200
RETCODE : ZRC=0x86100025=-2045771739=SQLP_MEDIA_VENDOR_DEV_ERR
"A vendor device reported a media error."
TSM的问题。原来prod上用的是TSM online bakcup，archive log mode。而本地根本就没有配置TSM bakcup。
刚开始想直接将logarchmeth1改成off,发现不行：
baccdsdata:/db/backups/prod_backup$ db2 update db cfg for smmnfp using logarchmeth1 off
SQL5099N The value "OFF" indicated by the database configuration parameter
"LOGARCHMETH1" is not valid, reason code "17". SQLSTATE=08004
Cannot switch to circular logging when database is rollforward pending.
那就改成本地archive：
baccdsdata:/db/backups$ mkdir arch_log
baccdsdata:/db/backups$ db2 update db cfg for smmnfp using logarchmeth1 DISK:/db/backups/arch_log
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
baccdsdata:/db/backups$ rollforward db smmnfp to end of logs and complete overflow log path (/db2/apply/logtarget)
-bash: syntax error near unexpected token `('
baccdsdata:/db/backups$ db2 "rollforward db smmnfp to end of logs and complete overflow log path (/db2/apply/logtarget)"
Rollforward Status
Input database alias = smmnfp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0055756.LOG - S0055757.LOG
Last committed transaction = 2014-04-25-12.23.22.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
至此终于问题终于解决了。原来bakcup/restore也没有想象中那么简单啊！
只是还有一个问题没能弄清楚：
backup中的logpath和target db的logpath是不一样的，如果做restore的时候没有指定NEWLOGPATH会怎么样呢？
PS: 在windows上用offline的DB做过简单的试验，没有什么发现，指不指定似乎都一样。
网友评论