189 8069 5689

oracle的offlinescn/onlinescn

offline scn/online scn

当表空间或数据文件被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



SYS@hyyk> alter tablespace users online;

Tablespace altered.

SQL> oradebug setmypid
Statement 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.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

从上面的小实验，我们可以得出如下结论：

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都会重新被置于最大值，即为无穷大.

分享标题：oracle的offlinescn/onlinescn
分享地址：http://jkwzsj.com/article/iecpho.html

其他资讯