Oracle Study之案例--数据恢复神器Flashback（4）
1、建立存储flashback archive表空间14:07:33 SYS@ test1 >create tablespace farch
14:08:21 2datafile '/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 2datafile '/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 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 2for i in1..1000 loop
14:35:34 3insert into emp2 select * from emp;
14:35:38 4commit;
14:35:41 5end loop;
14:35:44 6end;
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"select * from tab;
TNAME TABTYPECLUSTERID
------------------------------ ------- ----------
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
----------------------------------------------------------
23recursive calls
0db block gets
10consistent gets
0physical reads
0redo size
422bytes sent via SQL*Net to client
415bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows 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会去将需要保存的数据存放在对应的适当内部数据表中；
页:
[1]