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

首先，我们切换一次日志，再次观察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://6mz.cn/article/iecpho.html

网站建设知识

oracle的offlinescn/onlinescn

其他资讯