Oracle10g Flashback 实践

Oracle10g Flashback 实践

一、概述

环境：RHEL4.5，oracle10.2.0.1

Flashback 是 ORACLE 自9i 就开始提供的一项特性，在9i 中利用oracle 查询多版本一致的特点，实现从回滚段中读取表一定时间内操作过的数据，可用来进行数据比对，或者修正意外提交造成的错误数据，该项特性也被称为Flashback Query。在 10g 中Flashback又得到了相当大的增强，利用Recycle Bin(回收站)和Flash Recovery Area(闪回区)的特性实现快速恢复删除表(Flashback Table)或做 数据库时间点恢复(Flashback Database)的功能。

要使用flashback 的特性，必须启用自动撤销 管理表空间，不仅是flashback query,也包括flashback table 和flashback database，而对于后两项还会有些其它的附加条件，比如flashback table 需要启用了recycle bin(回收站)，flashback database 还要求必须启用了flashback area(闪回区)。

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1

在一种情况下，undo 表空间能够确保undo 中的数据在undo_retention 指定时间过期前一定有效，就是为undo 表空间指定Retention Guarantee，指定之后，oracle 对于undo 表空间中未过期的undo 数据不会覆盖，例如：

SQL> Alter tablespace undotbs1 retention guarantee;

如果想禁止undo 表空间retention guarantee，如例：
SQL> Alter tablespace undotbs1 retention noguarantee;

二、Flashback Query(闪回查询)

Flashback Query 是利用多版本读一致性的特性从UNDO 表空间读取操作前的记录数据！

1、As of timestamp的示例

SQL> create table tab_test as select rownum id from dba_users;
SQL> delete from tab_test where id<5;
SQL> commit;

假设当前距离删除数据已经有3分钟左右的话：
SQL> select count(*) from tab_test as of timestamp sysdate-3/1440 where id<10;
SQL> insert into tab_test select * from tab_test as of timestamp sysdate-3/1440 where id<5;
SQL> commit;

提示：as of timestamp|scn 的语法是自9iR2 后才开始提供支持，如果是9iR1 版本，需要使用DBMS_FLASHBACK包来应用flashback query 的特性。

2、As of scn的示例

如果需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp 的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，通过scn 方式则能够确保记录的约束一致性。

获取当前scn 的方式非常多，比如：
SQL> select current_scn from v$database;
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  191460
SQL> delete tab_test where id>5;
SQL> commit;
SQL> select * from tab_test as of scn 191460;
SQL> insert into tab_test select * from tab_test as of scn 191460 where id not in (select id from tab_test);
SQL> commit;

事实上，Oracle 在内部都是使用scn，即使你指定的是as of timestamp，oracle 也会将其转换成scn，系统时间标记与scn 之间存在一张表，即SYS 下的SMON_SCN_TIME。
SQL> select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time_dp from sys.smon_scn_time;

3、Versions between版本查询

功能更加强大，通过versions between 能够查看指定时间段内undo 表空间中记录的不同版本（注意只包括被提交的记录）。

版本查询过程中提供了多个伪列如下：
VERSIONS_STARTSCN/VERSIONS_STARTTIME 记录操作时的scn 或时间，如果为空，表示该行记录是在查询范围外创建的。

VERSIONS_ENDSCN/VERSIONS_ENDTIME 记录失效时的scn 或时间，如果为空，说明记录当前时间在当前表内存在，或者已经被删除了，可以配合着VERSIONS_OPERATION 列来看，如果VERSIONS_OPERATION 列值为D，说明该列已被删除，如果该列为空，则说明记录在这段时间无操作。

VERSIONS_XID 该操作的事务ID

VERSIONS_OPERATION 对该行执行的操作：I 表示insert，D 表示delete，U 表示update。提示：对于索引键的update 操作，版本查询可能会将其识别成两个操作DELETE 和INSERT。

SQL> update tab_test set id=id+100 where id>5;
SQL> commit;
SQL> delete tab_test where id<5;
SQL> commit;
SQL> insert into tab_test values (888);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  191611
SQL> select id,VERSIONS_XID,versions_startscn,to_char(VERSIONS_STARTTIME,'yyyy-mm-dd hh24:mi:ss') VERSIONS_STARTTIME,versions_endscn,to_char(VERSIONS_ENDTIME,'yyyy-mm-dd hh24:mi:ss') VERSIONS_ENDTIME,versions_operation from tab_test versions between scn 191460 and 191611 order by 3;

