對於數據庫上了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.
至此，重建成功。
