AUL/MyDUL Recovery Step by Step
AUL/MyDUL is an alternative to Oracle DUL. The following is a step-by-step example of how to recover using this utility.
Get a file list
We can get a file list from v$datafile view. If you haven't lost your control file you can mount the database and run the query, otherwise you can manually list all the files.
SQL> col name format a40
SQL> select file#,rfile#,name from v$datafile;
FILE# RFILE# NAME
---------- ---------- ----------------------------------------
1 1 C:\ORACLE\ORADATA\SYSTEM01.DBF
2 2 C:\ORACLE\ORADATA\UNDOTBS01.DBF
3 3 C:\ORACLE\ORADATA\SYSAUX01.DBF
4 4 C:\ORACLE\ORADATA\TEST01.DBF
5 5 C:\ORACLE\ORADATA\AULTEST01.DBF
Create a config file for AUL/MyDUL
The config file is a pure text file, which contains three columns (file#, rfile#, name). We just copy the rows returned from the query to a text file "db10g.cfg". If the file headers of data files are not corrupted, "file#" and "rfile#" will be corrected when you open the config file in AUL/MyDUL.
C:\MYDUL>type db10g.cfg
0 0 C:\ORACLE\ORADATA\SYSTEM01.DBF
0 0 C:\ORACLE\ORADATA\UNDOTBS01.DBF
0 0 C:\ORACLE\ORADATA\SYSAUX01.DBF
0 0 C:\ORACLE\ORADATA\TEST01.DBF
0 0 C:\ORACLE\ORADATA\AULTEST01.DBF
Open config file in AUL/MyDUL
AUL/MyDUL will read the file list from config file, and read the file header, and correct the wrong "file#"/"rfile#" you provided in the config file. Also we have 5 files. But the demo licence can only allow you to open 4 files.
Registration Code: 55E2-4639-864D-F8C3
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g, Trial Version 3.1.9
(C) Copyright Lou Fangxin 2005-2006 (AnySQL.net), all rights reserved.
AUL> open db10g.cfg
* ts# fno rfn ver bsize blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------------------
Y 0 1 1 a2 8192 32768 C:\ORACLE\ORADATA\SYSTEM01.DBF
Y 1 2 2 a2 8192 8192 C:\ORACLE\ORADATA\UNDOTBS01.DBF
Y 2 3 3 a2 8192 8192 C:\ORACLE\ORADATA\SYSAUX01.DBF
Y 4 4 4 a2 8192 3072 C:\ORACLE\ORADATA\TEST01.DBF
Unload the dict information
AUL/MyDUL will unload "USER$","OBJ$","TAB$","COL$" information to four text files. With dict information, recovery will be very easy for you.
AUL> UNLOAD TABLE USER$;
2006-08-04 09:39:00
2006-08-04 09:39:00
AUL> UNLOAD TABLE OBJ$;
2006-08-04 09:39:07
2006-08-04 09:39:08
AUL> UNLOAD TABLE TAB$;
2006-08-04 09:39:12
2006-08-04 09:39:13
AUL> UNLOAD TABLE COL$;
2006-08-04 09:39:17
2006-08-04 09:39:18
AUL> ! dir AUL*.TXT
Volume in drive C has no label.
Volume Serial Number is 5CF4-08FE
Directory of C:\MYDUL
2006-08-04 09:39 1,479,173 AULCOL.TXT
2006-08-04 09:39 272,935 AULOBJ.TXT
2006-08-04 09:39 36,112 AULTAB.TXT
2006-08-04 09:39 428 AULUSR.TXT
4 File(s) 1,788,648 bytes
Use LIST command to generate the recover command
The "LIST TABLE username" command will print out the commands to recover all the tables under given username.
AUL> LIST TABLE MYDUL
UNLOAD TABLE MYDUL.T_PART TO T_PART.txt;
UNLOAD TABLE MYDUL.T_OBJECT TO T_OBJECT.txt;
UNLOAD TABLE MYDUL.T_LOB TO T_LOB.txt;
UNLOAD TABLE MYDUL.T_CLOB TO T_CLOB.txt;
UNLOAD TABLE MYDUL.T_PART PARTITION P2 TO T_PART_P2.txt;
UNLOAD TABLE MYDUL.T_PART PARTITION P1 TO T_PART_P1.txt;
Run the command to recover the rows
I will try to recover the rows of table "T_PART". This table is partitioned. We need to recover it partition by partition. Here I omit the "TO filename" to print rows to the screen directly.
AUL> UNLOAD TABLE MYDUL.T_PART PARTITION P2;
2006-08-04 09:40:42
Unload OBJD=9928 FILE=4 BLOCK=19 CLUSTER=0 ...
150,1
2006-08-04 09:40:42
AUL> UNLOAD TABLE MYDUL.T_PART PARTITION P1;
2006-08-04 09:40:51
Unload OBJD=9927 FILE=4 BLOCK=11 CLUSTER=0 ...
50,1
2006-08-04 09:40:51
Following is the query result from SQL*Plus:
SQL> SELECT * FROM T_PART;
COL1 COL2
---------- ----------
50 1
150 1
SQL> SELECT * FROM T_PART PARTITION (P1);
COL1 COL2
---------- ----------
50 1
SQL> SELECT * FROM T_PART PARTITION (P2);
COL1 COL2
---------- ----------
150 1
Congratulations
The only thing left is congratulations!
If you hit some problems, check the command reference page, or contact me.