昨晚一条SQL更新了整个表，吓得我瑟瑟发抖……

路在脚下 2023-08-11 13:53:05

一、背景

在系统运营中，经常会有一些业务变更，需要直接更新表中的一些数据。最近我们遇到了一件棘手的生产事件，开发同事本来要更新表中的很少一部分数据，但是由于UPDATE SQL编写问题，将整个表的一个字段进行了更新。SQL大概是长这样的：

UPDATE tab_order a set a.status = '01' WHEREEXISTS (   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。
tab_order 有1亿行数据，表大小30G。
undo表空间30G左右。
更新动作执行了40分钟。

二、处理方案

处理误更新数据我们有几种方式：

1.事务未结束，直接rollback

Oracle的事务是手动提交的，可能有挽回余地，因为修改的数据量较多，回滚时间会比较长。其它类型数据库大部分是自动提交的，我们可以通过显示打开事务的方式进行操作。

SQL> begin;  /* Mysql等在自动提交模式下可以使用begin的方式打开事务，Oracle不需要*/SQL> UPDATE tab_order a set a.status = '01' ... ...;SQL> rollback;

2.使用闪回查询

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

SQL>  SELECT * FROM tab_order aAS OF TIMESTAMP TO_TIMESTAMP('2023-03-27 21:10:00', 'YYYY-MM-DD HH24:MI:SS'); 
/* 查询的时间应该位于 update之前的最近时刻，如果undo信息被覆盖了，会报错ORA-01555，则无法使用该方法恢复*/
/*如果闪回查询可以查到数据,可以新建一个表用来存储历史数据，进行恢复*/SQL> CREATE TABLE tab_old as SELECT * FROM tab_order aAS OF TIMESTAMP TO_TIMESTAMP('2023-03-27 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);
查看你有哪些归档日志加入了列表中select LOW_TIME,HIGH_TIME,LOW_SCN,NEXT_SCN from v$logmnr_logs;
分析日志execute dbms_logmnr.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
查询分析结果，可以按照表明 likeselect username,sql_redo,sql_undo from v$logmnr_contents where operation='UPDATE' and sql_redo like '%tab_order a%';

Mysql 的binlog 中记录了新旧值，需要通过工具生成回滚SQL。

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

本次恢复由于几个方面因素不满足，我们使用了最终的异机备份恢复的方案。异机恢复过程中，涉及的表空间较多，为了快速恢复表，我采取了部分恢复的方式，加速了表导出的过程。

/* 1.编辑pfile */*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl'*.db_block_size=8192*.db_name='orcl'*.diagnostic_dest='/u01/app/oracle'*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/arch/orcl'*.log_archive_format='%t_%s_%r.dbf'*.pga_aggregate_target=10G*.sga_target=20G*.undo_tablespace='UNDOTBS1'
/* 2.从nbu带库中恢复控制文件 */set DBID=3717431565;run{allocate channel ch00 type 'SBT_TAPE';send 'nb_ora_serv=nbu01pri01bak01';send 'nb_ora_client=racdb01';restore controlfile from 'cntrl_214631_1_1131658062';release channel ch00;} 
/* 3.编辑脚本进行部分表空间恢复 */#!/bin/bash#this is rman auto full backup scriptexport ORACLE_SID=orclrman target / log=/home/oracle/restore.log <run {ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';send 'nb_ora_serv=nbu01pri01bak01';send 'nb_ora_client=racdb01';set newname for database to '/u01/app/oracle/oradata/crm/%b';set until time "to_date('2023-03-27 21:10:00','yyyy-mm-dd hh24:mi:ss')"; restore tablespace system,sysaux,UNDOTBS1,UNDOTBS2,TBS_ORCL; /*仅恢复和表有关的表空间 */switch datafile all;recover database skip tablespace TBS_HB,TBS_AUDIT,TBS_OGG,USERS; /*跳过其它表空间的恢复*/RELEASE CHANNEL CH1;RELEASE CHANNEL CH2;}EOFexit
/*4.恢复完使用exp导出*/
$ exp "'/ as sysdba'" table=userA.tab_order file=tab_order.dmp/* 恢复时可以新建一个用户 */$ imp "'/ as sysdba'" file=tab_order.dmp fromuser=userA touser=userB/* 然后根据恢复的表把原来的值UPDATE回来  */SQL> UPDATE userA.tab_order a , userB.tab_order b      SET a.status = b.tab_order WHERE a.id=b.id