flashback

--1、闪回时间点查询
--Select ...From Table_Name As Of Timestamp | Scn Exp 
SQL> Insert Into testtbl Values(1,'a');
1 row inserted
SQL> Insert Into testtbl Values(2,'b');
1 row inserted
SQL> Insert Into testtbl Values(3,'c');
1 row inserted
SQL> Commit;
Commit complete
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 21:50:58


SQL> 
SQL> Update testtbl Set Content=Content || '1' Where Id In(2,3);
2 rows updated
SQL> Commit;
Commit complete
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 21:51:36


SQL> 
SQL> Delete testtbl Where Id=1;
1 row deleted
SQL> Commit;
Commit complete
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 21:51:59


SQL> select * from testtbl;
        ID CONTENT
---------- --------------------
         2 b1
         3 c1


SQL> Select * From testtbl As Of Timestamp to_timestamp('20150904 21:50:58','yyyymmdd hh24:mi:ss');
        ID CONTENT
---------- --------------------


SQL> Select * From testtbl As Of Timestamp to_timestamp('20150904 21:51:18','yyyymmdd hh24:mi:ss');
        ID CONTENT
---------- --------------------
         1 a
         2 b
         3 c


SQL> Select * From testtbl As Of Scn timestamp_to_scn(to_date('20150904 21:51:18','yyyymmdd hh24:mi:ss'));
        ID CONTENT
---------- --------------------
         1 a
         2 b
         3 c


SQL> 
SQL> Select * From testtbl As Of Timestamp to_timestamp('20150904 21:51:46','yyyymmdd hh24:mi:ss');
        ID CONTENT
---------- --------------------
         1 a
         2 b1
         3 c1
SQL> Select * From testtbl As Of Scn timestamp_to_scn(to_date('20150904 21:51:46','yyyymmdd hh24:mi:ss'));
        ID CONTENT
---------- --------------------
         1 a
         2 b1
         3 c1


SQL> 
SQL> Select * From testtbl As Of Timestamp to_timestamp('20150904 21:51:59','yyyymmdd hh24:mi:ss');
        ID CONTENT
---------- --------------------
         2 b1
         3 c1
SQL> Select * From testtbl As Of Scn timestamp_to_scn(to_date('20150904 21:51:59','yyyymmdd hh24:mi:ss'));
        ID CONTENT
---------- --------------------
         2 b1
         3 c1


SQL> 
--2、闪回版本查询
--Select versions_starttime,versions_endtime,versions_operation,verstions_xid From Table_Name 
--versions Between Timestamp|Scn Exp|Minvalue  And  Exp|Maxvalue 
SQL> Select to_char(versions_starttime,'yyyymmdd hh24:mi:ss') As versions_starttime,to_char(versions_endtime,'yyyymmdd hh24:mi:ss') As versions_endtime,
  2         versions_xid,versions_operation,Id,Content
  3  From testtbl Versions Between Timestamp Minvalue And Maxvalue
  4  /
VERSIONS_STARTTIME VERSIONS_ENDTIME  VERSIONS_XID     VERSIONS_OPERATION         ID CONTENT
------------------ ----------------- ---------------- ------------------ ---------- --------------------
20150904 21:51:56                    04001B00C0110000 D                           1 a
20150904 21:51:35                    070006005C110000 U                           3 c1
20150904 21:51:35                    070006005C110000 U                           2 b1
20150904 21:50:56  20150904 21:51:35 0500210097170000 I                           3 c
20150904 21:50:56  20150904 21:51:35 0500210097170000 I                           2 b
20150904 21:50:56  20150904 21:51:56 0500210097170000 I                           1 a
6 rows selected


SQL> 
SQL> Select to_char(versions_starttime,'yyyymmdd hh24:mi:ss') As versions_starttime,to_char(versions_endtime,'yyyymmdd hh24:mi:ss') As versions_endtime,
  2         versions_xid,versions_operation,Id,Content
  3  From testtbl Versions Between Scn Minvalue And Maxvalue
  4  /
VERSIONS_STARTTIME VERSIONS_ENDTIME  VERSIONS_XID     VERSIONS_OPERATION         ID CONTENT
------------------ ----------------- ---------------- ------------------ ---------- --------------------
20150904 21:51:56                    04001B00C0110000 D                           1 a
20150904 21:51:35                    070006005C110000 U                           3 c1
20150904 21:51:35                    070006005C110000 U                           2 b1
20150904 21:50:56  20150904 21:51:35 0500210097170000 I                           3 c
20150904 21:50:56  20150904 21:51:35 0500210097170000 I                           2 b
20150904 21:50:56  20150904 21:51:56 0500210097170000 I                           1 a
6 rows selected


SQL> 
SQL> Select to_char(versions_starttime,'yyyymmdd hh24:mi:ss') As versions_starttime,to_char(versions_endtime,'yyyymmdd hh24:mi:ss') As versions_endtime,
  2         versions_xid,versions_operation,Id,Content
  3  From testtbl Versions Between Timestamp to_timestamp('20150904 21:51:18','yyyymmdd hh24:mi:ss') And to_timestamp('20150904 21:51:46','yyyymmdd hh24:mi:ss')
  4  /
