offline scn/online scn

当表空间或数据文件被offline时，其对应的数据文件的scn会写入一个值,被称为offline scn. 不过有一些值得注意的地方，我这里用实验来进行剖析，让大家更容易理解：
查看当前的4号表空间的scnSYS@hyyk> select file#,checkpoint_change# from v$datafile where ts#=4;
FILE# CHECKPOINT_CHANGE#---------- ------------------ 41819632

SYS@hyyk> select file#,checkpoint_change# from v$datafile_header where ts#=4;
FILE# CHECKPOINT_CHANGE#---------- ------------------ 41819632
SYS@hyyk> alter tablespace users offline;
Tablespace altered.
SQL> oradebug setmypidSQL> 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.dbfcreation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1tablespace 4, index=4 krfil=4 prev_file=0unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00Checkpoint cnt:201 scn: 0x0000.001be049 02/22/2018 13:02:20Stop scn: 0x0000.001be049 02/22/2018 13:02:20Creation Checkpointed at scn:0x0000.00004649 08/15/2009 00:17:30thread:0 rba:(0x0.0.0)enabledthreads:00000000 00000000 00000000 00000000 00000000 00000000..................Offline scn: 0x0000.001bc3b5 prev_range: 1Online Checkpointed at scn:0x0000.001bc3f0 02/12/2018 11:35:20thread:1 rba:(0x3c.183.10)

数据文件DATA FILE #4:name #4: /u01/app/oracle/oradata/hyyk/users01.dbfcreation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1tablespace 4, index=4 krfil=4 prev_file=0unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00Checkpoint cnt:201 scn: 0x0000.001be049 02/22/2018 13:02:20Stop scn: 0x0000.001be049 02/22/2018 13:02:20Creation Checkpointed at scn:0x0000.00004649 08/15/2009 00:17:30thread:0 rba:(0x0.0.0)enabledthreads:00000000 00000000 00000000 00000000 00000000 00000000.......................Offline scn: 0x0000.001bc3b5 prev_range: 1 ---offline scnOnline Checkpointed at scn:0x0000.001bc3f0 02/12/2018 11:35:20 ----如果该datafileonline,那么该值将被更新 ......................thread:1 rba:(0x3c.183.10)enabledthreads:01000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 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# STATUSCHECKPOINT_CHANGE# ONLINE_CHANGE#---------- ------- ------------------ -------------- 1 SYSTEM1827251945184 2 ONLINE1827251945184 3 ONLINE1827251945184 4 OFFLINE18268891819632 5 ONLINE1827251974060 6 ONLINE18272510 7 ONLINE18272510 8 ONLINE18272510

SYS@hyyk> select file#,STATUS,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header order by 1; --------------------数据文件头部
FILE# STATUSCHECKPOINT_CHANGE# CHECKPOINT_COUNT---------- ------- ------------------ ---------------- 1 ONLINE1827251201 2 ONLINE1827251201 3 ONLINE1827251126 4 OFFLINE0 0 5 ONLINE1827251122 6 ONLINE182725172 7 ONLINE182725159 8 ONLINE182725159


SYS@hyyk> alter tablespace users online;
Tablespace altered.
SQL> oradebug setmypidStatement processed.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
SQL> oradebug tracefile_name/u01/app/oracle/diag/rdbms/hyyk/hyyk/trace/hyyk_ora_26044.trc
DATA FILE #4:name #4: /u01/app/oracle/oradata/hyyk/users01.dbfcreation size=0 block size=8192 status=0xe head=4 tail=4 dup=1tablespace 4, index=4 krfil=4 prev_file=0unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00Checkpoint cnt:202 scn: 0x0000.001be1dd 02/22/2018 13:20:24Stop scn: 0xffff.ffffffff 02/22/2018 13:02:20Creation Checkpointed at scn:0x0000.00004649 08/15/2009 00:17:30thread:0 rba:(0x0.0.0)enabledthreads:00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.001be049 prev_range: 2Online Checkpointed at scn:0x0000.001be1dd 02/22/2018 13:20:24 ---从这里可以看到，文件头的onlinescn值也更新了。thread:1 rba:(0x3f.8.10)enabledthreads:01000000 00000000 00000000 00000000 00000000 00000000
从上面的小实验，我们可以得出如下结论：
1. archivelog模式下,当表空间offline时，其对应的数据文件头stop scn会更新，同时controlfile中该datafile 的stop scn信息也会更新.此时也会更新offline scn，并且offline scn等于stop scn.
2. 当online后，对应的数据文件头的online scn等于datafile checkpoint scn值。
3. 当online后，controlfile中关于该表空对应的datafile和对应的数据文件头的stop scn都会重新被置于最大值，即为无穷大.