Oracle中删除的列数据可以进行恢复么？

bisal的个人杂货铺 2025-04-21

161

点击标题下「蓝色微信名」可快速关注

技术群的朋友问了个问题，Oralce数据库误删除了一列，还可以恢复么？

有朋友提出闪回可以恢复删除的列（包括数据)，这个可行么？

实践是检验真理的唯一标准，创建一张测试表，

CREATE TABLE t_flash_01 (id NUMBER, c1 varchar2(1), c2 varchar2(1));

含三个列字段，

我们删除c2列，

ALTER TABLE t_flash_01 DROP COLUMN c2;

提示不能闪回，

flashback TABLE t_flash_01 TO timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*SQL 错误 [8189] [72000]: ORA-08189: 因为未启用行移动功能, 不能闪回表*/

这是因为需要表打开行移动功能，才能提供闪回，执行如下操作，

ALTER TABLE t_flash_01 enable ROW movement;

再次闪回表，当前提示的是"无法读取数据 - 表定义已更改"，说明如果表出现了定义改变，例如增列、删列等，就无法通过闪回进行表的恢复，原因就是闪回操作利用的是UNDO，但是drop column是个DDL操作，直接提交，不会记录到UNDO中，因此不能通过flashback table操作将表恢复到列删除前的状态，

flashback TABLE t_flash_01 TO timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*SQL 错误 [1466] [72000]: ORA-01466: 无法读取数据 - 表定义已更改*/

但是能通过闪回查询，检索到之前未删除列的历史数据，

SELECT * FROM t_flash_01 AS OF timestamp (systimestamp - INTERVAL '1' minute);

drop column操作会直接修改数据字典，并可能物理重组数据块，但其实这儿有很多的细节，如果drop column的是表定义的最后一列，有可能进行恢复，但如果drop column的是表定义的中间某列，后面的列是会覆盖删除的列定义，相当于这列实际被抹掉了，原则上很难恢复，如果还是要恢复，就需要通过一系列递归SQL的反向执行，来尝试进行恢复。但无论采用什么方法恢复，只对表结构进行了恢复，drop列的数据是无法进行恢复的。

因此，drop column操作还是比较单向的，恢复成本较高，执行前需要三思。

那么，针对drop column的场景应该怎么做更好？

可以在删除列的操作中增加UNUSED参数，即仅标记列为未使用，数据块保留原值，这就给数据恢复提供了可能，

ALTER TABLE t_flash_01 SET unused COLUMN c2;

我们可以通过LogMiner，挖掘日志的方式，实现对结构和数据的恢复，过程还是有些复杂的。

1. 为了测试简单些，我们的恢复场景，选择的是UNUSED删除列操作后立即采用Redo进行恢复，

exec dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

2. 增加Redo日志文件，

begin
  dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/oradata/BISALCDB/redo01.log', options=>dbms_logmnr.new);  
  end;
/


begin
  dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/oradata/BISALCDB/redo02.log', options=>dbms_logmnr.addfile);  
  end;
/


begin
  dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/oradata/BISALCDB/redo03.log', options=>dbms_logmnr.addfile);  
  end;
/

3. 启动LogMiner，

