【Oracle Database】闪回（FlashBack）

闪回版本查询SQL> set line 200SQL> col starttime for a30SQL> col endtime for a30SQL> col operation for a30SQL> select to_char(versions_starttime,'yyyy-mm-dd hh34:mi:ss') starttime,to_char(versions_endtime,'yyyy-mm-dd hh34:mi:ss') endtime,   versions_xid xid,versions_operation operation from soe.customers versions   between timestamp to_date('2018-09-22 14:00:00','yyyy-mm-dd hh34:mi:ss') and sysdate where versions_xid is not null;STARTTIME                      ENDTIME                        XID              OPERATION------------------------------ ------------------------------ ---------------- ------------------------------2018-09-22 14:00:36                                           05001100B8030000 U2018-09-22 13:59:59                                           05000700B8030000 U2018-09-22 14:00:25                                           08001B00CC030000 U2018-09-22 14:00:14                                           05001000B8030000 I2018-09-22 14:00:20                                           09001700C9030000 I2018-09-22 14:00:05                                           0B00200041000000 I2018-09-22 14:00:05                                           09000700CA030000 I2018-09-22 14:00:11                                           03001800B4030000 I8 rows selected.闪回事务查询SQL> select undo_sql from flashback_transaction_query where xid = hextoraw('03001800B4030000');UNDO_SQL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------delete from "SOE"."LOGON" where ROWID = 'AAAVU+AAGAAAavRAB6';delete from "SOE"."CARD_DETAILS" where ROWID = 'AAAVU4AAHAAAQEoABz';delete from "SOE"."ADDRESSES" where ROWID = 'AAAVU3AAHAAAbuoAAy';delete from "SOE"."CUSTOMERS" where ROWID = 'AAAVU2AAGAAAaqxAAO';SQL> select undo_sql from flashback_transaction_query where xid = hextoraw('08001B00CC030000');UNDO_SQL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------update "SOE"."CUSTOMERS" set "PREFERRED_ADDRESS" = '1162533' where ROWID = 'AAAVU2AAGAAACC9AAS';delete from "SOE"."ADDRESSES" where ROWID = 'AAAVU3AAHAAAbumAA5';

闪回表--scnSQL> alter table soe.customers enable row movement;Table altered.SQL> select count(*) from soe.customers;  COUNT(*)----------    975317SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER------------------------                 1217679                 SQL> delete from soe.customers where nls_territory = 'Korea';12470 rows deleted.SQL> commit;Commit complete.SQL> select count(*) from soe.customers;  COUNT(*)----------    962847SQL> flashback table soe.customers to scn 1217679;Flashback complete.SQL> select count(*) from soe.customers;  COUNT(*)----------    975317    闪回表--timestamp    SQL> select count(*) from soe.customers;  COUNT(*)----------    975317SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER------------------------                 1221943SQL> select scn_to_timestamp(1221943) scn from dual;SCN---------------------------------------------------------------------------23-SEP-18 04.17.24.000000000 PMSQL> select count(*) from soe.customers;  COUNT(*)----------    975317SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1222481SQL> select scn_to_timestamp(1222481) scn from dual;SCN---------------------------------------------------------------------------23-SEP-18 04.22.21.000000000 PMSQL> select count(*) from soe.customers;  COUNT(*)----------    975340SQL> flashback table soe.customers to timestamp to_timestamp('2018-09-23 16:17:24','yyyy-mm-dd hh34:mi:ss');Flashback complete.SQL> select count(*) from soe.customers;  COUNT(*)----------    975317

闪回删除SQL> show parameter recyclebin;NAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------recyclebin                           string                            ONSQL> drop table warehouses;Table dropped.SQL> show recyclebin;ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME---------------- ------------------------------ ------------ -------------------WAREHOUSES       BIN$doc0/FwhEVPgU8kBqMBrow==$0 TABLE        2018-09-23:17:07:50SQL> flashback table warehouses to before drop;Flashback complete.SQL> select count(*) from warehouses;  COUNT(*)----------      1000      SQL> alter index "BIN$doc0/FwgEVPgU8kBqMBrow==$0" rename to warehouses_pk;Index altered.SQL> alter table warehouses rename constraint "BIN$doc0/FwfEVPgU8kBqMBrow==$0" to warehouses_pk;Table altered.

闪回数据库SQL> startup mount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2260088 bytesVariable Size             322962312 bytesDatabase Buffers          687865856 bytesRedo Buffers               55848960 bytesDatabase mounted.SQL> alter database flashback on;Database altered.SQL> alter database open;Database altered.SQL> select dbid,name,flashback_on,current_scn from v$database;      DBID NAME                        FLASHBACK_ CURRENT_SCN---------- --------------------------- ---------- -----------1085678857 KING                        YES            1229459SQL> show parameter db_flashbackNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------db_flashback_retention_target        integer                           1440SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';Session altered.SQL> select count(*) from orders;  COUNT(*)----------   1430069   SQL> select sysdate from dual;SYSDATE-------------------2018-09-24 13:43:57SQL> truncate table orders;Table truncated.SQL> select count(*) from orders;  COUNT(*)----------         0         SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2260088 bytesVariable Size             322962312 bytesDatabase Buffers          687865856 bytesRedo Buffers               55848960 bytesDatabase mounted.SQL> flashback database to timestamp to_timestamp('2018-09-24 13:43:57','yyyy-mm-dd hh34:mi:ss');Flashback complete.SQL> alter database open read only;Database altered.SQL> conn soe/soeConnected.SQL> select count(*) from orders;  COUNT(*)----------   1430069   SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2260088 bytesVariable Size             322962312 bytesDatabase Buffers          687865856 bytesRedo Buffers               55848960 bytesDatabase mounted.SQL> alter database open resetlogs;Database altered.SQL> conn soe/soeConnected.SQL> select count(*) from orders;  COUNT(*)----------   1430069

总结：闪回版本查询，闪回事务查询，闪回表与UNDO有关，与闪回日志没有任何关系     闪回删除与recyclebin有关，与闪回日志没有任何关系     闪回数据库与闪回日志有关