Flashback database 架构

Flashback Database 整个架构包括一个进程Recover Writer(RVWR)后台进程、Flashback Database Log 日志和Flash Recover Area

一旦数据库启用了Flashback Database,则RVWR进程会启动,RVWR进程会向Flash Recover Area内写入Flashback Database Log,这些日志包括的是数据块的"前镜像"(before image)。这也是Flashback Database比不完全恢复快的原因

ps -ef| grep rvwr

oracle 11742 1 0 09:38 ? 00:00:03 ora_rvwr_PROD

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

设置闪回恢复区

闪回恢复区主要通过3个初始化参数来设置和管理:

db_recovery_file_dest：指定闪回恢复区的位置

db_recovery_file_dest_size：指定闪回恢复区的可用空间大小

db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟，也就是一天。当然，实际上可回退的时间还决定于闪回恢复区的大小，因为里面保存了回退所需要的flash log。所以这个参数要和db_recovery_file_dest_size配合修改。

sys@PROD>show parameter recover

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/flash

db_recovery_file_dest_size big integer 4G

sys@PROD>show parameter retention

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440单位是分钟

undo_retention integer 5400

1440分钟时24小时代表数据库能回退24小时

Flashback Query 和 Flashback Table 都是利用UNDO内容来完成的，所以回复效果取决于保留的UNDO内容多少，要达到理想效果，就要考虑UNDO_RETENTION的参数设置。

UNDO_RETENTION参数定义的是提交修改后的UNDO记录还要保留多长时间。但是ORACLE并不强制保留这么长时间，如果UNDO空间不够，即使还没有达到时间限制，这些记录还是会被覆盖。可以通过在UNDO表空间或者数据库设置retention guarantee 来强制这种保留时间限制，缺省是这个特性没有启用。这个属性可以再dba_tablespace视图中查看。

sys@PROD>select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME RETENTION

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

SYSTEM NOT APPLY

SYSAUX NOT APPLY

UNDOTBS NOGUARANTEE

TEMPTS NOT APPLY

USERS NOT APPLY

LXTB NOT APPLY

TOOLS NOT APPLY

alter tablespace undotbs retention guarantee;

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

启动数据库Flashback功能（开闪回一定要开归档！）

1.数据库启动到MOUNT

检查Flashback功能，缺省时功能是关闭的

select name,flashback_on from v$database;

NAME	FLASHBACK_ON
---------	------------------
PROD	NO

启动Flashback功能

alter database flashback on;

2.设置db_flash_retention_target参数

alter system set db_flashback_retention_target=1440;

3.开库

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

闪回查询

利用select * from emp1 as of timestamp to_timestamp('2014-03-30 00:34:09','yyyy-mm-dd hh24:mi:ss');

Flashback Table

注意：SYS用户不支持闪回

ORA-08185: Flashback not supported for user SYS

Flashback Table也是使用UNDO tablespace的内容来实现对数据的回退。该命令相对简单，输入：flashback table table_name to scn(to timestamp) 即可。

flashback table tm1 to timestamp to_timestamp('2014-06-14 22:50:00','yyyy-mm-dd hh24:mi:ss');

scott@PROD>flashback table tm1 to timestamp to_timestamp('2014-06-14 22:50:00','yyyy-mm-dd hh24:mi:ss');

