When our Oracle can't recover damage, Oracle also provides a DUL tool to extract data from DataFile.
This experiment is carried out without losing the SYSTEM tablespace, and Oracle can work in a normal operation.
Set up a test environment:
1. Create TableSpace:
Create TableSpace DUL
DataFile 'd: /database/oracle/oradata/ora817/dul.dbf' size
10m
Extent Management Local Uniform Size 40K;
2. Create Table and Data under User Dlinger:
Create Table Test_P (ID VARCHAR2 (10), Name Varchar2 (20)) TableSpace DUL
Partition by Range (ID)
(Partition ID1 Values Less Than ",
Partition ID2 VALUES LESS THAN ('BC')
);
INSERT INTO TEST_P VALUES ('AA', 'DLINGER');
INSERT INTO TEST_P VALUES ('BB', 'DLINGER');
Create Table Test TableSpace DUL AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <5000;
Use DUL Tools:
In D: Create a DUL directory, extract the DUL tool in this directory.
1. Create Init.dul and Control.dul files
Under Win2000, Oracle8i version
Init.dul:
OSD_BIG_ENDIAN_FLAG = FALSE
OSD_DBA_FILE_BITS = 10
OSD_C_STRUCT_ALIGNMENT = 32
OSD_FILE_LEADER_SIZE = 1
OSD_word_size = 32
Control_file = D: /DUL/Control.dul
DB_BLOCK_SIZE = 8192
export_mode = true
Compatible = 8
FILE = DUMP
Control.dul
SQL> Connect INTERNAL
SQL> Spool Control.dul
SQL> SELECT TS #, RFILE #, Name from V $ datafile;
SQL> Spool off
The result of modifying the output is the following format:
0 1 d: /database/oracle/oradata/ora817/system01.dbf
1 2 D: /Database/oRacle/oradata/ora817/rbs01.dbf
2 3 D: /Database/oracle/oradata/ora817/USERS01.DBF
17 4 D: /DATABASE/Orcle/oradata/ora817/imp.dbf
4 5 D: /Database/oRacle/oradata/ora817/tools01.dbf
5 6 D: /DATABASE/Orcle/oradata/ora817/indx01.dbf
6 7 D: /DATABASE/Orcle/oradata/ora817/dr01.dbf
7 8 D: /DATABASE/Orcle/oradata/ora817/nnc_data01.ora8 9 D: /Database/oRacle/oradata/ora817/nnnc_data02.ora
9 10 D: /Database/oRacle/oradata/ora817/nnc_data03.ora
10 11 D: /Database/oRacle/oradata/ora817/nnc_index01.ora
11 12 D: /Database/oRacle/oradata/ora817/nnc_index02.ora
12 13 D: /DATABASE/Orcle/oradata/ora817/nnc_index03.ora
13 14 D: /Database/oRacle/oradata/ora817/oem_repository.ora
14 15 D: /Database/oracle/oradata/ora817/test.ora
18 16 D: /DATABASE/oracle/oradata/ora817/exp.dbf
19 17 D: /Database/oracle/oradata/ora817/dul.dbf
2. Using DUL Tools
Unload the Object Information:
D: / DUL> DUL.EXE DICTV8.DDL
Data Unloader
8.1.0
.2.2 - Internal use only - on fri aug 13 16:07:04 2004
With 64-bit io functions
Copyright (C) 1994/2001 Bernard Van Duijnen All Rights Reserved.
Parameter Altered
Parameter Altered
Parameter Altered
Parameter Altered
. unloading Table Obj $ 25647 ROWS Unlined
. Unloading Table Tab $ 595 ROWS Unlined
unloading table
COL
$ 20493 Rows Unlined
. unloading Table User $ 44 ROWS Unlined
EXIT AND RESTART DUL to Load The First Four Dictionary Tables in the cache
Optionally for Partitioned Tables, Indexes or Lobs or for Migrate
Use bootstrap procedure
For Full Bootstrap Start With the Following Commands:
Scan Database;
Bootstrap; (And Follow the Instructions)
Next, you can start exporting data:
DUL provides four modes of export data: unload database; unload table; unload extent, here, we use unload users to export two test tables created above.
D: / DUL> DUL
Data Unloader
8.1.0
.2.2 - Internal use only - on fri aug 13 16:11:00 2004
WITH 64-BIT IO FunctionScopyRight (C) 1994/2001 Bernard Van Duijnen All Rights Reserved.
DUL: WARNING: RECREATING FILE "DUL.LOG"
Loaded 44 entries from user.dat
Dul: Warning: increased the size of dc_Objects from 25000 to 50000 Entries
Loaded 25647 entries from obj.dat
Loaded 595 entries from tab.dat
Loaded 20493 entries from col.dat
DUL> UNLOAD User Dlinger;
About to Unload Dlinger's Tables ...
. unloading Table test 4999 rows unloaded
DUL: Error: No Partitions Found for Partitioned Table Test_P
. unloading Table Test_P
TABLE TEST_P TOTAL 0 ROWS Unlined
We found that Table Test successfully spread, but for partition table TEST_P, there is no exported data.
In the D: / DUL directory, two DMP files DUMP001 and DUMP002
Let's first put DUMP001 IMP to the user DLING
C: / Documents and Settings / Duanl> IMP UserId = DLING / DLING FULL = Y File = 'D: / DUL / DUMP
001.dmp ';
Import: Release
8.1.7
.4.1 - Production ON Friday August 13 16:22:39 2004
(c) CopyRight 2000 Oracle Corporation. All Rights Reserved.
Connect to: Oracle8i
Enterprise
Edition Release
8.1.7
.4.1 - Production
WITH THE PartInog Option
JServer Release
8.1.7
.4.1 - Production
Exported by the regular path from export: V
07.00.07
Created file
Warning: This object is exported by Bernard's Dul, not the current user
Benard's DUL object is being imported into DLING
Import Table "Test" 4999 line is imported
Successfully terminated, but a warning appears.
View data:
C: / Documents and Settings / Duanl> SQLPLUS DLING / DLING
SQL * Plus: Release
8.1.7
.0.0 - Production ON Friday August 13 16:27:06 2004
(c) CopyRight 2000 Oracle Corporation. All Rights Reserved.
Connected to:
Oracle8i
Enterprise
Edition Release
8.1.7
.4.1 - Production
WITH THE PartInog Option
JServer Release
8.1.7
.4.1 - Production
SQL> SELECT Count (*) from test;
Count (*)
------------
4999
Then handle partition table test_p separately, here we need to use scan tables in DUL,
Prior to this, I must first scan Database.
DUL> Scan DataBase;
TableSpace 0, Data File 1: 33791 Blocks Scanned
TableSpace 1, Data File 2: 38399 Blocks Scanned
TableSpace 2, Data File 3: 13823 Blocks Scanned
TableSpace 17, Data File 4: 1279 Blocks Scanned
TableSpace 4, Data File 5: 1535 Blocks Scanned
TableSpace 5, Data File 6: 7423 Blocks Scanned
TableSpace 6, Data File 7: 11263 Blocks Scanned
TableSpace 7, Data File 8: 6399 Blocks SCANNED
TableSpace 8, Data File 9: 2559 Blocks Scanned
TableSpace 9, Data File 10: 2559 Blocks Scanned
TableSpace 10, Data File 11: 1279 Blocks Scanned
TableSpace 11, Data File 12: 1279 Blocks Scanned
TableSpace 12, Data File 13: 1279 blocks scanned
TableSpace 13, Data File 14: 3840 Blocks Scanned
TableSpace 14, Data File 15: 12799 Blocks Scanned
TableSpace 18, Data File 16: 1279 Blocks Scanned
TableSpace 19, Data File 17: 1279 Blocks Scanned
D: / DUL1>
D: / DUL1> DUL> Scan.out &
Scan Tables;
Here we use the results of Scan Tables to SCAN.Out, read this file, we discover the information related to table test_p:
Analyzing segment: data object id = 26918 segment header at (file = 17 block = 84)
Heap Organized Table
DUL: WARNING: Column 1: Type Based ON Optimistic Nice Voting
COL
Seen Max Pct Printable Numbers Dates RowIDS
No Count INTSZ NULL> 75% 100% Any Nice Any Nice Any Nice
1 1 2 0% 100% 100% 100% 0% 0% 0% 0% 0%
2 1 7 0% 100% 100% 0% 0% 0% 0% 0% 0%
"AA" "DLINGER"
UNLOAD TABLE OBJNO26918 (Col001 Char (2), COL002 Char (7))
Storage (Dataobjno 26918);
Analyzing segment: data object id = 26919 segment header at (file = 17 block = 89) Heap Organized Table
DUL: WARNING: Column 1: Type Based ON Optimistic Nice Voting
COL
Seen Max Pct Printable Numbers Dates RowIDS
No Count INTSZ NULL> 75% 100% Any Nice Any Nice Any Nice
1 1 2 0% 100% 100% 100% 0% 0% 0% 0% 0%
2 1 7 0% 100% 100% 0% 0% 0% 0% 0% 0%
"BB" "DLINGER"
UNLOAD TABLE OBJNO26919 (COL001 Char (2), COL002 Char (7))
Storage (DataObjno 26919);
We will come out to COPY this two commands:
UNLOAD TABLE OBJNO26918 (Col001 Char (2), COL002 Char (7))
Storage (Dataobjno 26918);
UNLOAD TABLE OBJNO26919 (COL001 Char (2), COL002 Char (7))
Storage (DataObjno 26919);
Then use Test_P's column definition statement to replace the relevant content:
Unload Table Objno26918 (ID varchar2 (10), Name varchar2 (20))
Storage (Dataobjno 26918);
Unload Table Objno26919 (ID varcha2 (10), Name varchar2 (20))
Storage (DataObjno 26919);
In DUL, for each partition, it will be handled in a separate table. Use such a command to export the data of the partition table:
DUL> Unload Table Objno26918 (ID varchar2 (10), Name varchar2 (20))
DUL 2> Storage (Dataobjno 26918);
. unloading Table Objno26918 1 ROW unlined
DUL> Unload Table Objno26919 (id varcha2 (10), Name varchar2 (20))
DUL 2> Storage (DataObjno 26919);
. unloading Table Objno26919 1 ROW unlined
DUL> Quit;
We put this DUMP file IMP to the user DLING:
We found that DLING users add two Table:
SQL> Connect DLING / DLING;
SQL> SELECT TABLE_NAME from User_Tables;
Table_name
------------------------------
Objno26918
Objno26919
Test
Then we recreate Table with Test_P, import the data of Objno26918 and Objno26919 to Table Test_P, and then Drop Objno26919 and Objno26918.: SQL> Create Table Test_P (ID VARCHAR2 (10), Name Varchar2 (20)) TableSpace DUL
2 Partition by Range (ID)
3 (Partition ID1 VALUES LESS THAN ('AB'),
4 Partition ID2 VALUES LESS THAN ('BC'));
Table created
SQL> INSERT INTO TEST_P SELECT * from Objno26918;
1 row inserted
SQL> INSERT INTO TEST_P SELECT * from Objno26919;
1 row inserted
SQL> commit;
Commit completion
