汉得融晶金融行业最佳实践Oracle 11g Recyclebin &

Flashback

作者: 扈建峰

创建日期: 2015-05-05

最后更新: 2015-05-05

控制码:

版本: 1.0

审批签字:

客户项目经理

融晶项目经理

文档控制

修改记录1

日期 作者 版本 修改参考

2015.05.05 扈建峰 1.0 初始版本

审阅

姓名 职位

分发

拷贝 姓名 地点

1234

File Ref: document.docx (v 1.0 )Company Confidential - For internal use only

文档控制 1iixiv52ii

目录

文档控制........................................................................................................ II

汉得融晶金融行业最佳实践-ORACLE 11G回收站.................................................4

综述.............................................................................................................................................4

01 ORACLE回收站（RECYCLEBIN）....................................................................5

回收站启动和关闭.....................................................................................................................5

查看回收站内容.........................................................................................................................6

还原回收站内容.........................................................................................................................6

清理回收站.................................................................................................................................6

02 ORACLE闪回（FLASHBACK）.......................................................................7

Flashback综述............................................................................................................................7

Flashback Query.........................................................................................................................7

Flashback Version Query............................................................................................................9

Flashback Version Query..........................................................................................................10

Flashback Table.........................................................................................................................10

Flashback Drop.........................................................................................................................11

Flashback Database...................................................................................................................13

File Ref: document.docx (v 1.0 )Company Confidential - For internal use only

文档控制 1iiixiv52iii

汉得融晶金融行业最佳实践-Oracle 11g回收站

综述

起因

在项目开发中，由于环境较多，DEV&TEST&UAT&PROD，我相信肯定有像我一样的技术，曾经犯过这样的错误：改表结构为图方便，直接Drop table 然后重新create，结果，弄错环境，把正式环境带有业务数据的表给 drop并重新 create了；为方便查看测试结果，delete table的所有数据，结果弄错环境把正式环境的数据给delete并 commit了。不知道怎么解决的，会直接吓懵；知道怎么解决的，也会不由自主的惊出一身冷汗。很惭愧，这两种错误，我都“有幸”经历过。

在此写下这份总结，为以后我大汉得源源不断的新人们，提供一道犯错之后的“不死金牌”。让然，就像胡伟山老师所说：在做数据（表）的改动和删除前，最好做备份。总是指望着“恢复”，可能有些东西，真要“恢复”起来，是需要代价滴

整理得有些粗略，毕竟Oracle博大精深。如果朋友们可以斧正并补充，那就太好啦！

前言

这篇文档是我在遇到问题并解决之后，才搜集资料整理出来的，并不能做到面面俱到。其次，关于 recyclebin和 flashback，已经出了很久很久，资料已经很多，并且可以解决我们遇到的大部分日常问题，所以我也没去参考官方英文文档（Developing Flashback Applications，有兴趣的朋友可以去看一下）。

01 Oracle回收站（recyclebin）Oracle回收站只能回收表和相关的对象包括索引、约束、触发器、嵌套表、大的

二进制对象（LOB）段和 LOB索引段。

从 Orace 10g开始，才有回收站的功能。

有了这个东西，很有意思的是，我们不需要开启闪回功能，即可完成一定的数据恢复。查看数据库是否开启闪回：

select flashback_on from v$database;

FLASHBACK_ON

------------------

NO

回收站启动和关闭

查看回收站状态

show parameter recyclebin;

如果你是 EBS R12.1.x的环境，或许在得到的是如下结果：

NAME TYPE VALUE

-------------------- ------------------------------

recyclebin string on

而 EBS R12.2.4版本的环境，结果就是 off。

这说明，在R12.2中，数据库中的回收站，默认是关闭的（胡伟山老师曾经提醒过我这一点，经试验确实如此）！

个人见解：我感觉，在R12.2项目的技术开发阶段，技术同事应该向技术负责人提出意见，要求开启数据库的回收站。如果考虑到性能空间等因素，可在上线后，关闭。

