[20210409]关于X$KCCDI的scn信息.txt

来源：这里教程网时间：2026-03-03 16:36:57 作者：

[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;

编辑推荐：

返回列表

[20210409]关于X$KCCDI的scn信息.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推