|
|
1:记录当前scn
% t; Y, e9 K1 m# k' W* w/ oselect dbms_flashback.get_system_change_number from dual;
- f) M$ g) g* F% vGET_SYSTEM_CHANGE_NUMBER# U$ {- |: S; _6 ^4 D+ \# W' u
------------------------! p6 w3 M3 ^0 M" `+ r5 U- |% V
11595728 _; H: N& F3 c
2:创建表
7 `3 B4 r* f$ A0 |% t/ ~CREATE TABLE team+ r: H6 }& ^" w+ s1 P% M
(
; Y/ l( p5 ]6 V$ M team_code VARCHAR2(3),0 _9 Y! A# d6 K) D
team_name VARCHAR2(30),! h8 T3 m0 J: G6 ^
country_code VARCHAR2(3)8 Y$ V" _6 g) A) _+ u9 R' ~/ ]
);
9 w( Y: m# K5 {8 U5 Y+ F- s6 |
+ w* Y3 ^4 P5 c. z# x( O5 S* o4 _INSERT INTO team VALUES ('MCL','McLaren','GBR');
; I5 @( v/ f6 j* Q$ O" E( UCOMMIT;
9 N* @; ]: J6 e( w$ ^" V- K. O2：记录当前scn6 ] c. a- v# q! I
SQL> select dbms_flashback.get_system_change_number from dual;
; D, p$ ^ \. }; u$ D: \6 y5 s0 S 4 P9 q- m1 R" h% [( e7 [/ j1 @
GET_SYSTEM_CHANGE_NUMBER3 T+ u. L$ Z) |; V
------------------------& R+ n+ d( R5 F9 g" v2 [5 ]
1159601
, o/ C3 }! y! f* d, Z
: c! [+ O" u, B2 a/ W4 ]( S3：通过scnscn 转储& `! R# _. a9 ]8 M: L9 T L4 f& z
4 L2 J% S% R, d3 n: ]1 walter system dump logfile '/u01/app/oracle/oradata/devdb/redo01.log' scn min 1159572 scn max 1159601
2 u/ H3 e! G: }3 Y; ]0 m# q & Q, h+ m/ c _# \8 }. ]9 `
4：文件分析
9 I# w& J$ D5 t9 Q- TREDO RECORD - Thread:1 RBA: 0x000019.00000fef.0010 LEN: 0x01e8 VLD: 0x0d6 Z( Q K8 j5 m0 ~8 n: f
SCN: 0x0000.0011b1a0 SUBSCN: 1 06/03/2016 15:18:11& U O$ v( v+ @1 m7 j1 Q* `. W* `
3 Q2 n* q/ R$ H* cTHEREAD： 线程号1;. _. @7 t0 N$ }" U
RBA: 序列号+块号+块里字节号
3 P% ~6 u9 n3 f; oLEN:长度
5 h9 O4 K! s2 K) LVLD：
- u/ N$ g/ x% Y7 XSCN：改变时间' ^8 p7 I9 {; Q- l6 f. R; J; }
select to_number('1195A5','xxxxxx') from dual 16to10
" \6 n! q: d) X. c4 sselect to_char(scn_to_timestamp(1159584),'YYYY-MM-DD HH24:MI:SS') from dual；* Q3 p+ W. j' K" [9 b0 f8 t1 Q, B
select timestamp_to_scn(to_date('2016-03-06 15:18:15','YYYY-MM-DD HH24:MI:SS')) from dual;, G# ~5 G+ Z9 K
CHANGE #1 TYP:0 CLS:1 AFN:5 DBA:0x014000a5 OBJ:73445 SCN:0x0000.0011aa1f SEQ:2 OP:11.2 ENC:0 RBL:0# g3 ~! N" H. m3 |- w8 [ P) q$ o
KTB Redo4 J# w( M+ c) v7 H d- f
op: 0x01 ver: 0x01) ]; t! b0 W7 |* U, f+ G( [/ B' F2 Y" t+ E
compat bit: 4 (post-11) padding: 17 X* | d' k/ n* o5 T h* m
op: F xid: 0x0006.01a.00000313 uba: 0x00c00a6a.00a7.39. ~ ?! q1 Q$ G0 N+ D4 a
1 S8 i4 ]" O' h1 e6 C; e$ t
KDO Op code: IRP row dependencies Disabled) r' Z0 D4 U8 [
xtype: XA flags: 0x00000000 bdba: 0x014000a5 hdba: 0x014000a25 N7 P3 j* N4 ]& ~4 [( i8 I
itli: 1 ispac: 0 maxfr: 4858
6 S% Q2 Y1 A( N u* [- btabn: 0 slot: 0(0x0) size/delt: 19( h: B( z: Y; v# P8 o8 X8 q2 }3 `
fb: --H-FL-- lb: 0x1 cc: 31 q/ G$ D/ T# z8 D& n, ^+ N( p2 u
null: ---& V# Y: Z2 X. {# I; V
col 0: [ 3] 4d 43 4c
: O2 R* E& H; v+ Rcol 1: [ 7] 4d 63 4c 61 72 65 6e3 \1 M# E5 J( V
col 2: [ 3] 47 42 52
$ |4 g+ d; |" X* L. F, |7 |
1 G2 A. Q% a. }* L2 I5 rchange #1 改变数. D( \5 D% N- ]; E. v9 d
TYP：0 改变类型4 E) s4 E* W& g+ \& V! U
CLS：1 参照X$BH class 1表示数据块
3 `+ p9 |6 y [" I& B6 ^& ^AFN：绝对数据文件号 59 [. g- n; N8 V! B4 c6 B
DBA：更改数据块地址 14000a5（5/165)# x1 }$ s9 b/ t; K" R
OBJ: object id1 W$ X* e9 n, a# ~# E: d
SCN:0x0000.0011aa1f
8 ?8 n* `/ o) D7 T7 @2 Zseq:2：序列号
! O3 s8 G- M7 g" x2 LOP:11.2 Insert Row Piece# e% F9 O. t, V" @5 S( i
: W" k3 ?2 r* [& o
KTB REDO% @5 _/ P Q1 I, d* B: T. `. B
op: 01 F 4 T! a( j# v9 ?( y
xid: 0x0006.01a.00000313 transaction ID
% _) y/ V* s2 j% ^, G3 suba: 0x00c00a6a.00a7.39 回滚段改变地址，序列号，块记录号1 g3 e1 P& N6 z# z: V, Y3 K
. y. R4 z g" @' V) _0 |
KDO: IRP(Insert Row Piece) row dependencies Disabled (默认创建表示非依赖，启动特性，ORA_ROWSCN伪列新特性）
. |% d: @6 J8 J7 I- l$ W5 bbdba：block address
0 @ \ v& }0 O6 ghdba：segment header address2 b$ `1 S# p% w
itli：事务slot 1
' b8 [/ a$ m+ G, x5 D ntabn: 0 表示非群集表: m8 i. Y" c) |2 O
slot: 0(0x0) slot number
! ]* H1 I% n! Z; `7 lsize/delt: 19 块改变大小，增加19 bytes
* q$ C5 l# O) K. b, Ofb: --H-FL-- 1 r" G$ v7 U3 T( w4 v
lb: 0x1 被锁住事务itil 1（与上面相符）
& ~9 l! L! n8 rcc: 3 插入列的数量
o3 u5 L+ ~+ P4 y ?# Y$ X5 E
/ s1 s0 o& `. z# R7 o; [col 0: [ 3] 4d 43 4c
O% p1 S4 O. k: c+ U8 G1 ~: Ycol 1: [ 7] 4d 63 4c 61 72 65 6e
2 [- a% e; g. Ocol 2: [ 3] 47 42 52
% \1 F0 n6 w* j+ n/ n% ^* Z以上插入数值 |
|
发表于 2018-9-18 10:12:44