聯(lián)系：手機(jī)(13429648788) QQ(107644445)
鏈接：http://www.xifenfei.com/2948.html
標(biāo)題：DB2日志參數(shù)介紹和修改歸檔模式
作者：惜分飛?版權(quán)所有[文章允許轉(zhuǎn)載,但必須以鏈接方式注明源地址,否則追究法律責(zé)任.]
cfg關(guān)于log參數(shù)
[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:主日志組日志文件個(gè)數(shù)(首次連接或激活時(shí)直接分配)
LOGSECOND:輔助日志組日志文件個(gè)數(shù)(主日志文件寫滿時(shí)按需分配)
LOGFILSIZ:每個(gè)日志文件頁(yè)數(shù),每頁(yè)大小為4K
LOGARCHMETH1/LOGARCHMETH2:OFF表示循環(huán)模式,其他值表示歸檔模式(disk:/xifenfei/archive)
NEWLOGPATH:修改新的日志路徑
LOGBUFSZ:日志緩沖區(qū)大小
MIRRORLOGPATH:日志鏡像路徑
LOGRETAIN:歸檔日志保留在日志文件中(不推薦該做法)
USEREXIT:歸檔日志通過(guò)用戶出口程序管理(8.2后不推薦該做法)
當(dāng)前日志位置
[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參數(shù)(改為歸檔模式)
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGPRIMARY 6DB20000I 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/redologDB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGARCHMETH1 disk:/home/db2inst1/xff/archivelogDB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. |
查看修改后參數(shù)
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log Log retain for recovery status = NO User exit for logging status = NO --沒(méi)有生效 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 |
參數(shù)生效情況
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/total 0[db2inst1@xifenfei ~]$ db2stop04/06/2012 05:33:24 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.[db2inst1@xifenfei ~]$ db2start04/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 xffSQL1116N A connection to or activation of database "XIFENFEI" cannot be made because of BACKUP PENDING. SQLSTATE=57019[db2inst1@xifenfei ~]$ db2 backup db xff to /tmpBackup 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 |
修改備份模式參數(shù)需要重啟并且備份庫(kù)后生效
測(cè)試歸檔日志
[db2inst1@xifenfei ~]$ db2 list history archive log all for xff List History File for xffNumber 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 xffNumber 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/C0000000total 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/NODE0000total 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 |
