Oracle 闪回特性简单测试

一、闪回版本查询

在不同时间点对字段更新，通过闪回版本查询具体时间点数据信息
1、在T1时间点10:10:20更新某个字段，并提交

09:58:23 SQL> update d1 set guid='dd' where t1=4;

1 row updated.

SQL> commit；

Commit complete.

2、通过闪回版本查询，查看已经提交的行记录

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,VERSIONS_OPERATION OPERATION，guid from d1
versions between timestamp to_timestamp('2018-08-06 10:30:00','yyyy-mm-dd hh24:mi:ss') and sysdate where t1=4;

STARTTIME                VERSIONS_XID      OPE GUID
2018-08-06 10:10:19     090006001D040000   U   dd

2018-08-06 09:58:16     0A001B00C8040000   U   d

二、表Drop闪回恢复

模拟在T1时间点误删除表，通过闪回从回收站恢复表到具体时间点
1、删除表

SQL> drop table d1;
Table dropped.

2、查看回收站

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
----------- ---------------------- ----------
D1       BIN$crsHDKHECErgUwEAAH9bjQ==$0 TABLE   2018-08-06:09:17:18

3、查看回收站的信息

SQL> select object_name,original_name,type from recyclebin;

OBJECT_NAME               ORIGINAL_NAME  TYPE
---------------   --------------------------------
BIN$crsHDKHECErgUwEAAH9bjQ==$0   D1      TABLE

4、只需闪回表删除恢复

SQL> flashback table d1 to before drop;
Flashback complete.

5、检查索引是否也被删除

SQL> select index_name from user_indexes where table_name='D1';

INDEX_NAME
---------------------------------------------------
BIN$crsHDKHFCErgUwEAAH9bjQ==$0

6、如果索引也被删除了，只需重命名

SQL> alter index "BIN$crsHDKHFCErgUwEAAH9bjQ==$0" rename to idx_guid;

Index altered.

SQL> select index_name from user_indexes where table_name='D1';

INDEX_NAME
----------------------------------------------------------
IDX_GUID

附测试truncate表是否可以闪回

14:25:08 SQL> truncate table d1;

14:25:53 SQL> select * from d1 as of timestamp to_timestamp('2018-08-06 14:24:40','yyyy-mm-dd hh24:mi:ss');
select * from d1 as of timestamp to_timestamp('2018-08-06 14:24:40','yyyy-mm-dd hh24:mi:ss')

ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

结论：truncate表无法通过闪回表恢复：

三、通过闪回查询插入恢复删除表

模拟在T1时间点误删除记录，通过闪回查询的具体时间点，插入恢复表数据
1、查询测试表tb1有5条记录

SQL> select * from tb1;

        T1 GUID
---------- ------------------------------------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e

SQL> set time on

2、删除其中1条记录

17:19:47 SQL> delete from tb1 where t1=5;

1 row deleted.

17:20:01 SQL> commit;

Commit complete.

SQL> select * from tb1;

        T1 GUID
---------- ------------------------------------------------------------
         1 a
         2 b
         3 c
         4 d
已删除完毕

3、创建备份表用来恢复数据

17:20:30 SQL> create table tb1_bak as select * from tb1 where 1=0;

Table created.

4、通过闪回查询发生delete操作的时间点

17:23:58 SQL> select * from tb1 as of timestamp to_timestamp('2018-07-26 17:20:00','yyyy-mm-dd hh24:mi:ss');

        T1 GUID
---------- ------------------------------------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e

5、向备份表插入被delete的数据

17:37:36 SQL> insert into tb1_bak select * from tb1 as of timestamp to_timestamp('2018-07-26 17:20:00','yyyy-mm-dd hh24:mi:ss') where tb1.t1=5;

1 row created.

6、验证备份表数据

17:37:58 SQL> select * from tb1_bak;

        T1 GUID
---------- ------------------------------------------------------------
         5 e

17:38:05 SQL> commit;

Commit complete.

四、闪回表到时间点

模拟T1时间点误更新表，不加where条件.通过闪回表将表直接恢复到具体时间点。
1、查询d1表原始数据

SQL> select * from d1;

        T1 GUID
------ -----------------------------------
         1 aaa
         2 bbbb
         3 c
         4 ddd

2、在T1时间点误操作更新数据，并提交

11:05:49 SQL> update d1 set guid='xxx';

4 rows updated.

11:14:39 SQL> commit;

Commit complete.

11:14:41 SQL> T1时间点已完成提交

3、通过闪回查询，查找T1时间点之前的数据

11:17:04 SQL> select * from d1 as of timestamp to_timestamp('2018-08-06 11:14:40','yyyy-mm-dd hh24:mi:ss');

        T1 GUID
---------- ------------------------------------------------------------
         2 bbbb
         3 c
         4 ddd
         1 aaa

 也可以使用sysdate查询几分钟前的数据
 SQL> select * from d2 as of timestamp (sysdate-5/1440);

        T1 GUID
---------- ------------------------------------------------------------
         2 aaaa
         1 bbb  

4、开启表行移动

 11:18:52 SQL> alter table d1 enable row movement;

Table altered.

5、闪回恢复表到T1时间点之前

11:19:14 SQL> flashback table d1 to timestamp to_timestamp('2018-08-06 11:14:40','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

6、再次查询数据

11:19:19 SQL> select * from d1;

        T1 GUID
---------- ------------------------------------------------------------
         1 aaa
         2 bbbb
         3 c
         4 ddd

#五、闪回数据库
1、检查归档是否打开

17:37:50 SQL> select log_mode from v$database;

LOG_MODE
------------------------------------
ARCHIVELOG

2、设置闪回区大小以及路径

15:42:42 SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;

System altered.

15:42:55 SQL> alter system set db_recovery_file_dest='/u01/fra';

3、打开数据库闪回

15:43:39 SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

15:43:50 SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

4、查看闪回可以恢复的最早时间点

15:50:32 SQL> select oldest_flashback_scn,to_char(oldest_flashback_time,'yyyy-mm-dd hh24:mi:ss') from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASHBACK_TIME,'YYYY-MM-DDHH24:MI:SS')
-------------------- ---------------------------------
2900632              2018-08-06 15:43:50

5、创建还原点

6:02:54 SQL> create restore point f1;

6、截断表

16:03:56 SQL> truncate table d2;

7、启动到mount并执行闪回数据库

16:04:05 SQL> shutdown immediate;
16:04:24 SQL> startup mount;
16:04:56 SQL> flashback database to restore point f1;
Flashback complete.

16:05:40 SQL> alter database open resetlogs;

8、查询数据

16:06:48 SQL> select * from d2;

        T1 GUID
     ------ -----
         2 aaaa
         1 bbb