闪回技术 基于11GR2

閃回技術：

1.閃回開啟

select flashback_on from v$database;

alter system set db_recovery_file_dest_size=20G scope=both;

alter system set db_recovery_file_dest='/u01/flashback'  scope=both;

shutdown immedaite

startup mount

alter database archivelog;

alter database flashback on;

alter database open;

select flashback_on from v$database;


2.閃回數據庫：

shutdown immediate;

startup mount;

語法一：

flashback database to scn XXX;

語法二：

flashback database to timestamp to_timestamp('2015-06-06','yyyy-mm-dd hh24:mis:ss');

alter database open resetlogs;

3.閃回刪除：

回收站查看：show recyclebin


回收站閃回：

flashback table "BIN$NYkLNqSiMXvgU940qMAjJw==$0" to before drop rename to new_emp;

4.閃回表：

首先要啟用行移動：ALTER TABLE TEST ENABLE ROW MOVEMENT;

閃回查詢：

語法一：

select * from test as of timestamp to_timestamp('2016-06-18 10:50:33','yyyy-mm-dd hh24:mi:ss');

語法二：
select * from test as of scn 6277615;

語法三：

select * from flashback_transaction_query a where a.table_name='test';


(1)數據被更新的表：

flashback table test to timestamp to_timestamp('2015-06-06','yyyy-mm-dd hh24:mis:ss');

flashback table test to scn XXX;


(2)被刪除表：

語法一：

flashback table test to before drop;

語法二：rename閃回刪除表

flashback table test to defore drop rename to new_test;


5.閃回版本查詢：

SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, sal
FROM scott.emp_1
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2016-06-21 11:07:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2016-06-21 11:28:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE  empno='7369';

6.閃回事務查詢：

select * from FLASHBACK_TRANSACTION_QUERY where logon_user='SCOTT';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27646902/viewspace-2120580/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27646902/viewspace-2120580/