DB2數(shù)據(jù)庫的備份與恢復(fù)

一、    查看數(shù)據(jù)庫

[db2inst1@localhost ~]$ db2 list db directory

二、    創(chuàng)建測試數(shù)據(jù)庫

$ db2 create database test

三、    啟動進(jìn)入創(chuàng)建的測試庫

$ 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  創(chuàng)建表

$ db2 "create table student(id int,fname varchar(30),age int)"

l  賦予用戶權(quán)限

db2 "grant use of tablespace  TABLESPACE_TEST to user XXX"

l  向表中插入數(shù)據(jù)

$ db2 "insert into student values (1,'Tom',22)"

l  查詢表內(nèi)容

四、    在線備份

備份指定庫

$ 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.

原因：數(shù)據(jù)庫在線備份需要啟用歸檔日志功能,剛裝的數(shù)據(jù)庫是沒有啟用的

備份到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  查看歸檔設(shè)置

$ db2 get db cfg for test|grep -i LOGARCHMETH

l  設(shè)置歸檔路徑

$ db2 update db cfg using logarchmeth2 disk:/opt/dbbak/arch

如果報

SQL1024N  A database connection does not exist.  SQLSTATE=08003

原因:需要備份數(shù)據(jù)庫一次,然后在修改主日志文件和此日志文件的歸檔路徑

解決方法:要連接數(shù)據(jù)庫時要備份一下。

解決方法：（需要離線備份一次，再連接數(shù)據(jù)庫）

l  顯示應(yīng)用狀態(tài)

$ db2 list applications

l  停掉數(shù)據(jù)庫應(yīng)用

$ db2 force applications all

l  再查看應(yīng)用狀態(tài)，無輸出

$ 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環(huán)境不能執(zhí)行

$ db2 list history backup all for test

l  啟動進(jìn)入庫

$ db2 connect to test

l  再做在線備份，可以進(jìn)行

$ db2 backup db TEST online to /opt/dbbak include logs

五、    刪除數(shù)據(jù)庫模擬破壞

$ db2 drop db test

$ db2 connect to test

SQL1013N  The database alias name or database name "TEST" could not be found. 

SQLSTATE=42705

六、    恢復(fù)數(shù)據(jù)庫

$ db2 restore db TEST from /opt/bak taken at 20170123152954

$ db2 restore db test taken at 20160513020408

這時候，連接數(shù)據(jù)庫，提示：

SQL1117N  A connection to or activation of database "TEST" cannot be made

because of ROLL-FORWARD PENDING.  SQLSTATE=57019

在前滾已經(jīng)過大于或等于 這個 的時間點之前，數(shù)據(jù)庫 "XXX"

不能脫離前滾暫掛狀態(tài)，因為節(jié)點 "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".

解決辦法：恢復(fù)日志

$ db2 restore db test logs from /opt/dbbak/ taken at 20160513020408 logtarget /opt/dbbak/

復(fù)制恢復(fù)的歸檔文件

$ cp /opt/dbbak/S0000000.LOG /home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREAM0000/LOGSTREAM0000

l  啟動進(jìn)入數(shù)據(jù)庫

l  查詢表和數(shù)據(jù)

數(shù)據(jù)恢復(fù)回來

l  關(guān)閉應(yīng)用和庫

$ db2 force applications all

$ db2stop force

SQL1064N  DB2STOP processing was successful.