DBA Tools

    All the table structure informations are stored in system tablespace, in the user data tablespace, there are only a data object id and the rows you wanted. So we could scan all the data files and recover all the rows, but we actually do not know which table the rows belong, the relation ship between data object id and tables are stored in system tablespace. Following is a step by step example of using AUL/MyDUL to do recovery.

    I create a very small tablespace contains only one datafile (Download it for demo), and create several tables in this tablespace, insert few rows, then do a checkpoint or shutdown normal to force Oracle write everything to disk, as following:

SQL> CREATE TABLE TEST1 (COL1 VARCHAR2(10));
SQL> CREATE TABLE TEST2 (COL1 VARCHAR2(10));
SQL> CREATE TABLE TEST3 (COL1 VARCHAR2(10));

    Then we open the config file, use "SCAN TABLE TO filename" to get the object list by scanning all the data blocks. As following:

AUL> open db10g.cfg
*  ts#  fno  rfn ver bsize     blocks filename
- ---- ---- ---- --- ----- ---------- ----------------------
Y    5    5    5 a2   8192        256 AULTEST01.DBF
AUL> scan table to scan_table.log
2006-08-12 13:02:43
2006-08-12 13:02:43

    Then we open the scan_table.log file, you could see the following information, the line started with "CMD:" is the unload command AUL/MyDUL guessed for you, why I call it guess is because the column type maybe incorrect for unloading. For each distinct object, 5 sample rows will be printed in hex mode. By grep utilities in Unix/Linux, we could quickly get a script to unload all the tables (include dropped or truncated tables, even piece of dropped or truncated tables). Following is the result of the scan table: