一条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种方案,由上到下依次恢复成本增加,相关经验可以参考借鉴。

更多精彩内容,关注我们▼▼

一条SQL更新了整个表,如何补救?插图

为您推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注