现在的位置: 首页 > 综合 > 正文

DB2日志的管理

2014年08月11日 ⁄ 综合 ⁄ 共 4783字 ⁄ 字号 评论关闭
DB重要的日志概念
$ db2 get db cfg for sample
 Log file size (4KB) (LOGFILSIZ) = 1000
 Number of primary log files (LOGPRIMARY) = 3
 Number of secondary log files (LOGSECOND) = 2
 Changed 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 sample
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X D 20131211000545 P D S0000010.LOG C0000003
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20131211000545
   End 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 SYSCATSPACE
  00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP T1 OFFLINE
 Start Time: 20131210002847
   End Time: 20131210002858
Earliest Log 
  数据库备份后的最早的需要保留的日志,和Oracle一样,该日志之前的日志可以删除和移开,
因为数据库已经备份,这些日志对数据库的恢复已经没有用处
Current Log 
  数据库备份时的当前活动日志
--删除日志
--不清除日志那么它会不断膨胀
-- 要依备份情况去删除日志.
 db2 connect to sample
 db2 prune history   20131210002847  and delete
 db2 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]$ db2level
DB21085I 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/cold
Backup 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 xcldb
DB20000I The ARCHIVE LOG command completed successfully.
[db2inst1@O11g64 bin]$ ls -lrt /home/db2inst1/db2inst1/mylog/db2inst1/XCLDB/NODE0000/C0000000
total 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]$ db2level
DB21085I 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/cold
Backup 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 xcldb
DB20000I The ARCHIVE LOG command completed successfully.
[db2inst1@O11g64 bin]$ ls -lrt /home/db2inst1/db2inst1/mylog/db2inst1/XCLDB/NODE0000/C0000000
total 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


MAIL:xcl_168@aliyun.com
Blog:http://blog.csdn.net/xcl168

抱歉!评论已关闭.