昨晚一条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。
• 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 a
AS 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 a
AS 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);


查询分析结果，可以按照表明 like
select 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 script
export ORACLE_SID=orcl
rman target / log=/home/oracle/restore.log <<EOF
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;
}
EOF
exit


/*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

三、总结

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

作者丨路在脚下

来源丨公众号:木讷大叔爱运维（ID:man8er）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

直播预告丨三位大数据专家齐聚，探讨实时计算、数据湖、数据治理、平台化建设与实践

时随着企业业务规模的不断扩大，建设兼顾效率和质量的大数据体系成为了业界的共同课题。为此，dbaplus社群携手爱奇艺三位大数据专家，围绕“爱奇艺复杂场景下的大数据体系建设与实践”这一主题开展线上直播分享，针对实时计算、数据湖、数据治理、平台化建设等议题进行深入探讨，给大家提供企业级大数据体系建设管理经验参考。

• 观看方式：线上直播间
• 直播日期：2023年8月18日（周五）
• 直播时间：14:30-17:00
• 直播地址：爱奇艺复杂场景下的大数据体系建设与实践