打开、关闭回收站

如果只是针对每个 session可以通过 alter session set recyclebin=off; 和 alter session set recyclebin=on;来关闭和开启。

如果是针对所有 session可以通过修改系统参数 recyclebin来实现,不过这里要注意 10g和 11g有点不一样,它们的参数类型不同：

10g可以用:alter system set recyclebin=off; 和 alter system set recyclebin=on;来关闭和开启

11g可以用:alter system set recyclebin=off deferred; 和 alter system set recyclebin=on deferred;来关闭和开启。

区别在于，11g中，对 system 级别的开启和关闭，需要加 deferred参数。这意味着，必须重新连接数据库，重新建立一个新的 session，才能看到和感受到回收站的开关变化。

扩展：了解Oracle参数类型

Oracle参数信息可以通过视图 v$parameter查看,其中有一列issys_modifiable表示参数类型,有 immediate,false,deferred 三种,其中 false表示

5

是静态参数,只要当 instance重新启动时才会生效,其他两种是动态参数,immediate表示修改参数后立即生效,deferred表示修改后要等下一个 session才能生效,修改时已经连接的 session是不会生效的.在用 alter 修改参数时如果是 false 或 immediate一般可以不写,如果是 deferred 则要写。

10g: select ISSYS_MODIFIABLE from v$parameter where name = 'recyclebin';的结果是 IMMEDIATE（未实验）。

11g: select ISSYS_MODIFIABLE from v$parameter where name = 'recyclebin';的结果是DEFERRED。

查看回收站内容

DBA&USER

select * from user_recyclebin;或 select * from recyclebin;

查看当前用户所 drop的对象(里面有对象之前的名字和 drop之后的名字)

select * from dba_recyclebin;

查看所有用户 drop 掉的对象

其实当一个对象 drop后,并且开启了回收站功能.它并没有真正被删除,实际上只是修改了一下名字，比如原来的表名是CUX_JECKFAN_DROP，drop之后，改为BIN$FVLgq6aNcVTgU5YRBAquOg==$0。

我们用 SELECT * FROM dba_recyclebin t where t.type='TABLE' AND T.original_name='原表名'，就可以查到，例如BIN$FVLgq6aNcVTgU5YRBAquOg==$0,它的命名规范是BIN$unique_id$version 其中BIN代表RecycleBin, unique_id是数据库中该对象的唯一标志，26 个字符长度 ,version表示该对象的版本号。

还原回收站内容

Drop table之后立即还原

假如有表 test 被 drop，drop后的 object_name是BIN$qAUuckGyd3TgQKjAFAFTAg==$0，还原可以用 flashback table "BIN$qAUuckGyd3TgQKjAFAFTAg==$0" to before drop (记得名字加双引号)，或者 flashback table test to before drop ;

Drop table之后又新建了同名 table，不能立即还原

假如 drop 掉一个表 test，为了掩盖错误，又新建了一个同名表 test，掩耳盗铃之举，数据可以证明一切。而这时候，又恰恰不能直接 flashback，只能再 drop test。然而此时，回收站里有两个同名的 test，但 object_name不同，就可以通过Object_name来 flashback了。当然，还可以重命名：flashback table test to before drop rename to test2。

清理回收站如果一次只清空一个表,可以用 PURGE table test 或 PURGE table

"BIN$qAUuckGyd3TgQKjAFAFTAg==$0";如果一次清空所有的可以用 PURGE recyclebin 或者 PURGE dba_recyclebin。

6

02 Oracle闪回（FlashBack）感谢任军老师和胡伟山老师两位领导的认可和建议，鼓励并支持我继续补充一下内容，尽量更好的方便和帮助到最广大人民群众。

Flashback综述

Flashback的目的

