db2 备份还原

时间:2021-09-22 18:01:17

一、导入导出

ixf:

db2 export to /tmp/xxx.csv of ixf lobs to . xml to . modified by codepage=1208 "select * from sysibm.dual"

db2 import from tablename of ixf  insert into schema.tablename

del:

db2 export to /tmp/file.csv of del modified by codepage=1208  "select * from sysibm.dual"

db2 import from tablename of del  insert into schema.tablename

导出表结构

db2look -d $DBNAME -z schema -e -dp -cor -o test1.sql

导出表数据

db2move $DBNAME export -sn sample

二、备份还原

离线备份:

db2 backup db sample to /data/dbbak/ compress

离线还原:

db2 restore database sample from  /data/dbbak/ into newdbname taken at 20090622164642

在线备份:

db2 backup db sample online  to /data/dbbak/ compress include logs

切记,需要拷贝备份时候生成的活动日志,否则无法还原

在线还原:

1、生成还原脚本,修改最终容器路径及数量

db2 restore db sample from /data/dbbak/ taken at 20090622164642 into sample redirect generate script xxxx.sql

2、执行还原

db2 -tvf xxxx.sql -z xxxx.log

把备份时候复制的活动日志拷贝到当前数据库的活动日志目录去,然后执行下面的前滚

db2 rollforward database sample to end of backup and complete

使用在线备份,需要配置归档模式

v9.7之前

db2 update db cfg for sample using userexit on

db2 update db cfg for sample using logretain on

db2 update db cfg for sample using trackmod on

v10.0

db2 update db cfg for sample using LOGARCHMENT1 "DISK:/data/archive"

db2 update db cfg for sample using trackmod on