暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / 深入解析 oracle drop table到底做了哪些变更？

深入解析 oracle drop table到底做了哪些变更？

原创 惠星星 云和恩墨技术服务团队 2021-01-26
2951

1. 内容概述

  oracle drop table操作后，如果未开启回收站且无有效备份，信息系统面临业务数据丢失的风险，
本文主要使用oracle dump、bbed、logmgr等多种工具解析drop table实现原理，使用多种方法实
现drop table操作后业务数据特殊恢复。欢迎关注公众号“ 数据库技术笔记”留言讨论。

复制

2.环境准备

create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
drop tablespace star including contents and datafiles;
create tablespace hsql datafile '/data2/enmo/hsql01.dbf' size 10M autoextend off;
drop table hsql.drop_1 purge;
create table hsql.drop_1(c_char1 char(10),c_char2 char(10)) tablespace hsql;


begin
for i in 1 .. 100000 loop
insert into hsql.drop_1 values(i,'orastar');
end loop;
commit;
end;
/

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1;

复制

3. 信息收集

set linesize 200 pagesize 200
col owner for a10
col segment_name for a10
select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='DROP_1';

OWNER	   SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- ---------- ----------- ------------ ------------------
HSQL	   DROP_1		5	   130 TABLE


set linesize 200 pagesize 9999
col owner for a10
col object_name for a20
select owner,object_name,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='DROP_1';

OWNER	   OBJECT_NAME		 OBJECT_ID DATA_OBJECT_ID
---------- -------------------- ---------- --------------
HSQL	   DROP_1		     13807	    13807


set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a60
select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents 
where segment_name ='DROP_1' order by extent_id;

OWNER	   SEGMENT_NAME 						 EXTENT_ID    FILE_ID	BLOCK_ID     BLOCKS
---------- ------------------------------------------------------------ ---------- ---------- ---------- ----------
HSQL	   DROP_1								 0	    5	     128	  8
HSQL	   DROP_1								 1	    5	     136	  8
HSQL	   DROP_1								 2	    5	     144	  8
HSQL	   DROP_1								 3	    5	     152	  8
HSQL	   DROP_1								 4	    5	     160	  8
.....								
HSQL	   DROP_1								17	    5	     384	128
HSQL	   DROP_1								18	    5	     512	128

19 rows selected.

SQL>

复制

4.dump段头块信息

alter session set tracefile_identifier='orastar_hdrseg_before_drop';
oradebug setmypid
alter system dump datafile 5 block 130;
oradebug close_trace
oradebug tracefile_name

输出结果: /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_16826_orastar_hdrseg_before_drop.trc

Extent Map
  -----------------------------------------------------------------
   0x01400080  length: 8
   0x01400088  length: 8
   0x01400090  length: 8
   0x01400098  length: 8
   0x014000a0  length: 8
   0x014000a8  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01400080 Data dba:  0x01400083
   Extent 1     :  L1 dba:  0x01400080 Data dba:  0x01400088
   Extent 2     :  L1 dba:  0x01400090 Data dba:  0x01400091
   Extent 3     :  L1 dba:  0x01400090 Data dba:  0x01400098
   Extent 4     :  L1 dba:  0x014000a0 Data dba:  0x014000a1
   Extent 5     :  L1 dba:  0x014000a0 Data dba:  0x014000a8
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01400081

复制

5. dump L2 信息

alter session set tracefile_identifier='orastar_L2_before_drop';
oradebug setmypid
alter system dump datafile 5 block 129;
oradebug close_trace
oradebug tracefile_name

输出结果: /u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_16850_orastar_L2_before_drop.trc

  L1 Ranges :
  --------------------------------------------------------
    0x01400080  Free: 1 Inst: 1
   0x01400090  Free: 1 Inst: 1
   0x014000a0  Free: 5 Inst: 1

复制

6. 使用工具记录 L3/L2/f_L1/l_L1/data 物理二进制变化情况

L3: dba 5,130
L2: dba 5,129
f_L1: dba 5,128
l_L1: dba 5,160
data: dba 5,131

复制

7. 检查REDO LOGFILE

set line 200
col MEMBER for a50
COL IS_RECOVERY_DEST_FILE FOR A30
col ARCHIVED for a10
col file_name for a60
col STATUS for a10
select member from v$logfile;
select g.group#,g.thread#,g.SEQUENCE#,g.bytes,g.members,g.ARCHIVED,g.STATUS,member from v$log g,v$logfile f where g.group#=f.group#;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED   STATUS     MEMBER
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
	  1	    1	      43  104857600	     1 NO	    CURRENT    /data2/enmo/redo01a.log
	 2	    1	      41  104857600	     1 YES	  INACTIVE   /data2/enmo/redo02a.log
	 3	    1	      42  104857600	     1 YES	  INACTIVE   /data2/enmo/redo03a.log 

