Oracle安全之 Oracle 11g flashback技术详解

时间：2024-01-10 20:30:20

Oracle11g提供的闪回技术用于对抗人为错误，主要有以下7种技术组成：

闪回查询-（闪回时间查询、闪回版本查询）；

闪回数据归档；

闪回事务查询；

闪回事务；

闪回表；

闪回删表；

闪回数据库。

闪回时间查询：as of子句

用于查询基于过去某一时间点、SCN的数据分布情况。

SQL> create table emp_temp3 as select * from emp;

Table created.

SQL> select * from emp_temp3;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20

7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30

7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10

14 rows selected.

SQL> select sysdate from dual;

SYSDATE

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

2016/09/06 10:33:34

SQL> delete from emp_temp3 where deptno=20;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp_temp3;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30

7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10

7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30

7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30

7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10

9 rows selected.

SQL>select * from emp_temp3 as of timestamp to_timestamp('2016/09/06 10:33:34','yyyy-mm-dd hh24:mi:ss');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20

7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30

7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10

14 rows selected.

SQL> select * from emp_temp3 as of timestamp (systimestamp - interval '15' minute);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30

7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10

7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30

7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30

7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10

9 rows selected.

SQL>

闪回版本查询：versions between … and …

查询过去某一时间窗口内的数据修改情况。

SQL> select sysdate from dual;

SYSDATE

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

2016/09/06 10:56:05

SQL> select * from emp_temp3 where empno=7369;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20

SQL> update emp_temp3 set sal=8000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL>

SQL> update emp_temp3 set sal=1000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL>

SQL> update emp_temp3 set sal=101 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> update emp_temp3 set sal=100 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE

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

2016/09/06 10:58:10

SQL> select * from emp_temp3 versions between timestamp to_timestamp('2016/09/06 10:56:05','yyyy/mm/dd hh24:mi:ss') and to_timestamp('2016/09/06 10:58:10','yyyy/mm/dd hh24:mi:ss') where empno=7369;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 00:00:00 100 20

7369 SMITH CLERK 7902 1980/12/17 00:00:00 101 20

7369 SMITH CLERK 7902 1980/12/17 00:00:00 1000 20

7369 SMITH CLERK 7902 1980/12/17 00:00:00 8000 20

7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20

通过以上查询可以看到在这段时间内7369号员工薪资变化了4次。以下查询添加了闪回版本的伪列信息：

SQL> select versions_xid,

versions_startscn,

versions_endscn,

versions_starttime,

versions_endtime,

empno,

sal

from emp_temp3 versions between timestamp to_timestamp('2016/09/06 10:56:05', 'yyyy/mm/dd hh24:mi:ss') and to_timestamp('2016/09/06 10:58:10', 'yyyy/mm/dd hh24:mi:ss')

where empno = 7369

order by versions_startscn nulls first;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_STARTTIME VERSIONS_ENDTIME EMPNO SAL

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

9517151 06-SEP-16 10.57.22 AM 7369 800

04002000992C0000 9517151 9517159 06-SEP-16 10.57.22 AM 06-SEP-16 10.57.31 AM 7369 8000

01001F00882C0000 9517159 9517168 06-SEP-16 10.57.31 AM 06-SEP-16 10.57.46 AM 7369 1000

020014006A2D0000 9517168 9517181 06-SEP-16 10.57.46 AM 06-SEP-16 10.57.55 AM 7369 101

08001500DF2E0000 9517181 06-SEP-16 10.57.55 AM 7369 100

SQL>

闪回数据归档：延长历史数据保存时间

闪回数据归档是将感兴趣的数据保存在普通表空间中。其主要作用即是延长闪回恢复窗口。

在users表空间中创建一个保存一月数据的数据归档，名称为fda1（操作者必须拥有falshback archive administer权限）：

SQL> create flashback archive fda1 tablespace users retention 1 month;

Flashback archive created.

创建一个默认的闪回数据归档，必须具有sysdba权限：

SQL> create flashback archive default fda_default tablespace users retention 1 month;

Flashback archive created.