4、Transaction query事务查询

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  192329
SQL> delete tab_test where id<3;
SQL> rollback;
SQL> delete tab_test where id=5;
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  192377
SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid in( select versions_xid from tab_test versions between scn 192329 and 192329);
注：由于事务ID 是保存在版本查询中，因此我们需要通过版本查询来关联出flashback 的事务信息，这也是前面操作前要通过dbms_flashback 包取scn 的原因。

5、DDL的影响

修改并提交过数据之后，对表做过DDL 操作，包括：drop/modify 列, move 表, drop 分区(如果有的话), truncate table/partition，这些操作会另undo 表空间中的撤销数据失效，对于执行过这些操作的表应用flashback query 会触发ORA-01466 错误。另外一些表结构修改语句虽然并不会影响到undo 表空间中的撤销记录，但有可能因表结构修改导致undo 中重做记录无法应用的情况，比如对于增加了约束，而flashback query 查询出的undo 记录已经不符合新建的约束条件，这个时候直接恢复显然不可能成功，你要么暂时disable 约束，要么通过适当逻辑，对要恢复的数据进行处理之后，再执行恢复。
另外，flashback query 对v$tables,x$tables 等动态性能视图无效，不过对于dba_*,all_*,user_*等数据字典是有效的。同时该特性也完全支持访问远端数据库，比如：
select * from tbl@dblink as of scn 360; 的形式。

三、Flashback Table(闪回表)

1、从RECYCLEBIN中恢复

要恢复recyclebin 中的表，注意语句如下：Flashback table [objName] to before drop，这个obj_name 即可以是表名，也可以是recyclebin 中的对象表(支持同时操作多个表，表名之间以逗号分隔即可，要记住单个flashback table 是在同一个事务中，因此这些表的恢复操作要么都成功要么都失败)，由于该项功能是恢复被删除表，因此官方对其还有另外一称谓：flashback drop。系统参数recyclebin默认是on，即启用了回收站功能，可以在session或system级别动态更改。每一个用户都会有一个自己的recycle bin。

SQL> conn /as sysdba
SQL> show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL> grant flashback any table to u_test;
SQL> conn u_test/iamwangnc
SQL> create table tab_test as select * from v$instance;
SQL> select count(*) from tab_test;
COUNT(*)
----------
         1
SQL> drop table tab_test;
SQL> select * from tab_test;
select * from tab_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select object_name,original_name,operation,type,createtime,droptime,dropscn from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      CREATETIME          DROPTIME               DROPSCN
------------------------------ -------------------------------- --------- ------------------------- ------------------- ------------------- ----------
BIN$XC9ucNX/thHgQAB/AQBpYw==$0 TAB_TEST                         DROP      TABLE                     2008-11-21:15:59:47 2008-11-21:16:05:21     246999
SQL> flashback table TAB_TEST to before drop;
SQL> select object_name,original_name from recyclebin;
no rows selected
SQL> select count(*) from tab_test;
COUNT(*)
----------
         1

Flashback table 语句同时提示了一个rename to [newTBname]的子句，如果要恢复的表在当前schema 中已经存在同名的表，建议你在恢复时通过rename to 子句为待恢复的表指定一个新的表名，不然数据库会报ORA-38312 错误，如：

SQL> flashback table tab_test to before drop rename to tab_test_bak;

当删除时指定purge 参数(等同于我们在 windows 中按着shift 删除)则无法闪回了，如：

SQL> drop table tab_test purge;

基于recycle bin 的表恢复，flashback drop 不能恢复参照完整性，这很容易理解，毕竟在该表删除之后，其被参照表是否有修改它已经无法控制了，因此如果该表有主外键约束的话，恢复之后，该约束是disable状态，需要dba 手工处理。

基于recycle bin 的表恢复，所操作的表必须是存在于本地管理表空间中。Flashback drop 不能恢复字典管理表空间中被删除的表，也不能恢复系统表。

基于recycle bin 的表恢复，被恢复的表的关联对象，比如其索引啦，约束的名称不会自动恢复成删除前的名称，而是系统自动生成的名称，如果你对表的索引约束有相关命名规范，那在恢复表之后，需要dba 手工将索引约束等改名。

当删除表时，信赖于该表的物化视图也会同时删除，但是由于物化视图并不会被放入recycle bin，因此当你执行flashback table to before drop 时，也不能恢复依赖其的物化视图，需要dba 手工介入重新创建。

相对于被删除的表而言，当数据文件空间不足时，oracle 会首先清理被删除表的索引，因此假如你执行flashback table to before drop 而发现缺少索引可能是正常的，说明你显然错过了最佳的恢复时机。

