今天中午同事误操作了一个动作,导致生产环境的550多条的业务数据丢失,折腾了10-15分钟把数据给恢复了。
晚上回到酒店在本机稍微小结和还原下操作过程,在此记录下。
- Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as crmii
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table flashback_table_test1 (id number(4),name varchar2(10));
Table created
SQL> create table flashback_table_test2 as select * from flashback_table_test1 where 1=0;
Table created
SQL> insert into flashback_table_test1 values(1,'张三');
1 row inserted
SQL> insert into flashback_table_test1 values(2,'李四');
1 row inserted
SQL> insert into flashback_table_test1 values(3,'王五');
1 row inserted
SQL> insert into flashback_table_test2 select * from flashback_table_test1;
3 rows inserted
SQL> commit;
Commit complete
SQL> select sysdate from dual;
SYSDATE
-----------
2010/3/29 2
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS1
SQL>
SQL> select * from flashback_table_test1;
ID NAME
----- ----------
1 张三
2 李四
3 王五
SQL> select * from flashback_table_test2;
ID NAME
----- ----------
1 张三
2 李四
3 王五
SQL> - 1、DELETE情况下的恢复
- SQL> delete from flashback_table_test1;
3 rows deleted
SQL> commit;
Commit complete
SQL> select * from flashback_table_test1;
ID NAME
----- ----------
SQL> select * from flashback_table_test1 as of timestamp (sysdate-3/1440);
ID NAME
----- ----------
1 张三
2 李四
3 王五
SQL> insert into flashback_table_test1 select * from flashback_table_test1 as of timestamp (sysdate-3/1440);
3 rows inserted
SQL> commit;
Commit complete
SQL> select * from flashback_table_test1;
ID NAME
----- ----------
1 张三
2 李四
3 王五
SQL> - 数据成功恢复。
- 2、Truncate情况下的恢复
- SQL> truncate table flashback_table_test2;
Table truncated
SQL> select * from flashback_table_test2 as of timestamp (sysdate-3/1440);
select * from flashback_table_test2 as of timestamp (sysdate-3/1440)
ORA-01466: 无法读取数据 - 表定义已更改
SQL> select * from flashback_table_test2 as of timestamp (sysdate-2/1440);
select * from flashback_table_test2 as of timestamp (sysdate-2/1440)
ORA-01466: 无法读取数据 - 表定义已更改
SQL> select * from flashback_table_test2 as of timestamp (sysdate-1/1440);
select * from flashback_table_test2 as of timestamp (sysdate-1/1440)
ORA-01466: 无法读取数据 - 表定义已更改
SQL> flashback table flashback_table_test2 to before drop rename to test2_r;
Done
SQL> select * from test2_r;
ID NAME
---------- --------------------
2 test 2
SQL> drop table flashback_table_test2;
Table dropped
SQL> flashback table flashback_table_test2 to before drop rename to test2_r_2;
Done
SQL> select * from test2_r_2;
ID NAME
----- ----------
SQL> - 恢复以失败告终;
- 3、Drop情况下恢复
- SQL> select * from flashback_table_test1;
- ID NAME
- ----- ----------
- 1 张三
- 2 李四
- 3 王五
- SQL> drop table flashback_table_test1;
- Table dropped
- SQL> select * from flashback_table_test1;
- select * from flashback_table_test1
- ORA-00942: 表或视图不存在
- SQL> flashback table flashback_table_test1 to before drop to rename flashback_table_test1_r;
- flashback table flashback_table_test1 to before drop to rename flashback_table_test1_r
- ORA-00933: SQL 命令未正确结束
- SQL> flashback table flashback_table_test1 to before drop rename to flashback_table_test1_r;
- Done
- SQL> select * from flashback_table_test1_r;
- ID NAME
- ----- ----------
- 1 张三
- 2 李四
- 3 王五
- SQL>
数据也成功恢复。
初步总结:
1、正常的Delete情况下是可以完全恢复数据的;
2、Truncate情况下使用普通的Flashback似乎无能为了;
3、Drop情况下也是可以恢复数据的;
4、有修改过表结构的情况下不能使用正常的查找SCN和timestamp来操作了,建议的操作是使用flashback复制到临时表;
5、遇到数据被误删除、表误删除的情况下,视情况以最快的数据停掉前段服务,停止任何数据操作,避免UNDO空间被覆盖、循环覆盖写。
才学疏浅,错误之处或看官有更好的建议和总结请多指教。
-The End-
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12215378/viewspace-1032627/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12215378/viewspace-1032627/