在创建闪回数据归档时也可以同时制定占用磁盘空间的配额：

SQL> create flashback archive fda2 tablespace users quota 1g retention 1 month;

Flashback archive created.

为特定表使用闪回数据归档方式：

为用户属于使用闪回数据归档权限：

SQL> show user

USER is "SYS"

SQL> grant flashback archive on fda1 to scott;

Grant succeeded.

SQL> grant flashback archive on fda2 to scott;

Grant succeeded.

SQL> grant flashback archive on fda_default to scott;

Grant succeeded.

为scott用户下的emp_temp2、emp_temp3表启用/回收闪回数据归档功能：

SQL> alter table emp_temp3 flashback archive fda1;

Table altered.

SQL> alter table emp_temp2 flashback archive;

Table altered.

SQL> alter table emp_temp2 no flashback archive;

Table altered.

创建表时可以指定闪回数据归档：

SQL>

create table EMP_TEMP4

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

) flashback archive fda1

tablespace USERS;

Table created.

查看闪回数据归档信息：

SQL>

select a.FLASHBACK_ARCHIVE_NAME fda_name,

a.RETENTION_IN_DAYS days,

ts.TABLESPACE_NAME,

ts.QUOTA_IN_MB quota,

t.TABLE_NAME,

t.ARCHIVE_TABLE_NAME

from dba_flashback_archive a,

dba_flashback_archive_ts ts,

dba_flashback_archive_tables t

where a.FLASHBACK_ARCHIVE_NAME = ts.FLASHBACK_ARCHIVE_NAME

and a.FLASHBACK_ARCHIVE_NAME = t.FLASHBACK_ARCHIVE_NAME

and t.OWNER_NAME = 'SCOTT'

/

FDA_NAME DAYS TABLESPACE_NAME QUOTA TABLE_NAME ARCHIVE_TABLE_NAME

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

FDA1 30 USERS EMP_TEMP3 SYS_FBA_HIST_283658

FDA1 30 USERS EMP_TEMP4 SYS_FBA_HIST_283670

清楚闪回数据归档历史数据办法：

清除10分钟前数据：

SQL> alter flashback archive fda1 purge before timestamp (systimestamp - interval '10' minute);

Flashback archive altered.

全部清除：

SQL> alter flashback archive fda_default purge all;

Flashback archive altered.

启用闪回数据归档支持绝大多数ddl语句，但是对于少数ddl语句时会遭遇ora-55610错误，比如“alter table … shrink space”,“alter table … move”,“alter table … exchange partition”。

1）普通表做分区交换：

SQL> drop table rpt;

Table dropped.

SQL>drop table source;

Table dropped.

SQL>

create table rpt (id number,name varchar2(30))

partition by range(id)

(

partition p1 values less than (100) tablespace users,

partition p2 values less than (200) tablespace users,

partition p3 values less than (maxvalue) tablespace users

);

Table created.

SQL>

create table source(id number,name varchar2(30));

Table created.

SQL> alter table rpt exchange partition p3 with table source;

Table altered.

结论：正常情况下，表没有启用闪回数据，进行exchange partition时可以正常进行。

2）普通表启用闪回数据归档，进行分区交换：

SQL> alter table source flashback archive fda1;

Table altered.

SQL> alter table rpt exchange partition p3 with table source;

Table altered.

结论：普通表启用闪回数据归档，不影响分区交换功能。

3）分区表启用闪回数据归档，进行分区交换：

SQL> alter table source no flashback archive;

Table altered.

SQL> alter table rpt flashback archive fda1;

Table altered.

SQL> alter table rpt exchange partition p3 with table source;

alter table rpt exchange partition p3 with table source

*

ERROR at line 1:

ORA-55610: Invalid DDL statement on history-tracked table

结论：分区表启用闪回数据归档后，进行分区交换会遭遇ora-55610错误。

启用闪回数据归档功能分区表进行exchange partition解决办法：

