案例:Oracle dul資料採礦 沒有Database Backup非常規恢複truncate刪除的資料表

標籤：

Oracle資料庫在沒有備份情況下在對錶中的某資料表進行truncate刪除後，通過oracle dul進行非常規恢複

 

1.準備oracle dul測試環境

SQL> select count(*) from t_xifenfei;  COUNT(*)----------     67854SQL> desc t_xifenfei Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- OWNER                                              VARCHAR2(30) OBJECT_NAME                                        VARCHAR2(128) SUBOBJECT_NAME                                     VARCHAR2(30) OBJECT_ID                                          NUMBER DATA_OBJECT_ID                                     NUMBER OBJECT_TYPE                                        VARCHAR2(19) CREATED                                            DATE LAST_DDL_TIME                                      DATE TIMESTAMP                                          VARCHAR2(19) STATUS                                             VARCHAR2(7) TEMPORARY                                          VARCHAR2(1) GENERATED                                          VARCHAR2(1) SECONDARY                                          VARCHAR2(1) NAMESPACE                                          NUMBER EDITION_NAME                                       VARCHAR2(30)SQL> select object_id,data_object_id from user_objects where object_name=‘T_XIFENFEI‘; OBJECT_ID DATA_OBJECT_ID---------- --------------     68332          68332SQL> TRUNCATE TABLE T_XIFENFEI;Table truncated.SQL> ALTER SYSTEM CHECKPOINT;System altered.SQL> select object_id,data_object_id from user_objects where object_name=‘T_XIFENFEI‘; OBJECT_ID DATA_OBJECT_ID---------- --------------     68332          68468SQL> SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME=‘T_XIFENFEI‘;TABLESPACE_NAME------------------------------USERSSQL> SELECT FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=‘USERS‘;   FILE_ID----------         4SQL> SELECT file#,RFILE#,NAME FROM V$DATAFILE;     FILE#     RFILE# NAME---------- ---------- --------------------------------------------------         1          1 E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF         2          2 E:\ORACLE\ORADATA\XIFENFEI\SYSAUX01.DBF         3          3 E:\ORACLE\ORADATA\XIFENFEI\UNDOTBS01.DBF         4          4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF         5          5 E:\ORACLE\ORADATA\XIFENFEI\UNDO01.DBF         6          6 E:\ORACLE\ORADATA\XIFENFEI\CZUM01.DBF6 rows selected.

備忘說明：因為我們為了測試，所以直接查詢出來了data_object_id，在實際的恢複中，我們需要使用logminer來找出來曆史dataobj#

2.dul恢複truncate 表

