[20190312]視圖v$datafile欄位OFFLINE_CHANGE#, ONLINE_CHANGE#.txt--//視圖v$datafile存在2個欄位OFFLINE_CHANGE#, ONLINE_CHANGE#,想當然會認為數據文件offline時記錄scn號的改變.--//真的嗎?通 ...
[20190312]視圖v$datafile欄位OFFLINE_CHANGE#, ONLINE_CHANGE#.txt
--//視圖v$datafile存在2個欄位OFFLINE_CHANGE#, ONLINE_CHANGE#,想當然會認為數據文件offline時記錄scn號的改變.
--//真的嗎?通過例子說明:
1.環境:
SYS@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 file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13279958095 2019-03-12 16:40:08 0 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13279958095 2019-03-12 16:40:08 0 0 0 0 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13279958095 2019-03-12 16:40:08 0 0 0 0 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13279958095 2019-03-12 16:40:08 0 0 0 0 ONLINE /mnt/ramdisk/book/users01.dbf
5 13279958095 2019-03-12 16:40:08 0 0 0 0 ONLINE /mnt/ramdisk/book/example01.dbf
6 13279958095 2019-03-12 16:40:08 0 0 0 0 ONLINE /mnt/ramdisk/book/tea01.dbf
6 rows selected.
--//我重建了控制文件,許多欄位當前是空的.
2.測試:
SYS@book> alter database datafile 6 offline ;
Database altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file# in (1,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13279958095 2019-03-12 16:40:08 0 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
6 13279958095 2019-03-12 16:40:08 0 13279959313 2019-03-12 16:53:06 0 0 0 RECOVER /mnt/ramdisk/book/tea01.dbf
--//實際上offline 數據文件時,在控制文件記錄的是LAST_CHANGE#,LAST_TIME.
SYS@book> recover datafile 6;
Media recovery complete.
--//註無法直接online,要執行recover.所以如果有需求要offline,應該養成隨手執行recover datafile N的習慣.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file# in (1,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13279958095 2019-03-12 16:40:08 0 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
6 13279959313 2019-03-12 16:53:06 0 13279959313 2019-03-12 16:53:06 0 0 0 OFFLINE /mnt/ramdisk/book/tea01.dbf
--//CHECKPOINT_CHANGE#=LAST_CHANGE#.status 從RECOVER=>OFFLINE. 看看看看文件頭的情況:
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file# in (1,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13279958095 2019-03-12 16:40:08 7 925702 ONLINE 1224 YES /mnt/ramdisk/book/system01.dbf SYSTEM
6 13279959313 2019-03-12 16:53:06 13276257767 925702 OFFLINE 607 NO /mnt/ramdisk/book/tea01.dbf TEA
--//文件頭的CHECKPOINT_CHANGE#與控制文件CHECKPOINT_CHANGE#一致.
--//註:v$datafile的信息來自控制文件,v$datafile_header的信息來自數據文件頭,不要搞混了.
SYS@book> alter database datafile 6 online ;
Database altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file# in (1,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13279958095 2019-03-12 16:40:08 0 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
6 13279960343 2019-03-12 16:58:40 0 0 0 0 ONLINE /mnt/ramdisk/book/tea01.dbf
--//可以發現數據文件 online後,OFFLINE_CHANGE#,ONLINE_CHANGE#欄位並沒有任何記錄.也就是這個欄位並不是記錄數據文件offline的scn號.
3.繼續測試:
--//既然不是數據文件offline時記錄scn號,自然想到表空間的offline,online有關.
SYS@book> alter tablespace tea offline ;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file# in (1,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13279958095 2019-03-12 16:40:08 0 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
6 13279960510 2019-03-12 17:01:12 0 13279960510 2019-03-12 17:01:12 0 0 0 OFFLINE /mnt/ramdisk/book/tea01.dbf
--//表空間offline,預設要更新文件頭的scn,你可以發現STATUS=OFFLINE(而不是recover).CHECKPOINT_CHANGE#=LAST_CHANGE#.
--//註 : alter tablespace tea offline immediate ;.這樣不更新文件頭,與offline 數據文件類似,你可以理解一組文件(表空間)offline.大家可以自行測試.
SYS@book> alter tablespace tea online ;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file# in (1,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
1 13279958095 2019-03-12 16:40:08 0 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
6 13279960695 2019-03-12 17:03:55 0 13279960510 13279960695 0 ONLINE /mnt/ramdisk/book/tea01.dbf
--//可以發現表空間tea online後,OFFLINE_CHANGE#,ONLINE_CHANGE#有信息,OFFLINE_CHANGE#記錄就是表空間offline時的scn,而ONLINE_CHANGE#記錄就是表空間online時的scn.
--//oracle為什麼這樣設計,假設你需要恢復1個數據文件,取出來的數據文件scn小於OFFLINE_CHANGE#,這樣恢復時,從OFFLINE_CHANGE# 到 ONLINE_CHANGE#的日誌或者歸檔可以跳過,
--//節約日誌應用與恢復時間.
--//自然要問,為什麼數據文件offline時為什麼沒有類似的記錄呢?而數據文件offline時,處於"不穩定狀態",不能直接online的,這樣控制文件僅僅記錄LAST_CHANGE#(offline時).
--//這樣恢復時日誌僅僅應用到LAST_CHANGE#就ok了,但是為什麼數據文件online時LAST_CHANGE#的信息不寫入OFFLINE_CHANGE#,online時的scn寫入ONLINE_CHANGE#呢?
--//感覺這個存在一點點歧義行,個人理解.
--//也許oracle在視圖欄位命名上不科學,應該將OFFLINE_CHANGE#, ONLINE_CHANGE#命名為TABLESPACE_OFFLINE_CHANGE#,TABLESPACE_ONLINE_CHANGE#更加科學一些.
