db2備份和恢復(fù)

本章介紹db2數(shù)據(jù)庫(kù)備份和恢復(fù)數(shù)據(jù)庫(kù)的方法。

備份和恢復(fù)方法的目的是讓我們的信息安全。在命令行界面(cli)或圖形用戶(hù)界面(gui)使用的備份和恢復(fù)工具，可以備份或恢復(fù)在db2 udb數(shù)據(jù)庫(kù)中的數(shù)據(jù)。

日志

日志文件包含錯(cuò)誤日志，這是用來(lái)從應(yīng)用程序錯(cuò)誤中恢復(fù)的。日志保留更改數(shù)據(jù)庫(kù)的記錄。有如下所述兩種類(lèi)型的日志記錄：

循環(huán)日志記錄

它是一種方法，其中舊的事務(wù)日志被覆蓋時(shí)，有必要分配一個(gè)新的事務(wù)日志文件，從而清除日志文件的序列和重復(fù)使用它們。允許需要全備份在離線模式。也就是說(shuō)，數(shù)據(jù)庫(kù)必須脫機(jī)采取完全備份。

歸檔日志記錄

該模式支持在線備份，并使用被稱(chēng)為前滾恢復(fù)日志文件數(shù)據(jù)庫(kù)恢復(fù)。備份模式可以從循環(huán)改為通過(guò)設(shè)置logretain或userexit為on存檔。對(duì)于歸檔日志記錄，備份設(shè)置數(shù)據(jù)庫(kù)需要的目錄是可寫(xiě)的db2進(jìn)程。

備份

使用backup命令，可以把整個(gè)數(shù)據(jù)庫(kù)備份副本。該備份副本包括數(shù)據(jù)庫(kù)系統(tǒng)文件，數(shù)據(jù)文件，日志文件，控制信息等。

可以備份脫機(jī)工作時(shí)或在線。

脫機(jī)備份

語(yǔ)法：[列出活動(dòng)的應(yīng)用/數(shù)據(jù)庫(kù)]

db2 list application

輸出：

auth id  application    appl.      application id                                                
db       # of   
         name           handle              
name    agents  
-------- -------------- ---------- ---------------------
----------------------------------------- -------- -----  
db2inst1 db2bp          39         
*local.db2inst1.140722043938                                   
one      1

語(yǔ)法：[使用的應(yīng)用程序強(qiáng)制應(yīng)用程序。處理id]

db2 "force application (39)"

輸出

db20000i  the force application command completed
successfully.

db21024i  this command is asynchronous and may not
be effective immediately.

語(yǔ)法：[終止數(shù)據(jù)庫(kù)連接]

db2 terminate

語(yǔ)法：[關(guān)閉數(shù)據(jù)庫(kù)]

db2 deactivate database one

語(yǔ)法：[執(zhí)行備份文件]

db2 backup database <db_name> to <location>

示例：

db2 backup database one to /home/db2inst1/

輸出

backup successful. the timestamp for this backup image is :
20140722105345

在線備份

首先，需要從循環(huán)日志改變到歸檔日志模式。

語(yǔ)法：[檢查數(shù)據(jù)庫(kù)使用循環(huán)或歸檔日志]

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) =

另外，在上述輸出，突出顯示的值處于關(guān)閉模式[logarchmeth1和logarchmeth2]，這意味著當(dāng)前數(shù)據(jù)庫(kù)中的“circullarlogging”模式。如果需要用'歸檔日志記錄“模式下工作，需要更改或添加路徑變量logarchmeth1和logarchmeth2出現(xiàn)在配置文件中。

更新logarchmeth1所需存檔目錄

語(yǔ)法：[創(chuàng)建目錄](méi)

mkdir backup
mkdir backup/archivedest

語(yǔ)法：[提供文件夾的用戶(hù)權(quán)限]

chown db2inst1:db2iadm1 backup/archivedest

語(yǔ)法：[更新配置logarchmeth1]

db2 update database configuration for one using logarchmeth1
'disk:/home/db2inst1/backup/archivedest'

可以采取離線備份的安全性，激活數(shù)據(jù)庫(kù)，并連接到它。

語(yǔ)法：[執(zhí)行在線備份]

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

輸出

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

使用下面的命令驗(yàn)證備份文件：

語(yǔ)法：

db2ckbkp <location/backup file>

示例：

db2ckbkp
/home/db2inst1/one.0.db2inst1.dbpart000.20140722112743.001

清單備份文件的歷史記錄

語(yǔ)法：

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:

從備份中恢復(fù)數(shù)據(jù)庫(kù)

從備份文件恢復(fù)數(shù)據(jù)庫(kù)，需要按照給定的語(yǔ)法：

語(yǔ)法：

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.

前滾全部位于日志目錄中的日志，其中包括剛剛在磁盤(pán)驅(qū)動(dòng)器發(fā)生故障前的最新變化。

語(yǔ)法：

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.