首页 » ORACLE 9i-23ai » scn format (scn格式)
scn format (scn格式)
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
对不起,这篇文章暂时关闭评论。