通常我们对数据库进行了误操作时, 需要把数据库Rollback到之前的版本.
一个常用的方法就是使用日志来进行数据库恢复. 这个方法虽然强大有效, 但是花费时间等成本高.
例如当我们只是误提交了1个delete语句, 丢失了删除行的数据时, 如果我们执行数据库恢复的话, 就需要断开当前所有server processes, 甚至需要关闭数据库,相当于暂停了所有的生产活动.
而且使用日志恢复的话, 还往往需要相当长的时间(取决于备份文件的复制时间和日志的应用时间)
一, 什么是Flashback
除了上面所说的日志恢复机制.
Oracle提供了另1个快速数据库恢复机制, 就是Flashback.
1.1 Flashback的简单原理
Oracle会将数据库数据的每1个改动记录在日志文件中, 所以理论上依靠日志文件, 是能将数据库回滚到任何一个时间点的.
而Flashback的机制有点类似与回收站, 会把数据库改动前的镜像放到undo表空间中.
如果用户要rollback1个数据库对象, 只需要找到undo表空间中对应的Undo数据即可.
1.2 Flashback的优点
很明显, Flashback并不依赖于日志文件, 只需Undo表空间中undo数据即可发挥作用.
所以Flashback可以满足用户逻辑错误的快速恢复.
所以优点如下:
1. 快速
2. 在线恢复, 无需关闭数据库
3. 操作简单.便捷.
1.3 Flashback的缺点
Flashback缺点同样明显.
1. 只适用于用户逻辑错误, 所谓逻辑错误就是用户对数据的唔操作, 例如误删除一些数据行等等.
而对于数据文件的损坏则无能为力(只能通过日志恢复).2. undo表空间的容量有限, 旧的undo数据有可能会被新的数据覆盖, 所以Flashback一般只适用于短时间内的恢复, 对于一段相当时间前的误操作, 很可能因为undo数据被覆盖而恢复失败.
1.4 启用Flashback功能
为了正常使用Flashback功能, 通常我们要打开补充日志(Supplemental logging)功能, oracle 11g数据库默认情况下补充日志是关闭的.
补充日志也分成几个级别,最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique),支持外键(foreign key)。包括LONG,LOB,LONG RAW及集合等字段类型均无法利用补全日志.
例如:
使用如下命令来打开最小补全日志.
- alter database add supplemental log data;
使用如下命令来查看补全日志是否被打开.
- SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;
- SUPPLEME SUP SUP SUP SUP
- -------- --- --- --- ---
- YES NO NO NO NO
使用Flashback 需要当前用户具有select any transaction 权限.
可以用dba账号执行:
- grant select any transaction to xxx;
来获得这个权限.
1.5 关于flashback 的undo设置, Automatic Undo Management
- SQL> show parameter undo;
- NAME TYPE VALUE
- ------------------------------------ ----------- -----------------------------
- undo_management string AUTO
- undo_retention integer 900
- undo_tablespace string UNDOTBS1
1.5.1 Parameter UNDO_MANAGEMENT
1.5.2 Parameter UNDO_TABLESPACE
1.5.3 Parameter UNDO_RETENTION
只有1中情况例外, 当为undo tablespace 启用retention guarantee.
oracle 可以保证undo 数据在undo_retention指定的时间内一定存在(不能被其他undo数据覆盖).
启用guarantee:
- Alter tablespace undotbs1 retention guarantee;
禁用guarantee:
- Alter tablespace undotbs1 retention noguarantee;
启用这个特性能保证undo数据在undo tablespace 内的存在时间, 但是也有代价的.
假如表空间已满, 而且不允许旧的undo数据被新数据覆盖. 为了保证多版本的读一致性(详见本文第五节), 新的事务的
操作就会受影响了.
所以还是那几句话: the size of undo tablespace is very importance.
二, Flashback的级别和成员
2.1 Flashback的级别
Flashback可以分为三个级别:
1.Database Level
数据库级别的flashback允许将数据库恢复到某个时间点, 当误删除1个user或误truncate 1张表是适用数据库级别的flashback.
2.Table level
表级flashback可以将1个table回滚到某个时间点或者某个SCN号, 也可以闪回通过Drop命令删除的表.
3.Transaction level
事务级闪回会记录用户事务的每个DML操作, 并给出相应rollback的DML指令. 比如insert操作的rollback指令就是delete.
一般用于rollback 用户已经commit的误操作事务.
而根据误操作对于数据的影响.
用户可以选择执行flashback操作或者flashback查询.(flashback query)
所谓falshback查询就是查询数据被DML操作的历史记录(一般就是commit的记录), 然后在此基础上确定是否进行flashback操作.
2.2 Flashback的成员
Flashback可以分为如下成员:
1.Flashback Database
2.Flashback Drop
3.Flashback Query
-- Flashback Query
-- Flashback Version Query
-- Flashback Transaction Query
4.Flashback Table
5.Flashback Data Archive
三, Flashback Version Query
首先我们介绍的第一个成员叫flashback 版本查询.
所谓Version是指数据库中每次因为事务commit 而产生的数据行变化情况, 每一次变化就是1个版本.
这里需要强调的是这里变化是因为事务commit 产生的变化, 未commit的事务引起的变化不会被Flashback Version query 检索出来.
Flash Version Query 查询使用的undo 表空间的Undo 数据, 一旦undo数据因为undo segment的空间压力被清除, 则产生无法flashback的情况.
通过versions between 关键字可以查询制定时间(timestamp) or 版本(scn号)区间内的的不同修改版本.
基于 SCN 的版本查询
SELECT <columns>
FROM <schema_name.table_name>
VERSIONS BETWEEN SCN <minimum_scn>AND <maximum_scn>
[WHERE <column_filter>]
[GROUP BY <non-aggregated_columns>]
[HAVING <group filter>
[ORDER BY <position_numbers_or_column_names>]
基于 TIMESTAMP 的版本查询
SELECT <columns>
FROM <schema_name.table_name>
VERSIONSBETWEEN timestamp to_timestamp('start_timestamp')and to_timestamp('end_timestamp')
[WHERE <column_filter>]
[GROUP BY <non-aggregated_columns>]
[HAVING <group filter>
[ORDER BY <position_numbers_or_column_names>]
返回的视图提供多个伪列. 包括:
VERSIONS_STARTSCN VERSIONS_STARTTIME
记录操作时(也就是产生这条记录)的scn或时间, 如果为空, 表示该行记录是查询范围外创建的.
VERSIONS_ENDSCN VERSIONS_ENDTIME
表示该记录失效时的scn或时间.
这里什么是失效? 所谓失效就是对应的数据行被修改或者删除.
例如事务1中在A时间点修改了数据行x. 那么数据行x在事务1中的starttime 是A, 但是endtime是空的, 因为事务1的修改一直维持.
直到事务2在B时间点再次修改数据行x, 那么数据行x在事务1中的endtime 就是B了, 因为事务1的修改已经失效.
也就是说, 如果这两列的数据是空, 代表在改断时间内无操作(update or delete)
VERSIONS_OPERATION
记录操作的类型, I 表示Insert, D表示Delete, U表示Update. 如果对索引键的update操作, flashback version query可能会表示为Delete和Insert两个动作.
VERSIONS_XID
表示该操作的事务ID(key)
例子:
- SQL> create table Test3(id numeric, name varchar2(10));
- Table created.
- SQL> insert into test3 select 1,'Jack' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> insert into test3 select 2,'Bill' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> insert into test3 select 3,'Gordon' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> update test3 set name = 'Billing' where id = 2;
- 1 row updated.
- SQL> commit;
- Commit complete.
- SQL> delete from test3 where id = 3;
- 1 row deleted.
- SQL> commit;
- Commit complete.
上面例子中我新建1个简单的table Test3, 然后插入了3个数据行, 更新了1条, 删除了1条, 注意的是每条语句后都commit了一次 .
接下来可以利用flashback versions query 来查询这张表被修改的版本信息.
- select id, name, versions_xid, versions_startscn, versions_endscn,
- to_char(versions_starttime,'YY/MM/DD HH24:MI:SS') as startime,
- to_char(versions_endtime,'YY/MM/DD HH24:MI:SS') as endtime,
- versions_operation
- from Test3 versions between scn minvalue and maxvalue where id > 0;
输出:
- ID NAME VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN STARTIME ENDTIME VERSIONS_OPERATION
- -- ---------- ---------------- ----------------- --------------- ----------------- ----------------- ------------------
- 3 Gordon 05001400BC090000 3028255 14/05/22 22:15:47 D
- 2 Billing 08001D002B090000 3028245 14/05/22 22:15:23 U
- 3 Gordon 020010004A090000 3028239 3028255 14/05/22 22:15:11 14/05/22 22:15:47 I
- 2 Bill 07000900D5060000 3028234 3028245 14/05/22 22:14:56 14/05/22 22:15:23 I
- 1 Jack 03000B0068090000 3028229 14/05/22 22:14:48 I
可以见到:
1. 在倒数第一行, 我们插入了一条数据1,Jack, 它的versions_scn为空, 因为自从insert后一直没有对这条数据行操作.
2. 倒数第2行, 具有version_scn数据, 因为它被倒数第4行的事务更新了(Bill - > Billing).
3. 同样道理, 倒数第3行的Gordon被删除.
四, Flashback Transaction Query
上面的flash versions query可以查出事务的操作时间, xid等关键信息.
而通过Flash Transaction Query则可以利用xid来获得回滚的sql statement. 用户可以根据需要利用这些undo sql来回滚数据.
所以 Flash Transaction Query 实际上是 Flash versions query 的扩展. 通常会配合使用.
例子:
例用上面的表, 新插入两条重复的数据.
- SQL> insert into test3 select 4, 'Paula' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> insert into test3 select 4, 'Paula' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
通过Flash versions query 可以查询到xid:
- select id, name, versions_xid
- from Test3 versions between scn minvalue and maxvalue where id > 3;
- ID NAME VERSIONS_XID
- ---------- ---------- ----------------
- 4 Paula 01001800D8060000
- 4 Paula 030013006A090000
而通过 Flash transaction query 则可以获得回滚sql.
- select operation, undo_sql from flashback_transaction_query
- where xid in (select versions_xid from Test3 versions between scn minvalue and maxvalue where id > 3)
输出:
- OPERATION UNDO_SQL
- -------------------------------- ------------------------------------------------------------------------------------------------
- INSERT delete from "BILL"."TEST3" where ROWID = 'AAASf4AAFAAACkXAAE';
- BEGIN
- INSERT delete from "BILL"."TEST3" where ROWID = 'AAASf4AAFAAACkXAAD';
- BEGIN
可以见到, insert 语句的 Undo sql是delete 语句, 而且是用Rowid来作为condition的 所以, 即使我插入两条完全相同的数据行, 也可以正确地回滚.
五, Flashback Query
接下来是Flash Query.
上面介绍的Flash versions query 和 Flash transaction query 都是查看每个commited的事务具体信息.
而Flash Query是查看某张table 在某个时刻的数据镜像, 依赖于undo表空间的undo数据.
一旦我们发现某个时间的数据满足我们的需求之后, 我们就可以例用该镜像来回滚整张table.
Flashback Query 利用多版本读一致性从undo tablesapce 读取操作前的记录数据. 可以用于进来数据对比或覆盖回滚.
所谓多版本读一致性就是:
oracle 采用了一种设计, 同过undo数据来保证写操作不影响读操作. 简单地讲, A事务正在写数据时, 回将数据的前映像写入undo表空间, 如果B事务读取同样的数据, 则会读到undo表空间的正确数据, 而不需要等A事务commit or rollbak. 这样保证了B事务不会读到A事务未就提交的数据.
Flashback Query 有多种方式构建查询recordset, 其选择范围可以基于时间(As of timpstamp) or 基于scn(As of scn).
语法:
使用 as of scn
SELECT <column_name_list>
FROM <table_name>
AS OF <SCN>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
使用 as of timestamp
SELECT <column_name_list>
FROM <table_name>
AS OF <TIMESTAMP>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
5.1 As of timestamp 例子:
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-24 22:44:21
- SQL> select * from test3;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Billing
- 4 Paula
- 4 Paula
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-24 22:46:16
- SQL> update test3 set name = 'Jason';
- 4 rows updated.
- SQL> commit;
- Commit complete.
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-24 22:48:30
- SQL> delete from test3;
- 4 rows deleted.
- SQL> commit;
- Commit complete.
解析:
1.在 22:44 时, Test3 表里有4行数据.
2.在 22:46 时, 把Test3 表全部更新成name = Jason
3. 在 22:48 时, 删除所有记录(模拟误删除).
假如当前时间是22:50 , 操作者想查看6分钟前和3分钟前的表映像:
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-24 22:50:54
- SQL> select * from test3 as of timestamp sysdate - 6/1440;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Billing
- 4 Paula
- 4 Paula
- SQL> select * from test3 as of timestamp sysdate - 3/1440;
- ID NAME
- ---------- ----------
- 1 Jason
- 2 Jason
- 4 Jason
- 4 Jason
假如操作者想把表恢复成6分钟前的版本
则配合insert操作就ok了, 注意下面timestamp就制定了具体值, 大概22:44 .
- SQL> insert into test3 select * from test3 as of timestamp to_timestamp('2014-05-24 22:44:05','YYYY-MM-DD hh24:mi:ss');
- 4 rows created.
- SQL> commit;
- Commit complete.
- SQL> select * from test3;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Billing
- 4 Paula
- 4 Paula
上面例子, 表明as of timestamp的确非常易用, 但是大部分情况吓, 我们建议使用as of scn来执行flashback query.
因为需要对多个有主外键约束的表进行恢复时, 如果使用as of timestamp 可能因为时间点不统一而导致插入失败.
5.2 As of scn 例子:
上面例子我们利用- select sysdate from dual;
来获得数据库当前时间.
相应地, 我们可以利用
- select current_scn from v$database;
来获得数据库的当前scn, 注意必须具有select any dictionary 的权限
下面是例子:
首先获得当前数据库scn
然后删除test3 所有数据行:
- SQL> select current_scn from v$database;
- CURRENT_SCN
- -----------
- 3088322
- SQL> delete from test3;
- 4 rows deleted.
- SQL> commit;
- Commit complete.
然后利用as of scn flashback query来获得具体scn时的test3映像:
- SQL> select * from test3 as of scn 3088322;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Billing
- 4 Paula
- 4 Paula
配合insert into语句恢复表数据:
- SQL> select * from test3 as of scn 3088322;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Billing
- 4 Paula
- 4 Paula
- SQL> insert into test3 select * from test3 as of scn 3088322;
- 4 rows created.
- SQL> commit;
Actually, 无论用户使用as of timestamp or scn, oracle在底层都是使用scn. 也就是oracle具有1个把timestamp转换成scn的机制.
oracle里有一张表来表示timestamp 和 scn 的对应关系. 这张表就是sys.smon_scn_time.
oracle每隔5分钟, 系统产生一次系统时间标记与scn的匹配记录并存入sys.smon_scn_time.
值得注意的时, 也就是在这5分钟所有的时间都是匹配同1个scn. 也就是说使用as of timestamp 5分钟内的不同timpstamp实际上都是指向同1个scn..
可以用如下语句来查看scn 和 timestamp之间的对应关系:
- select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time;
5.3 伪列 ORA_ROWCN
- SQL> select ora_rowscn, id , name from test3;
- ORA_ROWSCN ID NAME
- ---------- ---------- ----------
- 3088585 1 Jack
- 3088585 2 Billing
- 3088585 4 Paula
- 3088585 4 Paula
- SQL> select current_scn from v$database;
- CURRENT_SCN
- -----------
- 3101850
- SQL> update test3 set name = 'Calvin' where id = 2;
- 1 row updated.
- SQL> select ora_rowscn, id , name from test3;
- ORA_ROWSCN ID NAME
- ---------- ---------- ----------
- 3088585 1 Jack
- 3088585 2 Calvin
- 3088585 4 Paula
- 3088585 4 Paula
- SQL> commit;
- Commit complete.
- SQL> select ora_rowscn, id , name from test3;
- ORA_ROWSCN ID NAME
- ---------- ---------- ----------
- 3101948 1 Jack
- 3101948 2 Calvin
- 3101948 4 Paula
- 3101948 4 Paula
上面的例子, 我只更新了1个数据行, 但是所有行的ora_rowscn都被更新了?
实际上, ora_rowscn默认是数据块(block) 级别的, 也就是说在同1个block中所有数据行都是同1个ora_rowscn. block中任意一行被修改, 该block中所有数据行的ora_rowscn 都会被刷新.
可以在建表时使用keyword rowdependencies, 可以改变ora_rowscn 的默认level, 令每一数据行都有独立的ora_rowscn.
六, Flashback Table
Flashback Table 也是利用undo 表空间的旧数据. 假如所需的undo数据由于保留的时间超过了初始化参数undo_retention所指定的值. 从而被其他事务覆盖掉的话, 就有可能恢复失败.
Flashback table 操作会修改表里的数据, 从而有可能引起data rows的行移动, 比如某一行datarow当前在A block中, 而在表闪回到以前的某个时间点上市, 在那个时间点那个 datarow 在B block中, 那么在执行Flashback table 前必须启用数据行的移动特性.
- Alter table <table_name> enable row movement;
Oracle 11g Flashback table 的特性:
1、在线操作;
2、恢复到指定时间点或 SCN 或 储存点(restore point)的任何数据;
3、自动恢复相关属性、如索引、触发器等(但是删除的索引, trigger不能通过flashback table来恢复)
4、满足分布式的一致性;
5、满足数据一致性,所有相关对象将自动一致;
6、闪回表技术是基于回滚数据(undo data)来实现的,因此,要想闪回表到过去的某
个时间上,必须确保与回滚表空间有关的参数设置合理。
语法:
Flashback table <table_name> to SCN <scn_number> [<ENABLE|DISABLE> TRIGGERS]
Flashback table <table_name> to Timestamp <scn_number> [<ENABLE|DISABLE> TRIGGERS]
注意的是, Flashback Table 可以向前Flashback, 一旦执行向前Flashback 后也可以执行向后flashback. 而 Sys schema下的table不能执行Flashback table操作.
6.1 Flashback table 的一些特性和注意事项
3. Flashback table 操作会被写入alert 日志文件.
4. Flashback table 操作会产生undo 和 redo 数据.
5. flashback query 对 v$tables,x$tables 等动态性能视图无效,不过对于dba_*,all_*,user_*等数据字典是有效的
6. 执行delete(不是drop)的操作的表也可以被flashback table 恢复, 但是被truncate 操作的表是不能被flashback table恢复的. 因为它使用undo数据.
基于undo的表恢复, flashback table 实际上做的也是dml操作(会在被操作的表上加上dml锁), 因此还需注意triggers的影响.
此时可以在后面附加 ENABLE | DISABLE TRIGGERS 字句来handle.
6.2 Flashback table 的例子:
6.2.1 基于TIMESTAMP 的FlashBack
1. 首先创建两张相同内容的表t1 和 t2- SQL> drop table t2;
- Table dropped.
- SQL> create table t1 as select * from user_objects;
- Table created.
- SQL> create table t2 as select * from t1;
- Table created.
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-25 12:10:50
2. 为表t1 创建两个indexes
- SQL> create index idx1_t1 on t1(object_name);
- Index created.
- SQL> create index idx2_t1 on t1(object_id);
- Index created.
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-25 12:14:30
3.删除1个index, 删除其中t1所有数据行, truncate t2.
- SQL> drop index idx1_t1;
- Index dropped.
- SQL> delete from t1;
- 11 rows deleted.
- SQL> commit;
- Commit complete.
- SQL> truncate table t2;
- Table truncated.
- SQL> select sysdate from dual;
- SYSDATE
- -------------------
- 2014-05-25 12:16:52
4. 查看row movement状态并启用 t1 和 t2的 row movement 属性.
- SQL> select table_name, row_movement from user_tables where table_name in ('T1','T2');
- TABLE_NAME ROW_MOVE
- ------------------------------ --------
- T1 DISABLED
- T2 DISABLED
- SQL> alter table t1 enable row movement;
- Table altered.
- SQL> alter table t2 enable row movement;
- Table altered.
- SQL> select table_name, row_movement from user_tables where table_name in ('T1','T2');
- TABLE_NAME ROW_MOVE
- ------------------------------ --------
- T1 ENABLED
- T2 ENABLED
5. 对t1 执行flashback操作, 可以看到所有被删除的数据行都被恢复了, 但是drop掉的索引无法恢复.
- SQL> flashback table t1 to timestamp to_timestamp('2014-05-25 12:13:00','yyyy-mm-dd hh24:mi:ss');
- Flashback complete.
- SQL> select count(1) from t1;
- COUNT(1)
- ----------
- 11
- SQL> select index_name, table_name from user_indexes;
- INDEX_NAME TABLE_NAME
- ------------------------------ ------------------------------
- IDX2_T1 T1
6. 尝试flashback table for t2 失败, 因为被truncate 的表无法通过flashback table 恢复.
- SQL> flashback table t2 to timestamp to_timestamp('2014-05-25 12:14:00','yyyy-mm-dd hh24:mi:ss');
- flashback table t2 to timestamp to_timestamp('2014-05-25 12:14:00','yyyy-mm-dd hh24:mi:ss')
- *
- ERROR at line 1:
- ORA-01466: unable to read data - table definition has changed
- SQL> select count(1) from t2;
- COUNT(1)
- ----------
- 0
6.2.2 基于restore point 的flashback table
- SQL> select * from test3;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Calvin
- 4 Paula
- 4 Paula
- SQL> create restore point zero;
- Restore point created.
- SQL> insert into test3 select 5,'Alice' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> create restore point one;
- Restore point created.
- SQL> insert into test3 select 6,'Hebe' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> create restore point two;
- Restore point created.
- SQL> insert into test3 select 7, 'Ella' from dual;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from test3;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Calvin
- 4 Paula
- 4 Paula
- 7 Ella
- 5 Alice
- 6 Hebe
- 7 rows selected.
2. 对test3 执行恢复到restore point two, 在其后插入的数据行7, Ella 没有恢复.
- SQL> alter table test3 enable row movement;
- Table altered.
- SQL> flashback table test3 to restore point two;
- Flashback complete.
- SQL> select * from test3;
- ID NAME
- ---------- ----------
- 1 Jack
- 2 Calvin
- 4 Paula
- 4 Paula
- 5 Alice
- 6 Hebe
- 6 rows selected.
- SQL> drop restore point one;
- Restore point dropped.
- SQL> drop restore point zero;
- Restore point dropped.
- SQL> drop restore point two;
- Restore point dropped.
6.2.3 对于具有fk约束关系的两个表一同恢复.
- SQL> create table t3 enable row movement as select * from scott.emp;
- Table created.
- SQL> create table t4 enable row movement as select * from scott.dept;
- Table created.
- SQL> select current_scn from v$database;
- CURRENT_SCN
- -----------
- 3108694
2. 为这两张表添加pk 及 fk约束.
- SQL> alter table t3 add constraint t3_empno_pk primary key(empno);
- Table altered.
- SQL> alter table t4 add constraint t4_deptno_pk primary key(deptno);
- Table altered.
- SQL> alter table t3 add constraint t3_t4_deptno_fk foreign key(deptno) references t4(deptno);
- Table altered.
3. 对应先后删除t3 和 t4 部门号码是20的数据.
- SQL> select empno, deptno from t3 where deptno = 10;
- EMPNO DEPTNO
- ---------- ----------
- 7782 10
- 7839 10
- 7934 10
- SQL> select current_scn from v$database;
- CURRENT_SCN
- -----------
- 3109085
- SQL> delete from t3 where deptno = 10;
- 3 rows deleted.
- SQL> delete from t4 where deptno = 10;
- 1 row deleted.
- SQL> commit;
- Commit complete.
4. 尝试单独恢复t3 到数据删除前, get到error, 因为约束关系(当前t4不存在部门号码=20)的数据.
- SQL> flashback table t3 to scn 3109085;
- flashback table t3 to scn 3109085
- *
- ERROR at line 1:
- ORA-02091: transaction rolled back
- ORA-02291: integrity constraint (BILL.T3_T4_DEPTNO_FK) violated - parent key
- not found
5. t3, t4 一起恢复就ok了!
- SQL> flashback table t3,t4 to scn 3109085;
- Flashback complete.
6.检查数据, 部门号码为20的数据已被恢复:
- SQL> select t3.empno, t4.deptno from t3, t4 where t4.deptno = t3.deptno and t4.deptno = 10;
- EMPNO DEPTNO
- ---------- ----------
- 7782 10
- 7839 10
- 7934 10
七, Flashback Drop
通过上面的介绍以及例子, 相信大家都知道flashback table 能恢复被删除/or 被修改的数据行.但是不能恢复被drop掉的index, 更别说恢复被drop掉的表了.
如果想恢复被drop掉的对象. oracle提供另1个flashback成员, 就是Flashback drop.
语法:
跟上面的Flashback table 很类似, 只不过to后面不再是scn or timestamp, 而是 before drop
Flashback table <table_name> to before drop;
在没有flashback技术之前, 如果想恢复用户唔删除的表和对象. 只能使用传统的数据恢复方式从备份中恢复.
有了flashback 之后, 当用户使用drop table删除一张表时, 该表并不会在数据库中立即删除, 而是保持原表的位置. 但是将删除的表重新命名, 并将删除的表的信息存储在回收站中, 回收占记录了被删除的表的新名字和旧名字. 显然此时被删除的表所占的空间没有被立即释放, 变成数据库可以使用的潜在空间. 记录在回收站的信息会保留一段时间, 知道回收站空间不足或使用purge指令删除回收站中的记录.
回收站是1个逻辑结构, 不具有物理数据结构, 只要删除的表信息记录在回收站, 就可以通过flashback 来恢复被drop的表. 每个表空间都有1个叫做回收站(recyclebin)的逻辑区域.
也就是讲: 跟上面flashback成员不同, drop掉的表or其他对象, 并没有undo数据被放入到Undo tablespace内, 而是存在于对象原本的表空间的回收站内.
Oracle回收站将用户所做的drop语句操作记录在一个系统表内. 即将被删除的对象写到1个数据字典中, 当不在需要被删除的对象时, 可以使用purge命令队回收站空间进行清除.
但是此时被删除的表原被所占物理数据块会被标记成可以用, 也就是说可以被其他新数据所覆盖. 而在这种事情发生之前, 用户就可以利用flashback 从回收站中恢复被误删除的表.
当1个被drop掉的表被flashback 恢复时, 这张表的约束, 包括pk, ui, not null都会被恢复. 但是外键约束不会被恢复, 需要手动添回.
我们可以通过如下命令来查看系统中回收站是否被启用:
- SQL> show parameter recyclebin
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- recyclebin string on
- SQL>
也可以禁用/启用 回收站, 当然一旦禁用, flashback drop将无法生效
- alter system/session set recyclebin=on|off;
使用如下命令清空回收站(当前用户)
- SQL> purge user_recyclebin;
- Recyclebin purged.
7.1 Flashback drop 的例子:
- SQL> select constraint_name, constraint_type, table_name from user_constraints where table_name = 'T3';
- CONSTRAINT_NAME C TABLE_NAME
- ------------------------------ - ------------------------------
- T3_EMPNO_PK P T3
- T3_T4_DEPTNO_FK R T3
- SQL> select count(1) from t3;
- COUNT(1)
- ----------
- 14
可见此时T3 具有1个主键和外键约束.
1.drop掉表T3
- SQL> drop table t3;
- Table dropped.
2.查看回收站, 见到有一条关于T3的记录:
- SQL> show recyclebin;
- ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
- ---------------- ------------------------------ ------------ -------------------
- T3 BIN$+jNxn7Lfw3XgQAB/AQApBg==$0 TABLE 2014-05-25:15:04:55
记录中还包括drop的时间哦.
3. 使用flashback drop 恢复表T3
- SQL> flashback table t3 to before drop;
- Flashback complete.
4.这时检查T3, 发现所有数据行被恢复, 主键也被恢复(被renamed哦), 但是外键就被丢失了.
- SQL> select count(1) from t3;
- COUNT(1)
- ----------
- 14
- SQL> select constraint_name, constraint_type, table_name from user_constraints where table_name = 'T3';
- CONSTRAINT_NAME C TABLE_NAME
- ------------------------------ - ------------------------------
- BIN$+jNxn7Ldw3XgQAB/AQApBg==$0 P T3
1562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