Oracle提供了dbms_flashback_archive包的DISASSOCIATE_FBA用于将基表与历史表进行分离操作，分离后的基表形式上等同于没有启用闪回数据归档的普通表，然后执行遭遇ora-55610错误的ddl语句，再操作完成后，再使用REASSOCIATE_FBA将基表与历史表关联起来即可。

1） 查询当前分区表rpt的历史表名称：

SQL> select a.FLASHBACK_ARCHIVE_NAME fda_name,

a.RETENTION_IN_DAYS days,

ts.TABLESPACE_NAME,

ts.QUOTA_IN_MB quota,

t.TABLE_NAME,

t.ARCHIVE_TABLE_NAME

from dba_flashback_archive a,

dba_flashback_archive_ts ts,

dba_flashback_archive_tables t

where a.FLASHBACK_ARCHIVE_NAME = ts.FLASHBACK_ARCHIVE_NAME

and a.FLASHBACK_ARCHIVE_NAME = t.FLASHBACK_ARCHIVE_NAME

and t.OWNER_NAME = 'SCOTT'

and t.TABLE_NAME=’RPT’

/

FDA_NAME DAYS TABLESPACE_NAME QUOTA TABLE_NAME ARCHIVE_TABLE_NAME

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

FDA1 30 USERS RPT SYS_FBA_HIST_283676

2）执行dbms_flashback_archive.DISASSOCIATE_FBA过程将rpt基表与历史表相分离：

SQL> exec dbms_flashback_archive.DISASSOCIATE_FBA('SCOTT','RPT')

PL/SQL procedure successfully completed.

3）执行遭遇ora-55610错误的ddl语句：

SQL> alter table rpt exchange partition p3 with table source;

Table altered.

4）使用dbms_flashback_archive.REASSOCIATE_FBA将基表与历史表重新关联起来：

SQL> exec dbms_flashback_archive.REASSOCIATE_FBA('SCOTT','RPT')

PL/SQL procedure successfully completed.

闪回事务查询

闪回事务查询返回的是可以回到以前状态的sql语句，并且如果使用闪回事务查询必须启用最小补充日志，闪回事务查询在表flashback_transantion_query上统一查询。闪回事务查询对于ddl语句无效，也不应该给予希望。

在数据库上启用最小补充日志：

SQL> alter database add supplemental log data;

Database altered.

SQL> select NAME,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

NAME SUPPLEMENTAL_LOG_DATA_MI

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

ORCL YES

测试：

SQL> insert into dept values(50,'yunwei','beijing');

1 row created.

SQL> commit;

Commit complete.

SQL> select versions_xid,versions_startscn,versions_endscn,versions_starttime,versions_endtime,deptno from dept versions between timestamp (systimestamp - interval '10' minute) and maxvalue order by versions_startscn nulls first;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_STARTTIME VERSIONS_ENDTIME DEPTNO

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

20

10

40

30

02000F007D2D0000 9532457 06-SEP-16 03.28.59 PM 50

根据事务号，查询事务下的sql：

SQL> select undo_sql from flashback_transaction_query where xid='02000F007D2D0000';

UNDO_SQL

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

delete from "SCOTT"."DEPT" where ROWID = 'AAAVRCAAEAAAACFAAA';

可以看到以上查询出的sql是对“insert into dept values(50,'yunwei','beijing');”的逆操作。

闪回事务

闪回事务可以撤销在数据库中已经发生的事务，其功能由dbms_flashback.transaction_backout过程实现。要想启用数据库的闪回事务功能，需要启用主键补充日志和外键依赖补充日志。

SQL> alter database add supplemental log data (primary key) columns;

Database altered.

SQL> alter database add supplemental log data (foreign key) columns;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_FK from v$database;

SUPPLEMENTAL_LOG_DATA_MI SUPPLEMEN SUPPLEMEN

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

YES YES YES

事务的依赖性：对于事务的撤销具有至关重要的影响。

WAW依赖；

主键依赖；

外键依赖。

transaction backout过程的options参数就是为了解决事务的依赖性而存在的。

1）nocascade，tx1不可以被其他任何事务依赖（即不存在tx2），否则撤销失败。

