分区数据库备份和恢复的问题。
zdybj 2003-01-14 04:33:55 各位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.