DB2的归档模式设置方法

文章由LinuxBoy分享于2019-03-30 04:03:05热评（206）

DB2的归档模式设置方法

DB2的日志使用通常有两种方式，既称之为循环日志，也就是不采用归档日志模式，另外一种就是归档日志模式，默认是循环日志模式，也就是无法进行日志归档或在线备份。通常来说决定DB2日志配置的几个database级别的参数如下：
当前的数据库版本为：
[db2inst1@localhost ]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09075"
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23291", and Fix Pack
"5".
Product is installed at "/opt/ibm/db2/V9.7".

[db2inst1@localhost ]$ db2 get db cfg|grep -i log
Log retain for recovery status = NO
User exit for logging status = YES
Log buffer size (4KB) (LOGBUFSZ) = 256
Log file size (4KB) (LOGFILSIZ) = 2000
Number of primary log files (LOGPRIMARY) = 20
Number of secondary log files (LOGSECOND) = 30
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000001.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) = 100
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:/db2src/TS/backup/
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =

如上常用的参数介绍如下：
LOGPRIMARY+LOGSECOND是日志文件的个数，而每个日志文件的大小由LOGFILSIZ参数设定，如上当前的日志文件大小为2000 pages,而page大小为4K，所以每个日志文件为8M，总共的日志空间为400M，NEWLOGPATH参数决定了日志文件存放目录路径，修改后可以动态生效。
First active log file就是指DB当前使用的活动日志文件名称
MAX_LOG 指定单个事物可以使用主日志空间的百分比，当达到这个百分比后，这个事物就得被回滚，默认设置为0，也就是单个事务日志空间使用不受限制，可以使用主附日志的所有空间。
SOFTMAX 指定软检查点频率和范围，单位为一个日志文件大小的百分比，100也就是一个日志文件，当发生数据库崩溃恢复时，需要保持一致状态，就会发生崩溃恢复，需要使用指定日志文件数来做恢复，当然日志量越小恢复就越快，通常保持默认值。
LOGARCHMETH1 这个即设置归档的方式，可以指定DISK、TSM等参数。
上面大概介绍了参数设置，那么如何配置DB2归档呢？
其实DB2的归档配置比较简单，只需要需要修改LOGARCHMETH1参数即可如下：
[db2inst1@localhost backup]$ db2 update db cfg using LOGARCHMETH1 'disk:/db2src/TS/backup'
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.

但是修改了这个参数还是无法生效的，需要执行重启数据库这里直接重启实例：
[db2inst1@localhost backup]$ db2stop force
11/25/2013 22:17:02 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@localhost backup]$ db2start
11/25/2013 22:17:07 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@localhost backup]$ db2 connect to sample
SQL1116N A connection to or activation of database "SAMPLE" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
发现重启后还是不能连接，这时数据库状态为BACKUP PENDING,需要做一个备份，记住是离线备份。

更多详情见请继续阅读下一页的精彩内容：

DB2的归档模式设置方法