Case: Oracle dul Data Mining non-regular recovery drop deleted data table without backup

non-routine recovery with Oracle Dul tool to recover data from drop-deleted Oracle data tables without backup

Dul to recover a drop object, you need to provide two information
1. Table space to which the deleted table belongs (not required)
2. Deleted table structure (required)

simulating the deletion of tables in a 1.Oracle database

--Creating a test table sql> create table T_dul_drop tablespace czum  2 as  3  select * from Dba_tables; Table created.--Backup is deleted to make it easier to compare and provide test table structure sql> CREATE TABLE T_dul_drop_bak tablespace users  2 as  select * from T_ Dul_drop; Table created. sql> alter system switch logfile; System altered. Sql> Select COUNT (*) from T_dul_drop;  COUNT (*)----------      1785sql> drop table Chf.t_dul_drop purge; Table dropped. Sql> alter system checkpoint; System altered.

2. Use Logminer to find data_object_id

Delete from "SYS". " obj$ "where" obj# "= ' 68474 ' and" dataobj# "= ' 68474 ' and" owner# "= ' A" and "NAME" = ' t_dul_drop ' and ' NAMESPACE ' = ' 1 '  and "SubName" is NULL and "type#" = ' 2 ' and "CTIME" = to_date (' 04-feb-13 ', ' dd-mon-rr ') and "MTIME" = to_date (' 04-feb-13 ', ' DD-MON-RR ') and "stime" = to_date (' 04-feb-13 ', ' dd-mon-rr ') and "STATUS" = ' 1 ' and ' Remoteowner ' is NULL and "Linkname" I  S null and "FLAGS" = ' 0 ' and "oid$" is null and "SPARE1" = ' 6 ' and "SPARE2" = ' 1 ' and "SPARE3" = ' A "and" SPARE4 "is null and "SPARE5" is null and "SPARE6" is null and ROWID = ' Aaaaasaabaaapzcaav ';

As you can see, the data_object_id of the deleted table is 68474.

3.DUL Recover deleted tables

--dul version E:\dul10>dul.exedata unloader 10.2.4.37-oracle Internal only-on Mon Feb 23:49:50 2013with 64-bit io funct Ionscopyright (c) 1994 Bernard van Duijnen All rights reserved. Strictly Oracle Internal use onlydul> ALTER SESSION SET use_scanned_extent_map = TRUE; Parameter altered--scan belongs to table space dul> scan tablespace 6;  Scanning tablespace 6, data file 6 ...  Segment header and 331 data blocks tablespace 6, data file 6:1279 blocks scannedreading EXT.dat entries loaded and Sorted entriesreading SEG.dat Entries loadedreading COMPATSEG.dat 0 entries loadedreading SCANNEDLOBPAGE.dat 0 entr IES loaded and sorted 0 entries--scan tables Get demand table (can check data sample) dul> scan tables; UNLOAD TABLE OBJNO68474 (COL001 VARCHAR2 (one), COL002 VARCHAR2 (+), COL003 VARCHAR2 (6), COL004 VARCHAR2 (+), COL00        5 VARCHAR2 (+), COL006 VARCHAR2 (5), COL007 number, COL008 number, COL009 number, COL010 number, COL011 number , COL012 number, COL013 number, COL014 number, COL015 CHAR, COL016 number, COL017 number, COL018 VARCHAR2 (3), COL019 VARCHAR2 (1), COL020 number, COL021 number, COL 022 number, COL023 number, COL024 number, COL025 number, COL026 number, COL027 number, COL028 VARCHAR2 (10) , COL029 VARCHAR2, COL030 VARCHAR2 (5), COL031 VARCHAR2 (7), COL032 number, COL033 DATE, COL034 VARCHAR2 (3), COL 035 VARCHAR2, COL036 VARCHAR2 (1), COL037 VARCHAR2 (1), COL038 VARCHAR2 (3), COL039 VARCHAR2 (7), COL040 V ARCHAR2 (7), COL041 VARCHAR2 (7), COL042 VARCHAR2 (8), COL043 VARCHAR2 (3), COL044 VARCHAR2 (2), COL045 VARCHAR2 (All), C OL046 VARCHAR2 (8), COL047 VARCHAR2 (3), COL048 VARCHAR2 (3), COL049 VARCHAR2 (8), COL050 VARCHAR2 (8), COL051 VARCHAR2 (5), COL052 VARCHAR2 (2), COL053 VARCHAR2 (2), COL054 VARCHAR2 (3), COL055 VARCHAR2 (7)) STORAGE (Dataobjno 68474                         );--Recover Delete table (Business supply table structure) dul> unload table T_dul_drop (2 OWNER VARCHAR2 (3 table_name) VarCHAR2 (4), Tablespace_name VARCHAR2 (+), 5 cluster_name                                             VARCHAR2 (6), Iot_name VARCHAR2 (7), STATUS VARCHAR2 (8), 8 pct_free number, 9 Pct_us      ED number, Ini_trans number                                     , Max_trans number, initial_extent                                        Number, next_extent number, min_extents Number, max_extents number, Pct_increas  E number, freelists number,    Freelist_groups                                Number, LOGGING VARCHAR2 (3), BAC Ked_up VARCHAR2 (1), Num_rows Numbe                                       R, BLOCKS number, empty_blocks                                          Number, avg_space number, chain_cnt Number, Avg_row_len number, Avg_spac      E_freelist_blocks number, num_freelist_blocks number                                          , Degree VARCHAR2 (), INSTANCES                                         VARCHAR2, CACHE VARCHAR2 (Ten), Table_lock VaRCHAR2 (8), sample_size number, last_analyzed                                           DATE, partitioned VARCHAR2 (3), Iot_type VARCHAR2 (temporary), Panax Notoginseng VARCHAR2 (1), Secon Dary VARCHAR2 (1), NESTED VARCHAR                                        2 (3), Buffer_pool VARCHAR2 (7), Flash_cache                                       VARCHAR2 (7), Cell_flash_cache VARCHAR2 (7), row_movement                                          VARCHAR2 (8), Global_stats VARCHAR2 (3), user_stats VARCHAR2 (3), DURATION VARCHAR2 (15)      , Skip_corrupt                                 VARCHAR2 (8), monitoring VARCHAR2 (3), CL Uster_owner VARCHAR2 (+), DEPENDENCIES Varc                                       HAR2 (8), Wuyi COMPRESSION VARCHAR2 (8), compress_for                                          VARCHAR2, DROPPED VARCHAR2 (3), read_only VARCHAR2 (3), segment_created VARCHAR2 (3), Result_ CACHE VARCHAR2 (7)) STORAGE (Dataobjno 68474);. Unloading table T_DUL_DROPDUL:Warning:Recreating file "T_dul_drop.ctl" 1785 rows unloaded

