Oracle 数据字典之x$kcccp

x$kcccp顾名思义为kernel cache checkpoint progress.
X$KCCCP--[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress
metalink 22241.1详细记录了x$表名缩写含义。现在已被Oracle从metalink上撤销掉了。首先看一下x$kcccp的结构，注意其黑体部分。CPODR_SEQ表示当前redolog的sequence，CPODR_BNO表示当前redolog写至哪个块，CPHBT表示ckpt 的heart beat点。
[quote]SQL> desc x$kcccp
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
CPTNO NUMBER
CPSTA NUMBER
CPFLG NUMBER
CPDRT NUMBER
CPRDB NUMBER
CPLRBA_SEQ NUMBER
CPLRBA_BNO NUMBER
CPLRBA_BOF NUMBER
[b] CPODR_SEQ NUMBER
CPODR_BNO NUMBER[/b]
CPODR_BOF NUMBER
CPODS VARCHAR2(16)
CPODT VARCHAR2(20)
CPODT_I NUMBER
[b]CPHBT NUMBER[/b]
CPRLS VARCHAR2(16)
CPRLC NUMBER
CPMID NUMBER
CPSDR_SEQ NUMBER
CPSDR_BNO NUMBER
CPSDR_ADB NUMBER[/quote]

通过以下SQL可以查询到生产库lgwr进程已经写至current redolog的哪一个块。
[quote]SQL> select le.leseq CURRENT_LOG_SEQUENCE#,cp.CPODR_BNO
2 from x$kcccp cp,x$kccle le
3 where LE.leseq =CP.cpodr_seq
4 and le. INST_ID=1;

CURRENT_LOG_SEQUENCE# CPODR_BNO
--------------------- ----------
57858 283[/quote]

如果生产库搭建了dataguard，并用rfs传输onlineredolog，在备份库是查不出standby redolog写到了哪一个块
[quote]SQL> select le.leseq CURRENT_LOG_SEQUENCE#,cp.CPODR_BNO
2 from x$kcccp cp,x$kccle le
3 where LE.leseq =CP.cpodr_seq
4 and le. INST_ID=1;

no rows selected[/quote]

在备份库仅仅记录了上次open时redolog的利用状态
[quote]
SQL> select CPODR_SEQ,CPODR_BNO,CPODR_BOF from x$kcccp ;

CPODR_SEQ CPODR_BNO CPODR_BOF
---------- ---------- ----------
53682 2 0[/quote]

当然了[b]x$kcccp是ckpt进程触发写的[/b]，记录在controlfile中，可以在mount状态下查询得到，并在备份库可以看到CPHBT不停的更新，一般是3秒钟更新一次
[quote]SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select CPHBT from x$kcccp;

CPHBT
----------
692394230
0
0
0
0
0
0
0

8 rows selected.

SQL> /

CPHBT
----------
692394231
0
0
0
0
0
0
0[/quote]

x$kcccp在很多情况作为Oracle视图的基表，以下就是gv$thread的一个例子
[quote]select rt.inst_id,rtnum,decode(bitand(rtsta,1),1,'OPEN','CLOSED'), decode(bitand(rtsta,6),0,'DISABLED',2,'PRIVATE',6,'PUBLIC','UNKNOWN'), rtnlf,tirsid,to_date(rtots,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), rtcln,rtseq, to_number(rtckp_scn), to_date(rtckp_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(rtenb), to_date(rtets,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(rtdis), to_date(rtdit,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), cpodr_seq, cpodr_bno, to_number(cpods), to_date(cpodt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian') from x$kccrt rt, x$kcctir tr, [b]x$kcccp[/b] where rtnlf != 0 and tr.inst_id = rt.inst_id and tirnum = rtnum and cptno = rtnum[/quote]