在有 Flashback之前，如果你对数据误操作，并已提交，这时想回退该误操作，将会是很件麻烦的事情。有人可能会说可以用备份恢复到误操作之前，但正确的操作数据也一起没了。唯一可能的办法就是日志挖掘，但日志挖掘非常繁琐，很难定位。

因此，Oracle 推出了 Flashback技术，主要目的就是为了恢复误操作。

Flashback Family

Oracle10g 引入的闪回技术包含

1 闪回查询（flashback query）

2闪回版本查询（flashback version query）

3闪回事务查询（flashback transcation query）

4闪回表（flashback table）

5闪回删除（flashback drop）

6闪回数据库（flashback database）。

其中闪回查询、闪回版本查询、闪回事务查询属于行级闪回。这三种闪回技术全部依赖于 undo表空间中的 undo数据。

闪回表、闪回删除属于表级闪回。闪回表也是从 undo中读取数据，闪回删除是依赖 recyclebin（就是第一部分提到的回收站） 。【所以说：在未开启回收站的情况下，1，2，3，4都是可以闪回的，但是如果DROP表了，就不能闪回了】

闪回数据库属于数据库级闪回。【个人感觉：做开发的话，估计很少用到。如果数据库真的被我们做开发的给玩儿坏了，那估计就要劳烦 DBA大人帮忙了】

Flashback Query

介绍

Flashback query是最基本的闪回功能，直接利用回滚段中的旧数据构造某一个时刻的一致性数据版本，让你能够看到过去某个时间的的数据，让你查看和重构意外被delete或者update的数据。

Flashback query(闪回查询)前提：

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

7

其中 undo_management = auto，设置自动 undo 管理（AUM），该参数默认设置为：auto；

Undo_retention = n(秒),设置决定 undo最多的保存时间，其值越大，就需要越多的 undo表空间的支持。修改 undo_retention的命令如下：

SQL> alter system set undo_retention = 3600;

System altered

执行演示

你可以根据 SCN 号和具体时间进行数据库查询。参数 undo_retention 表明在回滚段中旧的信息被覆盖之前保留的时间。通过使用 AS OF 字句，你可以查询一个表中不同时段的快照。AS OF 能够通过指定的 SCN 或者时间戳来获取所有行的单个版本，如果你指定 SCN，则后面必须跟数字，如果你指定 timestamp，则后面必须指定一个具体时间，Oralce数据会返回在该时间点或者 SCN 号时的数据。

根据 SCN的闪回查询：

1、初始化数据

SQL> create table t(a number,b number);

Table created.

SQL> insert into t values(1,1);

1 row created.

SQL> insert into t values(2,2);

1 row created.

SQL> insert into t values(3,3);

1 row created.

SQL> commit;

Commit complete.

2、查询当前时间、当前 SCN 号

SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual; ---获取当前时间

SYSDATE

-----------------

20140313 22:37:48

SQL> select dbms_flashback.get_system_change_number from dual; ---获取当前SCN

GET_SYSTEM_CHANGE_NUMBER

------------------------

1136165

3、删除部分数据

SQL> delete from t where b=3;

1 row deleted.

SQL> commit;

Commit complete.

4、根据 SCN 或者时间进行闪回查询

8

SQL> select * from t;

A B

---------- ----------

1 1

2 2

SQL> select * from t as of scn 1136165; ---根据 SCN 号得到在该 SCN 号时的数据情况

A B

---------- ----------

1 1

2 2

3 3

SQL> select * from t as of timestamp to_timestamp('20140313 22:37:48','yyyymmdd hh24:mi:ss'); --得到具体时间点的数据

A B

---------- ----------

1 1

2 2

3 3

Flashback Version Query

介绍

10g新引入的Version Query可以看到过去某个时间段内，数据是如何变化的，也就是数据的演变历史，为此，10g还引入了一个新的伪列 ORA_ROWSCN：记录该数据最后一次修改时的 SCN。

执行演示

SQL >create table test (id number, name varchar(10));

