Oracle drop table 模拟及恢复方案

场景说明

本文模拟Oracle数据库非分区表/分区表drop table后恢复过程。

问题模拟

### 1. 创建测试表
drop table hsql.drop_1 purge;
CREATE TABLE hsql.drop_1
(    "OBJ#" NUMBER NOT NULL ENABLE,
        "DATAOBJ#" NUMBER,
        "OWNER#" NUMBER NOT NULL ENABLE,
        "NAME" VARCHAR2(30) NOT NULL ENABLE,
        "NAMESPACE" NUMBER NOT NULL ENABLE,
        "SUBNAME" VARCHAR2(30),
        "TYPE#" NUMBER NOT NULL ENABLE,
        "CTIME" DATE NOT NULL ENABLE,
        "MTIME" DATE NOT NULL ENABLE,
        "STIME" DATE NOT NULL ENABLE,
        "STATUS" NUMBER NOT NULL ENABLE,
        "REMOTEOWNER" VARCHAR2(30),
        "LINKNAME" VARCHAR2(128),
        "FLAGS" NUMBER,
        "OID$" RAW(16),
        "SPARE1" NUMBER,
        "SPARE2" NUMBER,
        "SPARE3" NUMBER,
        "SPARE4" VARCHAR2(1000),
        "SPARE5" VARCHAR2(1000),
        "SPARE6" DATE
)
PARTITION BY RANGE(OBJ#)
 (
     PARTITION p_2000 VALUES LESS THAN (2000) TABLESPACE hsql,
     PARTITION p_4000 VALUES LESS THAN (4000) TABLESPACE hsql,
     PARTITION p_6000 VALUES LESS THAN (6000) TABLESPACE hsql,
     PARTITION p_8000 VALUES LESS THAN (8000) TABLESPACE hsql,
     PARTITION p_maxvalue VALUES LESS THAN (maxvalue) TABLESPACE hsql
);
insert into hsql.drop_1 select * from obj$;
commit;
alter system checkpoint;
select count(1) from hsql.drop_1;
alter system archive log current;

### 2. truncate测试表
drop table hsql.drop_1 purge;
select count(1) from hsql.drop_1;

恢复过程

### 0. 设置read only表空间  <--可以不设置，部分恢复数据会被覆盖
alter tablespace hsql read only;
### 1. 创建中间表
CREATE TABLE sys.drop_1_bak
(    "OBJ#" NUMBER NOT NULL ENABLE,
        "DATAOBJ#" NUMBER,
        "OWNER#" NUMBER NOT NULL ENABLE,
        "NAME" VARCHAR2(30) NOT NULL ENABLE,
        "NAMESPACE" NUMBER NOT NULL ENABLE,
        "SUBNAME" VARCHAR2(30),
        "TYPE#" NUMBER NOT NULL ENABLE,
        "CTIME" DATE NOT NULL ENABLE,
        "MTIME" DATE NOT NULL ENABLE,
        "STIME" DATE NOT NULL ENABLE,
        "STATUS" NUMBER NOT NULL ENABLE,
        "REMOTEOWNER" VARCHAR2(30),
        "LINKNAME" VARCHAR2(128),
        "FLAGS" NUMBER,
        "OID$" RAW(16),
        "SPARE1" NUMBER,
        "SPARE2" NUMBER,
        "SPARE3" NUMBER,
        "SPARE4" VARCHAR2(1000),
        "SPARE5" VARCHAR2(1000),
        "SPARE6" DATE
);

#### 新创建的中间表做为drop恢复的表结构使用
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set linesize 300 pagesize 9999
col owner for a20
col object_name for a20
select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,LAST_DDL_TIME from dba_objects where owner='SYS' and object_name='DROP_1_BAK';

OWNER                OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         LAST_DDL_TIME
-------------------- -------------------- ------------------------------ ---------- -------------- ------------------- -------------------
SYS                  DROP_1_BAK                                               53812          53812 TABLE               2022-12-28 09:10:19

SQL>

### 2. 查询归档日志
#### 预估drop时间并查询日志信息
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set linesize 300 pagesize 9999
col name for a100
col f_scn for a20
col n_scn for a20
select SEQUENCE#,name,status,to_char(FIRST_CHANGE#) f_scn,FIRST_TIME,to_char(NEXT_CHANGE#) n_scn,NEXT_TIME from v$archived_log
where first_time>=to_date('2022-12-28 08:00:00','yyyy-mm-dd hh24:mi:ss')
and first_time<=to_date('2022-12-28 09:00:00','yyyy-mm-dd hh24:mi:ss');

### 3. logmnr挖掘变更信息
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_82_1124471677.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_83_1124471677.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 200 pagesize 9999
col USR for a10
col sql_redo for a100
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,OPERATION,SQL_REDO FROM 
V$LOGMNR_CONTENTS
WHERE TABLE_NAME in ('OBJ$','DROP_1','TAB$');
 Step 6   End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();

### 4. 整理变更信息
#### 获取drop前的dataobj#信息
delete from "SYS"."OBJ$" where "OBJ#" = '53806' and "DATAOBJ#" IS NULL and "OWNER#" = '36' and "NAME" = 'DROP_1'
delete from "SYS"."OBJ$" where "OBJ#" = '53811' and "DATAOBJ#" = '53811' and "OWNER#" = '36' and "NAME" = 'DROP_1'
delete from "SYS"."OBJ$" where "OBJ#" = '53810' and "DATAOBJ#" = '53810' and "OWNER#" = '36' and "NAME" = 'DROP_1'
delete from "SYS"."OBJ$" where "OBJ#" = '53809' and "DATAOBJ#" = '53809' and "OWNER#" = '36' and "NAME" = 'DROP_1'
delete from "SYS"."OBJ$" where "OBJ#" = '53808' and "DATAOBJ#" = '53808' and "OWNER#" = '36' and "NAME" = 'DROP_1'
delete from "SYS"."OBJ$" where "OBJ#" = '53807' and "DATAOBJ#" = '53807' and "OWNER#" = '36' and "NAME" = 'DROP_1'

### 5. copy datafile  <--建议copy数据文件，所有恢复操作在备份数据文件中操作。
#### 与业务确认表空间信息
SQL> select ts#,name from ts$;

       TS# NAME
---------- ----------------------------------------------------------------------------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         3 TEMPTS1
         4 USERS
         5 HSQL

6 rows selected.

SQL>

set linesize 300 pagesize 9999
col name for a60
select 'copy datafile '||file#||' to '||'''/home/oracle/bak/'||file#||'.dbf'||''';' r_script,name,ts# from v$datafile where ts#=0
union all
select 'copy datafile '||file#||' to '||'''/home/oracle/bak/'||file#||'.dbf'||''';' r_script,name,ts# from v$datafile where ts# in (5)
;

rman target /
copy datafile 1 to '/home/oracle/bak/1.dbf';
copy datafile 7 to '/home/oracle/bak/7.dbf';
copy datafile 8 to '/home/oracle/bak/8.dbf';
copy datafile 9 to '/home/oracle/bak/9.dbf';
copy datafile 5 to '/home/oracle/bak/5.dbf';
copy datafile 6 to '/home/oracle/bak/6.dbf'; 

### 6. 配置控制文件信息
[oracle@enmodb xdul]$ ls
asmdisk.txt  config.txt  control.txt  data  dataobj.txt  dump  map  xdul
[oracle@enmodb xdul]$ cat control.txt 
/home/oracle/bak/1.dbf
/home/oracle/bak/5.dbf
/home/oracle/bak/6.dbf
/home/oracle/bak/7.dbf
/home/oracle/bak/8.dbf
/home/oracle/bak/9.dbf
[oracle@enmodb xdul]$

### 7. 业务数据unload
[oracle@enmodb xdul]$ ./xdul 
XDUL>undrop
input OBJ#:53812
the dataobj# info read from dataobj.txt: 
dataobj[0]=53811
dataobj[1]=53810
dataobj[2]=53809
dataobj[3]=53808
dataobj[4]=53807
input TS#: 5
Unloading table: SYS.DROP_1_BAK,object ID: 53812
Unloading segment,storage(Obj#=53812 DataObj#=0 TS#=5 Cluster=0)
file_name: /home/oracle/bak/5.dbf, block_no: 148,dataobj#: 53807, 85 rows unloaded
...
file_name: /home/oracle/bak/5.dbf, block_no: 151,dataobj#: 53807, 87 rows unloaded
Total: 13526 rows unloaded
XDUL>
### 8. 业务数据导入
SQL> @HSQL_TRUNC_1.sql

Table created.

SQL> 
[oracle@enmodb dump]$ sqlldr  \'/ as sysdba \' control=SYS_DROP_1_BAK.ctl 

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 28 09:21:10 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1548
Commit point reached - logical record count 3096
Commit point reached - logical record count 4644
Commit point reached - logical record count 6192
Commit point reached - logical record count 7740
Commit point reached - logical record count 9288
Commit point reached - logical record count 10836
Commit point reached - logical record count 12384
Commit point reached - logical record count 13526
[oracle@enmodb dump]$ sqlplus / as sysdba
SQL> select count(1) from sys.drop_1_bak;

  COUNT(1)
----------
     13526

SQL> 

### 9. 业务侧核对数据

### 10. 设置表空间read write
alter tablespace hsql read write;