复制

8. drop table 操作

drop table hsql.drop_1 purge;
alter system archive log current;
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;

复制

9. 使用logmgr检查变更操作

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch1/1_25_1042492403.dbf', OPTIONS => DBMS_LOGMNR.NEW);

Step 4   Start LogMiner.   
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
              DBMS_LOGMNR.COMMITTED_DATA_ONLY);               
Step 5   Query the V$LOGMNR_CONTENTS view.
set linesize 200 pagesize 9999
col USR for a10
col sql_redo for a30
col SQL_UNDO for a30
col SEG_OWNER for a10
col SEG_NAME for a10
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,SEG_OWNER,SEG_NAME,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROM  
   V$LOGMNR_CONTENTS;
   --WHERE TABLE_NAME='FILE$' and OPERATION in ('DELETE','INSERT');

SQL> set linesize 200 pagesize 9999
col USR for a10
col sql_redo for a30
col SQL_UNDO for a30
col SEG_OWNER for a10
col SEG_NAME for a10
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,SEG_OWNER,SEG_NAME,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROMSQL> SQL> SQL> SQL> SQL> SQL>   
   V$LOGMNR_CONTENTS;  2  

T_TIME		    SEG_OWNER  SEG_NAME   USR	     SQL_REDO			    SQL_UNDO			   OPERATION
------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------------------------------
2020-05-28 04:28:30			  UNKNOWN    set transaction read write;				   START
2020-05-28 04:28:30 HSQL       DROP_1	  UNKNOWN    drop table hsql.drop_1;					   DDL
2020-05-28 04:28:30 SYS        COL$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
2020-05-28 04:28:30 SYS        COL$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
....
2020-05-28 04:28:30 SYS        TAB$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
2020-05-28 04:28:30 SYS        OBJ$	  UNKNOWN    delete from "SYS"."OBJ$" where insert into "SYS"."OBJ$"("OBJ# DELETE
                                						      "OBJ#" = '13822' and "DATAOBJ ","DATAOBJ#","OWNER#","NAME","
                                						     #" = '13822' and "OWNER#" = '3 NAMESPACE","SUBNAME","TYPE#","
                                						     3' and "NAME" = 'DROP_1' and " CTIME","MTIME","STIME","STATUS
                                						     NAMESPACE" = '1' and "SUBNAME" ","REMOTEOWNER","LINKNAME","FL
                                						      IS NULL and "TYPE#" = '2' and AGS","OID$","SPARE1","SPARE2",
                                						      "CTIME" = TO_DATE('28-MAY-20' "SPARE3","SPARE4","SPARE5","SP
                                						     , 'DD-MON-RR') and "MTIME" = T ARE6") values ('13822','13822'
                                						     O_DATE('28-MAY-20', 'DD-MON-RR ,'33','DROP_1','1',NULL,'2',TO
                                						     ') and "STIME" = TO_DATE('28-M _DATE('28-MAY-20', 'DD-MON-RR'
                                						     AY-20', 'DD-MON-RR') and "STAT ),TO_DATE('28-MAY-20', 'DD-MON
                                						     US" = '1' and "REMOTEOWNER" IS -RR'),TO_DATE('28-MAY-20', 'DD
                                						      NULL and "LINKNAME" IS NULL a -MON-RR'),'1',NULL,NULL,'0',NU
                                						     nd "FLAGS" = '0' and "OID$" IS LL,'6','1','33',NULL,NULL,NULL
                                						      NULL and "SPARE1" = '6' and " );
                                						     SPARE2" = '1' and "SPARE3" = '
                                						     33' and "SPARE4" IS NULL and "
                                						     SPARE5" IS NULL and "SPARE6" I
                                						     S NULL and ROWID = 'AAAAASAABA
                                						     AAFmdAAC';
                                
2020-05-28 04:28:30 SYS        SEG$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
2020-05-28 04:28:30 SYS        SEG$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
2020-05-28 04:28:30			  UNKNOWN    commit;							   COMMIT
2020-05-28 04:28:30			  UNKNOWN    set transaction read write;				   START
2020-05-28 04:28:30 SYS        SEG$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
2020-05-28 04:28:30			  UNKNOWN    commit;							   COMMIT
2020-05-28 04:28:30			  UNKNOWN    set transaction read write;				   START
2020-05-28 04:28:30 SYS        SEG$	  UNKNOWN    Unsupported		    Unsupported 		   UNSUPPORTED
2020-05-28 04:28:30			  UNKNOWN    commit;							   COMMIT

28 rows selected.

SQL>
      
Step 6   End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();

复制

10. oracle dump redo 日志

oradebug setmypid
alter system dump logfile '/arch1/1_25_1042492403.dbf';
oradebug close_trace
oradebug tracefile_name

作者是通读redo dump总结以下实现过程，建议有兴趣的同学，仔细分析redo dump结果。

### 11. drop  操作过程总结
ktubu redo: slt: 9 rci: 0 opc: 11.1 objn: 21 objd: 2 tsn: 0  ---- COL$
CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x00407a8b OBJ:2 SCN:0x0000.ff03b47e SEQ:1 OP:11.3 ENC:0 RBL:0   ----1. COL$(Opcode 3 : Drop Row Piece)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00404d5f OBJ:48 SCN:0x0000.ff03b857 SEQ:1 OP:10.4 ENC:0 RBL:0  ----1.1. I_COL1(delete leaf row)
ktubu redo: slt: 9 rci: 44 opc: 10.22 objn: 49 objd: 49 tsn: 0                                   ----1.2. I_COL2(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407387 OBJ:50 SCN:0x0000.ff03b857 SEQ:1 OP:10.4 ENC:0 RBL:0  ----1.3. I_COL3(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407a8b OBJ:2 SCN:0x0000.ff03b856 SEQ:1 OP:11.3 ENC:0 RBL:0   ----2. COL$ 2(Opcode 3 : Drop Row Piece)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00404d5f OBJ:48 SCN:0x0000.ff03b857 SEQ:2 OP:10.4 ENC:0 RBL:0  ----2.1. I_COL1(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00405a96 OBJ:49 SCN:0x0000.ff03b857 SEQ:2 OP:10.4 ENC:0 RBL:0  ----2.2. I_COL2(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407387 OBJ:50 SCN:0x0000.ff03b857 SEQ:2 OP:10.4 ENC:0 RBL:0  ----2.3. I_COL3(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407a8b OBJ:2 SCN:0x0000.ff03b857 SEQ:1 OP:11.3 ENC:0 RBL:0   ----3. TAB$(Opcode 3 : Drop Row Piece)
CHANGE #2 TYP:2 CLS:1 AFN:1 DBA:0x0040599d OBJ:18 SCN:0x0000.ff03b478 SEQ:1 OP:11.3 ENC:0 RBL:0  ----4. OBJ$(Opcode 3 : Drop Row Piece)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00405b26 OBJ:36 SCN:0x0000.ff03b859 SEQ:1 OP:10.4 ENC:0 RBL:0  ----41. I_OBJ1(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00407744 OBJ:37 SCN:0x0000.ff03b859 SEQ:1 OP:10.4 ENC:0 RBL:0  ----42. I_OBJ2(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00406d8b OBJ:39 SCN:0x0000.ff03b859 SEQ:1 OP:10.4 ENC:0 RBL:0  ----43. I_OBJ4(delete leaf row)
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x0040774c OBJ:40 SCN:0x0000.ff03b859 SEQ:1 OP:10.4 ENC:0 RBL:0  ----44. I_OBJ5(delete leaf row)
CHANGE #2 TYP:2 CLS:1 AFN:1 DBA:0x00406dee OBJ:8 SCN:0x0000.ff03b521 SEQ:1 OP:11.19 ENC:0 RBL:0  ----5. SEG$(type# 5->3)
CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x00406dee OBJ:8 SCN:0x0000.ff03b85b SEQ:1 OP:11.19 ENC:0 RBL:0  ----SEG$ Block cleanout record
CHANGE #1 TYP:0 CLS:4 AFN:6 DBA:0x01800082 OBJ:13805 SCN:0x0000.ff03b4aa SEQ:3 OP:14.4 ENC:0 RBL:0   ----6. L3 DELETE: entry:5 redo operation on extent map
CHANGE #1 TYP:0 CLS:12 AFN:6 DBA:0x01800003 OBJ:4294967295 SCN:0x0000.ff03b4a9 SEQ:1 OP:22.5 ENC:0 RBL:0  --17. datafile 6 block 3: File BitMap Block Redo
CHANGE #1 TYP:0 CLS:13 AFN:6 DBA:0x01800002 OBJ:4294967295 SCN:0x0000.ff03b860 SEQ:1 OP:22.2 ENC:0 RBL:0  --18. datafile 6 block 2: File BitMap Block Redo
CHANGE #1 TYP:0 CLS:4 AFN:6 DBA:0x01800082 OBJ:13805 SCN:0x0000.ff03b860 SEQ:1 OP:14.4 ENC:0 RBL:0  --6.1. L3 DELETE: entry:4 Opcode 4 : Undo to Add extent operation **sethwm
CHANGE #1 TYP:0 CLS:4 AFN:6 DBA:0x01800082 OBJ:13805 SCN:0x0000.ff03b860 SEQ:2 OP:14.4 ENC:0 RBL:0  --6.2 L3 DELETE: entry:3 sethwm
CHANGE #1 TYP:0 CLS:4 AFN:6 DBA:0x01800082 OBJ:13805 SCN:0x0000.ff03b860 SEQ:3 OP:14.4 ENC:0 RBL:0  --6.3 L3 DELETE: entry:2 sethwm
CHANGE #1 TYP:0 CLS:4 AFN:6 DBA:0x01800082 OBJ:13805 SCN:0x0000.ff03b860 SEQ:4 OP:14.4 ENC:0 RBL:0  --6.4 L3 DELETE: entry:1 sethwm
CHANGE #1 TYP:0 CLS:4 AFN:6 DBA:0x01800082 OBJ:13805 SCN:0x0000.ff03b860 SEQ:5 OP:14.2 ENC:0 RBL:0  --7   L3: Opcode 2 : Unlock Segment Header 
CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x00406dee OBJ:8 SCN:0x0000.ff03b85f SEQ:1 OP:11.3 ENC:0 RBL:0      --8. SEG$ delete

复制

11. 使用工具观察L3/L2/f_L1/l_L1/data 变化

L3操作前后对比

20201225215544

L2操作前后对比

20201225215610

第一个L1操作前后对比

20201225215707

最后一个L1操作前后对比

20201225215843

第一个data block操作前后对比

20201225215908

详细恢复方法请见下回分解。

oracle
最后修改时间：2021-01-31 13:16:00
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
971次阅读
2025-03-17 11:33:53
Oracle DataGuard高可用性解决方案详解
孙莹
411次阅读
2025-03-26 23:27:33
墨天轮个人数说知识点合集
JiekeXu
355次阅读
2025-04-01 15:56:03
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
344次阅读
2025-04-08 09:12:48
Oracle SQL 执行计划分析与优化指南
Digital Observer
312次阅读
2025-04-01 11:08:44
Oracle 19c RAC更换IP实战，运维必看！
szrsu
289次阅读
2025-04-08 23:57:08
风口浪尖！诚通证券扩容采购Oracle 793万...
Roger的数据库专栏
287次阅读
2025-03-24 09:42:53
切换Oracle归档路径后，不能正常删除原归档路径上的归档文件
dbaking
282次阅读
2025-03-19 14:41:51
oracle定时任务常用攻略
virvle
281次阅读
2025-03-25 16:05:19
MySQL 有没有类似 Oracle 的索引监控功能？
JiekeXu
260次阅读
2025-03-19 23:43:22
惠星星
暂无图片
关注
92
文章
181
粉丝
119K+
浏览量
暂无图片
获得了218次点赞
暂无图片
内容获得65次评论
暂无图片
获得了319次收藏
热门文章
oracle drop table purge无备份bbed恢复
2021-01-25 6722浏览
20230704_一键安装Oracle11g/19C/21C_单机/RAC集群/standalone/PostgreSQL_工具分享_开放源码
2021-11-04 5200浏览
20211119_一键安装Oracle 21C RAC_1731秒完成
2021-11-19 4807浏览
Oracle异常恢复_ORA-01189: file is from a different RESETLOGS than previous files
2021-08-11 4188浏览
从问题出发，解密Oracle rdba结构
2021-07-23 4098浏览
在线实训环境入口
Oracle 18C 在线实训环境
查看详情
最新文章
20250225_kingbase数据库扩容 从单机到主备读写分离
2025-02-27 106浏览
20250221_kingbase数据库单机安装手册(图形_命令行_静默)
2025-02-21 114浏览
20250219_pg数据库物理迁移升级方案
2025-02-19 119浏览
sqlserver alwayson安装部署手册
2025-02-17 221浏览
20241212_pg数据库逻辑迁移方案
2024-12-16 111浏览
目录
  • 1. 内容概述
  • 2.环境准备
  • 3. 信息收集
  • 4.dump段头块信息
  • 5. dump L2 信息
  • 6. 使用工具记录 L3/L2/f_L1/l_L1/data 物理二进制变化情况
  • 7. 检查REDO LOGFILE
  • 8. drop table 操作
    • 9. 使用logmgr检查变更操作
  • 10. oracle dump redo 日志
  • 11. 使用工具观察L3/L2/f_L1/l_L1/data 变化
    • L3操作前后对比
    • L2操作前后对比
    • 第一个L1操作前后对比
    • 最后一个L1操作前后对比
    • 第一个data block操作前后对比