联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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