4. Simulate business rules provided, create tables

Sql> CREATE TABLE T_dul_drop as SELECT * from T_dul_drop_bak where 1=0; Table created.

5. Importing Oracle's deleted data

E:\dul10>sqlldr Chf/xifenfei control=t_dul_drop.ctlsql*loader:release 11.2.0.3.0-production on Mon Feb 4 23:35:57 20  13Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved. Commit Point reached-logical Record Count 64Commit point reached-logical Record Count 128Commit Point Reached-logica L Record Count 192Commit point reached-logical Record Count 256Commit point reached-logical Record Count 320Commit poi NT Reached-logical Record Count 384Commit point reached-logical Record Count 448Commit point reached-logical Record Count 512Commit Point reached-logical Record Count 576Commit point reached-logical Record Count 640Commit Point Reache D-logical Record Count 704Commit point reached-logical Record Count 768Commit point reached-logical Record Count 832 Commit Point reached-logical Record Count 896Commit point reached-logical Record Count 960Commit Point reached-logic Al Record Count 1024Commit Point reached-logical Record Count 1088Commit Point reached-logical Record Count 1152Commit point reached-logical Record Count 1216Commit Point Reached-lo Gical Record Count 1280Commit point reached-logical Record Count 1344Commit point reached-logical Record Count 1408Com MIT point reached-logical Record Count 1472Commit point reached-logical Record Count 1536Commit Point Reached-logica L Record Count 1600Commit point reached-logical Record Count 1664Commit point reached-logical Record Count 1728Commit Point Reached-logical Record Count 1785

6. Verifying Recovery Data Results

Sql> Select COUNT (*) from T_dul_drop;  COUNT (*)----------      1785sql> Select Owner,table_name from T_dul_drop where Rownum<10;owner                          table_ NAME------------------------------------------------------------SYS                            idl_char$sys                            idl_ub2$sys                            idl_ Sb4$sys                            error$sys                            settings$sys                            ncomp_dll$sys                            procedurejava$sys                            procedurec$sys                            Procedureplsql$9 rows selected.

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

The most authoritative and professional example of Oracle Case resource Summary: Oracle DUL Data Mining non-regular recovery drop deleted data table without backup

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

Oracle Research Center Key Words:Oracle Dul Data Mining non-regular recovery drop deleted data table without backup

Case: Oracle dul Data Mining non-regular recovery drop deleted data table without backup