1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
15
:
44
:
05
SYS@ cuug>select flashback_archive_name,status
from
dba_flashback_archive;
FLASHBACK_ARCHIVE_NA STATUS
-------------------- -------
FBA1 DEFAULT
FBA2
FBA3
FBA4
15
:
44
:
09
SYS@ cuug>drop flashback archive fba1;
Flashback archive dropped.
16
:
13
:
22
SYS@ cuug>select flashback_archive_name,status
from
dba_flashback_archive;
FLASHBACK_ARCHIVE_NA STATUS
-------------------- -------
FBA2
FBA3
FBA4
16
:
13
:
26
SYS@ cuug>alter table scott.dept flashback archive;
alter table scott.dept flashback archive
*
ERROR
at
line
1
:
ORA
-55608
: Default Flashback Archive does
not
exist
|
1
2
|
CREATE USER fbda_admin IDENTIFIED BY fbda_admin;
GRANT FLASHBACK ARCHIVE ADMINISTER TO fbda_admin;
|
1
2
3
4
5
6
|
GRANT FLASHBACK ANY TABLE TO hr;
GRANT EXECUTE ON DBMS_FLASHBACK TO hr;
GRANT FLASHBACK ANY TABLE TO oe;
GRANT EXECUTE ON DBMS_FLASHBACK TO oe;
GRANT FLASHBACK ANY TABLE TO sh;
GRANT EXECUTE ON DBMS_FLASHBACK TO sh;
|
1
2
3
|
CREATE TABLESPACE fbda
DATAFILE
'/u01/app/oracle/oradata/ORCL/tsp_fdba01.dbf'
SIZE 24M;
|
1
2
3
4
5
6
7
|
CREATE FLASHBACK ARCHIVE fbda_1
TABLESPACE fbda
QUOTA 1M
RETENTION
5
DAY;
GRANT FLASHBACK ARCHIVE ON fbda_1 TO hr;
GRANT FLASHBACK ARCHIVE ON fbda_1 TO oe;
GRANT FLASHBACK ARCHIVE ON fbda_1 TO sh;
|
1
2
3
4
5
6
7
|
CREATE FLASHBACK ARCHIVE fbda_2
TABLESPACE fbda
QUOTA 4M
RETENTION
1
YEAR;
GRANT FLASHBACK ARCHIVE ON fbda_2 TO hr;
GRANT FLASHBACK ARCHIVE ON fbda_2 TO oe;
GRANT FLASHBACK ARCHIVE ON fbda_2 TO sh;
|
1
2
3
4
5
6
7
|
CREATE FLASHBACK ARCHIVE fbda_3
TABLESPACE fbda
QUOTA 20M
RETENTION
7
YEAR;
GRANT FLASHBACK ARCHIVE ON fbda_3 TO hr;
GRANT FLASHBACK ARCHIVE ON fbda_3 TO oe;
GRANT FLASHBACK ARCHIVE ON fbda_3 TO sh;
|
1
|
ALTER FLASHBACK ARCHIVE fbda_2 SET DEFAULT;
|
1
|
ALTER FLASHBACK ARCHIVE fbda_1 SET DEFAULT;
|
1
|
ALTER TABLE hr.applicants FLASHBACK ARCHIVE;
|
1
2
3
|
ALTER TABLE hr.departments FLASHBACK ARCHIVE fbda_1;
ALTER TABLE hr.job_history FLASHBACK ARCHIVE fbda_2;
ALTER TABLE oe.customers FLASHBACK ARCHIVE fbda_3;
|
1
|
ALTER TABLE hr.departments NO FLASHBACK ARCHIVE;
|
1
2
3
4
5
6
7
8
9
10
11
|
17
:
04
:
39
SYS@ cuug>drop flashback archive fbda_1;
Flashback archive dropped.
17
:
04
:
39
SYS@ cuug>drop flashback archive fbda_2;
Flashback archive dropped.
17
:
04
:
48
SYS@ cuug>drop flashback archive fbda_3;
Flashback archive dropped.
17
:
04
:
51
SYS@ cuug>drop flashback archive fbda_4;
Flashback archive dropped.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DELETE FROM hr.applicants
WHERE application_date <= TO_DATE(
'11-10-2008'
,
'dd-mm-yyyy'
);
COMMIT;
INSERT INTO hr.applicants
SELECT *
FROM hr.applicants
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(
'2008-12-04 10:00'
,
'yyyy-mm-dd hh24:mi'
)
AND MAXVALUE
WHERE VERSIONS_OPERATION =
'D'
;
COMMIT;
|
1
2
|
ALTER FLASHBACK ARCHIVE fbda_1
PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL
'1'
DAY);
|
1
2
|
ALTER FLASHBACK ARCHIVE fbda_1
MODIFY TABLESPACE fbda QUOTA 2M;
|
1
2
|
ALTER FLASHBACK ARCHIVE fbda_3
MODIFY RETENTION
90
DAY;
|
1
2
3
4
5
6
|
DROP TABLESPACE fbda_extd INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE fbda_extd
DATAFILE
'/u01/app/oracle/oradata/ORCL/tsp_fdba_extd01.dbf'
SIZE 16M;
ALTER FLASHBACK ARCHIVE fbda_1
ADD TABLESPACE fbda_extd;
|
1
|
DROP FLASHBACK ARCHIVE fbda_1;
|
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
|
1
、建立存储flashback archive表空间
14
:
07
:
33
SYS@ test1 >create tablespace farch
14
:
08
:
21
2
datafile
'/u01/app/oracle/oradata/test1/farch01.dbf'
size 100m;
Tablespace created.
14
:
09
:
17
SYS@ test1 >select file_id,file_name,tablespace_name
from
dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1
/u01/app/oracle/oradata/test1/system01.dbf SYSTEM
6
/u01/app/oracle/oradata/test1/dict1.dbf DICT1
9
/dsk1/oradata/test1/users02.dbf USERS
4
/u01/app/oracle/oradata/test1/users01.dbf USERS
3
/u01/app/oracle/oradata/test1/test1.dbf TEST1
2
/u01/app/oracle/oradata/test1/sysaux01.dbf SYSAUX
......
16
/u01/app/oracle/oradata/test1/farch01.dbf FARCH
7
/u01/app/oracle/oradata/test1/undotbs2.dbf UNDOTBS2
13
/u01/app/oracle/oradata/test1/tbs_16.dbf TBS_16
16
rows selected.
2
、建立flashback archive在farch表空间,retention为
1
个月
14
:
09
:
26
SYS@ test1 >create flashback archive ftb1 tablespace farch retention
1
month;
Flashback archive created.
3
、设置默认flashback archive(可以建立多个flashback archive)
14
:
13
:
15
SYS@ test1 >alter flashback archive ftb1 set default;
Flashback archive altered.
14
:
14
:
20
SYS@ test1 >col FLASHBACK_ARCHIVE_NAME
for
a30
14
:
14
:
31
SYS@ test1 >select flashback_archive_name,status
from
dba_flashback_archive
FLASHBACK_ARCHIVE_NAME STATUS
------------------------------ -------
FTB1 DEFAULT
4
、建立一个小的undo tablespace用于测试
14
:
14
:
31
SYS@ test1 >show parameter undo
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer
900
undo_tablespace string UNDOTBS2
14
:
17
:
18
SYS@ test1 >create undo tablespace sm_undo
14
:
17
:
37
2
datafile
'/u01/app/oracle/oradata/test1/sm_undo01.dbf'
size 4m;
Tablespace created.
14
:
18
:
22
SYS@ test1 >alter system set undo_tablespace=sm_undo ;
System altered.
14
:
18
:
43
SYS@ test1 >show parameter undo
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer
900
undo_tablespace string SM_UNDO
5
、授权用户可以使用flashback archive
14
:
29
:
25
SYS@ test1 >grant flashback archive
on
ftb1 to scott;
Grant succeeded.
6
、设置table启用flashback archive
14
:
29
:
29
SCOTT@ test1 >alter table emp1 flashback archive ;
Table altered.
7
、建立测试环境(查询闪回归档可以基于scn或timestamp)
14
:
30
:
08
SYS@ test1 >select current_scn
from
v$database;
CURRENT_SCN
-----------
12015381
14
:
32
:
15
SCOTT@ test1 > select count(*)
from
emp1
COUNT(*)
----------
13001
DML误操作:
14
:
33
:
18
SCOTT@ test1 >
delete
from
emp1
where
rownum <
3001
;
3000
rows deleted.
14
:
33
:
23
SCOTT@ test1 >commit;
Commit complete.
14
:
33
:
33
SCOTT@ test1 >select count(*)
from
emp1;
COUNT(*)
----------
10001
循环脚本,覆盖undo block:
14
:
35
:
25
SCOTT@ test1 >begin
14
:
35
:
27
2
for
i
in
1.
.1000
loop
14
:
35
:
34
3
insert
into
emp2 select *
from
emp;
14
:
35
:
38
4
commit;
14
:
35
:
41
5
end loop;
14
:
35
:
44
6
end;
14
:
35
:
45
7
/
PL/SQL procedure successfully completed.
14
:
35
:
47
SCOTT@ test1 >set autotrace
on
14
:
36
:
01
SCOTT@ test1 >select count(*)
from
emp1
as
of scn
12015381
;
COUNT(*)
----------
13001
Elapsed:
00
:
00
:
00.13
Execution Plan
----------------------------------------------------------
Plan hash value:
3926065282
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
1
| |
91
(
2
)|
00
:
00
:
02
| | |
|
1
| SORT AGGREGATE | |
1
| | | | | |
|
2
| VIEW | |
340
| |
91
(
2
)|
00
:
00
:
02
| | |
|
3
| UNION-ALL | | | | | | | |
|*
4
| FILTER | | | | | | | |
|
5
| PARTITION RANGE SINGLE| |
1
|
26
|
6
(
0
)|
00
:
00
:
01
|
1
|
1
|
|*
6
| TABLE ACCESS FULL | SYS_FBA_HIST_18440 |
1
|
26
|
6
(
0
)|
00
:
00
:
01
|
1
|
1
|
|*
7
| FILTER | | | | | | | |
|*
8
| HASH JOIN OUTER | |
339
| 675K|
91
(
2
)|
00
:
00
:
02
| | |
|*
9
| TABLE ACCESS FULL | EMP1 |
339
|
4068
|
84
(
0
)|
00
:
00
:
02
| | |
|*
10
| TABLE ACCESS FULL | SYS_FBA_TCRV_18440 |
1
|
2028
|
6
(
0
)|
00
:
00
:
01
| | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4
- filter(NULL IS NOT NULL)
6
- filter(
"ENDSCN"
>
12015381
AND
"ENDSCN"
<=
12015350
AND (
"STARTSCN"
IS NULL OR
"STARTSCN"
<=
12015381
))
7
- filter(
"STARTSCN"
<=
12015381
OR
"STARTSCN"
IS NULL)
8
- access(
"T"
.ROWID=CHARTOROWID(
"RID"
(+)))
9
- filter(
"T"
.
"VERSIONS_STARTSCN"
IS NULL)
10
- filter((
"ENDSCN"
(+) IS NULL OR
"ENDSCN"
(+)>
12015350
) AND (
"STARTSCN"
(+) IS NULL OR
"STARTSCN"
(+)<
12015350
))
Note
-----
- dynamic sampling used
for
this
statement (level=
2
)
Statistics
----------------------------------------------------------
26
recursive calls
0
db block gets
9140
consistent gets
0
physical reads
0
redo size
424
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
3
sorts (memory)
0
sorts (disk)
1
rows processed
通过查询的执行计划可以看到,数据是从SYS_FBA_TCRV_18440(闪回区)读出,可以证明此历史数据不是从undo block而是flashback archive读出。
8
、和flashback archive相关的视图
14
:
36
:
21
SCOTT@ test1 >desc SYS_FBA_TCRV_18440
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
RID VARCHAR2(
4000
)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(
8
)
OP VARCHAR2(
1
)
14
:
39
:
51
SCOTT@ test1 >select count(*)
from
SYS_FBA_TCRV_18440;
COUNT(*)
----------
3000
14
:
40
:
18
SCOTT@ test1 >select *
from
tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP1 TABLE
EMP2 TABLE
SALGRADE TABLE
SYS_FBA_DDL_COLMAP_18440 TABLE
SYS_FBA_HIST_18440 TABLE
SYS_FBA_TCRV_18440 TABLE
9
rows selected.
14
:
41
:
02
SCOTT@ test1 >select table_name,tablespace_name
from
user_tables
where
table_name like
'%FBA%'
;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_FBA_DDL_COLMAP_18440 FARCH
SYS_FBA_TCRV_18440 FARCH
SYS_FBA_HIST_18440
14
:
41
:
47
SCOTT@ test1 >col object_name
for
a30
14
:
42
:
02
SCOTT@ test1 >select object_name,object_type
from
user_objects
where
object_name like
'%FBA%'
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_FBA_DDL_COLMAP_18440 TABLE
SYS_FBA_HIST_18440 TABLE PARTITION
SYS_FBA_HIST_18440 TABLE
SYS_FBA_TCRV_18440 TABLE
SYS_FBA_TCRV_IDX_18440 INDEX
14
:
42
:
36
SCOTT@ test1 >col table_name
for
a10
14
:
42
:
46
SCOTT@ test1 >col owner_name
for
a10
14
:
42
:
52
SCOTT@ test1 >col FLASHBACK_ARCHIVE_NAME
for
a20
14
:
43
:
02
SCOTT@ test1 >col ARCHIVE_TABLE_NAME
for
a20
14
:
43
:
12
SCOTT@ test1 >select *
from
user_flashback_archive_tables
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
---------- ---------- -------------------- -------------------- --------
EMP1 SCOTT FTB1 SYS_FBA_HIST_18440 ENABLED
14
:
43
:
49
SCOTT@ test1 >col table_name
for
a30
14
:
44
:
04
SCOTT@ test1 >select table_name
from
dict
where
table_name like
'%FLASHBACK_ARCHIVE%'
TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TS
DBA_FLASHBACK_ARCHIVE_TABLES
USER_FLASHBACK_ARCHIVE_TABLES
14
:
44
:
05
SCOTT@ test1 >desc USER_FLASHBACK_ARCHIVE
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER_NAME VARCHAR2(
30
)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(
255
)
FLASHBACK_ARCHIVE# NOT NULL NUMBER
RETENTION_IN_DAYS NOT NULL NUMBER
CREATE_TIME TIMESTAMP(
9
)
LAST_PURGE_TIME TIMESTAMP(
9
)
STATUS VARCHAR2(
7
)
14
:
44
:
45
SCOTT@ test1 >select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,FLASHBACK_ARCHIVE#,RETENTION_IN_DAYS
from
USER_FLASHBACK_ARCHIVE;
OWNER_NAME FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
---------- -------------------- ------------------ -----------------
SYS FTB1
1
30
Elapsed:
00
:
00
:
00.10
案例
2
:
14
:
45
:
22
SCOTT@ test1 >alter table emp flashback archive;
Table altered.
Elapsed:
00
:
00
:
00.13
14
:
58
:
08
SCOTT@ test1 >select count(*)
from
emp;
COUNT(*)
----------
13
Elapsed:
00
:
00
:
00.01
14
:
58
:
22
SCOTT@ test1 >
delete
from
emp
where
empno=
7788
;
1
row deleted.
Elapsed:
00
:
00
:
00.08
14
:
58
:
31
SCOTT@ test1 >commit;
Commit complete.
Elapsed:
00
:
00
:
00.03
14
:
58
:
34
SCOTT@ test1 >select count(*)
from
emp;
COUNT(*)
----------
12
Elapsed:
00
:
00
:
00.00
14
:
58
:
38
SCOTT@ test1 >set autotrace
on
14
:
58
:
55
SCOTT@ test1 >select count(*)
from
emp
as
of timestamp to_timestamp(
'2015-01-15 14:55:00'
,
'yyyy-mm-dd hh24:mi:ss'
);
COUNT(*)
----------
13
Elapsed:
00
:
00
:
00.01
Execution Plan
----------------------------------------------------------
Plan hash value:
2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
1
|
6
(
0
)|
00
:
00
:
01
|
|
1
| SORT AGGREGATE | |
1
| | |
|
2
| TABLE ACCESS FULL| EMP |
409
|
6
(
0
)|
00
:
00
:
01
|
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
23
recursive calls
0
db block gets
10
consistent gets
0
physical reads
0
redo size
422
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
1
rows processed
从查询的执行计划可以看出,此次历史数据的查询是从undo block读出!
为了实现Flashback Archive的功能,Oracle新引入了一个实例进程为FBDA(Flashback Archived Process)。该进程启动时随着数据库同时启动。FBDA的作用如下:
FBDA首先从buffer cache中的undo表空间数据中查找过去数据表时间点数据。这点是与flashback query的特性相似;
如果要查找的数据在undo tablespace中,但是该块没有在buffer cache中。FBDA会从undo segment中获取到数据块,复制在buffer cache中;
当进行flashback archive操作的数据表发生修改的时候,FBDA会去将需要保存的数据存放在对应的适当内部数据表中;
|