oracle数据库offline,oracle的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