（原创）使用9i的flashback恢复丢失的数据

yjf6660 · 发表于 2005-1-24 17:33

转贴：原著：biti_rainy
undo tablespace 中 flashback 的功能和注意事项（缺陷?）

在9i中，若使用undo tablespace则oracle自动管理回滚段，通过设置 undo_retention 可在undo tablespace中保留这么长时间的数据，这样可以避免 snapshot  too old 错误，同时也可以通过 flashback 而得到某个时间点之前的数据，但这里要强调的几点是：

1：  flashback 功能不支持DDL语句，如果已经truncate掉的数据是不能找回来的
2：看起来是这样的
oracle 每隔5分钟会将产生的 SCN 对应一个 TIME 做记录
也就是说通常只记录了SCN,但是每5分钟会记录 SCN and TIME
当采用 time 来做flashback 的时候就有可能产生偏差

请关注实验4 ！！！！

实验1：使用flashback 功能

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
               36501397

SQL> insert into tf values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_flashback.enable_at_system_change_number(36501397);

PL/SQL procedure successfully completed.

SQL> select * from tf;

      A
----------
      1
      2

实验2： flashback 不支持 DDL

接着上面的步骤
SQL> truncate table tf;

Table truncated.

SQL>  exec dbms_flashback.enable_at_system_change_number(36501397);

PL/SQL procedure successfully completed.

SQL>  select * from tf;
  select * from tf
            *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL>  exec dbms_flashback.disable;

实验3 ：  我们创建一个表，立即看看效果，所有过程在5分钟以内，很段的时间内完成的,我们将无法查询数据！

SQL> drop table tf;

Table dropped.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
               36509390

SQL> create table tf( a number);

Table created.

SQL> insert into tf values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
               36509490

SQL> insert into tf values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec  dbms_flashback.enable_at_system_change_number(36509390);

PL/SQL procedure successfully completed.

SQL> select * from tf;
select * from tf
            *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL>  exec  dbms_flashback.enable_at_system_change_number(36509490);

PL/SQL procedure successfully completed.

SQL> select * from tf;
select * from tf
            *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.

实验四：通过时间来做,我们仔细看下面的时间和数据的关系！

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

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:09:04 创建表之前的时间  time1

SQL> create table test1 (a number);

Table created.

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

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:09:20 创建表之后还没有插入数据的时间 time2

SQL> insert into test1 values(1);

1 row created.

SQL> commit;

Commit complete.

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

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:09:43 创建表之后5分钟内插入了1条记录 time3

SQL> insert into test1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_lock.sleep(300); 休眠5分钟

PL/SQL procedure successfully completed.

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

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:15:55 休眠后再插入数据 time4

SQL> insert into test1 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:09:04','yyyy-mm-dd hh24:mi:ss')); time1

PL/SQL procedure successfully completed.

SQL> select * from test1;
select * from test1
            *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.
SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:09:20','yyyy-mm-dd hh24:mi:ss')); time2

PL/SQL procedure successfully completed.

SQL> select * from test1;

no rows selected

SQL>  exec  dbms_flashback.disable;

SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:09:43','yyyy-mm-dd hh24:mi:ss')); time3

PL/SQL procedure successfully completed.

SQL> select * from test1;

no rows selected

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:15:55','yyyy-mm-dd hh24:mi:ss')); time4

PL/SQL procedure successfully completed.

SQL> select * from test1;

      A
----------
      1
      2

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.