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 <location>
示例：
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 <location> taken at <timestamp>
示例：
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.
