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点。

引用

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
CPODR_SEQ                                          NUMBER
CPODR_BNO                                          NUMBER
CPODR_BOF                                          NUMBER
CPODS                                              VARCHAR2(16)
CPODT                                              VARCHAR2(20)
CPODT_I                                            NUMBER
CPHBT                                              NUMBER
CPRLS                                              VARCHAR2(16)
CPRLC                                              NUMBER
CPMID                                              NUMBER
CPSDR_SEQ                                          NUMBER
CPSDR_BNO                                          NUMBER
CPSDR_ADB                                          NUMBER

通过以下SQL可以查询到生产库lgwr进程已经写至current redolog的哪一个块。

引用

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

如果生产库搭建了dataguard，并用rfs传输onlineredolog，在备份库是查不出standby redolog写到了哪一个块

引用

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

在备份库仅仅记录了上次open时redolog的利用状态

引用

SQL> select CPODR_SEQ,CPODR_BNO,CPODR_BOF from x$kcccp ;

CPODR_SEQ  CPODR_BNO  CPODR_BOF
---------- ---------- ----------
     53682          2          0

当然了x$kcccp是ckpt进程触发写的，记录在controlfile中，可以在mount状态下查询得到，并在备份库可以看到CPHBT不停的更新，一般是3秒钟更新一次

引用

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

x$kcccp在很多情况作为Oracle视图的基表，以下就是gv$thread的一个例子

引用

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, x$kcccp  where rtnlf != 0 and tr.inst_id = rt.inst_id  and tirnum = rtnum and cptno = rtnum