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.