SQL >insert into test values (1,'first');

SQL >commit;

SQL >update test set name='second' where id=1;

SQL >commit;

SQL >update test set name='third' where id=1;

SQL >commit;

我们创建了一个测试表，插入一条数据，并对该数据进行两次更新，下面看如何用 Version Query 获得数据的演变历史：

SQL>select versions_xid,versions_startscn,versions_endscn,versions_operation,id,name from test versions between scn minvalue and maxvalue where id=1;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION ID NAME

---------------- ----------------- --------------- -------------------- ---------- ----------

12000900D0720000 6629455993 U 1 third

0C0018003D720000 6629455988 6629455993 U 1 second

9

0D002A00AD6E0000 6629455888 6629455988 I 1 first

从下往上看，正好对应着我们之前对该数据的操作历史。

从上面加黑这行可以看出，在 SCN 6629455888和 6629455993 这段期间，数据的状态为(1,second)，对应的操作为 update.

Flashback Version Query

概述

Flashback transcation query 能够查询事务对表所做的操作，通过UNDO SQL可以取消对事务所做的修改。查询 FLASHBACK_TRANSACTION_QUERY这个数据字典表来获取字典事务 ID的信息。感兴趣的，可以去玩玩儿这个视图。不过，我这里貌似没玩儿出什么……

Flashback Table

概述

Flashback table复原一个表到某个时间点，或者某个 SCN，而不用回复数据文件。闪回表依赖 UNDO数据。

前提：You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.

You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.

注意1：表结构改变的时候，不能进行闪回，即只能闪回数据，而不能闪回表结构。

注意2：在真正回退之前，必须启用 row movement，否则会报错ORA-08189: cannot flashback the table because row movement is not enabled。

注意3：flashback table to scn 或者 to timestamp，如果当前存在索引，闪回到创建索引之前的时间点，索引仍然存在。如果当前 drop了索引，那闪回到创建索引的时间点时，索引不会被恢复。

注意4：By default, the database disables triggers on the affected table before performing aFLASHBACK TABLE operation, and after the operation returns them to the state they were in before the operation (enabled or disabled). If you wish for triggers on a table to apply during FLASHBACK TABLE, then add an ENABLE TRIGGERS clause to the FLASHBACK TABLE statement。（缺省情况下，闪回表时，表上的 triggers是不能使用的，可以使用enable triggers来是闪回的同时启用 triggers。）

FLASHBACK TABLE table_name TO TIMESTAMP timestamp ENABLE TRIGGERS;

演示

SQL> create table test222(vname varchar2(100));

Table created

SQL> insert into test222 values('1');

1 row inserted

SQL> insert into test222 values('2');

10

1 row inserted

SQL> insert into test222 values('3');

1 row inserted

SQL> insert into test222 values('4');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test222;

VNAME

--------------------

1

2

3

4

SQL> select sysdate from dual;

SYSDATE

-----------

2015/5/7 15:33:02

SQL> delete from test222 t where t.vname='2';

1 row deleted

SQL> commit;

Commit complete

SQL> select * from test222;

VNAME

------------------------

1

3

4

SQL> ALTER TABLE test222 ENABLE ROW MOVEMENT;

Table altered

SQL> FLASHBACK TABLE test222 TO TIMESTAMP TO_TIMESTAMP('2015/5/7 15:33:02', 'YYYY/MM/DD HH24:MI:SS') ;

Done

SQL> select * from test222;

VNAME

-----------------------------------

1

2

3

4

对于表结构改变的演示，例如增加一列 alter table TEST222 add vemail varchar2(100)，可以试验一下，无论怎么闪回，数据表将总是保持两列。

11

Flashback Drop

概述

Flashback drop是从 Oracle 10g开始才有的功能，原理是每个表空间都会有严格回收站的逻辑区域，当 drop 时，被删除的表及其关联对象不会被物理删除，只是转移到回收站中，给用户提供一个恢复的可能。

