[20210409]關於X$KCCDI的scn資訊.txt

lfree發表於2021-04-12

[20210409]關於X$KCCDI的scn資訊.txt

--//被問及X$KCCDI檢視中的scn資訊。說真的,自己也很少關注這些,而且非常容易混淆,看看對應是那些。
1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281459836      13281481815      13281482197      04/09/2021 16:29:35  13281460797      04/09/2021 11:42:04  13281481819      13281482205

--//如果你查詢gv$datasbe檢視定義可以知道:
DISCN      gv$database的CHECKPOINT_CHANGE#
DIFAS      gv$database的ARCHIVE_CHANGE#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DICKP_SCN  gv$database的CONTROLFILE_CHANGE#
DICKP_TIM  gv$database的CONTROLFILE_TIME
DIARS      gv$database的ARCHIVELOG_CHANGE#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DICUR_SCN  gv$database的CURRENT_SCN

--//DISSC_SCN,DISSC_TIM先放一邊。注意DICKP_SCN,DICKP_TIM與檢查點沒有關係,不要被欄位命名給矇蔽。
--//可以看出DIFAS,DIARS 最不好理解。

SYS@book> @ logfile
GROUP# STATUS     TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       905    52428800       512       1 YES ACTIVE       13281444099 2021-04-09 08:05:09  13281481819 2021-04-09 16:23:43
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       906    52428800       512       1 NO  CURRENT      13281481819 2021-04-09 16:23:43 2.814750E+14
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       904    52428800       512       1 YES INACTIVE     13281401277 2021-04-08 22:18:57  13281444099 2021-04-09 08:05:09
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//DIARS 的值13281481819 與當前redo的FIRST_CHANGE#一致。

RMAN> delete archivelog all  ;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=58 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=128 device type=DISK
List of Archived Log Copies for database with db_unique_name BOOK
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
163     1    905     A 2021-04-09 08:05:09
        Name: /u01/app/oracle/archivelog/book/1_905_896605872.dbf

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/archivelog/book/1_905_896605872.dbf RECID=163 STAMP=1069431824
Deleted 1 objects

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281481819      13281481815      13281483906      04/09/2021 16:53:09  13281483900      04/09/2021 16:53:09  0                13281483920

--//注意看DIARS=0,說明實際上DIARS記錄的是Archivelog Highest SCN.因為當前我清空了全部歸檔。
--//另外DISSC_SCN DISSC_TIM也發生了變化,說明這個scn以及時間與備份相關。

RMAN> backup spfile;

Starting backup at 2021-04-09 16:56:52
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2021-04-09 16:56:52
channel ORA_DISK_1: finished piece 1 at 2021-04-09 16:56:53
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/backupset/2021_04_09/o1_mf_nnsnf_TAG20210409T165652_j705pny5_.bkp tag=TAG20210409T165652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-04-09 16:56:53

Starting Control File and SPFILE Autobackup at 2021-04-09 16:56:54
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2021_04_09/o1_mf_s_1069433814_j705ppfq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-09 16:56:55

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281481819      13281481815      13281484166      04/09/2021 16:56:55  13281484161      04/09/2021 16:56:55  0                13281484176
--//基本可以確定DISSC_SCN DISSC_TIM與備份相關。

SYS@book> alter system archive log current ;
System altered.

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281481819      13281484279      13281484284      04/09/2021 16:58:45  13281484161      04/09/2021 16:56:55  13281484283      13281484287
--//注意執行alter system archive log current ;後,DIARS 與DIFAS僅僅相差4。

SYS@book> alter system switch logfile;
System altered.

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281481819      13281484279      13281484397      04/09/2021 17:00:30  13281484161      04/09/2021 16:56:55  13281484397      13281484399
--//注意看alter system archive log current ;與alter system switch logfile;的區別,可以發現前者DIFAS會發生變化,而後者DIFAS不會發生變化。

SYS@book> @ logfile
GROUP# STATUS     TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       908    52428800       512       1 NO  CURRENT      13281484397 2021-04-09 17:00:30 2.814750E+14
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       906    52428800       512       1 YES ACTIVE       13281481819 2021-04-09 16:23:43  13281484283 2021-04-09 16:58:44
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       907    52428800       512       1 YES ACTIVE       13281484283 2021-04-09 16:58:44  13281484397 2021-04-09 17:00:30
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//redo02.log ,redo03.log的STATUS=ACTIVE.

SYS@book> alter system switch logfile;
System altered.

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281484283      13281484283      13281485807      04/09/2021 17:13:36  13281484161      04/09/2021 16:56:55  13281485807      13281485810
--//DIFAS=13281484283,也就是執行alter system switch logfile;後,SEQUENCE#=906被覆蓋,DIFAS等於=SEQUENCE#=907的FIRST_CHANGE#。
--//也就是可以理解DIFAS一般情況下記錄的是online redo的最小scn。再一次alter system switch logfile;,DIFAS等於
--// =SEQUENCE#=908的FIRST_CHANGE# 13281484397。

SYS@book> alter system switch logfile;
System altered.

SYS@book> @ logfile
GROUP# STATUS     TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       908    52428800       512       1 YES ACTIVE       13281484397 2021-04-09 17:00:30  13281485807 2021-04-09 17:13:36
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       909    52428800       512       1 YES ACTIVE       13281485807 2021-04-09 17:13:36  13281485842 2021-04-09 17:14:04
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       910    52428800       512       1 NO  CURRENT      13281485842 2021-04-09 17:14:04 2.814750E+14
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281484397      13281484397      13281485842      04/09/2021 17:14:04  13281484161      04/09/2021 16:56:55  13281485842      13281485882
--//與我前面的推測一致。
--//仔細看我原來寫的blog
http://blog.itpub.net/267265/viewspace-2151257/ => 20180226alter system archive log current

