DB2利用表空间备份重建数据库

对于数据库上了T级,或数十T,全库备份一次时长太长了. 如果只是数据库中一部份表空间的更新比较频繁. 可以用表空间备份频繁备份那一部份好了.其它放在全备一起,全备的备份频率可以适当降低点. 而且比较有特色的是它可以在有日志和数据库表空间备份,但没有数据库全备份的情况下，利用表空间备份重建出数据库. DB2这个功能还是比较不错的。

1. 准备好目录 su - db2inst1 mkdir -p /home/db2inst1/xcldb_tb cd /home/db2inst1/xcldb_tb mkdir tbs1 tbs2 tbs3

2. 准备好表空间 db2 "connect to xcldb" db2 "create tablespace tbs1" db2 "create tablespace tbs2" db2 "create tablespace tbs3"
db2 "create table t1(a int) in tbs1" db2 "create table t2(a int) in tbs2" db2 "create table t3(a int) in tbs3" db2 "insert into t1 values(1)" db2 "insert into t2 values(2)" db2 "insert into t3 values(3)"

3.备份数据库的表空间 db2 connect to xcldb --查看表空间明细 db2 list tablespaces show detail --查看表空间使用的容器 db2 list tablespace containers for 0 --执行备份 -- 重建的表空间中必须包括 SYSCATSPACE(系统编目) 20131217232023 db2 "backup db xcldb tablespace(SYSCATSPACE,USERSPACE1,SYSTOOLSPACE,TBS1,TBS2,TBS3) to /home/db2inst1/xcldb_tb "

4.破坏数据库,将数据库目录下的东西全删除了 这里注意不要用db2 drop db 来删除库 --删除文件 [db2inst1@O11g64 db2inst1]$ cd /home/db2inst1/db2inst1/NODE0000/XCLDB [db2inst1@O11g64 XCLDB]$ ls T0000000 T0000001 T0000002 T0000003 T0000004 T0000005 T0000006 [db2inst1@O11g64 XCLDB]$ rm -rf * [db2inst1@O11g64 XCLDB]$ ls --停掉应用 [db2inst1@O11g64 XCLDB]$ db2 force application all DB20000I The FORCE APPLICATION command completed successfully. DB21024I This command is asynchronous and may not be effective immediately. --停掉实例 [db2inst1@O11g64 XCLDB]$ db2stop 12/17/2013 23:52:00 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. --重新启动 [db2inst1@O11g64 XCLDB]$ db2start 12/17/2013 23:52:04 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. --再连接就没了 [db2inst1@O11g64 XCLDB]$ db2 connect to xcldb SQL0293N Error accessing a table space container. SQLSTATE=57048 [db2inst1@O11g64 XCLDB]$ cd /home/db2inst1/xcldb_tb [db2inst1@O11g64 xcldb_tb]$ ls cnfbk.sh tbs1 tbs2 tbs3 XCLDB.3.db2inst1.NODE0000.CATN0000.20131217232023.001

5.恢复 [db2inst1@O11g64 xcldb_tb]$ db2 restore db xcldb rebuild with all tablespaces in database taken at 20131217232023 SQL2561W Warning! Rebuilding a database from a table space image or using a subset of table spaces. The target database will be overwritten. The restore utility also reports the following sqlcode "2539". Do you want to continue ? (y/n) y DB20000I The RESTORE DATABASE command completed successfully.

6.前滚 --能查到备份信息,说明有恢复出来库 ,但还需要前滚才能恢复 [db2inst1@O11g64 xcldb_tb]$ db2 list history all for xcldb --前滚数据库 [db2inst1@O11g64 xcldb_tb]$ db2 rollforward db xcldb to end of logs SQL1268N Roll-forward recovery stopped due to error "24" while retrieving log file "S0000001.LOG" for database "XCLDB" on node "0". [db2inst1@O11g64 xcldb_tb]$ db2 rollforward db xcldb stop Rollforward Status Input database alias = xcldb Number of nodes have returned status = 1 Node number = 0 Rollforward status = not pending Next log file to be read = Log files processed = - Last committed transaction = 2013-12-17-15.20.23.000000 UTC DB20000I The ROLLFORWARD command completed successfully.

7.检测 [db2inst1@O11g64 xcldb_tb]$ db2 connect to xcldb Database Connection Information Database server = DB2/LINUXX8664 9.1.3 SQL authorization ID = DB2INST1 Local database alias = XCLDB [db2inst1@O11g64 xcldb_tb]$ db2 "select * from t1" A ----------- 1 1 record(s) selected.
至此，重建成功。