数据库恢复实例五:处理用户错误FLASHBACK篇

这里的用户指的非DBA用户,用户错语有两种:误删表,误数据

用户错误如下:

oak用户下的test1表由于失误删除,请求恢复

SQL>  select count(*) from test;

 select count(*) from test

                      *

ERROR at line 1:

ORA-00942: table or view does not exist

解决方法:

1.确认删除的表在recyclebin中还存在

SQL> select owner, original_name ,type ,droptime from dba_recyclebin

  2  where wner='OAK';

OWNER ORIGINAL_N TYPE  DROPTIME

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

OAK   TEST      TABLE 2008-12-19:10:59:33

2.使用FlashBack Table闪回表

SQL> flashback table oak.test to before drop;

你可以在闪回时重命名表如:

flashback table oak.test to before drop rename to test1;

3.确认数据已恢复

SQL> select count(*) from test;

  COUNT(*)

----------

    109394

用户错误如下:

下删除语句时忘记where条件,且已提交

1. 结合用户给出的时间确认删除点

SQL>select distinct(start_scn) ,start_timestamp ,table_name ,operation from flashback_transaction_query where table_owner='OAK';

START_SCN   START_TIMESTAMP        TABLE_NAME   OPERATION

24119576634  2008/12/19 下午 01:39:46     TEST                DELETE

确认SCN为 24119423572

如果通过上的SQL找不出SCN,那就使用timestamp

如:

select count(*) from test as of timestamp (to_timestamp('2008-12-20 15:20:00','yyyy-mm-dd hh24:mi:ss'));

2.恢复数据

SQL> insert into test select * from test as of scn 24119576634;

109466 rows created.

commit;

如果FlashBack不能解决用户错误,你可以expdp备份或logminer来恢复数据