转载

V$LOG的底层表之X$KCCLE深入剖析

V$LOG的底层表之X$KCCLE深入剖析

x$kccle---- [K]ernel [C]ache [C]ontrolfile management [L]ogfil[E] record

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

[sql] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1. SQL> set autotrace trace exp  
  2. SQL> set linesize 200  
  3. SQL> select * from v$log;  
  4.   
  5. Execution Plan  
  6. ----------------------------------------------------------  
  7. Plan hash value: 2536105608  
  8.   
  9. --------------------------------------------------------------------------------------------  
  10. | Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
  11. --------------------------------------------------------------------------------------------  
  12. |   0 | SELECT STATEMENT         |                 |     1 |   197 |     0   (0)| 00:00:01 |  
  13. |   1 |  NESTED LOOPS            |                 |     1 |   197 |     0   (0)| 00:00:01 |  
  14. |*  2 |   FIXED TABLE FULL       | X$KCCLE         |     1 |   148 |     0   (0)| 00:00:01 |  
  15. |*  3 |   FIXED TABLE FIXED INDEX| X$KCCRT (ind:1) |     1 |    49 |     0   (0)| 00:00:01 |  
  16. --------------------------------------------------------------------------------------------  
  17.   
  18. Predicate Information (identified by operation id):  
  19. ---------------------------------------------------  
  20.   
  21.    2 - filter("LE"."LEDUP"<>0 AND "LE"."INST_ID"=USERENV('INSTANCE'))  
  22.    3 - filter("RT"."INST_ID"=USERENV('INSTANCE'AND "LE"."LETHR"="RT"."RTNUM")  
  23.   
  24. SQL>   


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

[sql] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1. SQL> desc x$kccle  
  2.  Name                                      Null?    Type  
  3.  ----------------------------------------- -------- ----------------------------  
  4.  ADDR                                               RAW(8)  
  5.  INDX                                               NUMBER  
  6.  INST_ID                                            NUMBER  实例号  
  7.  LENUM                                              NUMBER  类似group_number  
  8.  LESIZ                                              NUMBER  logfile的size,是操作系统的blocks数目  
  9.  LESEQ                                              NUMBER  v$log中的SEQUENCE#,log file 的sequence#,它等于v$log.sequence# 
  10.  LEHWS                                              NUMBER  
  11.  LEBSZ                                              NUMBER  block size(操作系统的块),log file的块大小,它也等于操作系统块大小  
  12.  LENAB                                              NUMBER  
  13.  LEFLG                                              NUMBER  表示日志的状态信息  
  14.  LETHR                                              NUMBER  v$log中的THREAD#   
  15.  LELFF                                              NUMBER  下一个logfile  
  16.  LELFB                                              NUMBER  前一个logfile  
  17.  LELOS                                              VARCHAR2(16) low scn类似v$log FIRST_CHANGE#  
  18.  LELOT                                              VARCHAR2(20) low time类似v$log  FIRST_TIME  
  19.  LENXS                                              VARCHAR2(16) nex scn 类似v$log中下一个logfile的low FIRST_CHANGE#相同  
  20.  LENXT                                              VARCHAR2(20) nex time类似v$log中下一个logfile的low FIRST_TIME 相同  
  21.  LEPVS                                              VARCHAR2(16) prev scn, 与上一个logfile的low scn   
  22.  LEARF                                              NUMBER  archive link forward   
  23.  LEARB                                              NUMBER   archive link backward   
  24.  LEFNH                                              NUMBER  
  25.  LEFNT                                              NUMBER  
  26.  LEDUP                                              NUMBER 等同于v$log中members  
  27.   
  28. SQL>   



下面对比一下:

[sql] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1. SQL> select * from v$log;      
  2.   
  3.     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME  
  4. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------  
  5.          1          1       2944  104857600          1 YES INACTIVE             121424676 21-MAY-14  
  6.          2          1       2945  104857600          1 NO  CURRENT              121427428 21-MAY-14  
  7.          3          1       2943  104857600          1 YES INACTIVE             121421889 21-MAY-14  
  8.   
  9. SQL>   
  10.   
  11. SQL>  select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;  
  12.   
  13.    INST_ID      LENUM      LESIZ      LESEQ      LEHWS      LEBSZ   LOG_SIZE      LENAB      LEFLG      LETHR      LELFF      LELFB LELOS  
  14. ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------  
  15.          1          1     204800       2944          2        512  104857600     190256          1          1          2          0 121424676  
  16.          1          2     204800       2945          1        512  104857600 4294967295          8          1          3          1 121427428  
  17.          1          3     204800       2943          2        512  104857600     190256          1          1          0          2 121421889  
  18.   
  19. SQL>   
  20. SQL>  select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;  
  21.   
  22.    INST_ID LENUM LESEQ      LETHR LELOT                LENXS            LENXT                LEPVS                 LEARF      LEARB      LEFNH      LEFNT      LEDUP  
  23. ---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------  
  24.          1     1  2944          1 05/21/2014 11:26:42  121427428        05/21/2014 11:26:55  121421889                 0          0          1          1          1  
  25.          1     2  2945          1 05/21/2014 11:26:55  281474976710655                       121424676                 0          0          2          2          1  
  26.          1     3  2943          1 05/21/2014 11:26:30  121424676        05/21/2014 11:26:42  121419052                 0          0          3          3          1  
  27.   
  28. SQL>   
[sql] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1.    

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

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

[sql] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1. SQL> select * from v$log;  
  2.   
  3.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME  
  4. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------  
  5.          1          1         19   52428800        512          2 NO  CURRENT                1013368 27-MAY-14   2.8147E+14  
  6.          2          1         17   52428800        512          2 NO  ACTIVE                 1013348 27-MAY-14      1013365 27-MAY-14  
  7.          3          1         18   52428800        512          2 NO  ACTIVE                 1013365 27-MAY-14      1013368 27-MAY-14  
  8.   
  9. SQL> oradebug setmypid  
  10. Statement processed.  
  11. SQL> oradebug dump controlf 12   
  12. Statement processed.  


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

[plain] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1. LOG FILE RECORDS  
  2. ***************************************************************************  
  3.  (size = 72, compat size = 72, section max = 16, section in-use = 3,  
  4.   last-recid= 17, old-recno = 0, last-recno = 0)  
  5.  (extent = 1, blkno = 10, numrecs = 16)  
  6. LOG FILE #1:  
  7.   name #5: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_1_9r1z70w0_.log  
  8.   name #6: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_1_9r1z70z2_.log  
  9.  Thread 1 redo log links: forward: 2 backward: 0  
  10.  siz: 0x19000 seq: 0x00000013 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2  
  11.  Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f7675  
  12.  Low scn: 0x0000.000f7678 05/27/2014 17:54:08  
  13.  Next scn: 0xffff.ffffffff 01/01/1988 00:00:00  
  14. LOG FILE #2:  
  15.   name #3: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_2_9r1z726s_.log  
  16.   name #4: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_2_9r1z7291_.log  
  17.  Thread 1 redo log links: forward: 3 backward: 1  
  18.  siz: 0x19000 seq: 0x00000011 hws: 0x2 bsz: 512 nab: 0x14 flg: 0x0 dup: 2  
  19.  Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f75ec  
  20.  Low scn: 0x0000.000f7664 05/27/2014 17:54:01  
  21.  Next scn: 0x0000.000f7675 05/27/2014 17:54:05  
  22. LOG FILE #3:  
  23.   name #1: /u01/app/oracle/oradata/RHYS/onlinelog/o1_mf_3_9r1z73jf_.log  
  24.   name #2: /u01/app/oracle/fast_recovery_area/RHYS/onlinelog/o1_mf_3_9r1z73lp_.log  
  25.  Thread 1 redo log links: forward: 0 backward: 2  
  26.  siz: 0x19000 seq: 0x00000012 hws: 0x2 bsz: 512 nab: 0x4 flg: 0x0 dup: 2  
  27.  Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f7664  
  28.  Low scn: 0x0000.000f7675 05/27/2014 17:54:05  
  29.  Next scn: 0x0000.000f7678 05/27/2014 17:54:08  


对应一下:

[sql] view plain copy
 V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析
  1. SQL> select inst_id,lenum,leseq,lethr,lelot,lenxs,lenxt,lepvs,learf,learb,lefnh,lefnt,ledup from x$kccle;  
  2.   
  3.    INST_ID LENUM LESEQ      LETHR LELOT                LENXS            LENXT                LEPVS                 LEARF      LEARB      LEFNH      LEFNT      LEDUP  
  4. ---------- ----- ----- ---------- -------------------- ---------------- -------------------- ---------------- ---------- ---------- ---------- ---------- ----------  
  5.          1     1    19          1 05/27/2014 17:54:08  281474976710655                       1013365                   0          0          5          6          2  
  6.          1     2    17          1 05/27/2014 17:54:01  1013365          05/27/2014 17:54:05  1013228                   0          0          3          4          2  
  7.          1     3    18          1 05/27/2014 17:54:05  1013368          05/27/2014 17:54:08  1013348                   0          0          1          2          2  
  8.   
  9. SQL>  select inst_id,lenum,lesiz,leseq,lehws,lebsz,lebsz*lesiz log_size,lenab,leflg,lethr,lelff,lelfb,lelos from x$kccle;  
  10.   
  11.    INST_ID LENUM      LESIZ LESEQ      LEHWS      LEBSZ   LOG_SIZE      LENAB      LEFLG      LETHR      LELFF      LELFB LELOS  
  12. ---------- ----- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------  
  13.          1     1     102400    19          1        512   52428800 4294967295          8          1          2          0 1013368  
  14.          1     2     102400    17          2        512   52428800         20          0          1          3          1 1013348  
  15.          1     3     102400    18          2        512   52428800          4          0          1          0          2 1013365  
  16.   
  17. 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的原因了。






查询当前操作系统的块大小:

SQL> select max(lebsz) from x$kccle;

MAX(LEBSZ)
----------
       512

大多数平台的操作系统块都为512byte

SQL> select leseq log_file_sequence,lesiz*512/1024/1024||'Mb' log_file_size,lebsz||'byte' os_block_size from x$kccle where lesiz>0;

LOG_FILE_SEQUENCE LOG_FILE_SIZE        OS_BLOCK_SIZE
----------------- -------------------- --------------------
               78 150Mb                512byte
               77 150Mb                512byte

SQL> select sequence#,bytes/1024/1024 log_file_size from v$log;

 SEQUENCE#        LOG_FILE_SIZE
---------- --------------------
        78                  150
        77                  150

查询当前日志文件使用率

SQL> select le.leseq    current_sequence,round(100*cp.cpodr_bno/le.lesiz,2)||'%' percentage,le.lesiz*512/1024/1024||'Mb' log_file_size,
  2  round((le.lesiz*512/1024/1024)*(cp.cpodr_bno/le.lesiz),2)||'Mb' used_size
  3  from x$kcccp cp,x$kccle le where le.leseq =cp.cpodr_seq and le.lesiz>0;

CURRENT_SEQUENCE   PERCENTAGE                                LOG_FILE_SIZE        USED_SIZE
--------------------------    ----------------------------------------- --------------------     ------------------------------------------
              78                    3.86%                                         150Mb                      5.78Mb

 

其中X$kcccp.cpodr_seq就是current logfile的sequence,具体可以看上一篇blog,x$kcccp.cpodr_bno为已写入日志文件最后一个RBA中的日志块数,所以用x$kcccp.cpodr_bno/x$kccle.lesiz就能得到当前日志文件的使用率




X$KCCCP——[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress

SQL> DESC X$KCCCP;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- --------------
 ADDR                                                           RAW(4) 
 INDX                                                           NUMBER  -----通常只需要关心indx=0
 INST_ID                                                        NUMBER
 CPTNO                                                          NUMBER
 CPSTA                                                          NUMBER
 CPFLG                                                          NUMBER
 CPDRT                                                  NUMBER ----select count(*) from v$bh where dirty='Y';检查点队列中的脏块数
 CPRDB                                                          NUMBER       
 CPLRBA_SEQ                                              NUMBER  ----最后一次增量检查点中LOW RBA中的序列号
 CPLRBA_BNO                                            NUMBER  ----最后一次增量检查点在LOW RBA中的块数
 CPLRBA_BOF                                           NUMBER  ----最后一次增量检查点在LOW RBA中的偏移量
 CPODR_SEQ                                    NUMBER  ----已写入日志文件的最后一个RBA的序列号=current日志$log.sequence#
CPODR_BNO                                            NUMBER  ----已写入日志文件最后一个RBA的块数
 CPODR_BOF                                                      NUMBER  ----已写入日志文件最后一个RBA的偏移量
 CPODS                                                          VARCHAR2(16)  ---- on disk rba 的 scn
 CPODT                                                          VARCHAR2(20)  ---- on disk rba 的时间
 CPODT_I                                                        NUMBER
 CPHBT                                                          NUMBER             -----心跳,每三秒钟加一
 CPRLS                                                          VARCHAR2(16)
 CPRLC                                                          NUMBER
 CPMID                                                          NUMBER
 CPSDR_SEQ                                                      NUMBER  
 CPSDR_BNO                                                      NUMBER
 CPSDR_ADB                                                      NUMBE

检查点队列中的脏块数

SQL> select cpdrt dirty,cpods on_disk_scn,cpodt on_disk_time,cphbt from x$kcccp where indx=0;
 
     DIRTY ON_DISK_SCN      ON_DISK_TIME              CPHBT
---------- ---------------- -------------------- ----------
        38 2346941          12/27/2009 23:17:56   706789519
 
SQL> select count(*) from v$bh where dirty='Y';
 
  COUNT(*)
----------
        38

当前日志序列号

SQL> select cpodr_seq,cpodr_bno from x$kcccp where indx=0;
 
 CPODR_SEQ  CPODR_BNO
---------- ----------
        33      30160
SQL> select sequence# from v$log where status='CURRENT';
 
 SEQUENCE#
----------
        33

实例恢复的时候会从CPLRBA_SEQ到CPODR_SEQ,高于cpodr_seq的还在log buffer中,未写入redo file 所以不能用来恢复。





About Me

...............................................................................................................................

本文来自于:http://blog.csdn.net/rhys_oracle/article/details/27225405、http://blog.csdn.net/robinson1988/article/details/5087993

本文在itpubhttp://blog.itpub.net/26736162)、博客园http://www.cnblogs.com/lhrbest和个人微信公众号(xiaomaimiaolhr)上有同步更新

本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

本文博客园地址:http://www.cnblogs.com/lhrbest

本文pdf小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群:230161599     微信群:私聊

联系我请加QQ好友(646634621),注明添加缘由

2017-03-26 09:00 ~ 2017-03-26 22:00魔都完成

文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。

V$LOG的底层表之X$KCCLE深入剖析V$LOG的底层表之X$KCCLE深入剖析

V$LOG的底层表之X$KCCLE深入剖析
V$LOG的底层表之X$KCCLE深入剖析
V$LOG的底层表之X$KCCLE深入剖析

正文到此结束
Loading...