oracle开发人员误删表的操作及恢复

记一次生产库上开发人员误删表的事故，某数据库开发人员用存储过程想删除某用户下的所有SC_A开头的表，具体操作如下：

1、误删除表的sql及原因

这里以删除的条件 UPPER(REPLACE('SC_', '_', '_A')) ||'%' 来查询貌似是正常的SC_A% 没有问题

 

 

但这里like查询所有符合SC_A开头的表，就会有问题了，多了一个SCAAA的表出来，因为”like  SC_A%” 中的”_”也是一个替代符，代替一个字符了，所以SCAAA这个表被误查询出来，也就被误删除了。

 

2、下面演示一下误删除表的操作

1) 下面是开发人员的存储过程(这里测试只截取了误删除表的操作)，循环删除当前用户(这里用scott用户进行测试) 的SC_A开头的所有表

 

2) 执行存储过程

 

3) 再次查询，发现SCAAA和SC_AAA两个表都被删除了

 

 

3、 误删除表的恢复过程

1) 查看回收站，查找被误删的表

 

进行闪回操作，把误删的表闪回到删除之前

SQL> flashback table scott.scaaa to before drop;

Flashback complete.

再次查询scaaa表已经被已经成功闪回，可以使用了

 

 

2) 如果没有启动回收站，可以使用logmnr找出drop的时间和scn号

添加日志，添加当时进行删除操作的时间相应的在线日志或者归档日志(添加第一个日志需要dbms_logmnr.new)
SQL>exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_21_906569321.dbf',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed

SQL>  exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_22_906569321.dbf',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed


开始分析
SQL>exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed

 
查询logmnr分析后的内容(删除的sql语句可以查询出来)
SQL>select scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like'drop%';

SCN   TIMESTAMP     SQL_REDO
----  ------       -------------------
293893 2016/3/16 1  drop table SCAAA AS "BIN$LiPJzHgpwlngU6j9qMAdIg==$0" ;
293901 2016/3/16 1  drop table SC_AAA AS "BIN$LiPJzHgqwlngU6j9qMAdIg==$0" ;

可以针对scn或者时间使用备份进行恢复操作，这里不再做具体说明，可依据备份的具体情况进行恢复。