### 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操作前後對比
![](http://img.blog.itpub.net/blog/2020/12/25/cd0f1820a196506b.png?x-oss-process=style/bb)

#### L2操作前後對比
![](http://img.blog.itpub.net/blog/2020/12/25/0aca22b4dfad4e18.png?x-oss-process=style/bb)

#### 第一個L1操作前後對比
![](http://img.blog.itpub.net/blog/2020/12/25/aa7d9599c35ec5fb.png?x-oss-process=style/bb)
#### 最後一個L1操作前後對比
![](http://img.blog.itpub.net/blog/2020/12/25/8f9fb477e5441f03.png?x-oss-process=style/bb)
#### 第一個data block操作前後對比
![](http://img.blog.itpub.net/blog/2020/12/25/ad864549d05c2171.png?x-oss-process=style/bb)

詳細恢復方法請見下回分解。

深入解析 oracle drop table內部原理

相關文章