可以看到 Flashback Drop，就是基于前面第一大部分提到的回收站来实现的了。

注意：对 system表空间下的表进行 drop，则无法闪回哦。

演示

关于如何闪回一个 drop的表，在前面“还原回收站”部分已经提到过，这里不再赘述。但是，一个有意思的地方是，闪回 drop table之后的索引处理，挺好玩儿的。

语言描述就是：闪回 table的同时，也会把它 Drop之前就有的 index给一起闪回来，但是，这个 index的名字却是回收站里的名字。需要我们重新命名才行。有图有真相：

SQL> create table tttt(a number,b number);

Table created

SQL> create index idx_tttt on tttt(a);

Index created

SQL> select index_name from user_indexes t where t.index_name='IDX_TTTT';

12

INDEX_NAME

------------------------------

IDX_TTTT

SQL> select table_name from user_tables t where t.TABLE_NAME='TTTT';

TABLE_NAME

------------------------------

TTTT

SQL> drop table tttt;

Table dropped

SQL> select table_name from user_tables t where t.TABLE_NAME='TTTT';

TABLE_NAME

------------------------------

SQL> select index_name from user_indexes t where t.index_name='IDX_TTTT';

INDEX_NAME

------------------------------

SQL> select object_name,original_name,type from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE

------------------------------ -------------------------------- --

BIN$FXqCtj4APZ3gU5YRBAp7PA==$1 IDX_TTTT INDEX

BIN$FXqCtj4BPZ3gU5YRBAp7PA==$0 TTTT TABLE

SQL> flashback table tttt to before drop;

Done

SQL> select object_name,original_name,type from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE

------------------------------ -------------------------------- -------------------------

SQL> select index_name from user_indexes t where t.index_name='BIN$FXqCtj4APZ3gU5YRBAp7PA==$1';

INDEX_NAME

------------------------------

BIN$FXqCtj4APZ3gU5YRBAp7PA==$1

Flashback Database

概述

Flashback Database 命令是为了加快原本很慢的时间点数据库恢复（point in time database recovery）过程。闪回可以取得完整的数据库恢复和使用归档日志的

13

前滚，主要目的是加快从“意外状态“中恢复。闪回数据库基于闪回日志 flashback log，闪回日志包含已修改数据块的”前影像“，可用于将数据库恢复到该时间点之前的状态。

闪回数据库允许复原整个数据库到具体的时间点，从而撤销该时间点以来的所有数据库的变化(用户误删除表中数据、 删除表、 truncate 表、 索引、 触发器， 表空间， Dba 误操作等）。简而言之，所有操作都可以闪回。

闪回数据库能够使你在不恢复数据文件备份的情况下使数据库倒回到先去的某个时间点。

你可以通过RMAN 或者 SQLPLUS来闪回数据库，替代使用复杂的过程。

使用数据库闪回可撤销导致逻辑数据损坏的变更，如果是介质丢失或者物理损坏，则必须使用传统的恢复方法进行数据库的恢复。

闪回数据库通过闪回日志来进行数据恢复，数据库会定期将数据块的前影像记录在闪回日志里面。

注意：Flashback 功能默认是关闭的：

SQL> select name,flashback_on from v$database;

NAME FLASHBACK_ON

--------- ------------------

O01DMS0 NO

所以，如果我们在犯错误（误操作导致数据损坏）之前并不知道要打开数据库的flashback 功能，这就可能导致我们无法通过 flashback database来还原。也就是说，即使是对 flashback database很熟悉，忘记把这个功能打开，也无力回天哦。

致歉

个人认为，对整个 database 进行 flashback的情况，在开发过程中并不多见，并且，我们也一般不会想到在 EBS环境刚安装好之后就去打开 database的flashback 功能（以防止技术小伙伴们的误操作）。所以，这部分我没有去试验，不完善之处，还请各位同事多多包涵呀。

14