首页 > 代码库 > DB2数据库的备份与恢复
DB2数据库的备份与恢复
一、 查看数据库
[db2inst1@localhost ~]$ db2 list db directory
二、 创建测试数据库
$ db2 create database test
三、 启动进入创建的测试库
$ db2 connect to test
l 列出激活的库
$ db2 list active databases
l 查看表空间
$ db2 list tablespaces
l 列出库中的表
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
0 record(s) selected.
l 创建表
$ db2 "create table student(id int,fname varchar(30),age int)"
l 赋予用户权限
db2 "grant use of tablespace TABLESPACE_TEST to user XXX"
l 向表中插入数据
$ db2 "insert into student values (1,‘Tom‘,22)"
l 查询表内容
四、 在线备份
备份指定库
$ db2 backup db TEST online to /opt/dbbak include logs
如果报：
SQL2413N Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
原因：数据库在线备份需要启用归档日志功能,刚装的数据库是没有启用的
备份到null
db2 backup db idsldap online to /dev/null
Backup successful. The timestamp for this backup image is : 20161228092925
增量备份：
/usr/bin/su - db2inst1 -c "db2 backup db FDBKDB61 online incremental use tsm >>/IBM/tsm/db2inst1/FDBKDB61_incbk.log 2>&1"
l 查看归档设置
$ db2 get db cfg for test|grep -i LOGARCHMETH
l 设置归档路径
$ db2 update db cfg using logarchmeth1 disk:/opt/dbbak/arch
如果报
SQL1024N A database connection does not exist. SQLSTATE=08003
原因:需要备份数据库一次,然后在修改主日志文件和此日志文件的归档路径
解决方法:要连接数据库时要备份一下。
解决方法：（需要离线备份一次，再连接数据库）
l 显示应用状态
$ db2 list applications
l 停掉数据库应用
$ db2 force applications all
l 再查看应用状态，无输出
$ db2 list applications
SQL1611W No data was returned by Database System Monitor.
l 做离线备份
$ db2 backup db TEST to /opt/dbbak
l 生成备份文件和归档日志
l 查看备份记录
> db2adutl query //该命令是TSM集成命令，无TSM环境不能执行
$ db2 list history backup all for test
l 启动进入库
$ db2 connect to test
l 再做在线备份，可以进行
$ db2 backup db TEST online to /opt/dbbak include logs
五、 删除数据库模拟破坏
$ db2 drop db test
$ db2 connect to test
SQL1013N The database alias name or database name "TEST" could not be found.
SQLSTATE=42705
六、 恢复数据库
$ db2 restore db TEST from /opt/bak taken at 20170123152954
$ db2 restore db test taken at 20160513020408
这时候，连接数据库，提示：
SQL1117N A connection to or activation of database "TEST" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
在前滚已经过大于或等于 这个 的时间点之前，数据库 "XXX"
不能脱离前滚暂挂状态，因为节点 "0" 中包含比指定时间更新的信息
解决办法：前滚日志
$ db2 rollforward database test to end of logs and complete
如果报：
SQL4970N Rollforward recovery stopped on the database named "TEST" because
the rollforward utility cannot reach the specified stop point (end-of-log or
point-in-time) on the following database partitions: "0".
解决办法：恢复日志
$ db2 restore db test logs from /opt/dbbak/ taken at 20160513020408 logtarget /opt/dbbak/
复制恢复的归档文件
$ cp /opt/dbbak/S0000000.LOG /home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREAM0000/LOGSTREAM0000
l 启动进入数据库
l 查询表和数据
数据恢复回来
l 关闭应用和库
$ db2 force applications all
$ db2stop force
SQL1064N DB2STOP processing was successful.
DB2数据库的备份与恢复