1
2
3
4
|
1
、Flashback query 特性flashback可以通过undo block查询过去时间点或scn的数据
2
、Flashback version query 特性可以得到特定的表在某一个时间段内的任何修改记录
3
、Flashback transaction query 特性可以限制用户在某一个事务级别上检查数据库的修改操作,适用于诊断问题、分析性能、审计事务。
4
、Flashback table 特性允许oracle通过flashback table语句,将表回滚到前一个时间点或者scn上。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
|
1
、构建测试环境
07
:
01
:
37
SQL> conn scott/tiger
Connected.
07
:
01
:
41
SQL> select *
from
test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
20
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
14
rows selected.
2
、DML误操作
07
:
01
:
45
SQL>
delete
from
test ;
14
rows deleted.
07
:
01
:
59
SQL> commit;
Commit complete.
07
:
02
:
03
SQL> select *
from
test;
no rows selected
07
:
02
:
05
SQL>
insert
into
test select *
from
emp
where
rownum <
3
;
2
rows created.
07
:
02
:
35
SQL> select *
from
test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
07
:
02
:
37
SQL> commit;
Commit complete.
2
、利用logminer工具查找误操作的时间点(挖掘current redo或archive log)
07
:
03
:
03
SQL> select *
from
v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
0
52428800
1
YES UNUSED
0
2
1
1
52428800
1
NO CURRENT
1261015
17
-AUG
-11
3
1
0
52428800
1
YES UNUSED
0
07
:
03
:
20
SQL> col member
for
a50
07
:
03
:
23
SQL> select group#,member
from
v$logfile
GROUP# MEMBER
---------- --------------------------------------------------
3
/u01/app/oracle/oradata/prod/redo03.log
2
/u01/app/oracle/oradata/prod/redo02.log
1
/u01/app/oracle/oradata/prod/redo01.log
11
:
19
:
31
SQL> conn /
as
sysdba
Connected.
11
:
19
:
35
SQL> select *
from
v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
12
52428800
2
YES INACTIVE
823116
29
-SEP
-11
2
1
14
52428800
2
NO CURRENT
828692
29
-SEP
-11
3
2
9
52428800
2
YES INACTIVE
824371
29
-SEP
-11
4
2
11
52428800
2
NO CURRENT
828868
29
-SEP
-11
5
1
13
52428800
2
YES INACTIVE
828670
29
-SEP
-11
6
2
10
52428800
2
YES INACTIVE
828817
29
-SEP
-11
6
rows selected.
11
:
19
:
41
SQL> col member
for
a50
11
:
19
:
57
SQL> select group# ,member
from
v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
2
+DG1/prod/onlinelog/group_2
.262.762877491
2
+RECOVERY/prod/onlinelog/group_2
.258.762877501
1
+DG1/prod/onlinelog/group_1
.261.762877473
1
+RECOVERY/prod/onlinelog/group_1
.257.762877479
3
+DG1/prod/onlinelog/group_3
.266.762877849
3
+RECOVERY/prod/onlinelog/group_3
.259.762877855
4
+DG1/prod/onlinelog/group_4
.267.762877859
4
+RECOVERY/prod/onlinelog/group_4
.260.762877867
6
+DG1/prod/onlinelog/group_6
.272.763037401
6
+RECOVERY/prod/onlinelog/group_6
.262.763037407
5
+DG1/prod/onlinelog/group_5
.271.763037441
GROUP# MEMBER
---------- --------------------------------------------------
5
+RECOVERY/prod/onlinelog/group_5
.261.763037613
12
rows selected.
启动数据库附加日志:
11
:
19
:
58
SQL>Alter database add supplemental log data;
分析current redolog:
11
:
20
:
07
SQL> execute dbms_logmnr.add_logfile(logfilename=>
'+DG1/prod/onlinelog/group_2.262.762877491'
,options=>dbms_logmnr.
new
);
PL/SQL procedure successfully completed.
11
:
20
:
57
SQL> alter session set nls_date_format=
'yyyy-mm-dd'
;
Session altered.
11
:
21
:
32
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
07
:
05
:
21
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
查看DML操作的时间点:
11
:
23
:
11
SQL> select username,scn,timestamp,sql_redo
from
v$logmnr_contents
where
seg_name=
'EMP1'
;
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ---------- --------------------------------------------------
830293
2011
-09
-29
delete
from
"SCOTT"
.
"EMP1"
where
"EMPNO"
=
'7369'
and
"ENAME"
=
'SMITH'
and
"JOB"
=
'CLERK'
and
"MGR
" = '7902' and "
HIREDATE" = TO_DATE(
'1980-12-17'
,
'yyyy-mm-dd'
)
and
"SAL"
=
'800'
and
"COMM"
IS NULL
and
"DEPTNO"
=
'20'
and
ROWID = 'AAAM01AAEAAAAGEA
AA';
3
、flashback query基于时间点的查询
07
:
08
:
42
SQL> conn scott/tiger
Connected.
07
:
08
:
48
SQL> select *
from
test
as
of timestamp to_timestamp(
'2011-08-17 07:01:59'
,
'yyyy-mm-dd hh24:mi:ss'
);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
20
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
14
rows selected.
将查询到的数据写入到表中:
07
:
08
:
50
SQL>
insert
into
test (select *
from
test
as
of timestamp to_timestamp(
'2011-08-17 07:01:59'
,
'yyyy-mm-dd hh24:mi:ss'
));
14
rows created.
07
:
09
:
10
SQL> select *
from
test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
20
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
16
rows selected.
---至此,数据恢复完成!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
07
:
09
:
13
SQL> conn /as sysdba
Connected.
07
:
10
:
28
SQL>
07
:
10
:
28
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1263945
07
:
10
:
39
SQL> conn scott/tiger
Connected.
07
:
13
:
44
SQL> delete from test;
16
rows deleted.
07
:
13
:
51
SQL> commit;
Commit complete.
07
:
13
:
56
SQL> select * from test as of scn
1263945
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC-
80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB-
81
1600
300
30
7369
SMITH CLERK
7902
17
-DEC-
80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB-
81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB-
81
1250
500
30
7566
JONES MANAGER
7839
02
-APR-
81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP-
81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY-
81
2850
30
7782
CLARK MANAGER
7839
09
-JUN-
81
2450
10
7788
SCOTT ANALYST
7566
19
-APR-
87
3000
20
7839
KING PRESIDENT
17
-NOV-
81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP-
81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY-
87
1100
20
7900
JAMES CLERK
7698
03
-DEC-
81
950
30
7902
FORD ANALYST
7566
03
-DEC-
81
3000
20
7934
MILLER CLERK
7782
23
-JAN-
82
1300
10
16
rows selected.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
15
:
49
:
45
SYS@ test3 >select scn,to_char(time_dp,
'yyyy-mm-dd hh24:mi:ss'
)
from
sys.smon_scn_time order by
2
desc;
SCN TO_CHAR(TIME_DP,'YY
---------- -------------------
623337
2015
-01
-13
07
:
45
:
26
623229
2015
-01
-13
07
:
40
:
13
623128
2015
-01
-13
07
:
35
:
28
623009
2015
-01
-13
07
:
30
:
12
622906
2015
-01
-13
07
:
25
:
24
622799
2015
-01
-13
07
:
20
:
11
622695
2015
-01
-13
07
:
15
:
12
622580
2015
-01
-13
07
:
10
:
24
622474
2015
-01
-13
07
:
05
:
11
622390
2015
-01
-13
07
:
01
:
17
622161
2015
-01
-13
06
:
56
:
04
622063
2015
-01
-13
06
:
51
:
20
621954
2015
-01
-13
06
:
46
:
04
621855
2015
-01
-13
06
:
41
:
19
|
1
2
3
4
5
6
7
8
9
|
col versions_xid format a16 heading
'XID'
col versions_startscn format
99999999
heading
'Vsn|Start|SCN'
col versions_endscn format
99999999
heading
'Vsn|End|SCN'
col versions_operation format a12 heading
'Operation'
select versions_xid, versions_startscn, versions_endscn,
decode( versions_operation,
'I'
,
'Insert'
,
'U'
,
'Update'
,
'D'
,
'Delete'
,
'Original'
)
"operation"
,
empno, enamefrom emp1
versions between scn minvalue
and
maxvalue;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
12
:
50
:
06
SYS@ test1 >conn scott/tiger
Connected.
12
:
50
:
14
SCOTT@ test1 >create table emp1
as
select *
from
emp;
Table created.
12
:
50
:
32
SCOTT@ test1 >
insert
into
emp1 select *
from
emp
where
empno=
7788
;
1
row created.
12
:
51
:
53
SCOTT@ test1 >commit;
Commit complete.
12
:
51
:
56
SCOTT@ test1 >update emp1 set sal=
9000
where
empno=
7369
;
1
row updated.
12
:
52
:
10
SCOTT@ test1 >commit;
Commit complete.
12
:
52
:
11
SCOTT@ test1 >
delete
from
emp
where
empno=
7788
;
1
row deleted.
12
:
52
:
23
SCOTT@ test1 >rollback;
Rollback complete.
col versions_xid format a16 heading
'XID'
col versions_startscn format
99999999
heading
'Vsn|Start|SCN'
col versions_endscn format
99999999
heading
'Vsn|End|SCN'
col versions_operation format a12 heading
'Operation'
select versions_xid, versions_startscn, versions_endscn,
decode( versions_operation,
'I'
,
'Insert'
,
'U'
,
'Update'
,
'D'
,
'Delete'
,
'Original'
)
"operation"
,
empno, ename
from
emp1
versions between scn minvalue
and
maxvalue;
Elapsed:
00
:
00
:
00.03
12
:
53
:
12
SCOTT@ test1 >select versions_xid, versions_startscn, versions_endscn,
12
:
54
:
33
2
decode( versions_operation,
'I'
,
'Insert'
,
'U'
,
'Update'
,
'D'
,
'Delete'
,
'Original'
)
"operation"
,
12
:
54
:
33
3
empno, ename
12
:
54
:
33
4
from
emp1
12
:
54
:
33
5
versions between scn minvalue
and
maxvalue;
Vsn Vsn
Start End
XID SCN SCN operation EMPNO ENAME
---------------- --------- --------- -------------------------------- ---------- ----------
08001F007D260000
11966074
Update
7369
SMITH
11966074
Original
7369
SMITH
Original
7499
ALLEN
Original
7521
WARD
Original
7566
JONES
Original
7654
MARTIN
Original
7698
BLAKE
Original
7782
CLARK
Original
7788
SCOTT
Original
7839
KING
Original
7844
TURNER
Original
7876
ADAMS
Original
7900
JAMES
Original
7902
FORD
Original
7934
MILLER
Vsn Vsn
Start End
XID SCN SCN operation EMPNO ENAME
---------------- --------- --------- -------------------------------- ---------- ----------
0A0020006B260000
11966067
Insert
7788
SCOTT
16
rows selected.
|
1
2
3
4
|
select xid, operation, commit_scn, undo_sql
from
flashback_transaction_query
where
xid
in
(select versions_xid
from
emp1 versions between scn minvalue
and
maxvalue);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
在使用flashback transaction query之前需要启动Oracle附加日志(supplemental redo log)
启动数据库附加日志:
13
:
04
:
58
SQL>Alter database add supplemental log data;
13
:
06
:
14
SYS@ test1 >col undo_sql
for
a50
13
:
07
:
17
SYS@ test1 >select xid, operation, commit_scn, undo_sql
2
from
flashback_transaction_query
3
where
xid
in
(
4
select versions_xid
5
from
scott.emp1
6
* versions between scn minvalue
and
maxvalue)
XID OPERATION COMMIT_SCN UNDO_SQL
---------------- -------------------------------- ---------- --------------------------------------------------
0A0020006B260000 INSERT
11966067
delete
from
"SCOTT"
.
"EMP1"
where
ROWID = 'AAAEgIAA
JAAAAC3AAA';
0A0020006B260000 BEGIN
11966067
08001F007D260000 UPDATE
11966074
update
"SCOTT"
.
"EMP1"
set
"SAL"
=
'800'
where
ROWI
D =
'AAAEgIAAJAAAACzAAA'
;
08001F007D260000 BEGIN
11966074
案例
2
:
13
:
08
:
40
SYS@ test1 >conn scott/tiger
Connected.
14
:
03
:
14
SCOTT@ test1 >
insert
into
emp1 select *
from
emp
where
rownum=
1
;
1
row created.
14
:
03
:
30
SCOTT@ test1 >commit;
Commit complete.
14
:
03
:
45
SCOTT@ test1 >update emp set sal=
9000
where
empno=
7788
;
1
row updated.
14
:
03
:
55
SCOTT@ test1 >commit;
Commit complete.
14
:
03
:
57
SCOTT@ test1 >
delete
from
emp
where
empno=
7369
;
0
rows deleted.
14
:
04
:
08
SCOTT@ test1 >commit;
Commit complete.
14
:
08
:
10
SYS@ test1 >col undo_sql
for
a50
14
:
08
:
24
SYS@ test1 >select xid, operation, commit_scn, undo_sql
2
from
flashback_transaction_query
3
where
xid
in
(
4
select versions_xid
5
from
scott.emp1
6
versions between timestamp
7
to_timestamp(
'2015-01-14 14:00:00'
,
'yyyy-mm-dd hh24:mi:ss'
)
8
*
and
to_timestamp(
'2015-01-14 14:05:00'
,
'yyyy-mm-dd hh24:mi:ss'
))
XID OPERATION COMMIT_SCN UNDO_SQL
---------------- -------------------------------- ---------- --------------------------------------------------
04001B0028260000 INSERT
11985210
delete
from
"SCOTT"
.
"EMP1"
where
ROWID = 'AAAEgIAA
JAAAAC1AAA';
04001B0028260000 BEGIN
11985210
|