案例:Oracle dul資料採礦 沒有備份情況下非常規恢複drop刪除的資料表

標籤：

通過Oracle dul工具在沒有備份情況下進行非常規恢複，找出drop刪除的Oracle資料表中的資料進行恢複

dul對被drop對象進行恢複，需要提供兩個資訊
1.被刪除表所屬資料表空間(非必須)
2.被刪除表結構(必須)

1.Oracle資料庫中類比刪除表

--建立測試表SQL> create table t_dul_drop tablespace czum  2  as  3  select * from dba_tables;Table created.--備份被刪除表資料，便於比較和提供測試表結構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.使用logminer找到data_object_id

delete from "SYS"."OBJ$" where "OBJ#" = ‘68474‘ and "DATAOBJ#" = ‘68474‘ and "OWNER#" = ‘61‘ 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" IS NULL and "FLAGS" = ‘0‘ and "OID$" IS NULL and "SPARE1" = ‘6‘ and "SPARE2" = ‘1‘ and "SPARE3" = ‘61‘ and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = ‘AAAAASAABAAAPzCAAV‘;

這裡可以知道，被刪除表的data_object_id為68474

3.DUL恢複被刪除表

--dul版本E:\dul10>dul.exeData UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013with 64-bit io functionsCopyright (c) 1994 2010 Bernard van Duijnen All rights reserved. Strictly Oracle Internal use OnlyDUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;Parameter altered--掃描所屬資料表空間DUL> scan tablespace 6;Scanning tablespace 6, data file 6 ...  13 segment header and 331 data blocks  tablespace 6, data file 6: 1279 blocks scannedReading EXT.dat 13 entries loaded and sorted 13 entriesReading SEG.dat 13 entries loadedReading COMPATSEG.dat 0 entries loadedReading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries--scan tables得到需求表(可以核對資料範例)DUL> scan tables;UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)        , COL004 VARCHAR2(20), COL005 VARCHAR2(30), 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, COL022 NUMBER, COL023 NUMBER        , COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER        , COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)        , COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)        , COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)        , COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)        , COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 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 );--恢複刪除表(業務提供表結構)DUL> unload table t_dul_drop(  2  OWNER                              VARCHAR2(30),  3  TABLE_NAME                         VARCHAR2(30),  4  TABLESPACE_NAME                                    VARCHAR2(30),  5  CLUSTER_NAME                                       VARCHAR2(30),  6  IOT_NAME                                           VARCHAR2(30),  7  STATUS                                             VARCHAR2(8) ,  8  PCT_FREE                                           NUMBER      ,  9  PCT_USED                                           NUMBER      , 10  INI_TRANS                                          NUMBER      , 11  MAX_TRANS                                          NUMBER      , 12  INITIAL_EXTENT                                     NUMBER      , 13  NEXT_EXTENT                                        NUMBER      , 14  MIN_EXTENTS                                        NUMBER      , 15  MAX_EXTENTS                                        NUMBER      , 16  PCT_INCREASE                                       NUMBER      , 17  FREELISTS                                          NUMBER      , 18  FREELIST_GROUPS                                    NUMBER      , 19  LOGGING                                            VARCHAR2(3) , 20  BACKED_UP                                          VARCHAR2(1) , 21  NUM_ROWS                                           NUMBER      , 22  BLOCKS                                             NUMBER      , 23  EMPTY_BLOCKS                                       NUMBER      , 24  AVG_SPACE                                          NUMBER      , 25  CHAIN_CNT                                          NUMBER      , 26  AVG_ROW_LEN                                        NUMBER      , 27  AVG_SPACE_FREELIST_BLOCKS                          NUMBER      , 28  NUM_FREELIST_BLOCKS                                NUMBER      , 29  DEGREE                                             VARCHAR2(20), 30  INSTANCES                                          VARCHAR2(20), 31  CACHE                                              VARCHAR2(10), 32  TABLE_LOCK                                         VARCHAR2(8) , 33  SAMPLE_SIZE                                        NUMBER      , 34  LAST_ANALYZED                                      DATE        , 35  PARTITIONED                                        VARCHAR2(3) , 36  IOT_TYPE                                           VARCHAR2(12), 37  TEMPORARY                                          VARCHAR2(1) , 38  SECONDARY                                          VARCHAR2(1) , 39  NESTED                                             VARCHAR2(3) , 40  BUFFER_POOL                                        VARCHAR2(7) , 41  FLASH_CACHE                                        VARCHAR2(7) , 42  CELL_FLASH_CACHE                                   VARCHAR2(7) , 43  ROW_MOVEMENT                                       VARCHAR2(8) , 44  GLOBAL_STATS                                       VARCHAR2(3) , 45  USER_STATS                                         VARCHAR2(3) , 46  DURATION                                           VARCHAR2(15), 47  SKIP_CORRUPT                                       VARCHAR2(8) , 48  MONITORING                                         VARCHAR2(3) , 49  CLUSTER_OWNER                                      VARCHAR2(30), 50  DEPENDENCIES                                       VARCHAR2(8) , 51  COMPRESSION                                        VARCHAR2(8) , 52  COMPRESS_FOR                                       VARCHAR2(12), 53  DROPPED                                            VARCHAR2(3) , 54  READ_ONLY                                          VARCHAR2(3) , 55  SEGMENT_CREATED                                    VARCHAR2(3) , 56  RESULT_CACHE                                       VARCHAR2(7)) 57   STORAGE( DATAOBJNO 68474 );. unloading table                T_DUL_DROPDUL: Warning: Recreating file "T_DUL_DROP.ctl"    1785 rows unloaded

4.類比商務規則提供，建立表

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;Table created.

5.匯入Oracle被刪除的資料

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 2013Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Commit point reached - logical record count 64Commit point reached - logical record count 128Commit point reached - logical record count 192Commit point reached - logical record count 256Commit point reached - logical record count 320Commit point 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 reached - logical record count 704Commit point reached - logical record count 768Commit point reached - logical record count 832Commit point reached - logical record count 896Commit point reached - logical record count 960Commit point reached - logical record count 1024Commit point reached - logical record count 1088Commit point reached - logical record count 1152Commit point reached - logical record count 1216Commit point reached - logical record count 1280Commit point reached - logical record count 1344Commit point reached - logical record count 1408Commit point reached - logical record count 1472Commit point reached - logical record count 1536Commit point reached - logical record count 1600Commit point reached - logical record count 1664Commit point reached - logical record count 1728Commit point reached - logical record count 1785

6.驗證恢複資料結果

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----------------------------------------

最權威、專業的Oracle案例資源匯總之案例:Oracle dul資料採礦 沒有備份情況下非常規恢複drop刪除的資料表

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

Oracle研究中心 關鍵詞：Oracle dul資料採礦沒有備份情況下非常規恢複drop刪除的資料表

案例:Oracle dul資料採礦 沒有備份情況下非常規恢複drop刪除的資料表