oracle drop table purge无备份bbed恢复(1/3)

来源：这里教程网时间：2026-03-03 16:21:20 作者：

### 1. 内容概述
```c
    oracle drop table purge后无有效备份集，信息系统面临业务数据丢失的风险，本文使用bbed工具，
针对该场景进行业务数据恢复。
```
### 2. 环境准备
```c
create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql 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 .. 1000000 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		     							13863	    13863

set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
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
HSQL	   DROP_1			19	    5	     640	128
HSQL	   DROP_1			20	    5	     768	128
HSQL	   DROP_1			21	    5	     896	128
HSQL	   DROP_1			22	    5	    1024	128
HSQL	   DROP_1			23	    5	    1152	128
HSQL	   DROP_1			24	    5	    1280	128
HSQL	   DROP_1			25	    5	    1408	128
HSQL	   DROP_1			26	    5	    1536	128
HSQL	   DROP_1			27	    5	    1664	128
HSQL	   DROP_1			28	    5	    1792	128
HSQL	   DROP_1			29	    5	    1920	128
HSQL	   DROP_1			30	    5	    2048	128
HSQL	   DROP_1			31	    5	    2176	128
HSQL	   DROP_1			32	    5	    2304	128
HSQL	   DROP_1			33	    5	    2432	128
HSQL	   DROP_1			34	    5	    2560	128
HSQL	   DROP_1			35	    5	    2688	128
HSQL	   DROP_1			36	    5	    2816	128
HSQL	   DROP_1			37	    5	    2944	128
HSQL	   DROP_1			38	    5	    3072	128
HSQL	   DROP_1			39	    5	    3200	128
HSQL	   DROP_1			40	    5	    3328	128
HSQL	   DROP_1			41	    5	    3456	128
HSQL	   DROP_1			42	    5	    3584	128
HSQL	   DROP_1			43	    5	    3712	128
HSQL	   DROP_1			44	    5	    3840	128
45 rows selected.
SQL>
```

### 4. 在线日志查询
```c
生产环境根据业务反馈的drop操作时间，查找archive log信息
SQL> alter system archive log current;
System altered.
SQL> set linesize 200 pagesize 9999 
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 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 YES	  INACTIVE   /data2/enmo/redo01a.log
	 2	    1	      44  104857600	     1 NO	  CURRENT    /data2/enmo/redo02a.log
	 3	    1	      42  104857600	     1 YES	  INACTIVE   /data2/enmo/redo03a.log
SQL>
```
### 5. drop操作
```c
drop table hsql.drop_1 purge;
alter system archive log current;
```
### 6. logmgr日志分析
```c
Step 1   Determine which redo log file was most recently archived by the database.
set line 200
col name for a80
SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
Step 2   Ensure that you have a complete list of redo log files.
SELECT NAME FROM V$ARCHIVED_LOG
   WHERE SEQUENCE# >= 203 AND SEQUENCE# <= 204
   ORDER BY SEQUENCE# ASC;
Step 3   Specify the list of the redo log files of interest.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch1/1_35_1042492403.dbf', OPTIONS => DBMS_LOGMNR.NEW);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_11_1056101196.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_12_1056101196.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
--EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_13_1056101196.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
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 3000
set pagesize 2000
col USR for a10
col sql_redo for a50
col SQL_UNDO for a50
COL OPERATION FOR A20
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROM  
   V$LOGMNR_CONTENTS
   WHERE  TABLE_NAME in ('DROP_1','SEG$','OBJ$','TAB$','COL$'); --and OPERATION in ('DELETE','INSERT');
   
Step 6   End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch1/1_44_1042492403.dbf', OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
              DBMS_LOGMNR.COMMITTED_DATA_ONLY); > > 
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 3000
set pagesize 2000
col USR for a10
col sql_redo for a50
col SQL_UNDO for a50
COL OPERATION FOR A20
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,USERNAME AS usr,SQL_REDO,SQL_UNDO,OPERATION FROM  
   V$LOGMNR_CONTENTS
   WHERE  TABLE_NAME in ('DROP_1','SEG$','OBJ$','TAB$','COL$');
T_TIME				       USR	  SQL_REDO					     SQL_UNDO						OPERATION
-------------------------------------- ---------- -------------------------------------------------- -------------------------------------------------- --------------------
2020-06-21 07:33:08		       UNKNOWN	  drop table hsql.drop_1 purge; 									DDL
2020-06-21 07:33:08		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
2020-06-21 07:33:08		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
2020-06-21 07:33:08		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
2020-06-21 07:33:08		       UNKNOWN	  delete from "SYS"."OBJ$" where "OBJ#" = '13863' an insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER# DELETE
						  													d "DATAOBJ#" = '13863' and "OWNER#" = '32' and "NA ","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MT
						  													ME" = 'DROP_1' and "NAMESPACE" = '1' and "SUBNAME" IME","STIME","STATUS","REMOTEOWNER","LINKNAME","FL
						  													 IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE(' AGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","S
						  													21-JUN-20', 'DD-MON-RR') and "MTIME" = TO_DATE('21 PARE5","SPARE6") values ('13863','13863','32','DRO
						  													-JUN-20', 'DD-MON-RR') and "STIME" = TO_DATE('21-J P_1','1',NULL,'2',TO_DATE('21-JUN-20', 'DD-MON-RR'
						  													UN-20', 'DD-MON-RR') and "STATUS" = '1' and "REMOT ),TO_DATE('21-JUN-20', 'DD-MON-RR'),TO_DATE('21-JU
						  													EOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" N-20', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1'
						  													 = '0' and "OID$" IS NULL and "SPARE1" = '6' and " ,'32',NULL,NULL,NULL);
						  													SPARE2" = '1' and "SPARE3" = '32' and "SPARE4" IS
						  													NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and
						  													 ROWID = 'AAAAASAABAAAFodAAB';
2020-06-21 07:33:08		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
2020-06-21 07:33:08		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
2020-06-21 07:33:08		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
2020-06-21 07:33:09		       UNKNOWN	  Unsupported					     Unsupported					UNSUPPORTED
11 rows selected.
SQL>
delete obj#: 13863,DATAOBJ: 13863
timestamp: 2020-06-21 07:33:08
```
### 7. 检查表已被删除
```c
select count(1) from hsql.drop_1; 
SQL> select count(1) from hsql.drop_1; 
select count(1) from hsql.drop_1
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> 
```