5568
社区成员
1.1w+
社区内容
- 主页
分区数据库备份和恢复的问题。
各位db2的专家:
DB2的资料好少！没有找到标准的备份样例。我现在要做一个db2的备份脚本，硬件环境是这样的：M85*2，hacmp es 441的环境，aix 433.10，db2版本 7.2.5 EEE，设置如下：
一个实例，主节点的目录NFS mount ， 存放catalog， 每个物理节点一个逻辑node。我要实现的要求是：每月一次全备，每周一次一级增量，即incremental 备份。备份使用TSM 5.1 , 磁带库。TSM和磁带库已经设置好。现在就差client的schedule了。
我做实验如下：
db2sampl 展开一个sample库，设置参数logretain和trackmod后，依次做主节点，用户表空间节点的offline的全备，之后向sample库建新表，insert数据，改变数据库的同时，依次做了一次online 的full backup 和online 的incremental 、delta 备份。也是先後做主节点和另外的节点。（我这里说的节点按照逻辑node说）
之后，drop sample 进行恢复。
首先，第一次试验，我恢复db2 主节点的history file :
$ db2 restore db sample history file use tsm taken at 20030112223107 buffer 100
DB20000I The RESTORE DATABASE command completed successfully.
之后恢复次节点的：
$ db2 restore db sample history file use tsm taken at 20030113163719 buffer 100
SQL2565N The option specified for RESTORE is not allowed with the backup image provided.
就不行了。
不管它继续，在主节点上操作：
$ db2 list history backup all for sample
List History File for sample
Number of matching file entries = 19
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20030111200331000 F S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20030111200331
End Time: 20030111200332
----------------------------------------------------------------------------
00001 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2009 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubcka
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
。。。。。。。。。。。
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
0 record(s) selected.
没有返回。
$ db2 restore db sample taken at 20030111224424 buffer 100
SQL2542N No match for a database image file was found based on the source
database alias "SAMPLE" and timestamp "20030111224424" provided.
$ db2ckrst -d sample -t 20030111224424 -r database
Suggested restore order of images using timestamp 20030111224424 for
database sample.
====================================================================
restore db sample incremental taken at 20030111224424
restore db sample incremental taken at 20030111224424
====================================================================
这个image 是个offline的full backup ， 可是db2ckrst提示要用incremental 参数！！ok 照做：
$ db2 restore db sample incremental use tsm taken at 20030111224424 buffer 100
SQL2523W Warning! Restoring to an existing database that is different from the
database on the backup image, but have matching names. The target database will b
e overwritten by the backup version. The Roll-forward recovery logs associated w
ith the target database will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
SQL1119N A connection to or activation of database "SAMPLE" cannot be made
because a previous restore is incomplete. SQLSTATE=57019
$ db2 restore db sample incremental use tsm taken at 20030111224424 buffer 100
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
Database Connection Information
Database server = DB2/6000 7.2.5
SQL authorization ID = DB2SETT
Local database alias = SAMPLE
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2SETT T 2003-01-11-19.36.48.851173
DEPARTMENT DB2SETT T 2003-01-11-19.36.46.861995
EMP_ACT DB2SETT T 2003-01-11-19.36.47.096736
EMP_PHOTO DB2SETT T 2003-01-11-19.36.47.589805
EMP_RESUME DB2SETT T 2003-01-11-19.36.48.366847
EMPLOYEE DB2SETT T 2003-01-11-19.36.46.921911
IN_TRAY DB2SETT T 2003-01-11-19.36.48.877535
ORG DB2SETT T 2003-01-11-19.36.46.493357
PROJECT DB2SETT T 2003-01-11-19.36.47.487168
SALES DB2SETT T 2003-01-11-19.36.48.597747
STAFF DB2SETT T 2003-01-11-19.36.46.679481
11 record(s) selected.
DB2的资料好少！没有找到标准的备份样例。我现在要做一个db2的备份脚本，硬件环境是这样的：M85*2，hacmp es 441的环境，aix 433.10，db2版本 7.2.5 EEE，设置如下：
一个实例，主节点的目录NFS mount ， 存放catalog， 每个物理节点一个逻辑node。我要实现的要求是：每月一次全备，每周一次一级增量，即incremental 备份。备份使用TSM 5.1 , 磁带库。TSM和磁带库已经设置好。现在就差client的schedule了。
我做实验如下：
db2sampl 展开一个sample库，设置参数logretain和trackmod后，依次做主节点，用户表空间节点的offline的全备，之后向sample库建新表，insert数据，改变数据库的同时，依次做了一次online 的full backup 和online 的incremental 、delta 备份。也是先後做主节点和另外的节点。（我这里说的节点按照逻辑node说）
之后，drop sample 进行恢复。
首先，第一次试验，我恢复db2 主节点的history file :
$ db2 restore db sample history file use tsm taken at 20030112223107 buffer 100
DB20000I The RESTORE DATABASE command completed successfully.
之后恢复次节点的：
$ db2 restore db sample history file use tsm taken at 20030113163719 buffer 100
SQL2565N The option specified for RESTORE is not allowed with the backup image provided.
就不行了。
不管它继续，在主节点上操作：
$ db2 list history backup all for sample
List History File for sample
Number of matching file entries = 19
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20030111200331000 F S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20030111200331
End Time: 20030111200332
----------------------------------------------------------------------------
00001 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2009 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubcka
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
。。。。。。。。。。。
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
0 record(s) selected.
没有返回。
$ db2 restore db sample taken at 20030111224424 buffer 100
SQL2542N No match for a database image file was found based on the source
database alias "SAMPLE" and timestamp "20030111224424" provided.
$ db2ckrst -d sample -t 20030111224424 -r database
Suggested restore order of images using timestamp 20030111224424 for
database sample.
====================================================================
restore db sample incremental taken at 20030111224424
restore db sample incremental taken at 20030111224424
====================================================================
这个image 是个offline的full backup ， 可是db2ckrst提示要用incremental 参数！！ok 照做：
$ db2 restore db sample incremental use tsm taken at 20030111224424 buffer 100
SQL2523W Warning! Restoring to an existing database that is different from the
database on the backup image, but have matching names. The target database will b
e overwritten by the backup version. The Roll-forward recovery logs associated w
ith the target database will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
SQL1119N A connection to or activation of database "SAMPLE" cannot be made
because a previous restore is incomplete. SQLSTATE=57019
$ db2 restore db sample incremental use tsm taken at 20030111224424 buffer 100
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
Database Connection Information
Database server = DB2/6000 7.2.5
SQL authorization ID = DB2SETT
Local database alias = SAMPLE
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2SETT T 2003-01-11-19.36.48.851173
DEPARTMENT DB2SETT T 2003-01-11-19.36.46.861995
EMP_ACT DB2SETT T 2003-01-11-19.36.47.096736
EMP_PHOTO DB2SETT T 2003-01-11-19.36.47.589805
EMP_RESUME DB2SETT T 2003-01-11-19.36.48.366847
EMPLOYEE DB2SETT T 2003-01-11-19.36.46.921911
IN_TRAY DB2SETT T 2003-01-11-19.36.48.877535
ORG DB2SETT T 2003-01-11-19.36.46.493357
PROJECT DB2SETT T 2003-01-11-19.36.47.487168
SALES DB2SETT T 2003-01-11-19.36.48.597747
STAFF DB2SETT T 2003-01-11-19.36.46.679481
11 record(s) selected.
...全文
zdybj 2003年01月22日
可以再撒分！各位帮忙啊，谢谢了！！
rainbowcn 2003年01月18日
学习中
zdybj 2003年01月14日
这时有表了，恢复次节点的，没有history，直接做版本恢复：
$ db2 restore db sample use tsm taken at 20030113154911 buffer 100
SQL2523W Warning! Restoring to an existing database that is different from the database on the backup image, but have matching nam
es. The target database will be overwritten by the backup version. The Roll-forward recovery logs associated with the target databa
se will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
SQL1117N A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
为什么？这个image是offline的full backup啊，还需要roll forward么?
我不知如何做了。同时感觉主节点的那个full backup 加incremental很怪异，就放弃了这次恢复。
第二次试验：
还是主节点history file restore , 用最新的即最后一次incremental delta的image做.之后按照db2ckrst的提示，做两次的offline的offline的full backup 的restore，这时次节点没有理它，直接再做主节点的online full backup的restore和两次incrementtal 、delta的restore，之后roll forward， 错误来了，告诉我：
$ db2 rollforward db sample to end of logs and stop
SQL1260N Database "SAMPLE" is not configured for roll-forward recovery on node(s) "20"
node 20 既是我的次节点的node number。于是去恢复次节点：
发现次节点有db cfg file了，于是update logretain和trackmod，结果进入了backup pending，要求我做backup, disconnect做offline的full backup ，提示我：
$ db2 backup db sample use tsm buffer 100
SQL0276N Connection to database "SAMPLE" cannot be made because it is in the restore pending state. SQLSTATE=08004
这下坏了！！
我不太懂这些东西。所以操作有些乱。反正恢复失败了。我现在的问题如下：
1、为何同一个image，db2ckrst提示要做两次的恢复！！？而且只恢复一次确实不行，这时提示restore incomplete：
$ db2 restore db sample incremental use tsm taken at 20030113161751 buffer 100
SQL2523W Warning! Restoring to an existing database that is different from the
database on the backup image, but have matching names. The target database will
be overwritten by the backup version. The Roll-forward recovery logs associate
d with the target database will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
SQL1119N A connection to or activation of database "SAMPLE" cannot be made
because a previous restore is incomplete. SQLSTATE=57019
2、是不是需要先做主次节点的版本恢复，才可以再做online的image的恢复？可是做版本恢复又提示我要rollforward，不知怎么办了！！是要rollforward到这个版本image的时间点么？
3、看restore还有automatic的子句,又如何可用呢？在我这个恢复计划里。
4、db2 list applications提示：
$ db2 list applications
SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032
这个命令是在db2sampl后，connect to sample后执行的，记得以前可以返回很多ID的，现在不行了，是什么agent没有启动呢？我没有中心db2stop或者force 、shutdown什么的。
5、History file 不能在次节点恢复么？
6、谁有完整的恢复脚本呢？我这么backup 可以么？哪里有详细一点的EEE版的管理、备份、恢复等的手册？比如log file 的管理，等等。各位大虾不吝赐教！！！！！先谢过了！！各位DBA们帮忙露一手吧!!
另外，看Data Recovery and High Availability Guide and Reference，提供的样例如下：
-- Before proceeding, ensure that:
-- The database manager is running
-- The SAMPLE database exists and is not in use.
-- Run the script by issuing:
-- db2 -f backrest.db2 -t
-- where -f tells the command line processor to read command input
-- from a file instead of from standard input, and
-- -t tells the command line processor to use a semicolon (
-- as the statement termination character.
echo This is CLP script: backrest.db2;
-- Ensure that the DB2 profile registry variable DB2_ENABLE_LDAP
-- is set to 'NO':
!db2set DB2_ENABLE_LDAP=NO;
echo Deleting old SAMPLE database backup images...;
!rm -f ./SAMPLE.0.*;
echo Updating the database configuration parameter LOGRETAIN to 'ON'...;
update db cfg for sample using logretain on;
echo Backing up the SAMPLE database...;
backup db sample;
echo Restoring the SAMPLE database as TESTBACK (1st pass)...;
restore db sample into testback redirect;
echo Listing the table spaces for the TESTBACK database...;
list tablespaces;
echo Defining new table space containers for Tablespace 2...;
set tablespace containers for 2 using (path"ts2con1");
echo Listing table space containers for Tablespace 2 (TESTBACK database)...;
list tablespace containers for 2;
echo Restoring the SAMPLE database as TESTBACK (2nd pass)...;
restore db sample continue;
echo Rolling the TESTBACK database forward...;
rollforward db testback stop;
echo Dropping the TESTBACK database...;
drop db testback;
echo Terminating the command line processor's back-end process...;
terminate;
-- End file
$ db2 restore db sample use tsm taken at 20030113154911 buffer 100
SQL2523W Warning! Restoring to an existing database that is different from the database on the backup image, but have matching nam
es. The target database will be overwritten by the backup version. The Roll-forward recovery logs associated with the target databa
se will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
SQL1117N A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
为什么？这个image是offline的full backup啊，还需要roll forward么?
我不知如何做了。同时感觉主节点的那个full backup 加incremental很怪异，就放弃了这次恢复。
第二次试验：
还是主节点history file restore , 用最新的即最后一次incremental delta的image做.之后按照db2ckrst的提示，做两次的offline的offline的full backup 的restore，这时次节点没有理它，直接再做主节点的online full backup的restore和两次incrementtal 、delta的restore，之后roll forward， 错误来了，告诉我：
$ db2 rollforward db sample to end of logs and stop
SQL1260N Database "SAMPLE" is not configured for roll-forward recovery on node(s) "20"
node 20 既是我的次节点的node number。于是去恢复次节点：
发现次节点有db cfg file了，于是update logretain和trackmod，结果进入了backup pending，要求我做backup, disconnect做offline的full backup ，提示我：
$ db2 backup db sample use tsm buffer 100
SQL0276N Connection to database "SAMPLE" cannot be made because it is in the restore pending state. SQLSTATE=08004
这下坏了！！
我不太懂这些东西。所以操作有些乱。反正恢复失败了。我现在的问题如下：
1、为何同一个image，db2ckrst提示要做两次的恢复！！？而且只恢复一次确实不行，这时提示restore incomplete：
$ db2 restore db sample incremental use tsm taken at 20030113161751 buffer 100
SQL2523W Warning! Restoring to an existing database that is different from the
database on the backup image, but have matching names. The target database will
be overwritten by the backup version. The Roll-forward recovery logs associate
d with the target database will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 connect to sample
SQL1119N A connection to or activation of database "SAMPLE" cannot be made
because a previous restore is incomplete. SQLSTATE=57019
2、是不是需要先做主次节点的版本恢复，才可以再做online的image的恢复？可是做版本恢复又提示我要rollforward，不知怎么办了！！是要rollforward到这个版本image的时间点么？
3、看restore还有automatic的子句,又如何可用呢？在我这个恢复计划里。
4、db2 list applications提示：
$ db2 list applications
SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032
这个命令是在db2sampl后，connect to sample后执行的，记得以前可以返回很多ID的，现在不行了，是什么agent没有启动呢？我没有中心db2stop或者force 、shutdown什么的。
5、History file 不能在次节点恢复么？
6、谁有完整的恢复脚本呢？我这么backup 可以么？哪里有详细一点的EEE版的管理、备份、恢复等的手册？比如log file 的管理，等等。各位大虾不吝赐教！！！！！先谢过了！！各位DBA们帮忙露一手吧!!
另外，看Data Recovery and High Availability Guide and Reference，提供的样例如下：
-- Before proceeding, ensure that:
-- The database manager is running
-- The SAMPLE database exists and is not in use.
-- Run the script by issuing:
-- db2 -f backrest.db2 -t
-- where -f tells the command line processor to read command input
-- from a file instead of from standard input, and
-- -t tells the command line processor to use a semicolon (
-- as the statement termination character.
echo This is CLP script: backrest.db2;
-- Ensure that the DB2 profile registry variable DB2_ENABLE_LDAP
-- is set to 'NO':
!db2set DB2_ENABLE_LDAP=NO;
echo Deleting old SAMPLE database backup images...;
!rm -f ./SAMPLE.0.*;
echo Updating the database configuration parameter LOGRETAIN to 'ON'...;
update db cfg for sample using logretain on;
echo Backing up the SAMPLE database...;
backup db sample;
echo Restoring the SAMPLE database as TESTBACK (1st pass)...;
restore db sample into testback redirect;
echo Listing the table spaces for the TESTBACK database...;
list tablespaces;
echo Defining new table space containers for Tablespace 2...;
set tablespace containers for 2 using (path"ts2con1");
echo Listing table space containers for Tablespace 2 (TESTBACK database)...;
list tablespace containers for 2;
echo Restoring the SAMPLE database as TESTBACK (2nd pass)...;
restore db sample continue;
echo Rolling the TESTBACK database forward...;
rollforward db testback stop;
echo Dropping the TESTBACK database...;
drop db testback;
echo Terminating the command line processor's back-end process...;
terminate;
-- End file
发动态
发帖子
DB2
创建于2007-08-27
加入
IBM DB2 是美国IBM公司开发的一套关系型数据库管理系统，它主要的运行环境为UNIX（包括IBM自家的AIX）、Linux、IBM i（旧称OS/400）、z/OS，以及Windows服务器版本
社区公告
暂无公告