[20210409]關於X$KCCDI的scn資訊.txt
[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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於SCN需要知道的事
- [20190505]關於latch 一些統計資訊.txt
- [20181123]快速提升scn注意.txt
- 關於資訊保安的
- [20191129]關於hugepages的問題.txt
- [20210410]關於time命令的解析.txt
- 關於-生物資訊-入門-的思考
- [20191206]確定sys.file$相關資訊.txt
- 基於flashback_scn的expdp匯出
- [20200223]關於latch and mutext的優化.txtMutex優化
- [20181229]關於字串的分配問題.txt字串
- 關於轉儲Oracle索引資訊的相關命令Oracle索引
- [20191202]關於hugepages相關問題.txt
- oracle基於SCN增量恢復Oracle
- [20210506]]關於ORA-01450.txt
- [201804012]關於hugepages 3.txt
- [20180306]關於DEFERRED ROLLBACK.txt
- [20190930]關於資料結構設計問題.txt資料結構
- [20200711]關於左右連線的問題.txt
- [20200416]關於軟軟解析的問題.txt
- 透過修改控制檔案scn推進資料庫scn資料庫
- 關於PostgreSQL的系統資訊函式的OIDSQL函式
- [20190821]關於CPU成本計算.txt
- [20210527]關於v$wait_chain.txtAI
- [20181123]關於降序索引問題.txt索引
- [20181212]關於truncate reuse storage.txt
- [20190401]關於semtimedop函式呼叫.txt函式
- [20180912]關於ANSI joins語法.txt
- [20180705]關於hash join 2.txt
- [20180403]關於時區問題.txt
- [20180306]關於DEFERRED ROLLBACK2.txt
- [20211018]運維中關於history的問題.txt運維
- 關於模型關聯 獲取不到關聯資訊 求教模型
- 關於 vue2.x 的 $attrs 和 $listenersVue
- 關於客戶端資訊流思考客戶端
- 關於oracle資料庫訊號量的問題Oracle資料庫
- [20221103]奇怪的mail資訊(整理版本).txtAI
- 【SCN】Oracle SCN 詳細介紹Oracle