[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2767597/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