e:\dul10>dul.exeData UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 00:20:08 2013with 64-bit io functionsCopyright (c) 1994 2010 Bernard van Duijnen All rights reserved. Strictly Oracle Internal use OnlyDUL: Warning: Recreating file "dul.log"Reading USER.dat 65 entries loadedReading OBJ.dat 67944 entries loaded and sorted 67944 entriesReading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entriesReading TAB.dat 1869 entries loadedReading COL.dat 77409 entries loaded and sorted 77409 entriesReading SEG.dat 23 entries loadedReading EXT.dat 54 entries loaded and sorted 54 entriesReading TABPART.dat 110 entries loaded and sorted 110 entriesReading TABCOMPART.dat 1 entries loaded and sorted 1 entriesReading TABSUBPART.dat 32 entries loaded and sorted 32 entriesReading INDPART.dat 127 entries loaded and sorted 127 entriesReading INDCOMPART.dat 0 entries loaded and sorted 0 entriesReading INDSUBPART.dat 0 entries loaded and sorted 0 entriesReading IND.dat 3729 entries loadedReading LOB.datDUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries 1109 entries loadedReading ICOL.dat 4868 entries loadedReading COLTYPE.dat 2823 entries loadedReading TYPE.dat 2082 entries loadedReading ATTRIBUTE.dat 8736 entries loadedReading COLLECTION.dat 591 entries loadedReading COMPATSEG.dat 0 entries loadedReading BOOTSTRAP.dat 60 entries loadedReading LOBFRAG.dat 1 entries loaded and sorted 1 entriesReading LOBCOMPPART.dat 0 entries loaded and sorted 0 entriesReading UNDO.dat 24 entries loadedReading TS.dat 7 entries loadedReading PROPS.dat 36 entries loadedDatabase character set is ZHS16GBKDatabase national character set is AL16UTF16Found db_id = 1422012639Found db_name = XIFENFEIDUL> SCAN DATAFILE 4;DUL: Warning: Recreating file "EXT.dat"DUL: Warning: Recreating file "SEG.dat"DUL: Warning: Recreating file "COMPATSEG.dat"DUL: Warning: Recreating file "SCANNEDLOBPAGE.dat"Scanning tablespace 4, data file 4 ...  23 segment header and 1601 data blocks  tablespace 4, data file 4: 2079 blocks scannedReading EXT.dat 54 entries loaded and sorted 54 entriesReading SEG.dat 23 entries loadedReading COMPATSEG.dat 0 entries loadedReading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entriesDUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;Parameter alteredDUL> scan extents;DUL: Warning: Recreating file "seen_tab.dat"DUL: Warning: Recreating file "seen_col.dat"Scanning extents without segment headerScanning extent id (dba 0, obj 68332)Analyzing segment: data object id 68332  heap organized tableCol    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   67854   18   0 100 100   0   0   0   0   0   0   0   0   0   0  44   0   0………… 14   67854    2   0   0   0 100 100   0   0   0   0   0   0   0   0   0   0   0|SYS| |C_FILE#_BLOCK#| || |8| |8| |CLUSTER| |20-JAN-2013 AD 17:13:18| |20-JAN-20…………:18| |2013-01-20:17:13:18| |VALID| |N| |N| |N| |1|UNLOAD TABLE OBJNO68332 ( COL001 VARCHAR2(18), COL002 VARCHAR2(30), COL003 VARCHAR2(27)        , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(18), COL007 DATE        , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(7), COL011 VARCHAR2(1)        , COL012 VARCHAR2(1), COL013 VARCHAR2(1), COL014 NUMBER )    STORAGE( DATAOBJNO 68332 );--自己拼接語句DUL> UNLOAD TABLE t_xifenfei_1(OWNER VARCHAR2(30),OBJECT_NAME  VARCHAR2(128),SUBOBJECT_NAME    VARCHAR2(30),OBJECT_ID  NUMBER,DATA_OBJECT_ID    NUMBER,OBJECT_TYPE  VARCHAR2(19),CREATED    DATE,LAST_DDL_TIME DATE,TIMESTAMP    VARCHAR2(19),STATUS VARCHAR2(7),TEMPORARY    VARCHAR2(1),GENERATED    VARCHAR2(1),SECONDARYVARCHAR2(1),NAMESPACE    NUMBER,EDITION_NAME VARCHAR2(30))  STORAGE( DATAOBJNO 68332 );. unloading table              T_XIFENFEI_1DUL: Warning: Recreating file "T_XIFENFEI_1.ctl"   67854 rows unloaded

補充說明：
1.在最新的dul 10.2.0.5.20中，不支援scan extents命令
2.在最新的dul 10.2.0.5.20中，挖出來的無資料字典資料，字串轉換為16進位值

 

 

--------------------------------------ORACLE-DBA----------------------------------------

最權威、專業的Oracle案例資源匯總之案例:Oracle dul資料採礦 沒有Database Backup非常規恢複truncate刪除的資料表

原文唯一網址：http://www.oracleplus.net/arch/oracle-20160522-217.html

Oracle研究中心 關鍵詞：Oracle dul資料採礦沒有Database Backup非常規恢複truncate刪除的資料表

案例:Oracle dul資料採礦 沒有Database Backup非常規恢複truncate刪除的資料表