如何删除回收站里的对象：
A.Purge 指定表，例如：
SQL> select object_name,original_name from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$JhA057bpRxKICIe/vNahyQ==$0 TAB_TEST
SQL> purge table tab_test;

或
SQL> purge table "BIN$JhA057bpRxKICIe/vNahyQ==$0";
B.Purge 指定表空间，例如：
SQL> purge tablespace ts_test;
C.清空recyclebin，例如：
SQL> purge recyclebin;

除上述的三种之外，还有一种方式就是删除recycle bin 中对象所属用户或所在表空间。

2、从UNDO中恢复

某些时候，我们要处理的表并不是被意外删除，而是被反复修改过多次，希望能回复到之前的某个时间点，借助flashback query 是可以，但flashback query 只是查询出记录，如果想做恢复还需要写出相应的insert 或update，也许还需要相当多的where 条件做判断，因此，我们需要更高效更严谨更简便的方式：flashback table tbname to scn/timestamp。

基于undo 的表恢复，被恢复的表必须启用row movement，不然会报ORA-08189 错误。
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  247826
SQL> delete from tab_test;
SQL> commit;
SQL> flashback table tab_test to scn 247826;
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> ALTER TABLE tab_test ENABLE ROW MOVEMENT;
SQL> flashback table tab_test to scn 247826;
SQL>

基于undo 的表恢复，flashback table 实际上做的也是dml 操作(会在被操作的表上加dml 锁)，因此还需要注意triggers 对其的影响，默认情况下，flashback table to scn/timestamp 在执行时会自动disable 掉与其操作表相差的triggers，如果你希望在此期间trigger 能够继续发挥做用，可以在flashback table 后附加ENABLE TRIGGERS 子句。
基于undo 的表恢复，索引会自动维护，但统计信息并不会恢复到指定的时间点。

四、Flashback Database(闪回数据库)

如果说flashback query 可用来恢复指定时间点的记录，flashback table 可用来恢复被删除或指定时间点的表，而flashback database，则直接提供一个数据库级别的恢复，可将数据库恢复到指定的时间点，相对于前两者，其粒度是最大的。

1、必备条件

A. 数据库启用了flashback database。
B. 数据库必须打开了flash recovery area，如果数据库是 RAC，flash recovery area 必须位于共享存储中。
C. 数据库必须处于archivelog 模式。

2、操作示例

Flashback database 即可以在 rman 中执行，也可以在sql*plus 中执行，其命令格式都是一样的，在这个示例中，我们所有操作都是在sql*plus 中执行。

A. 检查是否启动了flash recovery area:
SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /orahome/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

B. 检查是否启用了归档
SQL> archive log list;

C. 检查是否启用了flashback database 和force_logging
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON       FOR
------------------ ---
NO                 NO
如果未启用则先启用：
SQL> alter system set db_flashback_retention_target=1440 scope=both;   (单位是分钟，1440表示一天)
SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database flashback on;
SQL> alter database force logging;
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON       FOR
------------------ ---
YES                YES
SQL> alter database open;

D. 查询当前的scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     248739

E. 模拟误删除操作
SQL> 做一些DML/DDL操作

F. 重新启动到mount 状态，然后执行恢复
SQL> conn /as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 248739;
提示：此处flashback database to timestamp 也是一样的，不过你需要估计好恢复到的时间点。

注意此处，在你执行完flashback database 命令之后，oracle 提供了两种方式让你修复数据库：
第一种直接 alter database open resetlogs 打开数据库，当然，指定scn 或者timestamp 时间点之后产生的数据统统丢失。
另一种方式是先执行 alter database open read only 命令以read-only 模式打开数据库，然后立刻通过逻辑导出的方式将误操作涉及表的数据导出，再执行recover database 命令以重新应用数据库产生的redo，将数据库修复到flashback database 操作前的状态，然后再通过逻辑导入的方式，将之前误操作的表重新导入，这样的话对现有数据的影响最小，不会有数据丢失。

G. 打开数据库
SQL> alter database open resetlogs;

H. 验证数据
SQL> 查询误操作的数据是否回到了从前

3、限制条件

A. Flashback database 是用来将数据库中的数年恢复至之前的某个时间点，而非介质恢复，你不可能使用flashback database 恢复之前被删除的某个数据文件。
B. 如果控制文件被重建，则在此之前所产生的所有flashback 日志统统失效，不能将数据库flashback到控制文件被重建之前。
C. 对于nologging 模式下插入的数据，flashback database 也恢复不了，因此建议数据库处于force logging模式。