admin管理员组文章数量:1029185
Oracle中删除的列数据可以进行恢复么？
有朋友提出闪回可以恢复删除的列（包括数据)，这个可行么？
实践是检验真理的唯一标准，创建一张测试表，
代码语言：javascript代码运行次数：0运行复制CREATE TABLE t_flash_01 (id NUMBER, c1 varchar2(1), c2 varchar2(1));含三个列字段，
我们删除c2列，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 DROP COLUMN c2;提示不能闪回，
代码语言：javascript代码运行次数：0运行复制flashback TABLE t_flash_01 TO timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*SQL 错误 [8189] [72000]: ORA-08189: 因为未启用行移动功能, 不能闪回表*/这是因为需要表打开行移动功能，才能提供闪回，执行如下操作，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 enable ROW movement;再次闪回表，当前提示的是"无法读取数据 - 表定义已更改"，说明如果表出现了定义改变，例如增列、删列等，就无法通过闪回进行表的恢复，原因就是闪回操作利用的是UNDO，但是drop column是个DDL操作，直接提交，不会记录到UNDO中，因此不能通过flashback table操作将表恢复到列删除前的状态，
代码语言：javascript代码运行次数：0运行复制flashback TABLE t_flash_01 TO timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*SQL 错误 [1466] [72000]: ORA-01466: 无法读取数据 - 表定义已更改*/但是能通过闪回查询，检索到之前未删除列的历史数据，
代码语言：javascript代码运行次数：0运行复制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参数，即仅标记列为未使用，数据块保留原值，这就给数据恢复提供了可能，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 SET unused COLUMN c2;我们可以通过LogMiner，挖掘日志的方式，实现对结构和数据的恢复，过程还是有些复杂的。
1. 为了测试简单些，我们的恢复场景，选择的是UNUSED删除列操作后立即采用Redo进行恢复，
代码语言：javascript代码运行次数：0运行复制exec dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);2. 增加Redo日志文件，
代码语言：javascript代码运行次数：0运行复制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，
代码语言：javascript代码运行次数：0运行复制exec dbms_logmnr.start_logmnr(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);4. 通过v$logmnr_contents，可以找到日志中存储的操作数据，
代码语言：javascript代码运行次数：0运行复制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 DDL5. 为了得到删除列的原始数据，根据v$logmnr_contents的sql_undo，可以得到delete删除数据的操作，据此反向解析出插入语句，再插入到原表，间接完成列字段的数据恢复操作，
代码语言：javascript代码运行次数：0运行复制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，
代码语言：javascript代码运行次数：0运行复制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还是很强大，经过几轮调整，得到如下语句，
代码语言：javascript代码运行次数：0运行复制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. 重建刚才删除的列字段，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 ADD c2 varchar2(1);9. 通过执行以下SQL，将重建的列字段数据进行恢复，
代码语言：javascript代码运行次数：0运行复制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';以上都是为了进行测试说明的，相对比较简单，如果真实环境，可能更加混乱，因此还是需要谨慎对待删除操作，当然，如果数据库进行了备份，可以通过Rman和Data Pump等形式进行恢复，会更简单一些。
关于LogMiner的DBMS_LOGMNR_D和DBMS_LOGMNR可以检索《PL/SQL Packages and Types Reference》，
oracle/doc/oracle-database_19_20200601/content/arpls/DBMS_LOGMNR.html#GUID-160E5CF9-3430-4B45-9D2A-94BAFC5E68B0
通过以上测试，我们了解到如何通过LogMiner恢复标记UNUSED删除列的数据，过程还是比较繁琐的，因此，我们要更加意识，对生产系统的任何操作，都需要严谨评估和执行，做好相应备份，方能在出现问题的时候有所应对。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。原始发表：2025-04-21，如有侵权请联系 cloudcommunity@tencent 删除flash测试日志数据oracleOracle中删除的列数据可以进行恢复么？
有朋友提出闪回可以恢复删除的列（包括数据)，这个可行么？
实践是检验真理的唯一标准，创建一张测试表，
代码语言：javascript代码运行次数：0运行复制CREATE TABLE t_flash_01 (id NUMBER, c1 varchar2(1), c2 varchar2(1));含三个列字段，
我们删除c2列，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 DROP COLUMN c2;提示不能闪回，
代码语言：javascript代码运行次数：0运行复制flashback TABLE t_flash_01 TO timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*SQL 错误 [8189] [72000]: ORA-08189: 因为未启用行移动功能, 不能闪回表*/这是因为需要表打开行移动功能，才能提供闪回，执行如下操作，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 enable ROW movement;再次闪回表，当前提示的是"无法读取数据 - 表定义已更改"，说明如果表出现了定义改变，例如增列、删列等，就无法通过闪回进行表的恢复，原因就是闪回操作利用的是UNDO，但是drop column是个DDL操作，直接提交，不会记录到UNDO中，因此不能通过flashback table操作将表恢复到列删除前的状态，
代码语言：javascript代码运行次数：0运行复制flashback TABLE t_flash_01 TO timestamp (SYSTIMESTAMP - INTERVAL '1' MINUTE);
/*SQL 错误 [1466] [72000]: ORA-01466: 无法读取数据 - 表定义已更改*/但是能通过闪回查询，检索到之前未删除列的历史数据，
代码语言：javascript代码运行次数：0运行复制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参数，即仅标记列为未使用，数据块保留原值，这就给数据恢复提供了可能，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 SET unused COLUMN c2;我们可以通过LogMiner，挖掘日志的方式，实现对结构和数据的恢复，过程还是有些复杂的。
1. 为了测试简单些，我们的恢复场景，选择的是UNUSED删除列操作后立即采用Redo进行恢复，
代码语言：javascript代码运行次数：0运行复制exec dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);2. 增加Redo日志文件，
代码语言：javascript代码运行次数：0运行复制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，
代码语言：javascript代码运行次数：0运行复制exec dbms_logmnr.start_logmnr(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);4. 通过v$logmnr_contents，可以找到日志中存储的操作数据，
代码语言：javascript代码运行次数：0运行复制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 DDL5. 为了得到删除列的原始数据，根据v$logmnr_contents的sql_undo，可以得到delete删除数据的操作，据此反向解析出插入语句，再插入到原表，间接完成列字段的数据恢复操作，
代码语言：javascript代码运行次数：0运行复制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，
代码语言：javascript代码运行次数：0运行复制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还是很强大，经过几轮调整，得到如下语句，
代码语言：javascript代码运行次数：0运行复制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. 重建刚才删除的列字段，
代码语言：javascript代码运行次数：0运行复制ALTER TABLE t_flash_01 ADD c2 varchar2(1);9. 通过执行以下SQL，将重建的列字段数据进行恢复，
代码语言：javascript代码运行次数：0运行复制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';以上都是为了进行测试说明的，相对比较简单，如果真实环境，可能更加混乱，因此还是需要谨慎对待删除操作，当然，如果数据库进行了备份，可以通过Rman和Data Pump等形式进行恢复，会更简单一些。
关于LogMiner的DBMS_LOGMNR_D和DBMS_LOGMNR可以检索《PL/SQL Packages and Types Reference》，
oracle/doc/oracle-database_19_20200601/content/arpls/DBMS_LOGMNR.html#GUID-160E5CF9-3430-4B45-9D2A-94BAFC5E68B0
通过以上测试，我们了解到如何通过LogMiner恢复标记UNUSED删除列的数据，过程还是比较繁琐的，因此，我们要更加意识，对生产系统的任何操作，都需要严谨评估和执行，做好相应备份，方能在出现问题的时候有所应对。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。原始发表：2025-04-21，如有侵权请联系 cloudcommunity@tencent 删除flash测试日志数据oracle本文标签： Oracle中删除的列数据可以进行恢复么
版权声明：本文标题：Oracle中删除的列数据可以进行恢复么？ 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1747576213a2180507.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论