场景说明
本文模拟Oracle数据库非分区表/分区表truncate table后恢复过程。
问题模拟
### 1. 创建测试表
drop table hsql.trunc_1 purge;
CREATE TABLE hsql.trunc_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.trunc_1 select * from obj$;
commit;
alter system checkpoint;
select count(1) from hsql.trunc_1;
alter system archive log current;
### 2. truncate测试表
truncate table hsql.trunc_1;
select count(1) from hsql.trunc_1;
工具使用说明
//使用手册: https://www.modb.pro/db/590882 工具下载：https://www.modb.pro/doc/95803
恢复过程
### 0. 设置read only表空间 <--可以不设置，部分恢复数据会被覆盖
alter tablespace hsql read only;
### 1. 查询业务表变更信息
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='TRUNC_1';
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE LAST_DDL_TIME
-------------------- -------------------- ------------------------------ ---------- -------------- ------------------- -------------------
HSQL TRUNC_1 53776 TABLE 2022-12-27 09:07:31
HSQL TRUNC_1 P_2000 53777 53782 TABLE PARTITION 2022-12-27 09:07:31
HSQL TRUNC_1 P_4000 53778 53783 TABLE PARTITION 2022-12-27 09:07:31
HSQL TRUNC_1 P_6000 53779 53784 TABLE PARTITION 2022-12-27 09:07:31
HSQL TRUNC_1 P_8000 53780 53785 TABLE PARTITION 2022-12-27 09:07:31
HSQL TRUNC_1 P_MAXVALUE 53781 53786 TABLE PARTITION 2022-12-27 09:07:31
6 rows selected.
SQL>
### 2. 查询归档日志
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-27 09:07:31','yyyy-mm-dd hh24:mi:ss')
and NEXT_TIME>=to_date('2022-12-27 09:07:31','yyyy-mm-dd hh24:mi:ss');
### 3. logmnr挖掘变更信息
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_73_1124471677.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 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='OBJ$';
Step 6 End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
### 4. 整理变更信息
"OBJ#" = '53781' and "DATAOBJ#"= '53781' --> "OBJ#" = '53781', "DATAOBJ#" = '53786'
"OBJ#" = '53780' and "DATAOBJ#"= '53780' --> "OBJ#" = '53780', "DATAOBJ#" = '53785'
"OBJ#" = '53779' and "DATAOBJ#"= '53779' --> "OBJ#" = '53779', "DATAOBJ#" = '53784'
"OBJ#" = '53778' and "DATAOBJ#"= '53778' --> "OBJ#" = '53778', "DATAOBJ#" = '53783'
"OBJ#" = '53777' and "DATAOBJ#"= '53777' --> "OBJ#" = '53777', "DATAOBJ#" = '53782'
### 5. copy datafile <--建议copy数据文件，所有恢复操作在备份数据文件中操作。
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 (
select ts# from tab$ where obj# in (
53781,
53780,
53779,
53778,
53777
)
)
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 (
53781,
53780,
53779,
53778,
53777
)
)
;
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
ts# fn rfn bsize blocks filename kscnbas_484 kcvcptim_496 kcvfhrlc_112 kscnbas_116
---- ---- ---- ----- -------- -------------------------------------------- -------- -------- -------- --------
0 1 1 8192 41600 /home/oracle/bak/1.dbf 1cc64e 4307154e 4306137d 1
5 5 5 8192 64000 /home/oracle/bak/5.dbf 1cc68b 43071553 4306137d 1
5 6 6 8192 64000 /home/oracle/bak/6.dbf 1cc69b 43071556 4306137d 1
0 7 7 8192 25600 /home/oracle/bak/7.dbf 1cc65d 43071550 4306137d 1
0 8 8 8192 25600 /home/oracle/bak/8.dbf 1cc66c 43071551 4306137d 1
0 9 9 8192 25600 /home/oracle/bak/9.dbf 1cc67c 43071552 4306137d 1
load control file 'control.txt' successful
load control file 'asmdisk.txt' successful
grpname dsknum diskpath f1b1locn
------- ------ -------- --------
XDUL>untrunc
input OBJ#:53776
the dataobj# info read from dataobj.txt:
dataobj[0]=53781
dataobj[1]=53780
dataobj[2]=53779
dataobj[3]=53778
dataobj[4]=53777
input TS#: 5
Unloading table: HSQL.TRUNC_1,object ID: 53776
Unloading segment,storage(Obj#=53776 DataObj#=0 TS#=5 Cluster=0)
file_name: /home/oracle/bak/6.dbf, block_no: 16276,dataobj#: 53777, 85 rows unloaded
...
file_name: /home/oracle/bak/6.dbf, block_no: 17300,dataobj#: 53778, 87 rows unloaded
...
file_name: /home/oracle/bak/6.dbf, block_no: 18324,dataobj#: 53779, 94 rows unloaded
...
file_name: /home/oracle/bak/6.dbf, block_no: 20543,dataobj#: 53781, 98 rows unloaded
Total: 23524 rows unloaded
XDUL>
### 8. 业务数据导入
SQL> @HSQL_TRUNC_1.sql
Table created.
SQL>
[oracle@enmodb dump]$ sqlldr \'/ as sysdba \' control=HSQL_TRUNC_1.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Dec 27 18:31:54 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 13525
[oracle@enmodb dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 27 18:32:00 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(1) from sys.trunc_1;
COUNT(1)
----------
13525
SQL>
### 9. 业务侧核对数据
### 10. 设置表空间read write
alter tablespace hsql read write;
最后修改时间：2022-12-28 09:50:43
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
