DB2日志参数介绍和修改归档模式
浏览:1345次 出处信息
cfg关于log参数
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log Log retain for recovery status = NO User exit for logging status = NO Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260 Log buffer size (4KB) (LOGBUFSZ) = 98 Log file size (4KB) (LOGFILSIZ) = 1024 Number of primary log files (LOGPRIMARY) = 13 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = Block log on disk full (BLK_LOG_DSK_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = OFF HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC First log archive method (LOGARCHMETH1) = OFF Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Log pages during index build (LOGINDEXBUILD) = OFF
LOGPRIMARY:主日志组日志文件个数(首次连接或激活时直接分配)
LOGSECOND:辅助日志组日志文件个数(主日志文件写满时按需分配)
LOGFILSIZ:每个日志文件页数,每页大小为4K
LOGARCHMETH1/LOGARCHMETH2:OFF表示循环模式,其他值表示归档模式(disk:/xifenfei/archive)
NEWLOGPATH:修改新的日志路径
LOGBUFSZ:日志缓冲区大小
MIRRORLOGPATH:日志镜像路径
LOGRETAIN:归档日志保留在日志文件中(不推荐该做法)
USEREXIT:归档日志通过用户出口程序管理(8.2后不推荐该做法)
当前日志位置
[db2inst1@xifenfei ~]$ ls -l /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/ total 53404 -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000000.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000001.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000002.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000003.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000004.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000005.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000006.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000007.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000008.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000009.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000010.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000011.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 5 16:20 S0000012.LOG
修改cfg参数(改为归档模式)
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGPRIMARY 6 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. [db2inst1@xifenfei ~]$ mkdir -p xff/redolog [db2inst1@xifenfei ~]$ mkdir -p xff/archivelog [db2inst1@xifenfei ~]$ db2 update db cfg for xff using NEWLOGPATH /home/db2inst1/xff/redolog DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. [db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGARCHMETH1 disk:/home/db2inst1/xff/archivelog DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
查看修改后参数
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log Log retain for recovery status = NO User exit for logging status = NO --没有生效 Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260 Log buffer size (4KB) (LOGBUFSZ) = 98 Log file size (4KB) (LOGFILSIZ) = 1024 Number of primary log files (LOGPRIMARY) = 6 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = /home/db2inst1/xff/redolog/NODE0000/ Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = Block log on disk full (BLK_LOG_DSK_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = OFF HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/ Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Log pages during index build (LOGINDEXBUILD) = OFF
参数生效情况
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/ total 0 [db2inst1@xifenfei ~]$ db2stop 04/06/2012 05:33:24 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst1@xifenfei ~]$ db2start 04/06/2012 05:33:34 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/ total 0 [db2inst1@xifenfei ~]$ db2 connect to xff SQL1116N A connection to or activation of database "XIFENFEI" cannot be made because of BACKUP PENDING. SQLSTATE=57019 [db2inst1@xifenfei ~]$ db2 backup db xff to /tmp Backup successful. The timestamp for this backup image is : 20120406053431 [db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/ total 24652 -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:33 S0000000.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:33 S0000001.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:33 S0000002.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:33 S0000003.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:33 S0000004.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:33 S0000005.LOG -rw------- 1 db2inst1 db2iadm1 512 Apr 6 05:33 SQLLPATH.TAG [db2inst1@xifenfei ~]$ db2 connect to xff Database Connection Information Database server = DB2/LINUX 9.5.9 SQL authorization ID = DB2INST1 Local database alias = XFF [db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log Log retain for recovery status = NO User exit for logging status = YES Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260 Log buffer size (4KB) (LOGBUFSZ) = 98 Log file size (4KB) (LOGFILSIZ) = 1024 Number of primary log files (LOGPRIMARY) = 6 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files = /home/db2inst1/xff/redolog/NODE0000/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = S0000005.LOG Block log on disk full (BLK_LOG_DSK_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = OFF HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/ Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Log pages during index build (LOGINDEXBUILD) = OFF
修改备份模式参数需要重启并且备份库后生效
测试归档日志
[db2inst1@xifenfei ~]$ db2 list history archive log all for xff List History File for xff Number of matching file entries = 0 [db2inst1@xifenfei ~]$ db2 "create table t_xifenfei like syscat.tables" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from syscat.tables" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from t_xifenfei" DB20000I The SQL command completed successfully. ……N次…… [db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei" 1 ----------- 94208 1 record(s) selected. [db2inst1@xifenfei ~]$ db2 list history archive log all for xff List History File for xff Number of matching file entries = 11 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- X D 20120406054854 1 D S0000000.LOG C0000000 ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- Comment: Start Time: 20120406054854 End Time: 20120406055029 Status: A ---------------------------------------------------------------------------- EID: 2 Location: /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000/S0000000.LOG ……省略…… Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- X D 20120406055041 P D S0000010.LOG C0000000 ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- Comment: Start Time: 20120406055041 End Time: Status: A ---------------------------------------------------------------------------- EID: 14 Location: /home/db2inst1/xff/redolog/NODE0000/S0000010.LOG [db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000 total 41080 -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000000.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000001.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000002.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000003.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000004.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000005.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000006.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000007.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000008.LOG -rw-r----- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000009.LOG [db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000 total 32868 -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000005.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000006.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000007.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000008.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000009.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000010.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000011.LOG -rw------- 1 db2inst1 db2iadm1 4202496 Apr 6 05:50 S0000012.LOG -rw------- 1 db2inst1 db2iadm1 512 Apr 6 05:33 SQLLPATH.TAG
建议继续学习:
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:oracle 9i数据库存在大量ora_j0**进程
后一篇:利用scn增量备份实现数据库增量恢复 >>
文章信息
- 作者:惜分飞 来源: 惜分飞
- 标签: DB2
- 发布时间:2012-04-19 23:25:38
建议继续学习
近3天十大热文
- [3808] QR码分析
- [70] Twitter/微博客的学习摘要
- [67] IOS安全–浅谈关于IOS加固的几种方法
- [67] Go Reflect 性能
- [65] android 开发入门
- [65] 如何拿下简短的域名
- [65] find命令的一点注意事项
- [64] 流程管理与用户研究
- [62] 图书馆的世界纪录
- [61] 读书笔记-壹百度:百度十年千倍的29条法则