DB2日誌的管理

DB重要的日誌概念 $ db2 get db cfg for sampleLog file size (4KB) (LOGFILSIZ) = 1000Number of primary log files (LOGPRIMARY) = 3Number of secondary log files (LOGSECOND) = 2Changed path to log files (NEWLOGPATH) =Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00005/SQLOGDIR/Overflow log path (OVERFLOWLOGPATH) =Mirror log path (MIRRORLOGPATH) =First active log file = S0000012.LOG Path to log files 記錄檔所在目錄First active log file最舊那個活動紀錄，這個日誌之前的所有日誌都已經完成資料同步，理論上這個日誌之前的日誌可以刪除，不會影響資料庫的一致性。 --如果歸檔記錄檔存放的位置發現問題,不能歸檔.可以通過設定FAILARCHPATH參數--它能在歸檔目錄發現問題時,DB2嘗試向此目錄中歸檔.--管理員一定要定期查看歸檔目錄和db2diag.log日誌 --查看具體日誌情況$ db2 list history archive log all for sampleOp Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID-- --- ------------------ ---- --- ------------ ------------ --------------X D 20131211000545 P D S0000010.LOG C0000003---------------------------------------------------------------------------- ----------------------------------------------------------------------------Comment:Start Time: 20131211000545End Time:Status: A----------------------------------------------------------------------------EID: 38 Location: /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/S0000010.LOG --查看備份情況$ db2 list history backup all for sample Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID-- --- ------------------ ---- --- ------------ ------------ --------------B D 20131210002847001 F O S0000010.LOG S0000010.LOG----------------------------------------------------------------------------Contains 2 tablespace(s): 00001 SYSCATSPACE00002 USERSPACE1----------------------------------------------------------------------------Comment: DB2 BACKUP T1 OFFLINEStart Time: 20131210002847End Time: 20131210002858 Earliest Log Database Backup後的最早的需要保留的日誌，和Oracle一樣,該日誌之前的日誌可以刪除和移開，因為資料庫已經備份，這些日誌對資料庫的恢複已經沒有用處Current Log Database Backup時的當前活動紀錄 --刪除日誌--不清除日誌那麼它會不斷膨脹-- 要依備份情況去刪除日誌.db2 connect to sampledb2 prune history 20131210002847 and deletedb2 connect reset --把 S0000010.LOG之前的所有日誌刪掉-- 建議只刪除 Earliest Log 之前的db2 prune logfile prior to S0000010.LOG -- 手工歸檔（前提是沒資料庫連接):
db2 archive log for db xcldb
db2 list history archive log all for xcldb 

關于歸檔記錄模式設定如果 "USEREXIT" 沒有啟用,可將 logarchmeth1 或 logarchmeth2 設定為 OFF 以外的值,使用資料庫可以進行前滾恢複。如果更新 userexit或 logretain 配置參數，將自動更新 logarchmeth1，反之亦然。然而，如果您正在使用 userexit 或 logretain, 必須將 logarchmeth2 設定為 OFF。另外,db2 8.2裡面還提供了FAILARCHPATH(容錯移轉日誌歸檔路徑) [db2inst1@O11g64 bin]$ db2levelDB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09013"with level identifier "01040107".Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00203", and Fix Pack"3".Product is installed at "/u01/db2v9". [db2inst1@O11g64 bin]$ mkdir -p /home/db2inst1/db2inst1/mylog[db2inst1@O11g64 bin]$ db2 "update db cfg for xcldb using logfilsiz 8 LOGARCHMETH1 disk:/home/db2inst1/db2inst1/mylog/"[db2inst1@O11g64 bin]$ mkdir -p /home/db2inst1/db2inst1/cold -- db2 rollforward db xcldb query status -- not pending[db2inst1@O11g64 bin]$ db2 backup db xcldb to /home/db2inst1/db2inst1/coldBackup successful. The timestamp for this backup image is : 20131213225508 First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/db2inst1/mylog/ [db2inst1@O11g64 bin]$ db2 get db cfg for xcldb|grep "LOGARCHMETH1"First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/db2inst1/mylog/ [db2inst1@O11g64 bin]$ db2 get db cfg for xcldb|grep "LOGFILSIZ"Log file size (4KB) (LOGFILSIZ) = 8[db2inst1@O11g64 bin]$ [db2inst1@O11g64 bin]$ db2 archive log for db xcldbDB20000I The ARCHIVE LOG command completed successfully. [db2inst1@O11g64 bin]$ ls -lrt /home/db2inst1/db2inst1/mylog/db2inst1/XCLDB/NODE0000/C0000000total 24-rw-r-----. 1 db2inst1 db2iadm1 12288 Dec 13 22:53 S0000000.LOG-rw-r-----. 1 db2inst1 db2iadm1 12288 Dec 13 23:07 S0000001.LOG

[db2inst1@O11g64 bin]$ db2levelDB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09013"with level identifier "01040107".Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00203", and Fix Pack"3".Product is installed at "/u01/db2v9". [db2inst1@O11g64 bin]$ mkdir -p /home/db2inst1/db2inst1/mylog[db2inst1@O11g64 bin]$ db2 "update db cfg for xcldb using logfilsiz 8 LOGARCHMETH1 disk:/home/db2inst1/db2inst1/mylog/"[db2inst1@O11g64 bin]$ mkdir -p /home/db2inst1/db2inst1/cold -- db2 rollforward db xcldb query status -- not pending[db2inst1@O11g64 bin]$ db2 backup db xcldb to /home/db2inst1/db2inst1/coldBackup successful. The timestamp for this backup image is : 20131213225508 First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/db2inst1/mylog/ [db2inst1@O11g64 bin]$ db2 get db cfg for xcldb|grep "LOGARCHMETH1"First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/db2inst1/mylog/ [db2inst1@O11g64 bin]$ db2 get db cfg for xcldb|grep "LOGFILSIZ"Log file size (4KB) (LOGFILSIZ) = 8[db2inst1@O11g64 bin]$ [db2inst1@O11g64 bin]$ db2 archive log for db xcldbDB20000I The ARCHIVE LOG command completed successfully. [db2inst1@O11g64 bin]$ ls -lrt /home/db2inst1/db2inst1/mylog/db2inst1/XCLDB/NODE0000/C0000000total 24-rw-r-----. 1 db2inst1 db2iadm1 12288 Dec 13 22:53 S0000000.LOG-rw-r-----. 1 db2inst1 db2iadm1 12288 Dec 13 23:07 S0000001.LOG 

推薦閱讀：

DB2資料庫效能調整和最佳化(第1、2版) PDF

DB2資料庫效能最佳化介紹