[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#更加科學一些.

[20190312]檢視v$datafile欄位OFFLINE_CHANGE#, ONLINE_CHANGE#.txt

相關文章