DB2 如何管理日志?

显示全部楼层 · 2007-10-20 08:38:44

各位:
有个如下问题请帮忙下忙 (归档模式):

1,是否 first active log 后面的都是 active log ?

举例:当前 active log 目录里包含有 s001,s002,s003,s004,s005,s006 ,那么如果 s003 是 first active log 是否 s004,s005,s006 都是 active log 呢?
2,如何处理归档日志?

举例: 还是上面提到的例子, 如果 s003 (first active log) 已经被"写满" 那么是否会归档 s001,s002,s003 (即使他是 active 的但也归档?)
3,是否会存在归档了但为被重用?

举例:还是上面的例子，当　first active log 已经到　s005 后，　那么 active log 目录包含有　s001(确认为　inactive log),s005,s006 但s002,s003 确已经被归档走且
　　　　　　　已经被删除，但s001 还存在？

千问 · 2007-10-20 08:38:44

1) yes
2) yes
3) possible, db2 will try to rename the old logs instead of deleting them, when they go inactive

千问 · 2007-10-20 08:38:44

原帖由 wangzhonnew 于 2011-1-13 03:25 发表
1) yes
2) yes
3) possible, db2 will try to rename the old logs instead of deleting them, when they go inactive

Hi , wang , thank you
BTW :for question 3 , when does db2 try to rename/remove the Inactive logs ?I can not understand why db2 did not rename/remove s001 but s002 ,s003 ?
as we know s001 is older than s002 and it is the first active log and s002 and s003 are Active logs too .
So I meet a issue in my product system of Logs , the Primary logs + Secondy logs is 40Gand the online log Dir filesystem is 60G ,but the system
hung with the online log Dir filesystem could not extend and db2 redo log full warning, as I check the system there are around 60G redo logs in the
online log Dir , I really can not understand why db2 did not clearup and reuse the disk sapce of onine log dir ? the db2 archive process is normal .
I hope get help from you , many thanks

千问 · 2007-10-20 08:38:44

### BEGIN LOG - DATE: 110113, TIME: 104917 ###
hostname
CNLFKUX012
$ db2 connect
Database Connection Information
Database server= DB2/AIX64 9.1.7
SQL authorization ID = LIMQIANG
Local database alias = BP1
$ db2 get db cfg|grep -i fisrt
$ db2 get db cfg|grep -i firs
First active log file
= S0169564.LOG
First log archive method
(LOGARCHMETH1) = DISK:/db2/BP1/log_archive/
$ df -g
FilesystemGB blocksFree %UsedIused %Iused Mounted on
/dev/hd4 3.002.68 11% 3292 1% /
/dev/hd2 8.001.38 83%7442519% /usr
/dev/hd9var0.500.35 31% 1013 2% /var
/dev/hd3 4.000.96 76% 2273 2% /tmp
/dev/hd1 1.000.38 62%952 2% /home
/proc
- -- - -/proc
/dev/hd10opt 0.500.45 11% 1043 1% /opt
/dev/fslv10 90.00 89.641% 27 1% /backup
/dev/fslv11 10.002.36 77% 3460 1% /sapdvd
/dev/lvperfmgr2.001.23 39%854 1% /IBM_TOOLS
/dev/lvfslv202.622.419% 70 1% /db2/BP1/db2bp1
/dev/lvfslv078.006.52 19%848 1% /db2/BP1/db2dump
/dev/lvfslv05135.00 50.74 63%684 1% /db2/BP1/log_archive
/dev/lvfslv09 60.00 54.749% 53 1% /db2/BP1/log_dir
/dev/lvfslv061.001.001%4 1% /db2/BP1/log_retrieve
/dev/lvfslv12 3450.000.00100%53365% /db2/BP1/sapdata1
/dev/lvsapdata2531.00369.71 31% 73 1% /db2/BP1/sapdata2
/dev/lvsapdata3472.50311.22 35% 73 1% /db2/BP1/sapdata3
/dev/lvsapdata4472.50311.22 35% 73 1% /db2/BP1/sapdata4
/dev/lvfslv08 54.00 53.991%254 1% /db2/BP1/saptemp1
/dev/lvfslv041.000.50 51%786 1% /db2/db2bp1
/dev/lvfslv03 60.003.32 95% 61625744% /sapmnt/BP1_export
/dev/lvfslv016.003.52 42% 3725 1% /usr/sap/BP1
/dev/lvfslv02 50.00 16.33 68%85278 3% /usr/sap/trans_export
CNLFKUX012_srv:/sapmnt/BP1_export 60.003.32 95% 61625744% /sapmnt/BP1
CNLFKUX012_srv:/usr/sap/trans_export 50.00 16.33 68%85278 3% /usr/sap/trans
$ cd /db2/BP1/log_dir
$ ls
NODE0000lost+found
$ cd N*
$ ls
S0167060.LOGS0167065.LOGS0168541.LOGS0168607.LOGS0169448.LOGS0169452.LOGS0169566.LOGS0169570.LOGS0169574.LOGS0169578.LOGS0169582.LOGlslog.log
S0167061.LOGS0167066.LOGS0168604.LOGS0168608.LOGS0169449.LOGS0169453.LOGS0169567.LOGS0169571.LOGS0169575.LOGS0169579.LOGS0169583.LOGmv.sh
S0167062.LOGS0167067.LOGS0168605.LOGS0169446.LOGS0169450.LOGS0169564.LOGS0169568.LOGS0169572.LOGS0169576.LOGS0169580.LOGSQLLPATH.TAGmv1.sh
S0167064.LOGS0168540.LOGS0168606.LOGS0169447.LOGS0169451.LOGS0169565.LOGS0169569.LOGS0169573.LOGS0169577.LOGS0169581.LOGls.sh mv2.sh
$ ls -l
total 11008088
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:43 S0167060.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:43 S0167061.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:43 S0167062.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:44 S0167064.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:44 S0167065.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:45 S0167066.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:45 S0167067.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:13 S0168540.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:13 S0168541.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:40 S0168604.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:40 S0168605.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:42 S0168606.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:43 S0168607.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:44 S0168608.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:20 S0169446.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:20 S0169447.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:20 S0169448.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:20 S0169449.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:21 S0169450.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:21 S0169451.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:21 S0169452.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 05:21 S0169453.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:36 S0169564.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:47 S0169565.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:48 S0169566.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:50 S0169567.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:11 S0169568.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:11 S0169569.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:12 S0169570.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:12 S0169571.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:13 S0169572.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:13 S0169573.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:14 S0169574.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:20 S0169575.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:22 S0169576.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:23 S0169577.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:24 S0169578.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:32 S0169579.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:35 S0169580.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:36 S0169581.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:47 S0169582.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 13 10:48 S0169583.LOG
-rw-------1 db2bp1 dbbp1adm512 Mar 06 2010SQLLPATH.TAG
-rwxr-xr-x1 root system 7034 Oct 27 21:16 ls.sh
-rw-r--r--1 root system
816 Dec 28 20:53 lslog.log
-rw-r--r--1 root system 1104 Dec 28 20:24 mv.sh
-rw-r--r--1 root system 1440 Dec 28 20:35 mv1.sh
-rw-r--r--1 root system 1704 Dec 28 20:41 mv2.sh
$
### END LOG - DATE: 110113, TIME: 105016 ###

