DB2資料庫利用備份實現異機還原執行個體分析

   一生產的DB2資料庫需要將其資料全部備份，然後還原匯入到一測試庫中，中間遇到一些問題，最後還是圓滿解決了，現將步驟記錄下來方便學習和分析。

系統內容：AIX5.3

資料庫： DB2 V9.1

兩台伺服器：  生產伺服器 192.168.11.178

                          測試伺服器 10.10.11.81

一、備份

分別備份一下生產伺服器和測試伺服器的資料庫兩個伺服器上資料庫一樣）

$  db2 backup db LAW online to /basefsnew/db2bak0111 include logs             #生產

備份成功。此備份映像的時間戳記是：20130111180236

$  db2 backup db LAW online to /basefs/db2bak0111 include logs      #測試

備份成功。此備份映像的時間戳記是：20130111190381

二、將生產庫的備份傳送到測試伺服器

大家可以利用SCP命令或其他檔案傳輸軟體來傳送備份，這裡提醒一下傳過來的備份需要改屬主和屬組還有許可權，否則將導致還原時出錯

三、具體還原過程和問題解決

$ db2 force applications all   先停止所有應用串連）
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

在測試伺服器上開始還原
$ db2 restore db LAW from /home taken at 20130111180236
SQL2539W  Warning!  Restoring to an existing database that is the same as the backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.
還原成功

但隨後串連資料庫時報錯

$ db2 connect to LAW
SQL1117N  A connection to or activation of database "LAW" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

這個提示是說需要前滾期間的日誌才能啟用資料庫

下面執行此句進行前滾

$ db2 rollforward db LAW to end of logs and complete
SQL4970N  Roll-forward recovery on database "LAW" cannot reach the specified
stop point (end-of-log or point-in-time) because of missing log file(s) on
node(s) "0".

提示缺失日誌，不能到達結束點

註：比較順利一次成功時，是這個狀態

$db2 rollforward db LAW to end of logs and complete

Rollforward Status

Input database alias = db
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2013-1-11-10.59.23.000000

DB20000I The ROLLFORWARD command completed successfully.
 

下面通過這個命令看一下資料庫的狀態和提示
$ db2 rollforward db LAW query status

                                 Rollforward Status

 Input database alias                   = LAW
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0003169.LOG
 Log files processed                    =  -
 Last committed transaction             = 2012-08-24-12.02.32.000000
通過這個命令，我們發現提示下一個日誌為S0003169.LOG，
需要找到這個檔案所在的路徑，將生產伺服器上從備份時間點後的日誌全部同步或拷貝到測試伺服器。
找到生產伺服器上DB2日誌，目錄為/home/db2logs/db2inst2/LAW/NODE0000/C0000004
將此目錄下從做備份的那個時間點後的日誌全部拷貝放到測試伺服器的相應目錄(可以放到庫備份的那個目錄)下，同時注意修改記錄檔的屬主、屬組和許可權，防止許可權不一致問題。

# chown db2inst1:db2iadm1 S0003170.LOG
# chown db2inst1:db2iadm1 S0003171.LOG
# chown db2inst1:db2iadm1 S0003172.LOG
# chown db2inst1:db2iadm1 S0003173.LOG
# chown db2inst1:db2iadm1 S0003174.LOG
# chown db2inst1:db2iadm1 S0003175.LOG
# chown db2inst1:db2iadm1 S0003176.LOG
#chmod 744  S0003170.LOG
#chmod 744  S0003171.LOG
#chmod 744  S0003172.LOG
#chmod 744  S0003173.LOG
#chmod 744  S0003174.LOG
#chmod 744  S0003175.LOG
#chmod 744  S0003176.LOG  

用如下命令來前滾日誌並停止日誌的溢出，注意"("/home")"格式，指的是日誌所在的路徑

$ db2 rollforward db LAW to end of logs and stop overflow log path "("/home")"

                                 Rollforward Status

 Input database alias                   = LAW
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0003169.LOG - S0003176.LOG
 Last committed transaction             = 2013-01-14-21.04.00.000000

DB20000I  The ROLLFORWARD command completed successfully.

最後串連資料庫，發現已成功了,經檢查資料一致。
$ db2 connect to LAW

   Database Connection Information

 Database server        = DB2/6000 9.1
 SQL authorization ID   = DB2INST1
 Local database alias   = LAW

經開發與測試人員驗證，一切正常。
 

本文出自 “滴水穿石” 部落格，謝絕轉載！