范例：使用db2trace解决未知restore错误

wangzhonnew · 发表于 2007-8-3 08:33

运行环境：
Server is running MPP/EEE.
DB21085I  Instance "dbguest5" uses "64" bits and DB2 code release "SQL09012"
with level identifier "01030107".
Informational tokens are "DB2 v9.1.0.2", "special_00000", "MI00184_00000", and
Fix Pack "2".
Product is installed at "/home/dbguest5/sqllib".

Operating system and level
Operating system is: Linux/X8664
OSS reports operating system level as: #1 SMP Fri Jan 14 15:41:33 UTC 2005.2.6.5-7.139-smp.

症状：
restore ... rebuid tablespace重定向恢复后，rollforward发生错误：
db2 "rollforward db aecoc2 to end of logs overflow log path ('/TMP/user/rick/logs')  "
SQL0752N Connecting to a database is not permitted within a logical unit
of work when the CONNECT type 1 setting is in use.  SQLSTATE=0A001

分析：
rebuid是v9中的新属性，可以从备份文件中将指定的若干个表空间恢复到新的数据库中。
在这个问题中，客户使用online backup image，其中有超过230个表空间，希望将其中的6个表空间恢复到新的数据库中用于单独分析。
screenshot如下：
restore db aecoc rebuild with tablespace (SYSCATSPACE, CLIENT, XCLIENT,
SECRTS,
SECRIXPC, XSECPASS) on /TMP/user/rick into aecoc2 redirect without
prompting
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use
automatic
storage can have their containers reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

set tablespace containers for 0 using (path
'/TMP/user/rick/restore/syscat')
SQL20319N  The SET TABLESPACE CONTAINERS command is not allowed on an
automatic storage table space.  SQLSTATE=55061

set tablespace containers for 9 using (file
'/TMP/user/rick/restore/CLIENT'
800)
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

set tablespace containers for 52 using (file
'/TMP/user/rick/restore/XCLIENT
' 3200)
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

set tablespace containers for 39 using (file
'/TMP/user/rick/restore/SECRTS'
1200)
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

set tablespace containers for 38 using (file
'/TMP/user/rick/restore/SECRIXP
C' 600)
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

set tablespace containers for 205 using (file
'/TMP/user/rick/restore/XSECPA
SS' 600)
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

restore db aecoc continue
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use
automatic
storage can have their containers reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

我们看到用户恢复6个表空间，对所有的表空间作重定向恢复，然后再紧接着的rollfowrad中报告如下错误：
db2 "rollforward db aecoc2 to end of logs overflow log path
> ('/TMP/user/rick/logs')
> "
SQL0752N Connecting to a database is not permitted within a logical unit
of
work when the CONNECT type 1 setting is in use.  SQLSTATE=0A001

$db2 query rollforward status
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

对于这个问题我们并没有多少解决的经验，因为环境为V9 FP2。于是使用最简单直接的方法，作trace。
刚开始分析rollfoward部分，只发现是在clp模块中通过判定一个不知道在哪里赋值得sqlcode产生-752的错误。
于是继续向前跟踪回restore continue部分，发现下列有趣的信息

1741       | | | | | | | | | | sqlbRestorePoolContainers entry
1742       | | | | | | | | | | | sqlbFilterRestoringPoolContainers entry
1743       | | | | | | | | | | | sqlbFilterRestoringPoolContainers data [probe 20]
1744       | | | | | | | | | | | sqlbFilterRestoringPoolContainers exit
1745       | | | | | | | | | | | sqlbQryPoolCont entry
...
3921       | | | | | | | | | | | sqlbSetPoolCont exit [rc = 0x840F0001 = -2079391743 = SQLO_ACCD]
3922       | | | | | | | | | | | sqlofmblkEx entry
3923       | | | | | | | | | | | sqlofmblkEx mbt [Marker:PD_OSS_FREED_MEMORY ]
3924       | | | | | | | | | | | sqlofmblkEx exit
3925       | | | | | | | | | | sqlbRestorePoolContainers exit [rc = 0x840F0001 = -2079391743 = SQLO_ACCD]

通过上面的分析我们并没有看到在set container的时候遇到过任何access denied的问题，那么这里为什么会有ACCD呢？于是去fmt里面看，发现：
..
1741 entry DB2 UDB buffer pool services sqlbRestorePoolContainers fnc (1.3.2.810.0)
pid 4566 tid 183040023744 cpid 5197 node 0
bytes 28

Data1    (PD_TYPE_PTR,8) Pointer:
0x0000002aa0702ea0
Data2    (PD_TYPE_UINT,4) unsigned integer:
3
这个3很明显说明的是正在restore的tablespace ID，然后下面的SQLO_ACCD说明遇到了访问错误问题。但是我们并没有redirect tablespace 3的container呀。而类似的问题也发生在tablespace 231上。

于是我们去db2 list tablespace里面查找，发现tablespace 3和tablespace 231均为temp space，而其状态为
Tablespace ID                      = 213
Name                               = TEMPSPACE04K
Type                               = System managed space
Contents                            = System Temporary data
State                               = 0x2001100
Detailed explanation:
   Restore pending
   Storage must be defined
   Storage may be defined

看来db2希望这两个tablespace被restore，而在上面的步骤中由于没有重定向这两个表空间造成了restore continue的错误。

于是，我们在set container中添加上这两个表空间，然后restore continue，紧接着rollfoward问题解决。

diablo2 · 发表于 2007-8-3 10:18

Good

askgyliu · 发表于 2007-8-3 11:29

提问：是不是因为ROLLFORWARD时要用到这些TEMPSPACE的原因？

Assume we have a table TABLE_1 in one of the six tablespaces, and we have a trigger on TABLE_1 that will store the old data in TABLE_1 into a history table TABLE_1_HIST if there is an update to TABLE_1, where TABLE_1_HIST is NOT in one of the six tablespaces to be restored.

Will such restore/rollforward fail by asking for the tablespace that houses TABLE_1_HIST?

哪位可以测试一下？

wangzhonnew · 发表于 2007-8-3 21:08

不大清楚，我感觉可能是在座backup得时候正好有对需要restore得tablespace得transaction使用tempspace，这样如果要RF得话，本身RF是redo+undo，在redo中如果原本的transaction必须使用tempspace那么现在这些tempspace也应该必须存在