ANBOB™

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

打赏

微信扫一扫，打赏作者吧～