x$kccle视图深入剖析

今天是2014-05-27，实在无聊顺便研究一下x$kccle的内容吧。如下全部是自己分析和实验结果，真实可靠。 1、如何获得v$log的底层表？我们可以通过autotrace完成查看如： SQL set autotrace trace expSQL set linesize 200SQL select * from v$log;Execution Pl

今天是2014-05-27，实在无聊顺便研究一下x$kccle的内容吧。如下全部是自己分析和实验结果，真实可靠。

1、如何获得v$log的底层表？我们可以通过autotrace完成查看如：

SQL> set autotrace trace exp
SQL> set linesize 200
SQL> select * from v$log;

Execution Plan
----------------------------------------------------------
Plan hash value: 2536105608

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   197 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |   197 |     0   (0)| 00:00:01 |
|*  2 |   FIXED TABLE FULL       | X$KCCLE         |     1 |   148 |     0   (0)| 00:00:01 |
|*  3 |   FIXED TABLE FIXED INDEX| X$KCCRT (ind:1) |     1 |    49 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LE"."LEDUP"<>0 AND "LE"."INST_ID"=USERENV(&＃39;INSTANCE&＃39;))
   3 - filter("RT"."INST_ID"=USERENV(&＃39;INSTANCE&＃39;) AND "LE"."LETHR"="RT"."RTNUM")

SQL> 

可以知道当我们在查看v$log视图的时候，其实就是查看的x$kccle视图字段信息。查看该视图如下：

SQL> desc x$kccle
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER  实例号
 LENUM                                              NUMBER  类似group_number
 LESIZ                                              NUMBER  logfile的size，是操作系统的blocks数目
 LESEQ                                              NUMBER  v$log中的SEQUENCE# 
 LEHWS                                              NUMBER
 LEBSZ                                              NUMBER  block size（操作系统的块）
 LENAB                                              NUMBER
 LEFLG                                              NUMBER  表示日志的状态信息
 LETHR                                              NUMBER  v$log中的THREAD# 
 LELFF                                              NUMBER  下一个logfile
 LELFB                                              NUMBER  前一个logfile
 LELOS                                              VARCHAR2(16) low scn类似v$log FIRST_CHANGE#
 LELOT                                              VARCHAR2(20) low time类似v$log  FIRST_TIME
 LENXS                                              VARCHAR2(16) nex scn 类似v$log中下一个logfile的low FIRST_CHANGE#相同
 LENXT                                              VARCHAR2(20) nex time类似v$log中下一个logfile的low FIRST_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

SQL> 

下面对比一下：

SQL> select * from v$log;    

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1       2944  104857600          1 YES INACTIVE             121424676 21-MAY-14
         2          1       2945  104857600          1 NO  CURRENT              121427428 21-MAY-14
         3          1       2943  104857600          1 YES INACTIVE             121421889 21-MAY-14

SQL> 

SQL>  select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;

   INST_ID      LENUM      LESIZ      LESEQ      LEHWS      LEBSZ   LOG_SIZE      LENAB      LEFLG      LETHR      LELFF      LELFB LELOS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
         1          1     204800       2944          2        512  104857600     190256          1          1          2          0 121424676
         1          2     204800       2945          1        512  104857600 4294967295          8          1          3          1 121427428
         1          3     204800       2943          2        512  104857600     190256          1          1          0          2 121421889

SQL> 
SQL>  select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;

   INST_ID LENUM LESEQ      LETHR LELOT                LENXS            LENXT                LEPVS                 LEARF      LEARB      LEFNH      LEFNT      LEDUP
---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------
         1     1  2944          1 05/21/2014 11:26:42  121427428        05/21/2014 11:26:55  121421889                 0          0          1          1          1
         1     2  2945          1 05/21/2014 11:26:55  281474976710655                       121424676                 0          0          2          2          1
         1     3  2943          1 05/21/2014 11:26:30  121424676        05/21/2014 11:26:42  121419052                 0          0          3          3          1

SQL> 

