DB2日志的管理

文章由LinuxBoy分享于2019-03-30 03:03:25热评（318）

DB2日志的管理

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

DB2日志的管理