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) = OFFLOGPRIMARY:主日志组日志文件个数(首次连接或激活时直接分配)
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