exec dbms_logmnr.start_logmnr(OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

4. 通过v$logmnr_contents，可以找到日志中存储的操作数据，

SQL> select sql_redo, row_id, sql_undo, operation from v$logmnr_contents where table_name = 'T_FLASH_02';
SQL_REDO                                                     ROW_ID             SQL_UNDO                                                     OPERATION
------------------------------------------------------------ ------------------ ------------------------------------------------------------ --------------------------------
ALTER TABLE "BISAL"."T_FLASH_01" RENAME TO "BIN$MpH7wp5LTbDg AAAAAAAAAAAAAAAAAB                                                              DDL
ZQAAAAAAAQ==$0" ;


DROP TABLE t_flash_01 AS "BIN$MpH7wp5LTbDgZQAAAAAAAQ==$0" ;  AAAAAAAAAAAAAAAAAB                                                              DDL


CREATE TABLE t_flash_01 (id NUMBER, c1 varchar2(1), c2 varch AAAAAAAAAAAAAAAAAB                                                              DDL
ar2(1));


insert into "BISAL"."T_FLASH_01"("COL 1","COL 2","COL 3") va AAAR7ZAAMAAACqNAAA delete from "BISAL"."T_FLASH_01" where "COL 1" = HEXTORAW('c INSERT
lues (HEXTORAW('c102'),HEXTORAW('61'),HEXTORAW('61'));                          102') and "COL 2" = HEXTORAW('61') and "COL 3" = HEXTORAW('6
                                                                                1') and ROWID = 'AAAR7ZAAMAAACqNAAA';
insert into "BISAL"."T_FLASH_01"("COL 1","COL 2","COL 3") va AAAR7ZAAMAAACqNAAB delete from "BISAL"."T_FLASH_01" where "COL 1" = HEXTORAW('c INSERT
lues (HEXTORAW('c103'),HEXTORAW('62'),HEXTORAW('62'));                          103') and "COL 2" = HEXTORAW('62') and "COL 3" = HEXTORAW('6
                                                                                2') and ROWID = 'AAAR7ZAAMAAACqNAAB';
insert into "BISAL"."T_FLASH_01"("COL 1","COL 2","COL 3") va AAAR7ZAAMAAACqNAAC delete from "BISAL"."T_FLASH_01" where "COL 1" = HEXTORAW('c INSERT
lues (HEXTORAW('c104'),HEXTORAW('63'),HEXTORAW('63'));                          104') and "COL 2" = HEXTORAW('63') and "COL 3" = HEXTORAW('6
                                                                                3') and ROWID = 'AAAR7ZAAMAAACqNAAC';
ALTER TABLE t_flash_01 enable ROW movement;                  AAAAAAAAAAAAAAAAAB                                                              DDL


ALTER TABLE t_flash_01 SET unused COLUMN c2;                 AAAAAAAAAAAAAAAAAB                                                              DDL

5. 为了得到删除列的原始数据，根据v$logmnr_contents的sql_undo，可以得到delete删除数据的操作，据此反向解析出插入语句，再插入到原表，间接完成列字段的数据恢复操作，

SQL> create table t_flash as select sql_undo from v$logmnr_contents where table_name = 'T_FLASH_01' and operation = 'INSERT';
Table created.

6. t_flash中存储的就是所有的列数据删除SQL，

SQL>select*from t_flash;
SQL_UNDO
------------------------------------------------------------
deletefrom "BISAL"."T_FLASH_01" where "COL 1" = HEXTORAW('c
102') and "COL 2" = HEXTORAW('61') and "COL 3" = HEXTORAW('6
1') and ROWID ='AAAR7ZAAMAAACqNAAA';


deletefrom "BISAL"."T_FLASH_01" where "COL 1" = HEXTORAW('c
103') and "COL 2" = HEXTORAW('62') and "COL 3" = HEXTORAW('6
2') and ROWID ='AAAR7ZAAMAAACqNAAB';


deletefrom "BISAL"."T_FLASH_01" where "COL 1" = HEXTORAW('c
104') and "COL 2" = HEXTORAW('63') and "COL 3" = HEXTORAW('6
3') and ROWID ='AAAR7ZAAMAAACqNAAC';

7. 观察每条delete语句，为了得到原始的数据，只需要得到ROWID和COL3列数据，因为rowid是行的物理位置，因此根据rowid直接更新col3，即可完成插入。例如第一条中的ROWID='AAAR7ZAAMAAACqNAAA'和COL3=HEXTORAW('61')，然后拼接出更新表的语句，即"update t_flash_01 set c2 = ... where rowid = ..."，DS还是很强大，经过几轮调整，得到如下语句，

select 'update t_flash_01 set c2=' 
       || UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(REGEXP_SUBSTR(sql_undo, '"COL 3" = HEXTORAW\(''([^'']+)''\)', 1, 1, NULL, 1))) 
       || '' where rowid = ' 
       || REGEXP_SUBSTR(sql_undo, 'ROWID = ''([^'']+)''', 1, 1, NULL, 1) || '';'
from t_flash;
------------------------------------------------------------------------
update t_flash_01 set c2='a' where rowid = 'AAAR7ZAAMAAACqNAAA';
update t_flash_01 set c2='b' where rowid = 'AAAR7ZAAMAAACqNAAB';
update t_flash_01 set c2='c' where rowid = 'AAAR7ZAAMAAACqNAAC';

8. 重建刚才删除的列字段，

ALTER TABLE t_flash_01 ADD c2 varchar2(1);

9. 通过执行以下SQL，将重建的列字段数据进行恢复，

update t_flash_01 set c2='a' where rowid = 'AAAR7ZAAMAAACqNAAA';
update t_flash_01 set c2='b' where rowid = 'AAAR7ZAAMAAACqNAAB';
update t_flash_01 set c2='c' where rowid = 'AAAR7ZAAMAAACqNAAC';