### 1. 内容概述 oracle drop table操作后，如果未开启回收站且无有效备份，信息系统面临业务数据丢失的风险， 本文主要使用oracle dump、bbed、logmgr等多种工具解析drop table实现原理，使用多种方法实 现drop table操作后业务数据特殊恢复。欢迎关注公众号“ 数据库技术笔记”留言讨论。 ### 2.环境准备 ```c 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. 信息收集 ```c 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 5 5 168 8 HSQL DROP_1 6 5 176 8 HSQL DROP_1 7 5 184 8 HSQL DROP_1 8 5 192 8 HSQL DROP_1 9 5 200 8 HSQL DROP_1 10 5 208 8 HSQL DROP_1 11 5 216 8 HSQL DROP_1 12 5 224 8 HSQL DROP_1 13 5 232 8 HSQL DROP_1 14 5 240 8 HSQL DROP_1 15 5 248 8 HSQL DROP_1 16 5 256 128 HSQL DROP_1 17 5 384 128 HSQL DROP_1 18 5 512 128 19 rows selected. SQL> ``` ### 4.dump段头块信息 ```c 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 信息 ```c 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 物理二进制变化情况 ```c 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 ```c 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 操作 ```c 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检查变更操作 ```c 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 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 COL$ UNKNOWN Unsupported Unsupported UNSUPPORTED 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 COL$ UNKNOWN Unsupported Unsupported UNSUPPORTED 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 COL$ UNKNOWN Unsupported Unsupported UNSUPPORTED 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 COL$ UNKNOWN Unsupported Unsupported UNSUPPORTED 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 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 日志 ```c oradebug setmypid alter system dump logfile '/arch1/1_25_1042492403.dbf'; oradebug close_trace oradebug tracefile_name 作者是通读redo dump总结以下实现过程，建议有兴趣的同学，仔细分析redo dump结果。 ``` ### 11. drop 操作过程总结 ```c 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 ``` ### 12. 使用工具观察L3/L2/f_L1/l_L1/data 变化 #### L3操作前后对比  #### L2操作前后对比  #### 第一个L1操作前后对比  #### 最后一个L1操作前后对比  #### 第一个data block操作前后对比  详细恢复方法请见下回分解。