x$kccle---- [K]ernel [C]ache [C]ontrolfile management [L]ogfil [E]ntries
SQL> desc x$kccle
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER 表示instance_id
LENUM NUMBER 等同于v$log中group#
LESIZ NUMBER logfile size, 用blocks来计数
LESEQ NUMBER 等同于v$log中sequence#
LEHWS NUMBER
LEBSZ NUMBER block size
LENAB NUMBER
LEFLG NUMBER flag, 表示日志的状态
LETHR NUMBER 等同于v$log中thread#
LELFF NUMBER log file forward, log file的前向链表，指向下一个log file
LELFB NUMBER log file backward, log file的后向链表，指向上一个log file
LELOS VARCHAR2(16) low scn, 等同于v$log中first_change#
LELOT VARCHAR2(20) low time, 等同于v$log中first_time
LENXS VARCHAR2(16) next scn, 与下一个logfile的low scn相同
LENXT VARCHAR2(20) next time
LEPVS VARCHAR2(16) prev scn, 与上一个logfile的low scn
LEARF NUMBER archive link forward
LEARB NUMBER archive link backward
LEFNH NUMBER
LEFNT NUMBER
LEDUP NUMBER 等同于v$log中members
v$log视图的内容主要是基于x$kccle表，以下是gv$log视图的定义。
select le.inst_id, le.lenum, le.lethr, le.leseq, le.lesiz*le.lebsz, ledup, decode(bitand(le.leflg,1),0,'NO','YES'),
decode(bitand(le.leflg,24), 8, 'CURRENT', 16,'CLEARING', 24,'CLEARING_CURRENT', decode(sign(leseq),0,'UNUSED',decode(sign((to_number(rt.rtckp_scn)-to_number(le.lenxs))* bitand(rt.rtsta,2)),-1,'ACTIVE','INACTIVE'))), to_number(le.lelos), to_date(le.lelot,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')
from x$kccle le, x$kccrt rt where le.ledup!=0 and le.lethr=rt.rtnum and le.inst_id = rt.inst_id通过与v$log的字段相比较，可以了解x$kccle部分字段的含义。
x$kccle中的内容也可以在controlfile的dump中找到：
sql> oradebug setmypid
sql> oradebug dump controlf 12dump文件保存在udump目录中
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 3, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
(name #1) /oradata/ora10g/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x0000003a hws: 0x5 bsz: 512 nab: 0x7998 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001cbcd3
Low scn: 0x0000.001d0e3e 05/29/2012 16:44:18
Next scn: 0x0000.001d65e9 05/29/2012 16:52:06
LOG FILE #2:
(name #2) /oradata/ora10g/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x0000003b hws: 0x2 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001d0e3e
Low scn: 0x0000.001d65e9 05/29/2012 16:52:06
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
(name #3) /oradata/ora10g/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000039 hws: 0x6 bsz: 512 nab: 0x203f flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001c6dd5
Low scn: 0x0000.001cbcd3 05/29/2012 16:37:13
Next scn: 0x0000.001d0e3e 05/29/2012 16:44:18
关于v$log中status的说明：
Log status:
-
UNUSED- Online redo log has never been written to. This is the state of a redo log that was just added, or just after aRESETLOGS, when it is not the current redo log. 其中x$kccle.leseq=0表示unused状态 -
CURRENT- Current redo log. This implies that the redo log is active. The redo log could be open or closed. 其中bitand(x$kccle.leflg,24)=8 表示current状态 -
ACTIVE- Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. 如果(checkpoint scn-logfile的next scn)<0, 则说明instance recovery时需要此logfile, 状态为active -
CLEARING- Log is being re-created as an empty log after anALTER DATABASE CLEAR LOGFILEstatement. After the log is cleared, the status changes toUNUSED. 其中bitand(x$kccle.leflg,24)=16 表示clearing状态 -
CLEARING_CURRENT- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. 其中bitand(x$kccle.leflg,24)=24 表示clearing_current状态 -
INACTIVE- Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived. 如果(checkpoint scn-logfile的next scn)>=0, 则说明instance recovery时不需要此logfile, 状态为inactive
与日志相关的几个SQL脚本
1. 监控在线日志写进程
select le.lethr "Thread Number", le.leseq "Current sequence", 100*cp.cpodr_bno/le.lesiz "Percent Full", cp.cpodr_bno "Current Block No", le.lesiz "Size in Blocks"
from x$kcccp cp, x$kccle le
where le.leseq=cp.cpodr_seq and bitand(le.leflg,24)=8;
2. 下次日志切换时，Oracle将要选择的日志组
How does Oracle determine the next log group to switch:
i) searching for the log group with lowest sequence# that is archived.
ii) If a new log group is added, then that new log group will have log sequence# as zero and so that group will be selected
iii) If there are two new log groups, then first one encountered in the above array is selected.
Third point is quite important, since if we drop a log group, then that log group is simply marked as deleted, not completely removed from the array. So, if the same log group is readded, then deleted entry is reused
单机：
SELECT indx, lenum group#, leseq sequence#, archived,
DECODE (leflg, 8, 'CURRENT') curstatus,
CASE WHEN leseq = minseq AND indx = minind THEN 'NEXT'
END NEXT
FROM (SELECT indx, lenum, leseq, leflg,
DECODE (BITAND (leflg, 1), 0, 'NO', 'YES') archived,
FIRST_VALUE (leseq) OVER (ORDER BY leseq, indx,BITAND (leflg, 1) DESC) minseq,
FIRST_VALUE (indx) OVER (ORDER BY leseq, indx, BITAND (leflg, 1) DESC) minind
FROM x$kccle
WHERE lesiz != 0 AND inst_id = USERENV ('instance'))
ORDER BY indx;
For RAC:
SELECT lethr, indx, lenum group#, leseq sequence#, archived,
DECODE (leflg, 8, 'CURRENT') curstatus,
CASE WHEN leseq = minseq AND indx = minind THEN 'NEXT'
END NEXT
FROM (SELECT lethr, indx, lenum, leseq, leflg,
DECODE (BITAND (leflg, 1), 0, 'NO', 'YES') archived,
FIRST_VALUE (leseq) OVER (partition by lethr ORDER BY leseq, indx,BITAND (leflg, 1) DESC) minseq,
FIRST_VALUE (indx) OVER ( partition by lethr ORDER BY leseq, indx, BITAND (leflg, 1) DESC) minind
FROM x$kccle
WHERE lesiz != 0 AND inst_id = USERENV ('instance'))
ORDER BY indx;