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.