Daily Archives: 2012 年 04 月 18 日

DB2日志参数介绍和修改归档模式

Posted on by

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
Posted in DB2 |