oracle dbms_flashback,【Oracle Database】闪回（FlashBack）

闪回版本查询

SQL> set line 200

SQL> col starttime for a30

SQL> col endtime for a30

SQL> col operation for a30

SQL> select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') starttime,to_char(versions_endtime,'yyyy-mm-dd hh24: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 hh24:mi:ss') and sysdate where versions_xid is not null;

STARTTIME                      ENDTIME                        XID              OPERATION

------------------------------ ------------------------------ ---------------- ------------------------------

2018-09-22 14:00:36                                           05001100B8030000 U

2018-09-22 13:59:59                                           05000700B8030000 U

2018-09-22 14:00:25                                           08001B00CC030000 U

2018-09-22 14:00:14                                           05001000B8030000 I

2018-09-22 14:00:20                                           09001700C9030000 I

2018-09-22 14:00:05                                           0B00200041000000 I

2018-09-22 14:00:05                                           09000700CA030000 I

2018-09-22 14:00:11                                           03001800B4030000 I

8 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';

闪回表--scn

SQL> alter table soe.customers enable row movement;

Table altered.

SQL> select count(*) from soe.customers;

COUNT(*)

----------

975317

SQL> 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(*)

----------

962847

SQL> 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(*)

----------

975317

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1221943

SQL> select scn_to_timestamp(1221943) scn from dual;

SCN

---------------------------------------------------------------------------

23-SEP-18 04.17.24.000000000 PM

SQL> select count(*) from soe.customers;

COUNT(*)

----------

975317

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1222481

SQL> select scn_to_timestamp(1222481) scn from dual;

SCN

---------------------------------------------------------------------------

23-SEP-18 04.22.21.000000000 PM

SQL> select count(*) from soe.customers;

COUNT(*)

----------

975340

SQL> flashback table soe.customers to timestamp to_timestamp('2018-09-23 16:17:24','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select count(*) from soe.customers;

COUNT(*)

----------

975317

闪回删除

SQL> show parameter recyclebin;

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

recyclebin                           string                            ON

SQL> 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:50

SQL> 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 bytes

Fixed Size                  2260088 bytes

Variable Size             322962312 bytes

Database Buffers          687865856 bytes

Redo Buffers               55848960 bytes

Database 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            1229459

SQL> show parameter db_flashback

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

db_flashback_retention_target        integer                           1440

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select count(*) from orders;

COUNT(*)

----------

1430069

SQL> select sysdate from dual;

SYSDATE

-------------------

2018-09-24 13:43:57

SQL> truncate table orders;

Table truncated.

SQL> select count(*) from orders;

COUNT(*)

----------

0

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2260088 bytes

Variable Size             322962312 bytes

Database Buffers          687865856 bytes

Redo Buffers               55848960 bytes

Database mounted.

SQL> flashback database to timestamp to_timestamp('2018-09-24 13:43:57','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn soe/soe

Connected.

SQL> select count(*) from orders;

COUNT(*)

----------

1430069

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2260088 bytes

Variable Size             322962312 bytes

Database Buffers          687865856 bytes

Redo Buffers               55848960 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> conn soe/soe

Connected.

SQL> select count(*) from orders;

COUNT(*)

----------

1430069

SQL> alter table soe.customers enable row movement;

SQL> select count(*) from soe.customers;

COUNT(*)

----------

1000000

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1439085

SQL> select scn_to_timestamp(1439085) scn from dual;

SCN

---------------------------------------------------------------------------

25-MAY-20 05.31.02.000000000 PM

SQL> delete from soe.customers where nls_territory = 'Korea';

12920 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from soe.customers;

COUNT(*)

----------

987080

SQL> select count(*) from soe.customers as of timestamp to_timestamp('2020-05-25 17:30:00','yyyy-mm-dd hh24:mi:ss');

COUNT(*)

----------

1000000

SQL> flashback table soe.customers to timestamp to_timestamp('2020-05-25 17:30:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select count(*) from soe.customers;

COUNT(*)

----------

1000000

总结：闪回版本查询，闪回事务查询，闪回表与UNDO有关，与闪回日志没有任何关系

闪回删除与recyclebin有关，与闪回日志没有任何关系

闪回数据库与闪回日志有关