数据IT技术技术文

一次一波三折的DB2数据库重定向恢复

2016-08-30  本文已影响719人  MJ老段

作为一个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做过简单的试验,没有什么发现,指不指定似乎都一样。

上一篇下一篇

猜你喜欢

热点阅读