2）cascade，将tx1和其依赖事务一同撤销。

3）nocascade_force，忽略依赖事务tx2，直接执行tx1的撤销sql将tx1撤销，如果没有约束上的冲突，撤销成功，否则约束报错将导致撤销失败。

4）nonconfilict_only，在不影响依赖事务tx2的前提下，撤销tx1的修改。其与nocascade_force不同处在于首先过滤一下tx1的sql，使其不会作用于tx2的数据行上。

以下举一例说明闪回事务的使用：

1）创建一张表（无约束关系）并且生成两个相互依赖的WAW事务：

SQL> create table emp_test as select * from emp;

Table created.

SQL> select * from emp_test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20

7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30

7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10

14 rows selected.

SQL> update emp_test set sal=sal*5;

14 rows updated.

SQL> commit;

Commit complete.

SQL> update emp_test set sal=sal*1.1 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

2）现在发现员工工资异常高，通过查看emp_test表相关的事务信息，发现最近有2个事务信息：

SQL> select distinct XID,COMMIT_SCN from flashback_transaction_query where table_name='EMP_TEST';

XID COMMIT_SCN

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

03001500F82C0000 9562593

05001C00602D0000 9562556

3）查看commit比较小的scn号（9562556）所对应的事务undo_sql撤销信息：

SQL> select xid,undo_sql from flashback_transaction_query where xid='05001C00602D0000' and table_name='EMP_TEST';

XID UNDO_SQL

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

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '1300' where ROWID = 'AABFREAAEAAAAFDAAN';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '3000' where ROWID = 'AABFREAAEAAAAFDAAM';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '950' where ROWID = 'AABFREAAEAAAAFDAAL';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '1100' where ROWID = 'AABFREAAEAAAAFDAAK';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '1500' where ROWID = 'AABFREAAEAAAAFDAAJ';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '5000' where ROWID = 'AABFREAAEAAAAFDAAI';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '3000' where ROWID = 'AABFREAAEAAAAFDAAH';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '2450' where ROWID = 'AABFREAAEAAAAFDAAG';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '2850' where ROWID = 'AABFREAAEAAAAFDAAF';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '1250' where ROWID = 'AABFREAAEAAAAFDAAE';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '2975' where ROWID = 'AABFREAAEAAAAFDAAD';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '1250' where ROWID = 'AABFREAAEAAAAFDAAC';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '1600' where ROWID = 'AABFREAAEAAAAFDAAB';

05001C00602D0000 update "SCOTT"."EMP_TEST" set "SAL" = '800' where ROWID = 'AABFREAAEAAAAFDAAA';

14 rows selected.

4）可以看出异常工资信息源自于此事务，尝试nocascade方式撤销此事务：

SQL> conn / as sysdba

Connected.

SQL> exec dbms_flashback.transaction_backout(1,sys.xid_array('05001C00602D0000'),options=>dbms_flashback.nocascade)

BEGIN dbms_flashback.transaction_backout(1,sys.xid_array('05001C00602D0000'),options=>dbms_flashback.nocascade); END;

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 1

5）现在通过cascade方式把关联事务也一起撤销：

SQL>

declare

v_xid sys.xid_array;

begin

v_xid := sys.xid_array('04000A00EA2C0000');

dbms_flashback.transaction_backout(1,v_xid,options=>dbms_flashback.cascade);

end;

/

declare

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [ktftbProcessKGL_4], [7901], [7842],

[], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 5

6）查看闪回事务报告：确定撤销操作是否准确。

SQL> select xid,dependent_xid,backout_mode from dba_flashback_txn_state;

7）查看数据表确定数据是否准确：

SQL>select * from emp_test;

8）确定撤销事务commit还是rollback：

SQL>commit

Commit complete.

闪回表

闪回表能够以表为单位进行数据恢复，闪回表也是利用了undo表空间中的撤销数据，所以其也受闪回查询4因素的影响。闪回查询不能为闪回表服务。闪回表的语法如下：flashback table [table_name] to [timestamp|scn] [timestamp_value|scn_value];

