引言
上一章节，介绍dul的配置，以及如何使用dul进行异常数据恢复。本章节介绍通过dul实现truncate操作的数据恢复。
dul实践步骤
测试数据生成
SQL> SQL> conn test/test
Connected.
SQL> create table t_truncate as select * from dba_users;
Table created.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> select count(1) from t_truncate;
COUNT(1)
----------
35
SQL> truncate table t_truncate;
Table truncated.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> select count(1) from t_truncate;
COUNT(1)
----------
0
获取表t_truncate的data_object_id
通过dbms_logmnr包解析redolog或者archivelog，获取data_object_id，下面案例通过解析redolog
SQL> select GROUP#,THREAD#,SEQUENCE# ,BYTES/1024/1024,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 STATUS
---------- ---------- ---------- --------------- ----------------
1 1 430 50 INACTIVE
2 1 431 50 CURRENT
3 1 429 50 INACTIVE
SQL> col MEMBER for a60
SQL> select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------
3 /u01/app/oracle/oradata/orcl/redo03.log
2 /u01/app/oracle/oradata/orcl/redo02.log
1 /u01/app/oracle/oradata/orcl/redo01.log
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/oradata/orcl/redo02.log', Options=>dbms_logmnr.new);
P/SQL procedure successfully completed.
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/oradata/orcl/redo01.log',Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> CREATE TABLE temp1 AS SELECT * FROM v$logmnr_contents;
Table created.
SQL> EXECUTE dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> col table_name for a10
SQL> col operation for a10
SQL> col data_obj# for 9999999999
SQL> col sql_redo for a60
SQL> set linesize 200
SQL> select t.table_name,t.data_obj#,t.sql_redo,t.timestamp,t.operation from temp1 t where t.table_name = 'T_TRUNCATE';
TABLE_NAME DATA_OBJ# SQL_REDO TIMESTAMP OPERATION
---------- ----------- ------------------------------------------------------------ ------------------- ----------
T_TRUNCATE 161418 create table t_truncate as select * from dba_users; 2019-03-01 02:10:30 DDL
T_TRUNCATE 161418 truncate table t_truncate; 2019-03-01 02:11:06 DDL
获取到T_TRUNCATE的data_object_id为161418留作下一步备用。
配置dul环境
配置init.dul和control.txt参数，以及调整时间参考上一章节。下面进行dul操作
[oracle@zsdb dul]$ ./dul
Data UnLoader: 12.0.0.0.0 - Internal Only - on Fri Mar 1 02:22:55 2019
with 64-bit io functions and the decompression option and the parallel option (beta)
Copyright (c) 1994 2018 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
Found db_id = 1581297075
Found db_name = ORCL
DUL> show datafiles;
ts# rf# start blocks offs open err file name
0 1 0 166401 0 1 0 /home/oracle/dul/system01.dbf
4 4 0 1441 0 1 0 /home/oracle/dul/users01.dbf
DUL> SCAN DATAFILE 4;
Scanning tablespace 4, data file 4 ...
11 segment header and 1164 data blocks
tablespace 4, data file 4: 1439 blocks scanned
Reading EXT.dat 29 entries loaded
and sorted 29 entries
Reading SEG.dat 11 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 0 entries loaded
and sorted 0 entries
DUL> ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered
DUL> scan extents;
Scanning extents without segment header
Scanning extent id (dba 0, obj 160982)
Analyzing segment: data object id 160982
heap organized table
DUL: Warning: Column 13: type based on heuristic guessing
DUL: Warning: Column 15: type based on heuristic guessing
Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB
no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice
1 35 21 0 100 100 5 0 0 0 0 0 0 0 0 0 31 0 0
。。。。。。。
Scanning extent id (dba 0, obj 161418) <===========与获取到的data_object_id一直，下面就是表t_truncate
Analyzing segment: data object id 161418
heap organized table
DUL: Warning: Column 13: type based on heuristic guessing
DUL: Warning: Column 15: type based on heuristic guessing
Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB
no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice
1 35 21 0 100 100 5 0 0 0 0 0 0 0 0 0 31 0 0
2 35 6 0 0 0 100 100 0 0 0 0 0 0 0 0 2 0 0
3 35 0 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
4 35 16 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
5 35 7 20 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
6 35 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
7 35 7 0 100 100 0 0 0 0 0 0 0 0 0 0 62 0 0
8 35 4 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
9 35 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
10 35 18 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
11 35 22 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
12 35 0 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
13 35 8 2 100 100 0 0 0 0 0 0 0 0 0 0 100 0 0
14 35 1 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
15 35 8 0 100 100 0 0 0 0 0 0 0 0 0 0 100 0 0
"GGS" "86" "" "OPEN" "" "16-MAY-2021 AD 14:16:09" "TBS_GGS" "TEMP" "17-NOV-2020 AD 14:16:09" "DEFAULT" "DEFAULT_CONSUMER_GROUP" "" "10G 11G " "N" "PASSWORD"
"V7PRO" "85" "" "OPEN" "" "09-APR-2021 AD 11:42:48" "V7_DATA" "TEMP" "11-OCT-2020 AD 11:42:48" "DEFAULT" "DEFAULT_CONSUMER_GROUP" "" "10G 11G " "N" "PASSWORD"
"MICHAEL" "94" "" "OPEN" "" "16-MAY-2021 AD 16:53:51" "USERS" "TEMP" "17-NOV-2020 AD 16:53:51" "DEFAULT" "DEFAULT_CONSUMER_GROUP" "" "10G 11G " "N" "PASSWORD"
"WHTEST" "92" "" "OPEN" "" "16-MAY-2021 AD 16:49:56" "USERS" "TEMP" "17-NOV-2020 AD 16:49:56" "DEFAULT" "DEFAULT_CONSUMER_GROUP" "" "10G 11G " "N" "PASSWORD"
"TEST" "95" "" "OPEN" "" "09-JUN-2021 AD 14:31:36" "USERS" "TEMP" "11-DEC-2020 AD 14:31:36" "DEFAULT" "DEFAULT_CONSUMER_GROUP" "" "10G 11G " "N" "PASSWORD"
UNLOAD TABLE OBJNO161418 ( COL001 VARCHAR2(21), COL002 NUMBER, COL003 CHAR
, COL004 VARCHAR2(16), COL005 DATE, COL006 DATE, COL007 VARCHAR2(7)
, COL008 VARCHAR2(4), COL009 DATE, COL010 VARCHAR2(18), COL011 VARCHAR2(22)
, COL012 CHAR, COL013 VARCHAR2(8), COL014 VARCHAR2(1), COL015 VARCHAR2(8) )
STORAGE( DATAOBJNO 161418 );
重新拼接SQL
DUL> UNLOAD TABLE test.t_truncate ( username VARCHAR2(21), user_id NUMBER, password CHAR
2 , account_status VARCHAR2(16), lock_date DATE, expiry_date DATE, default_tablespace VARCHAR2(7)
3 , temporary_tablespace VARCHAR2(4), created DATE, profile VARCHAR2(18), initial_rsrc_consumer_group VARCHAR2(22)
4 , external_name CHAR, password_versions VARCHAR2(8), editions_enabled VARCHAR2(1), authentication_type VARCHAR2(8) )
5 STORAGE( DATAOBJNO 161418 );
. unloading table T_TRUNCATE 35 rows unloaded
DUL> exit
Life is DUL without it
[oracle@zsdb dul]$ ll TEST_T_TRUNCATE.dmp
-rw-r--r-- 1 oracle oinstall 6519 Mar 1 02:27 TEST_T_TRUNCATE.dmp
导入测试
[oracle@zsdb dul]$ imp test/test file=/home/oracle/dul/TEST_T_TRUNCATE.dmp full=y ignore=y
Import: Release 11.2.0.3.0 - Production on Fri Mar 1 02:32:08 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V07.00.07 via conventional path
Warning: the objects were exported by Bernard's DUL, not by you
. importing Bernard's DUL's objects into TEST
. importing Bernard's DUL's objects into TEST
. . importing table "T_TRUNCATE" 35 rows imported
Import terminated successfully without warnings.
[oracle@zsdb dul]$ sqlplus / as sysdba
set pagesize 999
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 1 02:32:11 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> select count(1) from TEST.T_TRUNCATE;
COUNT(1)
----------
35
至此数据恢复成功。