场景说明
本文模拟Oracle数据库非分区表/分区表delete后恢复过程。对于该场景建议恢复方案： 方案一、闪回查询恢复，恢复条件 undo信息未被覆盖。 方案二、logmgr日志挖掘，恢复条件 开启附加日志并保留足够归档日志。 方案三、备份集恢复，恢复条件 存在可用的备份集。 方案四、data unload方式，block内数据未被覆盖 本节主要演练方案四使用data unload方式进行恢复
问题模拟
### 1. 创建测试表
drop table hsql.del_1 purge;
drop table hsql.del_2 purge;
CREATE TABLE hsql.del_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
) tablespace hsql;
CREATE TABLE hsql.del_2
( "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
);
### 2. delete部分数据
insert into hsql.del_1 select * from obj$;
insert into hsql.del_2 select * from obj$;
commit;
alter system checkpoint;
select count(1) from hsql.del_1;
select count(1) from hsql.del_2;
alter system archive log current;
alter system archive log current;
alter system archive log current;
delete from hsql.del_1 where obj#>10000;
delete from hsql.del_2 where obj#>10000;
commit;
alter system checkpoint;
alter system archive log current;
alter system archive log current;
alter system archive log current;
### 3. 查询数据字典信息
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='HSQL' and object_name in ('DEL_1','DEL_2');
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE LAST_DDL_TIME
-------------------- -------------------- ------------------------------ ---------- -------------- ------------------- -------------------
HSQL DEL_1 53840 53840 TABLE 2022-12-28 19:50:31
HSQL DEL_2 53841 TABLE 2022-12-28 19:50:31
HSQL DEL_2 P_2000 53842 53842 TABLE PARTITION 2022-12-28 19:50:31
HSQL DEL_2 P_4000 53843 53843 TABLE PARTITION 2022-12-28 19:50:31
HSQL DEL_2 P_6000 53844 53844 TABLE PARTITION 2022-12-28 19:50:31
HSQL DEL_2 P_8000 53845 53845 TABLE PARTITION 2022-12-28 19:50:31
HSQL DEL_2 P_MAXVALUE 53846 53846 TABLE PARTITION 2022-12-28 19:50:31
7 rows selected.
SQL>
恢复过程
### 0. 设置read only表空间 <--可以不设置，部分恢复数据会被覆盖
alter tablespace hsql read only;
### 1. copy datafile <--建议copy数据文件，所有恢复操作在备份数据文件中操作。
set linesize 300 pagesize 9999
col name for a60
select distinct * from (
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 (
select ts# from tab$ where obj# in (
53840,
53841,
53842,
53843,
53844,
53845,
53846
)
)
union all
select 'copy datafile '||file#||' to '||'''/home/oracle/bak/'||file#||'.dbf'||''';' r_script,name,ts# from v$datafile where ts# in (
select ts# from TABPART$ where obj# in (
53840,
53841,
53842,
53843,
53844,
53845,
53846
)
)
)
;
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 5 to '/home/oracle/bak/5.dbf';
copy datafile 6 to '/home/oracle/bak/6.dbf';
copy datafile 9 to '/home/oracle/bak/9.dbf';
### 2. 配置控制文件信息
[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]$
### 3. 业务数据unload
[oracle@enmodb xdul]$ ./xdul
XDUL>save dict
success unload dict.
XDUL>undel
input OBJ#: 53840 <--非分区表objid
Unloading table: HSQL.DEL_1,object ID: 53840
Unloading segment,storage(Obj#=53840 DataObj#=53840 TS#=5 File#=6 Block#=130 Cluster=0)
file_name: /home/oracle/bak/6.dbf, block_no: 131,dataobj#: 53840, 0 rows unloaded
...
file_name: /home/oracle/bak/5.dbf, block_no: 223,dataobj#: 53840, 0 rows unloaded
Total: 3715 rows unloaded
XDUL>XDUL>undel
input OBJ#: 53841 <-- 分区表objid,程序自动识别分区信息
Unloading table: HSQL.DEL_2,object ID: 53841
Unloading segment,storage(Obj#=53841 DataObj#=0 TS#=0 File#=0 Block#=0 Cluster=0)
file_name: /home/oracle/bak/6.dbf, block_no: 4370,dataobj#: 53846, 74 rows unloaded
...
file_name: /home/oracle/bak/6.dbf, block_no: 319,dataobj#: 53842, 0 rows unloaded
Total: 3715 rows unloaded
XDUL>
### 4. 创建恢复中间表并导入数据
[oracle@enmodb dump]$ sqlplus / as sysdba
SQL> @HSQL_DEL_1.sql
[oracle@enmodb dump]$ sqlldr \'/ as sysdba \' control=HSQL_DEL_1.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 28 20:03:23 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 3715
[oracle@enmodb dump]$ sqlplus / as sysdba
SQL> select count(1) from sys.del_1;
COUNT(1)
----------
3715
SQL>
### 5. 业务侧核对数据
### 6. 设置表空间read write
alter tablespace hsql read write;
最后修改时间：2023-01-12 16:58:03
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
