DB2備份和恢復

本章介紹DB2數據庫備份和恢復數據庫的方法。

備份和恢復方法的目的是讓我們的信息安全。在命令行界面(CLI)或圖形用戶界面(GUI)使用的備份和恢復工具，可以備份或恢復在DB2 UDB數據庫中的數據。

日誌

日誌文件包含錯誤日誌，這是用來從應用程序錯誤中恢復的。日誌保留更改數據庫的記錄。有如下所述兩種類型的日誌記錄：

循環日誌記錄

它是一種方法，其中舊的事務日誌被覆蓋時，有必要分配一個新的事務日誌文件，從而清除日誌文件的序列和重複使用它們。允許需要全備份在離線模式。也就是說，數據庫必須脫機採取完全備份。

歸檔日誌記錄

該模式支持在線備份，並使用被稱爲前滾恢復日誌文件數據庫恢復。備份模式可以從循環改爲通過設置LOGRETAIN或USEREXIT爲ON存檔。對於歸檔日誌記錄，備份設置數據庫需要的目錄是可寫的DB2進程。

備份

使用Backup命令，可以把整個數據庫備份副本。該備份副本包括數據庫系統文件，數據文件，日誌文件，控制信息等。

可以備份脫機工作時或在線。

脫機備份

語法：[列出活動的應用/數據庫]

db2 list application

輸出：

Auth Id Application Appl. Application Id DB # of Name Handle Name Agents -------- -------------- ---------- --------------------- ----------------------------------------- -------- ----- DB2INST1 db2bp 39 *LOCAL.db2inst1.140722043938 ONE 1

語法：[使用的應用程序強制應用程序。處理ID]

db2 "force application (39)"

輸出

DB20000I The FORCE APPLICATION command completed
successfully. DB21024I This command is asynchronous and may not be effective immediately.

語法：[終止數據庫連接]

db2 terminate

語法：[關閉數據庫]

db2 deactivate database one

語法：[執行備份文件]

db2 backup database <db_name> to

示例：

db2 backup database one to /home/db2inst1/

輸出

Backup successful. The timestamp for this backup image is : 20140722105345

在線備份

首先，需要從循環日誌改變到歸檔日誌模式。

語法：[檢查數據庫使用循環或歸檔日誌]

db2 get db cfg for one | grep LOGARCH

輸出

First log archive method (LOGARCHMETH1) = OFF Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF Options for logarchmeth2 (LOGARCHOPT2) =

另外，在上述輸出，突出顯示的值處於關閉模式[LOGARCHMETH1和LOGARCHMETH2]，這意味着當前數據庫中的「CIRCULLARLOGGING」模式。如果需要用'歸檔日誌記錄「模式下工作，需要更改或添加路徑變量LOGARCHMETH1和LOGARCHMETH2出現在配置文件中。

更新LOGARCHMETH1所需存檔目錄

語法：[創建目錄]

mkdir backup
mkdir backup/ArchiveDest

語法：[提供文件夾的用戶權限]

chown db2inst1:db2iadm1 backup/ArchiveDest

語法：[更新配置LOGARCHMETH1]

db2 update database configuration for one using LOGARCHMETH1 'DISK:/home/db2inst1/backup/ArchiveDest'

可以採取離線備份的安全性，激活數據庫，並連接到它。

語法：[執行在線備份]

db2 backup database one online to /home/db2inst1/onlinebackup/ compress include logs

輸出

db2 backup database one online to /home/db2inst1/onlinebackup/ compress include logs

使用下面的命令驗證備份文件：

語法：

db2ckbkp <location/backup file>

示例：

db2ckbkp /home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001

清單備份文件的歷史記錄

語法：

db2 list history backup all for one

輸出

List History File for one Number of matching file entries = 4 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- B D 20140722105345001 F D S0000000.LOG S0000000.LOG ------------------------------------------------------------ ---------------- Contains 4 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE 00004 TS1 ------------------------------------------------------------ ---------------- Comment: DB2 BACKUP ONE OFFLINE Start Time: 20140722105345 End Time: 20140722105347 Status: A ------------------------------------------------------------ ---------------- EID: 3 Location: /home/db2inst1 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- B D 20140722112239000 N S0000000.LOG S0000000.LOG ------------------------------------------------------------ ------------------------------------------------------------- ------------------------------- Comment: DB2 BACKUP ONE ONLINE Start Time: 20140722112239 End Time: 20140722112240 Status: A ------------------------------------------------------------ ---------------- EID: 4 Location: SQLCA Information sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2413 sqlerrml: 0 sqlerrmc: sqlerrp : sqlubIni
sqlerrd : (1) 0 (2) 0 (3) 0 (4) 0 (5) 0 (6) 0 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- B D 20140722112743001 F D S0000000.LOG S0000000.LOG ------------------------------------------------------------ ---------------- Contains 4 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE 00004 TS1 ------------------------------------------------------------- ---------------- Comment: DB2 BACKUP ONE OFFLINE Start Time: 20140722112743 End Time: 20140722112743 Status: A ------------------------------------------------------------- ---------------- EID: 5 Location: /home/db2inst1 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID ------------------------------------------------------------- ---------------- R D 20140722114519001 F 20140722112743 ------------------------------------------------------------ ---------------- Contains 4 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 00003 SYSTOOLSPACE 00004 TS1 ------------------------------------------------------------ ---------------- Comment: RESTORE ONE WITH RF Start Time: 20140722114519 End Time: 20140722115015 Status: A ------------------------------------------------------------ ---------------- EID: 6 Location:

從備份中恢復數據庫

從備份文件恢復數據庫，需要按照給定的語法：

語法：

db2 restore database <db_name> from taken at

示例：

db2 restore database one from /home/db2inst1/ taken at 20140722112743

輸出：

SQL2523W Warning! Restoring to an existing database that is different from the database on the backup image, but have matching names. The target database

will be overwritten by the backup version. The Roll-forward
recovery logs

associated with the target database will be deleted. Do you want to continue ? (y/n) y

DB20000I The RESTORE DATABASE command completed successfully.

前滾全部位於日誌目錄中的日誌，其中包括剛剛在磁盤驅動器發生故障前的最新變化。

語法：

db2 rollforward db <db_name> to end of logs and stop

示例：

db2 rollforward db one to end of logs and stop

輸出：

Rollforward Status Input database alias = one Number of members have returned status = 1 Member ID = 0 Rollforward status = not pending Next log file to be read = Log files processed = S0000000.LOG - S0000001.LOG Last committed transaction = 2014-07-22- 06.00.33.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.