|
|
1:记录当前scn* B' K4 V9 q8 D8 s2 q! m, a
select dbms_flashback.get_system_change_number from dual;
. d+ n/ n5 g; `GET_SYSTEM_CHANGE_NUMBER
2 h& }3 i4 M7 f( g------------------------$ R/ T, Q) `+ Q$ i
1159572; F& _/ y z8 Y! ~7 N2 F9 Y
2:创建表
/ G7 m" w9 g8 g) nCREATE TABLE team6 W8 \6 N8 x9 {. R5 j
(
& Y- r& x0 P8 x' w5 W# U team_code VARCHAR2(3),7 Q# x+ s7 _, T/ L. d; a4 D H
team_name VARCHAR2(30),
' i W8 }& N9 [- P9 e country_code VARCHAR2(3)( c+ a( y) l$ R0 V6 o
);
# o8 `: k. t+ Y
) [3 x# q3 K, i5 J" K- x2 {INSERT INTO team VALUES ('MCL','McLaren','GBR');9 ^) V" y. n5 D3 }
COMMIT;
; P6 l3 V ?7 b, g% _0 \( @2：记录当前scn
2 t0 A% C+ D: s8 c, u% RSQL> select dbms_flashback.get_system_change_number from dual;" [5 U7 I5 b/ k. f
8 ~4 v, f! W$ Q) r5 s5 ]# QGET_SYSTEM_CHANGE_NUMBER
% N( I$ e F2 E1 t------------------------) z. o8 l1 }. z% V
1159601) V/ {8 @* T6 P
8 e) F7 r5 A3 q M3：通过scnscn 转储- F% t: N6 h1 m" l4 c
- |: E1 o' ?: w" {9 T/ k* balter system dump logfile '/u01/app/oracle/oradata/devdb/redo01.log' scn min 1159572 scn max 11596011 B) r/ Y# A$ ]$ V A: `
+ u5 S* p" l8 S' s5 }
4：文件分析+ ]7 @6 l! b2 _% Y
REDO RECORD - Thread:1 RBA: 0x000019.00000fef.0010 LEN: 0x01e8 VLD: 0x0d7 U* Z$ x* g3 p1 G, G9 ^0 j: V9 I& p
SCN: 0x0000.0011b1a0 SUBSCN: 1 06/03/2016 15:18:11& M' z4 b* w7 ?; x) O8 r. I
' s' |% n5 y. u8 FTHEREAD： 线程号1;
+ G! M0 z' G9 S" zRBA: 序列号+块号+块里字节号
; e- |0 K9 J$ m6 t0 J4 eLEN:长度
9 J* ]/ ]! I3 A2 z+ G5 {) o' z. EVLD：. s, ?8 d3 [$ b: X& i4 l9 O
SCN：改变时间
. S1 v0 c+ _9 e; u2 M; Jselect to_number('1195A5','xxxxxx') from dual 16to10- P9 k/ [6 {& H" b2 x
select to_char(scn_to_timestamp(1159584),'YYYY-MM-DD HH24:MI:SS') from dual；& z+ \6 v/ g. ~$ S6 E
select timestamp_to_scn(to_date('2016-03-06 15:18:15','YYYY-MM-DD HH24:MI:SS')) from dual;# H: `/ h7 `. O5 A
CHANGE #1 TYP:0 CLS:1 AFN:5 DBA:0x014000a5 OBJ:73445 SCN:0x0000.0011aa1f SEQ:2 OP:11.2 ENC:0 RBL:0
& m/ O$ j. K. T1 fKTB Redo
& n; _8 @: H7 x5 E0 v6 r) hop: 0x01 ver: 0x017 p6 E8 `7 x% F( r* B3 `" G
compat bit: 4 (post-11) padding: 1# n; n$ j7 I4 W3 ?' i
op: F xid: 0x0006.01a.00000313 uba: 0x00c00a6a.00a7.39
" \9 l6 _9 d! A# V1 J 6 P2 K7 @" w( D% O1 f* [
KDO Op code: IRP row dependencies Disabled, l6 w# N: l$ m! n; ~
xtype: XA flags: 0x00000000 bdba: 0x014000a5 hdba: 0x014000a28 L& s, ~ c3 B* _% ^
itli: 1 ispac: 0 maxfr: 48586 x: Q7 z- {0 E2 |
tabn: 0 slot: 0(0x0) size/delt: 19, S0 E8 R P, Q" J; z" `9 Y2 U) Q5 h
fb: --H-FL-- lb: 0x1 cc: 3
5 L. S" `7 _! n8 c( n4 h* I# I' Inull: ---
% y7 v7 a! ]1 u8 _/ a3 V/ lcol 0: [ 3] 4d 43 4c0 S1 W' R3 j5 k. l4 V& B! v6 ~
col 1: [ 7] 4d 63 4c 61 72 65 6e9 O: I6 X+ n% u
col 2: [ 3] 47 42 52
, U3 c! j- a; S/ `5 ~: U
+ T: F; W6 g) D5 i V! U1 Q. M7 tchange #1 改变数
* }8 ]+ B8 T$ k6 U6 fTYP：0 改变类型
& f7 J2 N5 _3 |, h( D8 yCLS：1 参照X$BH class 1表示数据块6 M. f- e0 P# c- l3 n+ W
AFN：绝对数据文件号 5% }6 l3 `# g! S9 G9 N6 z
DBA：更改数据块地址 14000a5（5/165)
$ z- g. _0 w6 u- c. o2 w3 x) e3 C, ROBJ: object id
: q7 v N9 q5 d* V* [) XSCN:0x0000.0011aa1f, w! y$ h1 C: a2 [: V8 p
seq:2：序列号
9 Z B* i1 _' o) A6 z2 t. _: [OP:11.2 Insert Row Piece
: W7 ^/ H. V9 _7 a 3 s5 u2 |% {; W* N/ O6 n8 H3 V# d$ @
KTB REDO
3 w6 E% L6 ~" W8 e' N* H( d# vop: 01 F . W8 s, N2 Q2 f7 g
xid: 0x0006.01a.00000313 transaction ID
, Z' A$ G: p) o3 @, Yuba: 0x00c00a6a.00a7.39 回滚段改变地址，序列号，块记录号
7 |: y0 t/ H6 \; a) k
- ^% T1 d# h- k- MKDO: IRP(Insert Row Piece) row dependencies Disabled (默认创建表示非依赖，启动特性，ORA_ROWSCN伪列新特性）
) n. H. s0 q+ l; x9 A+ Qbdba：block address
( T. \8 o+ A+ phdba：segment header address
' `3 e& ?' k. }3 I( f8 @( Witli：事务slot 1+ C! [+ K0 Q9 i3 w: k. a
tabn: 0 表示非群集表
4 r( ~! k- Q$ @# ]6 m- z slot: 0(0x0) slot number
, Z3 Y: E# {6 [" E5 A. `size/delt: 19 块改变大小，增加19 bytes
9 c# |# Y5 B' C3 J. h- Sfb: --H-FL-- % d1 Z( T7 v1 }& G ^
lb: 0x1 被锁住事务itil 1（与上面相符）. e% d& ]3 Q* T/ M$ g! ?4 h
cc: 3 插入列的数量
" g$ F% j' q) I5 ^
' s; }5 X5 G" u9 `" k1 _' ^ D2 {4 kcol 0: [ 3] 4d 43 4c- c1 |* t3 K H* k
col 1: [ 7] 4d 63 4c 61 72 65 6e
- s Q" r) U! |col 2: [ 3] 47 42 52( t1 B- E$ W* s( _
以上插入数值 |
|
发表于 2018-9-18 10:12:44