标题: 范例：使用db2trace解决未知restore错误 [打印本页] 作者: wangzhonnew 时间: 2007-8-3 08:33 标题: 范例：使用db2trace解决未知restore错误 运行环境：
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".
于是我们去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
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?
