Case: Oracle dul Data Mining No database backup unconventional recovery truncate deleted data table

Oracle Databases perform unconventional recoveries through Oracle Dul after truncate deletion of a table of data in the absence of a backup

1. Preparing the Oracle Dul test Environment

Sql> Select COUNT (*) from T_xifenfei;    COUNT (*)----------67854sql> desc T_xifenfei Name Null?                                              Type-----------------------------------------------------------------------------OWNER                                     VARCHAR2 (+) object_name VARCHAR2 (+) Subobject_name                                     VARCHAR2 (object_id) Number data_object_id                                            Number object_type VARCHAR2 (+) CREATED                                          Date Last_ddl_time Date TIMESTAMP                                          VARCHAR2 (+) STATUS VARCHAR2 (7) Temporary                       VARCHAR2 (1) GENERATED VARCHAR2 (1) Secondary                   VARCHAR2 (1) NAMESPACE number Edition_name VARCHAR2 (+) 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:\ORAC Le\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.

Note: Because we want to test, so the direct query out data_object_id, in the actual recovery, we need to use Logminer to find out the history dataobj#

2.dul Recovery TRUNCATE TABLE

E:\dul10>dul.exedata unloader 10.2.4.37-oracle Internal only-on Mon Feb 00:20:08 2013with 64-bit io functionscop Yright (c) 1994 Bernard van Duijnen All rights reserved. Strictly Oracle Internal use OnlyDUL:Warning:Recreating file "Dul.log" Reading USER.dat + entries loadedreading OBJ.dat 67944 entries loaded and sorted 67944 entriesreading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entriesreading TAB.D At 1869 entries loadedreading COL.dat 77409 entries loaded and sorted 77409 entriesreading SEG.dat all entries Loadedreadin g EXT.dat entries loaded and sorted, entriesreading TABPART.dat, entries loaded and sorted, entriesreading TABC OMPART.dat 1 entries loaded and sorted 1 entriesreading TABSUBPART.dat + entries loaded and sorted + entriesreading INDP ART.dat 127 entries loaded and sorted 127 entriesreading INDCOMPART.dat 0 entries loaded and sorted 0 entriesreading INDSU BPART.dat 0 Entries loaded and sorted 0 entriesreading IND.dat 3729 entries LoadedreAding LOB.datDUL:Warning:Increased the size of dc_lobs from 1024x768 to 8192 entries 1109 entries loadedreading ICOL.dat 486 8 Entries loadedreading COLTYPE.dat 2823 entries loadedreading TYPE.dat 2082 entries loadedreading ATTRIBUTE.dat 8736 entr IES loadedreading COLLECTION.dat 591 entries loadedreading COMPATSEG.dat 0 entries loadedreading BOOTSTRAP.dat entries Loadedreading LOBFRAG.dat 1 entries loaded and sorted 1 entriesreading LOBCOMPPART.dat 0 entries loaded and sorted 0 Entri esreading UNDO.dat Entries loadedreading TS.dat 7 entries loadedreading PROPS.dat/entries Loadeddatabase character s ET is Zhs16gbkdatabase national character set is Al16utf16found db_id = 1422012639Found db_name = xifenfeidul> SCAN DAT Afile 4;dul:warning:recreating file "EXT.dat" DUL:Warning:Recreating file "SEG.dat" DUL:Warning:Recreating file "Compa  TSEG.dat "DUL:Warning:Recreating file" SCANNEDLOBPAGE.dat "scanning tablespace 4, data file 4 ... Segment header and 1601 data blocks TablespAce 4, data file 4:2079 blocks scannedreading EXT.dat entries loaded and sorted entriesreading SEG.dat at entries l Oadedreading 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 7   5%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), COL002 VARCHAR2 (+), COL003 VARCHAR2 (+), COL004 number, COL005 NUM        BER, COL006 VARCHAR2, COL007 date, COL008 date, COL009 VARCHAR2 (+), COL010 VARCHAR2 (7), COL011 VARCHAR2 (1) , COL012 VARCHAR2 (1), COL013 VARCHAR2 (1), COL014 number) STORAGE (Dataobjno 68332);--Self-stitching statement dul> UNLOAD TABLE T_xifenfei_1 (OWNER VARCHAR2 (), object_name VARCHAR2 (+), Subobject_name VARCHAR2 (+), object_id Number,data_object _id Number,object_type VARCHAR2 (+), CREATED date,last_ddl_time date,timestamp VARCHAR2 (+), STATUS VARCHAR2 (7), TE Mporary VARCHAR2 (1), GENERATED VARCHAR2 (1), SECONDARYVARCHAR2 (1), NAMESPACE number,edition_name VARCHAR2 ()) STORA GE (Dataobjno 68332);. Unloading table T_XIFENFEI_1DUL:Warning:Recreating file "T_xifenfei_1.ctl" 67854 rows unloaded

Additional notes:
1. In the latest Dul 10.2.0.5.20, the scan extents command is not supported
2. In the latest Dul 10.2.0.5.20, no data dictionary data is dug out and the string is converted to a 16 binary value

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

The most authoritative and professional Oracle Case Resource Summary case: Oracle Dul Data Mining No database backup unconventional recovery truncate deleted data table

Original URL: http://www.oracleplus.net/arch/oracle-20160522-217.html

Oracle Research Center Key Words:Oracle dul Data Mining No database backup unconventional recovery truncate deleted data table

Case: Oracle dul Data Mining No database backup unconventional recovery truncate deleted data table