一条SQL更新了整个表，如何回退

背景：

我们的一些业务测试系统，数据库一般也是由开发同事自行维护，所以不可避免会有一些问题，经常会有开发同事火急火燎的打电话给我们，如果语气急切，态度恭谨，一般就是误操作数据了（八九不离十）。

最近我们就遇到了一起误更新数据的事件。

由于update SQL编写问题，开发同事将整个表的一个字段进行了更新。

SQL：

UPDATE tab_order a set a.status = '01'
WHERE
EXISTS (
SELECT
order_id
FROM
tab_tmp b,
tab_order c
WHERE
b.cust_no = c.cust_no
AND b.state = 3):

 

开发同事本意是希望根据tab_tmp b表的字段更新tab_order a 表的字段status值为’01’，但是由于exists里面不应该再次出现tab_order c导致子查询恒为真，全表的字段被更新。

处理方案

不同的数据库处理方式不同，但整体思路大同小异，本次事件发生在Oracle数据库上，以Oracle为例，处理误更新数据我们有几种方式：

1.事务未结束，直接rollback

Oracle数据库事务是手动提交的，如果还没有提交可能有挽回余地，但数据量比较大的话回滚时间会比较长。其它类型数据库大部分是自动提交的，因此更新数据前稳妥起见，我们可以通过显示打开事务的方式进行操作。 以Mysql为例，在自动提交模式下可以使用begin的方式打开事务：

SQL> begin;
SQL> UPDATE tab_order a set a.status = '01';
SQL> rollback;

2.使用闪回查询

如果事务已经提交了，当数据库undo表空间足够，undo_retention保留时间足够长，是可能会查到修改前的原数据的。那么可以优先考虑闪回查询恢复。

SQL> show parameter unodNAME            TYPE        VALUE
---------------------------------
undo_management string      AUTO
undo_retrntion  intrger     900
undo_tablespace string      UNDOTBS1
/* 默认undo_retention为900s

SQL> SELECT * FROM tab_order a

 

AS OF TIMESTAMP TO_TIMESTAMP('2023-04-20 21:10:00', 'YYYY-MM-DD HH24:MI:SS');
/* 查询的时间应该位于 update之前的最近时刻，如果undo信息被覆盖了，会报错ORA-01555，则无法使用该方法恢复*/

/*如果闪回查询可以查到数据,可以新建一个表用来存储历史数据，进行恢复*/
SQL> CREATE TABLE tab_old as SELECT * FROM tab_order a
AS OF TIMESTAMP TO_TIMESTAMP('2023-04-20 21:10:00', 'YYYY-MM-DD HH24:MI:SS');

除了Oracle，tidb、oceanbase等数据库也提供了这个功能。

3.使用LogMinner挖掘日志，执行undo sql恢复

如果报错ORA-01555，意味着数据已经过期，闪回查询无法查询到数据。如果能准确知道修改了哪些数据的情况下，可以优先考虑LogMinner恢复原数据。LogMinner主要依托于挖掘DML SQL执行期间生成的redo log中的原值来恢复数据，在LogMinner挖掘后日志后会看到 undo sql（回滚sql），可以用来直接恢复数据。

加入日志，如果不确定时间就多家几组日志
execute dbms_logmnr.add_logfile('/opt/oracle/archive/1_85_782895629.dbf',dbms_logmnr.new);
分析日志
execute dbms_logmnr.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);

查询分析结果，可以按照表明 like
select username,sql_redo,sql_undo from v$logmnr_contents where operation='UPDATE' and
sql_redo like '%tab_order a%';

Mysql 的binlog 中记录了新旧值，需要通过工具生成回滚SQL，SQLServer也有类似的工具。4.使用expdp/impdp的方式恢复如果有逻辑备份且表数据未发生变动，可以考虑使用逻辑备份恢复。逻辑备份使用起来比较灵活，不过只能恢复到备份那一刻的数据，不适用数据变动频繁的业务表。

 

4.使用备份恢复异机恢复表，导出、导入恢复

本次恢复由于几个方面因素不满足，我们使用了最终的异机备份恢复的方案。

1、恢复参数文件。
设置环境变量：export ORACLE_SID=xxx
登录RMAN:rman target /
在RMAN里把数据库起到nomount状态：RMAN>  startup nomount;
设置DBID:RMAN>  set dbid=3931082997
恢复spfile：RMAN>  restore spfile from '/backup/test/c-3931082997-20131204-02';
关闭数据库：RMAN>  shutdown immediate;
把数据库起到nomount状态：RMAN>  startup nomount;
2、恢复控制文件。RMAN>  restore controlfile from '/backup/test/ctl_HNCDFHQ_20131204_21_1';
把数据库启动到mount状态：RMAN>  alter database mount;
3、恢复数据文件：
如果备份不在备份时所在的目录，在新的目录。可以用此命令注册到控制文件。RMAN>  catalog start with '/oradata1/backup';restore数据库：RMAN>  run{allocate channel c1 type disk ;allocate channel c2 type disk ;restore database ;
release channel ch1;release channel ch2;}

recover 数据库：RMAN>  recover database;SQL>  recover database until cancel using backup controlfile ;
--选择redo的绝对路径，一个一个试，有多少组redo，就试多少次SQL>  alter database open resetlogs;
/*4.恢复完使用expdp导出*/
$ expdp "'/ as sysdba'" table=userA.tab_order dumpfilefile=tab_order.dmp directory=dumpdir/* 恢复时可以新建一个用户 */$ impdp "'/ as sysdba'" dumpfile=tab_order.dmp directory=dumpdir remap_schema=userA:userB/* 然后根据恢复的表把原来的值UPDATE回来  */SQL> UPDATE userA.tab_order a , userB.tab_order b set a.status = b.tab_order where a.id=b.id 

总结上面的4种方案，由上到下依次恢复成本增加，相关经验可以参考借鉴。