flashback系列文章一（flashback query）

创建一个测试表作为“小白鼠”实验一下
SQL> create table test(id number,name varchar2(50));

Table created.

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

1 row created.

SQL> insert into test values(2,'jack');

1 row created.

SQL> insert into test values(3,'rose');

1 row created.

SQL> insert into test values(4,'harward');

1 row created.

SQL> insert into test values(5,'obma');

1 row created.

SQL> commit;

Commit complete.

查看下数据库当前的时间和scn号

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-12-30 16:26:55

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  765545

现在我们模拟一些误操作，把姓名为rose和jack的行删除掉

SQL> delete from test where name='rose' or name='jack';

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 thomas
         4 harward
         5 obma
可以看到jack和rose已经不在test表中了
此时我们发现删除数据的操作时误操作，并且不想使用数据库的介质恢复来找回数据，那就可以试着用下flashback query。
使用scn查询

SQL> select * from test as of scn 765545;

        ID NAME
---------- --------------------------------------------------
         1 thomas
         2 jack
         3 rose
         4 harward
         5 obma

使用timestamp查询

SQL> select * from test as of timestamp to_timestamp('2013-12-30 16:26:55','yyyy-mm-dd hh24:mi:ss');

        ID NAME
---------- --------------------------------------------------
         1 thomas
         2 jack
         3 rose
         4 harward
         5 obma

我们把flashback query查询到的数据恢复到临时表中

SQL> create table test_recovery as select * from test where 1=2;

Table created.

SQL> insert into test_recovery
  2  select * from test as of scn 765545;

5 rows created.

SQL> commit;

Commit complete.

这样，我们就可以找到被误删除的数据了。



也可以把当前会话返回至之前的时间点

SQL> execute dbms_flashback.enable_at_time(to_timestamp('2013-12-30 16:26:55','yyyy-mm-dd hh24:mi:ss'));

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 thomas
         2 jack
         3 rose
         4 harward
         5 obma

SQL>  execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 thomas
         4 harward
         5 obma
这样也是可以找到丢掉的数据的。

其实使用flashback query进行恢复的难点还是如何找到合适的时间点(timestamp)或者scn,在实际使用时需要多试几次，找到合适的恢复点进行恢复。

需注意以下几点：
1、undo表空间自动管理

SQL> show parameter undo_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO

2、undo_retention参数可以设置要保留undo时间，但是并不能保证undo一定被保留，如果事务比较繁忙，还是会被覆盖掉

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

3、把undo表空间设置成retention guarantee可以保证undo_retention时间内的undo，但是这将影响到事务的进行，导致数据库停止

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

SQL> alter tablespace undotbs1 retention noguarantee;

Tablespace altered.

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26613085/viewspace-1065694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26613085/viewspace-1065694/