VERSIONS_STARTTIME VERSIONS_ENDTIME  VERSIONS_XID     VERSIONS_OPERATION         ID CONTENT
------------------ ----------------- ---------------- ------------------ ---------- --------------------
20150904 21:51:35                    070006005C110000 U                           3 c1
20150904 21:51:35                    070006005C110000 U                           2 b1
                   20150904 21:51:56                                              1 a
                   20150904 21:51:35                                              2 b
                   20150904 21:51:35                                              3 c
SQL> Select to_char(versions_starttime,'yyyymmdd hh24:mi:ss') As versions_starttime,to_char(versions_endtime,'yyyymmdd hh24:mi:ss') As versions_endtime,
  2         versions_xid,versions_operation,Id,Content
  3  From testtbl Versions Between Scn  Timestamp_to_scn(to_timestamp('20150904 21:51:18','yyyymmdd hh24:mi:ss'))
  4                            And Timestamp_to_scn(to_timestamp('20150904 21:51:46','yyyymmdd hh24:mi:ss'));
VERSIONS_STARTTIME VERSIONS_ENDTIME  VERSIONS_XID     VERSIONS_OPERATION         ID CONTENT
------------------ ----------------- ---------------- ------------------ ---------- --------------------
20150904 21:51:35                    070006005C110000 U                           3 c1
20150904 21:51:35                    070006005C110000 U                           2 b1
                   20150904 21:51:56                                              1 a
                   20150904 21:51:35                                              2 b
                   20150904 21:51:35                                              3 c


SQL> 


--3、闪回表
--flashback table table_name to scn|timestamp exp
SQL> Create Table testtbl(Id Number,Content varchar2(20));
Table created


SQL> 
SQL> Insert Into testtbl Values(1,'a');
1 row inserted
SQL> Insert Into testtbl Values(2,'b');
1 row inserted
SQL> Insert Into testtbl Values(3,'c');
1 row inserted
SQL> Commit;
Commit complete
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 22:12:48


SQL> 
SQL> Update testtbl Set Content=Content || '1' Where Id In(2,3);
2 rows updated
SQL> Commit;
Commit complete
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 22:12:55


SQL> 
SQL> Delete testtbl Where Id=1;
1 row deleted
SQL> Commit;
Commit complete
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 22:13:07


SQL> select * from testtbl;
        ID CONTENT
---------- --------------------
         2 b1
         3 c1


SQL> alter table testtbl enable row movement;
Table altered


SQL> Flashback Table testtbl To Timestamp to_date('20150904 22:12:48','yyyy-mm-dd hh24:mi:ss');
Done


SQL> select * from testtbl;
        ID CONTENT
---------- --------------------
         1 a
         2 b
         3 c


SQL> Flashback Table testtbl To Scn Timestamp_to_scn(to_date('20150904 22:12:59','yyyy-mm-dd hh24:mi:ss'));
Done


SQL> select * from testtbl;
        ID CONTENT
---------- --------------------
         1 a
         2 b1
         3 c1


--4、闪回删除
--flashback table table_name to before drop [rename to new_table_name]


SQL> create table a(id number);
Table created


SQL> drop table a;
Table dropped


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
BIN$C6LDnjOeQbK3OMsdI9qaYw==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:27:31


SQL> flashback table a to before drop;
Done


SQL> select * from a;
        ID
----------


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------


SQL> SQL> create table a(id number);
Table created


SQL> insert into a values(1);
1 row inserted


SQL> commit;
Commit complete


SQL> select * from a;
        ID
----------
         1


SQL> drop table a;
Table dropped


SQL> create table a(id number);
Table created


SQL> insert into a values(2);
1 row inserted


SQL> commit;
Commit complete


SQL> select * from a;
        ID
----------
         2


SQL> drop table a;
Table dropped


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
BIN$qjidafK7RLKtYQpdMzNMag==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:28:51
BIN$7x9wH8GkQi+ppoc4QW7qJw==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:29:51
BIN$gxRp6SToTxqGpDbpB1q4nQ==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:29:26


SQL> flashback table a to before drop;
Done
--后删除的先恢复
SQL> select * from a;   
        ID
----------
         2


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
BIN$qjidafK7RLKtYQpdMzNMag==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:28:51
BIN$gxRp6SToTxqGpDbpB1q4nQ==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:29:26


SQL> flashback table a to before drop rename to a1;
Done


SQL> select * from a1;
        ID
----------
         1


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
BIN$qjidafK7RLKtYQpdMzNMag==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:28:51


SQL> flashback table "BIN$qjidafK7RLKtYQpdMzNMag==$0" to before drop ;
flashback table "BIN$qjidafK7RLKtYQpdMzNMag==$0" to before drop
ORA-38312: 原始名称已被现有对象使用


SQL> flashback table "BIN$qjidafK7RLKtYQpdMzNMag==$0" to before drop rename to aa;
Done


SQL> select * from aa;
        ID
----------


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------


