flashback之flashback table oracle数据和表 恢复

flashback之flashback table
SQL> flashback table "BIN$AhT4Yh0NEoTgU8g4qMAP/A==$0" to before drop; 恢复删除的指定的 数据表和数据

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

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,original_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,original_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.