flashback之flashback table

《三思笔记》--读书笔记

1，从recycle bin中恢复

（1）简单删除表恢复

SQL> drop table book_list;

Table dropped.

其实没有删除，查看recycle bin

SQL> select object_name,original_name from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$AhOOEum+C8/gU8g4qMAxQQ==$0 FLASH_TBL
BIN$AhT4Yh0LEoTgU8g4qMAP/A==$0 SYS_C0011460
BIN$AhT4Yh0MEoTgU8g4qMAP/A==$0 BOOK_LIST

可以看到，original_name 中有book_list表

下面恢复这个表

SQL> flashback table BOOK_LIST to before drop;

Flashback complete.

SQL> select * from BOOK_LIST;

    BOOKID BOOKNAME             CREATE_DATE
---------- -------------------- ------------
         1 sansi's note         31-AUG-14
         2 about sansi          31-AUG-14

显然恢复成功，再查看recyclebin

SQL> select object_name,original_name from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$AhOOEum+C8/gU8g4qMAxQQ==$0 FLASH_TBL

虽然此时表已经回来了，但是查看索引的时候，会发现，索引的名字还保留的是在recycle bin中的名字

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

INDEX_NAME
------------------------------
BIN$AhT4Yh0LEoTgU8g4qMAP/A==$0

因此，我们还需要手动执行alter index rename,将索引名称按照指定的数据库对象命名规范进行修改

SQL> alter index "BIN$AhT4Yh0LEoTgU8g4qMAP/A==$0" rename to SYS_C0011460;

Index altered.

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

INDEX_NAME
------------------------------
SYS_C0011460

（2）稍稍复杂一点的表恢复

如果要恢复的表与当前schema中已经存在同名的表，直接恢复会触发ora-38312错误，如下

SQL> flashback table FLASH_TBL to before drop;
flashback table FLASH_TBL to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

此时就需要rename to 了，如下

SQL> flashback table FLASH_TBL to before drop rename to FLASH_TBL01;

Flashback complete.

（3）从多次删除中恢复

如先删除表flash_tbl，在没有flashback drop的时候又建了一个表flash_tbl，之后又将这个flash_tbl删除，如下

SQL> drop table flash_tbl;

Table dropped.

SQL> alter table flash_tbl01 rename to flash_tbl;

Table altered.

SQL> drop table flash_tbl;

Table dropped.

SQL> create table flash_tbl(id number);

Table created.

SQL> drop table flash_tbl;

Table dropped.

查看recyclebin

SQL> select object_name,original_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$AhT4Yh0OEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:47:12
BIN$AhT4Yh0PEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:47:38
BIN$AhT4Yh0NEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:46:36

原始表名都是flash_tbl，这些对象在恢复的时候，会有顺序，测试如下

SQL> flashback table  FLASH_TBL to before drop;

Flashback complete.

SQL>  select object_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$AhT4Yh0OEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:47:12
BIN$AhT4Yh0NEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:46:36

显然是最先被删除的表最先被恢复

我们还可以指定恢复所需要的表，如下

SQL> select object_name,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME
------------------------------ -------------------------------- -------------------
BIN$AhT4Yh0OEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:47:12
BIN$AhT4Yh0QEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:51:55
BIN$AhT4Yh0NEoTgU8g4qMAP/A==$0 FLASH_TBL                        2014-09-02:20:46:36

SQL> flashback table "BIN$AhT4Yh0NEoTgU8g4qMAP/A==$0" to before drop;

Flashback complete.

这样就可以想恢复哪个恢复哪个了

2，从undo 表空间中恢复

实验如下，查看一下表，并记录scn

SQL> select * from flash_tbl;

        ID VL
---------- --
         8 G
         9 H
        10 I
        11 J
        12 K
        13 L
        14 M
        15 N
       116 O
       117 P
       118 Q

        ID VL
---------- --
       119 R
       120 S
       201 A1
       202 B1

15 rows selected.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1792983

下面进行一系列操作

SQL> update flash_tbl set id = id+100 where id >10;

12 rows updated.

SQL> insert into flash_tbl values (21,'Z');

1 row created.

SQL> delete flash_tbl where id =8;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from flash_tbl;

        ID VL
---------- --
         9 H
        10 I
       111 J
       112 K
       113 L
       114 M
       115 N
       216 O
       217 P
       218 Q
       219 R

        ID VL
---------- --
       220 S
       301 A1
       302 B1
        21 Z

15 rows selected.

若此时发现，操作有误，希望回退到没有做操作的时候，那么用scn进行查询

SQL> select * from flash_tbl as of scn 1792983;

        ID VL
---------- --
         8 G
         9 H
        10 I
        11 J
        12 K
        13 L
        14 M
        15 N
       116 O
       117 P
       118 Q

        ID VL
---------- --
       119 R
       120 S
       201 A1
       202 B1

15 rows selected.

可见，数据可以恢复到那个scn，下面进行恢复操作

SQL> flashback table flash_tbl to scn 1792983;
flashback table flash_tbl to scn 1792983
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

却发现报错，原因是，基于undo的表恢复，被恢复的表必须启用row movement，表的row movement属性用来控制是否允许修改列值所造成的记录移动，表的row movement属性为disable时，如果记录值有移动操作，则更新语句会触发ora-08189错误

要查看某表是否启用了row movement，可以到数据字典user_tables中查询，如下

SQL> select row_movement from user_tables where table_name = 'FLASH_TBL';

ROW_MOVE
--------
DISABLED

启用

SQL> ALTER TABLE FLASH_TBL ENABLE ROW MOVEMENT;

Table altered.

SQL> select row_movement from user_tables where table_name = 'FLASH_TBL';

ROW_MOVE
--------
ENABLED

然后再恢复表

SQL> flashback table flash_tbl to scn 1792983;

Flashback complete.

SQL> select * from flash_tbl;

        ID VL
---------- --
         8 G
         9 H
        10 I
        11 J
        12 K
        13 L
        14 M
        15 N
       116 O
       117 P
       118 Q

        ID VL
---------- --
       119 R
       120 S
       201 A1
       202 B1

15 rows selected.