现在单独关注一下leflg字段，当该值为8说明是正在使用的logfile group：

dump出控制文件中的log file信息如下：

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         19   52428800        512          2 NO  CURRENT                1013368 27-MAY-14   2.8147E+14
         2          1         17   52428800        512          2 NO  ACTIVE                 1013348 27-MAY-14      1013365 27-MAY-14
         3          1         18   52428800        512          2 NO  ACTIVE                 1013365 27-MAY-14      1013368 27-MAY-14

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump controlf 12 
Statement processed.

注意：也可以使用alter system set events ‘immediate trace name controlf level 12&＃39;;完成控制文件的dump操作：

LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 17, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #5: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_1_9r1z70w0_.log
  name #6: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_1_9r1z70z2_.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000013 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f7675
 Low scn: 0x0000.000f7678 05/27/2014 17:54:08
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
  name #3: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_2_9r1z726s_.log
  name #4: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_2_9r1z7291_.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000011 hws: 0x2 bsz: 512 nab: 0x14 flg: 0x0 dup: 2
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f75ec
 Low scn: 0x0000.000f7664 05/27/2014 17:54:01
 Next scn: 0x0000.000f7675 05/27/2014 17:54:05
LOG FILE #3:
  name #1: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_3_9r1z73jf_.log
  name #2: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_3_9r1z73lp_.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000012 hws: 0x2 bsz: 512 nab: 0x4 flg: 0x0 dup: 2
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f7664
 Low scn: 0x0000.000f7675 05/27/2014 17:54:05
 Next scn: 0x0000.000f7678 05/27/2014 17:54:08

对应一下：

SQL> select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;

   INST_ID LENUM LESEQ      LETHR LELOT                LENXS            LENXT                LEPVS                 LEARF      LEARB      LEFNH      LEFNT      LEDUP
---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------
         1     1    19          1 05/27/2014 17:54:08  281474976710655                       1013365                   0          0          5          6          2
         1     2    17          1 05/27/2014 17:54:01  1013365          05/27/2014 17:54:05  1013228                   0          0          3          4          2
         1     3    18          1 05/27/2014 17:54:05  1013368          05/27/2014 17:54:08  1013348                   0          0          1          2          2

SQL>  select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;

   INST_ID LENUM      LESIZ LESEQ      LEHWS      LEBSZ   LOG_SIZE      LENAB      LEFLG      LETHR      LELFF      LELFB LELOS
---------- ----- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
         1     1     102400    19          1        512   52428800 4294967295          8          1          2          0 1013368
         1     2     102400    17          2        512   52428800         20          0          1          3          1 1013348
         1     3     102400    18          2        512   52428800          4          0          1          0          2 1013365

SQL> 

拿group3做一个分析：如下：

LOG FILE #3:（代表第三个group）
name #1: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_3_9r1z73jf_.log（第一个日志文件）
name #2: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_3_9r1z73lp_.log（第二个日志文件）
Thread 1（线程1x$kccle.lethr） redo log links: forward: 0（对应x$kccle的LELFF为0) backward: 2(对应x$kccle的lelfb为2)
siz: 0x19000(对应x$kccle的lesiz转为10进制正好是102400）： seq: 0x00000012（转为10进制为18对应x$kccle的leseq）： hws: 0x2（对应x$kccle的lehws为2） bsz: 512（数据块对应x$kccle的lebsz）
nab: 0x4（对应lenab为4） flg: 0x0（对应x$kccle的leflg） dup: 2（对应x$kccle的ledup）
Archive links: fwrd: 0（前一个链表x$kccle的learf) back: 0 (对应x$kccle的learb）Prev scn: 0x0000.000f7664（转为10进制scn为1013348，对应lepvs)
Low scn: 0x0000.000f7675 05/27/2014 17:54:05(对应x$kccle的lelos）
Next scn: 0x0000.000f7678 05/27/2014 17:54:08（对应x$kccle的lenxt）

由此可知x$kccle正式抓取的是控制文件中的内容，这也是为什么数据块在mount状态下，可以查看v$log的原因了。