千问 · 2007-10-20 08:38:44

how many primary+secondary logs are set in db cfg? for now it seems there are only 40 logs in the dir, each one is 128M, which is about 5G log space used only.
the total log space shouldn't exceed the total amount of primary+secondary log, so with 60G log space for 128M each, it should end up with 480 primary+secondary? or are you using infinite logging?

千问 · 2007-10-20 08:38:44

原帖由 wangzhonnew 于 2011-1-13 12:10 发表
how many primary+secondary logs are set in db cfg? for now it seems there are only 40 logs in the dir, each one is 128M, which is about 5G log space used only.
the total log space shouldn't exceed the total amount of primary+secondary log, so with 60G log space for 128M each, it should end up with 480 primary+secondary? or are you using infinite logging?

Dear wang :
The primary log (20) + the secondy log(235)is 255 * 128 = 32G
Now , the log files which generated before the first active log still remained in the online log dir but no clearup (rename/remove)and the log number
is not sequential, and those logswillremain until total amount of them over 32G to 60G and db2 hung , because I have removed some of them manually,
the total amount do not over than 32G .
I hope youkindly clarify :
1, is it nomal that the online logs have a gapand why ?
2, when does db2 rename them and from wich log it will be used , the oldest one ? is it right ?
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:43 S0167060.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:43 S0167061.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:43 S0167062.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:44 S0167064.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:44 S0167065.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:45 S0167066.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 10 05:45 S0167067.LOG
----------------------------------------------------------------------------here, have a gap, from my understanding it should be sequential ,but it is not, I am sure did not remove

them manually
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:13 S0168540.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:13 S0168541.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:40 S0168604.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:40 S0168605.LOG
-rw-------1 db2bp1 dbbp1adm134193152 Jan 12 05:42 S0168606.LOG
[ 本帖最后由 joyw 于 2011-1-13 13:24 编辑 ]

千问 · 2007-10-20 08:38:44

it's normal to have gap, i don't have quick answer why it could happen but it should be possible~~
usually db2 should rename them starting from the lowest id, but i've seen cases when there's issue in security plugin which cause log files unable to be renamed, guess you may want to open an PMR to ibm when seeing the behavior again (when there are more than 255 logs in active log dir)

千问 · 2007-10-20 08:38:44

Hi Wang , I have raised a SAP message , so far no update from SAP , once it have update from SAP or IBM I 'll discuss with you
anyway ,thanks a lot