--//當我手工執行 alter system archive log current ;,執行為前臺程式完成歸檔,DIFAS等於對應就是執行命令時的scn。
--//而後臺執行歸檔,實際上有ora_arcN_<SID>完成,寫入的就是onlie redo的最小scn。

--//繼續上個星期的測試說明驗證我的判斷:
$ cat aa.txt
select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
alter system archive log current ;
select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;

SYS@book> @ aa.txt
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281486401      13281484397      13281486476      04/12/2021 08:42:54  13281484161      04/09/2021 16:56:55  13281485842      13281487515

System altered.
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281486401      13281487515      13281487519      04/12/2021 08:43:33  13281484161      04/09/2021 16:56:55  13281487519      13281487520

--//DIFAS=前面一次執行select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;看到的DICUR_SCN值。
--//也驗證我前面的推斷,手工執行 alter system archive log current ;,執行為前臺程式完成歸檔,DIFAS等於對應就是執行命令時的scn。

SYS@book> @ logfile
GROUP# STATUS     TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       911    52428800       512       1 NO  CURRENT      13281487519 2021-04-12 08:43:33 2.814750E+14
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       909    52428800       512       1 YES INACTIVE     13281485807 2021-04-09 17:13:36  13281485842 2021-04-09 17:14:04
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       910    52428800       512       1 YES ACTIVE       13281485842 2021-04-09 17:14:04  13281487519 2021-04-12 08:43:33
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.

$ rlrman
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 12 08:46:15 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BOOK (DBID=1337401710)

RMAN> list copy of archivelog  all ;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name BOOK
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
164     1    906     A 2021-04-09 16:23:43
        Name: /u01/app/oracle/archivelog/book/1_906_896605872.dbf

165     1    907     A 2021-04-09 16:58:44
        Name: /u01/app/oracle/archivelog/book/1_907_896605872.dbf

166     1    908     A 2021-04-09 17:00:30
        Name: /u01/app/oracle/archivelog/book/1_908_896605872.dbf

167     1    909     A 2021-04-09 17:13:36
        Name: /u01/app/oracle/archivelog/book/1_909_896605872.dbf

168     1    910     A 2021-04-09 17:14:04
        Name: /u01/app/oracle/archivelog/book/1_910_896605872.dbf
--//seq=910已經歸檔,刪除看看。DIARS=13281487519,也就是Archivelog Highest SCN。

RMAN> delete archivelog sequence 910;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=101 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=114 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=128 device type=DISK
List of Archived Log Copies for database with db_unique_name BOOK
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
168     1    910     A 2021-04-09 17:14:04
        Name: /u01/app/oracle/archivelog/book/1_910_896605872.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/archivelog/book/1_910_896605872.dbf RECID=168 STAMP=1069663413
Deleted 1 objects

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281486401      13281487515      13281487948      04/12/2021 08:49:44  13281487942      04/12/2021 08:49:43  13281485842      13281487979
--//DIARS=13281485842,對應指令碼logfile的輸出,可以發現就是seq=909那行的NEXT_CHANGE#=13281485842,再次驗證DIARS等於Archivelog Highest SCN。
--//另外注意DISSC_SCN,DISSC_TIM也發生變化,說明這2個欄位與備份有關。

SYS@book> alter system switch logfile;
System altered.

SYS@book> alter system switch logfile;
System altered.

SYS@book> alter system switch logfile;
System altered.

SYS@book> @ logfile
GROUP# STATUS     TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       914    52428800       512       1 NO  CURRENT      13281488454 2021-04-12 08:56:57 2.814750E+14
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       912    52428800       512       1 YES INACTIVE     13281488445 2021-04-12 08:56:53  13281488449 2021-04-12 08:56:54
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       913    52428800       512       1 YES INACTIVE     13281488449 2021-04-12 08:56:54  13281488454 2021-04-12 08:56:57
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281488454      13281488445      13281488454      04/12/2021 08:56:57  13281487942      04/12/2021 08:49:43  13281488454      13281488490
--//DIFAS=13281488445,相當於線上日誌的最小scn。

SYS@book> alter database drop logfile group 2;
Database altered.

SYS@book> select discn,difas,dickp_scn,dickp_tim,dissc_scn,dissc_tim,diars,dicur_scn from X$KCCDI;
DISCN            DIFAS            DICKP_SCN        DICKP_TIM            DISSC_SCN        DISSC_TIM            DIARS            DICUR_SCN
---------------- ---------------- ---------------- -------------------- ---------------- -------------------- ---------------- ----------------
13281488454      13281488445      13281489452      04/12/2021 08:59:43  13281487942      04/12/2021 08:49:43  13281488454      13281489456
--//這樣操作DIFAS並沒有變化。

SYS@book> alter database add logfile group 2('/mnt/ramdisk/book/redo02.log') size 50M reuse;
Database altered.

--//至此,基本解析了X$KCCDI的scn資訊.
--//附上logfile指令碼。
$ cat logfile.sql
set numw 12
column con_id noprint
column type format a10
column status format a10
column group# format 99999
column thread# format 99999
column sequence# format 99999999
column members format 9999
column blocksize format 999
column BYTES format 9999999999
column member  form a76
SELECT b.*,a.*
  FROM v$log a, v$logfile b
 WHERE a.group#(+) = b.group#
 ORDER BY a.group#, b.member;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2767597/,如需轉載,請註明出處,否則將追究法律責任。

相關文章