SQL> drop table a;
Table dropped


SQL> drop table a1;
Table dropped


SQL> drop table aa;
Table dropped


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
BIN$STaScVbsTau5DUtd/xPwaw==$0 AA                               DROP      TABLE                     USERS                          2015-09-04:22:32:48
BIN$bAqTF7ChSiW3RtKEa+PsrQ==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:32:44
BIN$FnKniKCITPyb1e2NcyGSig==$0 A1                               DROP      TABLE                     USERS                          2015-09-04:22:32:46
--清空回收站
SQL> purge recyclebin;
Done


SQL> 
--更改session的recyclebin值
SQL> show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on


SQL> alter session set recyclebin=off;
Session altered


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------


SQL> create table a(id number);
Table created


SQL> drop table a;
Table dropped


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------


SQL> alter session set recyclebin=on;
Session altered


SQL> create table a(id number);
Table created


SQL> drop table a;
Table dropped


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------
BIN$37fIS7sSTX+WY2rMsMPxbA==$0 A                                DROP      TABLE                     USERS                          2015-09-04:22:35:50


SQL> 


--不放入回收站
SQL> create table a(id number);
Table created


SQL> drop table a purge;
Table dropped


SQL> select a.object_name,a.original_name,a.operation,a.type,a.ts_name,a.droptime from user_recyclebin a;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ -------------------


SQL> 


--清空回收站的方式
  purge tablespace tablespace_name 
        user       user_name
        table      table_name
        index      index_name
        recyclebin dba_recyclebin


--5、闪回数据库
--设置闪回数据库
SQL> startup mount;
ORACLE 例程已经启动。


Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             289407440 bytes
Database Buffers          239075328 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
SQL> alter system set db_recovery_file_dest='d:\flashback_database' scope=spfile
;


系统已更改。


SQL> alter system set db_recovery_file_dest_size=3g;


系统已更改。


SQL> alter system set db_flashback_retention_target=2880;


系统已更改。


SQL> alter database archivelog;


数据库已更改。


SQL> alter database flashback on
  2  /


数据库已更改。


SQL> alter database open;


数据库已更改。


 


--查看db_recovery_file_dest
      db_recovery_file_dest_size
      db_flashback_retention_target
 SQL> show parameter db_recovery


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      d:\flb
db_recovery_file_dest_size           big integer 3G
SQL> show parameter db_flashback_retention_target


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     2880
SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
下一个存档日志序列   1
当前日志序列           1
SQL> select flashback_on from v$database;


FLASHBACK_ON
------------------
YES
SQL> Select * From v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------------------- ---------------- -------------- ------------------------
           659392830 2015/9/4 22:51:34                 2880        8192000                        0


SQL> 




--开始测试
SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 23:12:09


SQL> Create Tablespace flashbacktbl Datafile 'd:\flashbacktbl01.dbf' Size 1m;
Tablespace created


SQL> Select tablespace_name From dba_tablespaces Where tablespace_name='FLASHBACKTBL';
TABLESPACE_NAME
------------------------------
FLASHBACKTBL


SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 23:14:03


SQL> create  table flashback_tmp2(id number);
Table created


SQL> Select to_char(Sysdate,'yyyymmdd hh24:mi:ss') From dual;
TO_CHAR(SYSDATE,'YYYYMMDDHH24:
------------------------------
20150904 23:14:26


SQL> 
SQL> startup mount
ORACLE 例程已经启动。


Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             289407440 bytes
Database Buffers          239075328 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
SQL> Flashback Database To Timestamp to_timestamp('20150904 23:12:09','yyyymmdd
hh24:mi:ss');


闪回完成。


SQL> alter database open read only;


数据库已更改。


SQL> Select tablespace_name From dba_tablespaces Where tablespace_name='FLASHBAC
KTBL';


未选定行


SQL> select * system.from flashback_tmp2;
select * from system.flashback_tmp2
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在




SQL>
SQL> startup mount
ORACLE 例程已经启动。


Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             289407440 bytes
Database Buffers          239075328 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
SQL> Flashback Database To Timestamp to_timestamp('20150904 23:14:03','yyyymmdd
hh24:mi:ss');


闪回完成。


SQL> alter database open resetlogs;


数据库已更改。


SQL> Select tablespace_name From dba_tablespaces Where tablespace_name='FLASHBAC
KTBL';


TABLESPACE_NAME
------------------------------
FLASHBACKTBL


SQL> select * from system.flashback_tmp2;
select * from system.flashback_tmp2
                     *
第 1 行出现错误:
ORA-00942: 表或视图不存在




SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。


Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             289407440 bytes
Database Buffers          239075328 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
SQL> Flashback Database To Timestamp to_timestamp('20150904 23:14:26','yyyymmdd
hh24:mi:ss');


闪回完成。


SQL> alter database open resetlogs;


数据库已更改。


SQL> Select tablespace_name From dba_tablespaces Where tablespace_name='FLASHBAC
KTBL';


TABLESPACE_NAME
------------------------------
FLASHBACKTBL


SQL> select * from system.flashback_tmp2;


未选定行


SQL>