當表空間或數據文件被offline時，其對應的數據文件的scn會寫入一個值,被稱為offline scn. 不過有一些值得注意的地方，
我這里用實驗來進行剖析，讓大家更容易理解：
查看當前的4號表空間的scn
SYS@hyyk> select file#,checkpoint_change# from v$datafile where ts#=4;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
4 1819632
SYS@hyyk> select file#,checkpoint_change# from v$datafile_header where ts#=4;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
4 1819632
SYS@hyyk> alter tablespace users offline;
Tablespace altered.
SQL> oradebug setmypid
SQL> alter session set events 'immediate trace name CONTROLF level 4';
SQL> alter session set events 'immediate trace name FILE_HDRS level 3';
SQL> oradebug close_trace
SYS@hyyk> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/hyyk/hyyk/trace/hyyk_ora_25045.trc
控制文件
DATA FILE #4:
name #4: /u01/app/oracle/oradata/hyyk/users01.dbf
creation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:201 scn: 0x0000.001be049 02/22/2018 13:02:20
Stop scn: 0x0000.001be049 02/22/2018 13:02:20
Creation Checkpointed at scn: 0x0000.00004649 08/15/2009 00:17:30
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
..................
Offline scn: 0x0000.001bc3b5 prev_range: 1
Online Checkpointed at scn: 0x0000.001bc3f0 02/12/2018 11:35:20
thread:1 rba:(0x3c.183.10)
數據文件
DATA FILE #4:
name #4: /u01/app/oracle/oradata/hyyk/users01.dbf
creation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:201 scn: 0x0000.001be049 02/22/2018 13:02:20
Stop scn: 0x0000.001be049 02/22/2018 13:02:20
Creation Checkpointed at scn: 0x0000.00004649 08/15/2009 00:17:30
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
.......................
Offline scn: 0x0000.001bc3b5 prev_range: 1 ---offline scn
Online Checkpointed at scn: 0x0000.001bc3f0 02/12/2018 11:35:20 ----如果該datafile online,那么該值將被更新
......................
thread:1 rba:(0x3c.183.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
首先，我們切換一次日志，再次觀察checkpoint的變化：
SQL> alter system switch logfile;
SYS@hyyk> select file#,STATUS,CHECKPOINT_CHANGE#,ONLINE_CHANGE# from v$datafile; ---------------------------------------------- 控制文件
FILE# STATUS CHECKPOINT_CHANGE# ONLINE_CHANGE#
---------- ------- ------------------ --------------
1 SYSTEM 1827251 945184
2 ONLINE 1827251 945184
3 ONLINE 1827251 945184
4 OFFLINE 1826889 1819632
5 ONLINE 1827251 974060
6 ONLINE 1827251 0
7 ONLINE 1827251 0
8 ONLINE 1827251 0
SYS@hyyk> select file#,STATUS,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header order by 1; -------------------- 數據文件頭部
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------- ------- ------------------ ----------------
1 ONLINE 1827251 201
2 ONLINE 1827251 201
3 ONLINE 1827251 126
4 OFFLINE 0 0
5 ONLINE 1827251 122
6 ONLINE 1827251 72
7 ONLINE 1827251 59
8 ONLINE 1827251 59
DATA FILE #4:
name #4: /u01/app/oracle/oradata/hyyk/users01.dbf
creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:202 scn: 0x0000.001be1dd 02/22/2018 13:20:24
Stop scn: 0xffff.ffffffff 02/22/2018 13:02:20
Creation Checkpointed at scn: 0x0000.00004649 08/15/2009 00:17:30
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.001be049 prev_range: 2
Online Checkpointed at scn: 0x0000.001be1dd 02/22/2018 13:20:24
|
---從這里可以看到，文件頭的online scn值也更新了。
|
thread:1 rba:(0x3f.8.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
