[转帖]scn format (scn格式)_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1966 | 回复: 0

主题： [转帖]scn format (scn格式)

dansmall

注册用户

等级：新兵
经验：52
发帖：71
精华：0
注册：2011-10-27
状态：离线
发送短消息息给dansmall

加好友发送短消息息给dansmall

发消息

发表于：

2015-3-18 16:57:31 | [全部帖] [楼主帖]

楼主

scn(system change number) 结构主要维护 oracle数据库内部的数据一致性，SCN 有两部分组成: Base and wrap，wrap 是16bit的数字，base是32bit的数字,这样其实就可以算scn的有效范围，它的格式(redo dump trace)是wrap.base，当base超过了2的32次方,然后wrap 就会加1，其实用sql 很好验证。

sys@ICME>select to_char(dbms_flashback.get_system_change_number,'xxxxxxxxxxxxxxxxxxxxxx'),
2 dbms_flashback.get_system_change_number curscn from dual;

TO_CHAR(DBMS_FLASHBACK. CURSCN
----------------------- ------------------------
309c4d413 13048796179

sys@ICME>select to_number(3,'xxxxxxx')*power(2,32)+to_number('09c4d413','xxxxxxxxxxx') from dual;

TO_NUMBER(3,'XXXXXXX')*POWER(2,32)+TO_NUMBER('09C4D413','XXXXXXXXXXX')
----------------------------------------------------------------------
13048796179

在Goldengate for oracle中也可以看到SCN的身影

GGSCI (dbserver56) 3> info ext eicmec

EXTRACT EICMEC Last Started 2013-10-10 13:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
2013-10-16 11:52:59 Seqno 4326, RBA 593920
SCN 3.163890420 (13048792308)

Tip:

这里的SCN 的格式刚好就是wrap.base,但显示的是十进制(Decimal)数字

sys@ICME>select 3*power(2,32)+163890420 from dual;
3*POWER(2,32)+163890420
———————–
13048792308
Note:

scn 并不是每个操作都会改变，从redo dump trace file中可以发现。使用下面的sql

select 'alter system dump logfile '||chr(39)||member||chr(39)
from v$log lg, v$logfile lgfile
where lg.group# = lgfile.group# and
lg.sequence# = (select sequence#-1 from v$log where status='CURRENT' and
thread#=(select thread# from v$instance ))
and lg.thread#=(select thread# from v$instance)
and rownum <2;

look at dump file

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000a.0080 LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 9 rdba: 0x0248bb3e BFT:(1024,38320958) non-BFT:(9,572222)
scn: 0x0003.09c42140 seq: 0x01 flg:0x06

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000a.00c4 LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 9 rdba: 0x02488367 BFT:(1024,38306663) non-BFT:(9,557927)
scn: 0x0003.09c42140 seq: 0x01 flg:0x06

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000a.0108 LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 11 rdba: 0x02c3025c BFT:(1024,46334556) non-BFT:(11,197212)
scn: 0x0003.09c4213c seq: 0x01 flg:0x06

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000a.014c LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 8 rdba: 0x0206e0bf BFT:(1024,34005183) non-BFT:(8,450751)
scn: 0x0003.09c4213c seq: 0x01 flg:0x06

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000a.0190 LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 11 rdba: 0x02c30200 BFT:(1024,46334464) non-BFT:(11,197120)
scn: 0x0003.09c42444 seq: 0x01 flg:0x04

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000a.01d4 LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 8 rdba: 0x0206d46c BFT:(1024,34002028) non-BFT:(8,447596)
scn: 0x0003.09c4213c seq: 0x01 flg:0x06

REDO RECORD - Thread:1 RBA: 0x0010e5.0000000b.0028 LEN: 0x0044 VLD: 0x02
SCN: 0x0003.09c43938 SUBSCN: 1 10/16/2013 11:20:08
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0

— 截至目前的版本 oracle rdbms 11203, goldengate 11.2

--转自