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.
至此，重建成功。
參考:
Blog: http://blog.csdn.net/xcl168
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！