flashback table tm1 to timestamp to_timestamp('2014-06-14 22:50:00','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-08194: Flashback Table operation is not allowed on materialized views

scott@PROD>create table tm2 as select * from tm1 as of timestamp to_timestamp('2014-06-14 22:50:00','yyyy-mm-dd hh24:mi:ss');

Table created.

如果存在基于这个表的物化视图，执行闪回操作时会报错 ,可以通过create一个新表来恢复

注意：如果想要对表进行flashback，必须允许表的row movement.

ORA-08189: cannot flashback the table because row movement is not enabled

SQL>Alter table table_name row movement;

要查看某表是否启用row movement，可以到user_tables 中查询(或all_tables,dba_tables).

sys@PROD>select TABLE_NAME,ROW_MOVEMENT from dba_tables where OWNER='SCOTT';

TABLE_NAME ROW_MOVE

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

DEPT DISABLED

EMP DISABLED

BONUS DISABLED

SALGRADE DISABLED

DEPT1 ENABLED

SYS_TEMP_FBT DISABLED

行迁移

意思就是,一个现存的行允许改变其rowid(物理存储地址),通常情况下,数据行在分配了空间之后,行的rowid就固定了,即使以后行长度超出预留的空间,也不会将其移动,在长数据行的情况下,表会产生行链接,对于io来说,行链接是不利的,为了性能,就需要将行调整为在单个物理存储地址下能保存行的所有信息,这需要行迁移.另外在flashback的时候,由于原始版本的行占据了相应的物理地址,所以,也需要行迁移.

其它的,比如说在收缩段空间的情况下,由于要把所有段数据向段前挤压,大部分行都需要改变其物理地址.也需要行迁移

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

实验1：用SYS用户闪回

14:36:13 sys@PROD>insert into ftest values(8000,'fan','MANAGER',null,'26-may-14',8000,null,40);

1 row created.

14:36:21 sys@PROD>select * from ftest;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

8000 fan MANAGER 26-MAY-14 8000 40

15 rows selected.

14:36:27 sys@PROD>commite;

14:36:39 sys@PROD>update ftest set sal=10000 where empno=8000;

1 row updated.

14:37:10 sys@PROD>commit;

Commit complete.

14:41:01 sys@PROD>flashback table ftest to timestamp to_timestamp('2014-05-26 14:36:00','yyyy-mm-dd hh24:mi:ss');

flashback table ftest to timestamp to_timestamp('2014-05-26 14:36:00','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

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

试验2：用SCOTT的用户闪回

14:52:01 scott@PROD>select * from dept1;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

7 OPERATIONS BOSTON

14:52:10 scott@PROD>update dept1 set deptno=44 where loc='BOSTON';

1 row updated.

14:52:35 scott@PROD>commit;

Commit complete.

14:52:38 scott@PROD>flashback table dept1 to timestamp to_timestamp('2014-05-26 14:52:00','yyyy-mm-dd hh24:mi:ss');

flashback table dept1 to timestamp to_timestamp('2014-05-26 14:52:00','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

14:53:36 scott@PROD>alter table dept1 enable row movement;

Table altered.

14:56:47 scott@PROD>flashback table dept1 to timestamp to_timestamp('2014-05-26 14:52:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

14:57:00 scott@PROD>select * from dept1;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

7 OPERATIONS BOSTON

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

试验3：闪回查询

15:06:14 scott@PROD>update dept1 set deptno=45 where loc='BOSTON';

1 row updated.

15:06:47 scott@PROD>commit;

Commit complete.

15:06:50 scott@PROD>update dept1 set deptno=46 where loc='BOSTON';

1 row updated.

15:06:55 scott@PROD>commit;

Commit complete.

14:57:05 scott@PROD>select * from dept1 as of timestamp to_timestamp('2014-05-26 14:52:00','yyyy-mm-dd hh24:mi:ss');

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

7 OPERATIONS BOSTON

15:04:21 scott@PROD>select * from dept1 as of timestamp to_timestamp('2014-05-26 14:53:00','yyyy-mm-dd hh24:mi:ss');

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

44 OPERATIONS BOSTON

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

Flashback Database

1.模拟误操作

16:12:22 scott@PROD>create table ftest as select * from dba_objects;

Table created.

16:12:45 scott@PROD>select count(*) from ftest;

COUNT(*)

----------

16242

2.truncate清空数据

16:12:58 scott@PROD>truncate table ftest;

Table truncated.

16:13:32 scott@PROD>select count(*) from ftest;

COUNT(*)

----------

0

3.Flashback Database 实际是对数据库的一个不完全恢复操作，因此需要关闭数据库，重启数据库到MOUNT状态；但是与不完全恢复不同的是它不需要restore操作；

sys@PROD>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@PROD>startup mount

sys@PROD>flashback database to timestamp to_timestamp('2014-05-26 16:13:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

恢复成功后先把数据库以read only的方式打开，检查是否达到预期的恢复目的，如果没有达到目的，还有机会进行第二次恢复；

sys@PROD>alter database open read only;

Database altered.

scott@PROD>select count(*) from ftest;

COUNT(*)

----------

16242

恢复成功，一resetlog方式打开数据库

关闭数据库

ys@PROD>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

启动到MOUNT

sys@PROD>startup mount

OPEN RESETLOGS

sys@PROD>alter database open resetlogs;

sys@PROD>select count(*) from scott.ftest;

COUNT(*)

----------

16242

恢复成功

如果第一次没有成功需要

1.shutdown immediate

2.startup mount

3.继续flashback database

4.alter database open read only

剩余步骤一样

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

Flashback Drop

这个功能是从Oracle 10g开始出现的，用于恢复用户误删除的对象(包括表、索引等)。这个技术依赖于Tablespace Recycle Bin(表空间回收站)。这个功能和windows的回收站非常类似

Tablespace Recycle Bin

从Oracle 10g开始，每个表空间都会有一个叫做回收站的逻辑区域，当用户执行drop命令时，被删除的表和表的关联对象(包括索引、约束、触发器、LOB段、LOB index段)不会被物理删除，这些对象先转到回收站中，这就给用户提供了一个恢复的可能。

show parameter recycle

NAME TYPE VALUE

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

recyclebin string on

sys@PROD>show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

EMP1 BIN$9ZG3UH9s7XbgQAB/AQAh1A==$0 TABLE 2014-03-27:15:41:41

EMP1 BIN$9ZJdfj8Mm6ngQAB/AQAeCw==$0 TABLE 2014-03-27:15:16:38

EMP1 BIN$9WlrO36I+1jgQAB/AQA9uA==$0 TABLE 2014-03-25:14:25:31

EMP2 BIN$9ZJdfj8Lm6ngQAB/AQAeCw==$0 TABLE 2014-03-27:15:16:34

EMP3 BIN$9ZLYhwS0uBLgQAB/AQAqDg==$0 TABLE 2014-03-27:17:02:33

EMP3 BIN$9ZJdfj8Nm6ngQAB/AQAeCw==$0 TABLE 2014-03-27:15:17:32

EMP4 BIN$9ZJdfj8Om6ngQAB/AQAeCw==$0 TABLE 2014-03-27:15:17:37

sys@PROD>select object_name,original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME

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

BIN$9WlrO36I+1jgQAB/AQA9uA==$0 EMP1

BIN$9ZJdfj8Lm6ngQAB/AQAeCw==$0 EMP2

BIN$9ZJdfj8Mm6ngQAB/AQAeCw==$0 EMP1

BIN$9ZJdfj8Nm6ngQAB/AQAeCw==$0 EMP3

BIN$9ZJdfj8Om6ngQAB/AQAeCw==$0 EMP4

BIN$9ZG3UH9s7XbgQAB/AQAh1A==$0 EMP1

BIN$9ZLYhwS0uBLgQAB/AQAqDg==$0 EMP3

可以像查看普通表一样，查看Recycle Bin 中这些对象的内容

sys@PROD>select * from "BIN$9ZG3UH9s7XbgQAB/AQAh1A==$0";

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

Flashback drop

sys@PROD>flashback table "BIN$9ZG3UH9s7XbgQAB/AQAh1A==$0" to before drop rename to empdrop;

Flashback complete.

sys@PROD>select * from empdrop;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

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

Flash Recovery Area 空间监控

col name format a32 heading 'file name'

col spc_lmt_mb format 9999.99 heading 'space|limit|(mb)'

col spc_usd_mb format 9999.99 heading'space|used|(mb)'

col spc_rcl_mb format 9999.99 heading 'reclm|space|(mb)'

col number_of_files format 99999 heading 'files'

select name,space_limit/1024/1024 spc_lmt_mb,space_used/1024/1024 spc_usd_mb,space_reclaimable/1024/1024 spc_rcl_mb,number_of_files from v$recovery_file_dest;

ITPub博客

Flashback

最新文章