Sys用户的所有表均不支持此项功能。

被闪回的表必须启动行移动功能：

SQL> flashback table emp_temp2 to timestamp (systimestamp - interval '20' minute);

flashback table emp_temp2 to timestamp (systimestamp - interval '20' minute)

*

ERROR at line 1:

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

SQL> alter table emp_temp2 enable row movement;

Table altered.

SQL> flashback table emp_temp2 to timestamp (systimestamp - interval '20' minute);

Flashback complete.

闪回删表

闪回删表即撤销drop table，其原理是oracle在删除一个对象时，并没有实机物理上删除对象，而且将其改变一个名字存储啊在recyclebin中，若对象已经在recyclebin中被清空，那么该对象也就无法恢复了。基本语法：flashback table [table_name] to before drop [rename to [new_name]];

闪回删表的一个小实例：

SQL> create table emp_t1 as select * from emp;

Table created.

SQL> drop table emp_t1;

Table dropped.

SQL> select * from emp_t1;

select * from emp_t1

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> flashback table emp_t1 to before drop;

Flashback complete.

SQL> select count(1) from emp_t1;

COUNT(1)

----------

14

闪回数据库

闪回数据库的命令是“flashback database to <pit>”，其中pit可以是SCN、时间或还原点，顾名思义，就是将整个数据库回退到过去的某个时间点。

闪回数据库使用两种日志：重做日志和闪回日志。闪回日志记录与重做日志相反的操作，闪回数据库就是利用闪回日志进行操作的。

闪回日志的保存时间由初始化参数db_flashback_retention_target控制，默认1天。

查看当前数据库是否开启了闪回日志功能，通过v$database.flashback_on查看。

SQL> select name,flashback_on from v$database;

NAME FLASHBACK_ON

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

ORCL NO

归档模式下，启用闪回数据日志功能：

SQL> alter database flashback on;

Database altered.

SQL> select name,flashback_on from v$database;

NAME FLASHBACK_ON

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

ORCL YES

闪回数据库语法：flashback database to scn|timestamp …

在执行闪回数据库的过程中可能遭遇“重做日志不够用”或“闪回日志不够用”的问题。

闪回数据库的试验：

1） 通过查看v$flashback_database_log视图，检查数据库最远可以恢复到哪个时间点：

select t.oldest_flashback_scn,

to_char(oldest_flashback_time, 'yyyy-mm-dd hh24:mi:ss')

from v$flashback_database_log t;

OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASHBACK_TIME,'YYYY-MM-DDHH24:MI:SS')

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

9540167 2016-09-06 17:43:29

2） 制作测试数据

SQL> create table emp1 as select * from emp;

Table created.

SQL> delete from emp_temp3;

14 rows deleted.

SQL> commit;

Commit complete.

3） 重启数据库至mount状态，进行闪回数据库操作：

SQL> conn / as sysdba

Connected.

SQL> startup force mount

ORACLE instance started.

Total System Global Area 2371719168 bytes

Fixed Size 2255512 bytes

Variable Size 738198888 bytes

Database Buffers 1610612736 bytes

Redo Buffers 20652032 bytes

Database mounted.

SQL> flashback database to scn 9540167;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(1) from scott.emp1;

select count(1) from scott.emp1

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select count(1) from scott.emp_temp3;

COUNT(1)

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

14

4） 以resetlogs的方式打开数据库：

SQL> startup force mount

ORACLE instance started.

Total System Global Area 2371719168 bytes

Fixed Size 2255512 bytes

Variable Size 738198888 bytes

Database Buffers 1610612736 bytes

Redo Buffers 20652032 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

总结

闪回查询、闪回事务查询、闪回表均需要rowid定位过去和现在的行，如果执行过行移动命令，在执行以上操作会遭遇ora-01466错误。

默认情况下，数据库的“闪回事务查询”，“闪回事务”，“闪回数据库”功能是关闭的；其他功能“闪回查询”，“闪回表”，“闪回删表”是可用的；“闪回数据归档”是为了增加恢复窗口。

相关文章