AnySQL.net English

    Change Log:

1, Change the register code length.

2, When mark a corrupted block, check the list first.

3, More effective logic to find block in the corrupted list.

4, New command "corrupt list" to list corrupted blocks.

    For example:

Register Code: Z239-VV3O-HT3G-AA43-G027
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g, release 3.2.0

(C) Copyright Lou Fangxin 2005-2006 (IamDBA.com), 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
AUL> corrupt file 4 block 10
AUL> corrupt file 4 block 11
AUL> corrupt file 4 block 8
AUL> corrupt file 4 block 11
AUL> corrupt file 4 block 4
AUL> corrupt list
Corrupted Blocks List:
  RDBA = 16777220 , FILE = 4 , BLOCK = 4
  RDBA = 16777224 , FILE = 4 , BLOCK = 8
  RDBA = 16777226 , FILE = 4 , BLOCK = 10
  RDBA = 16777227 , FILE = 4 , BLOCK = 11
AUL> bcheck rdba 16777224
Block Tail Unmatched!
AUL> bcheck rdba 16777223
Block Tail Matched!

    Due to the register code length change, the license logic have been changed, download the latest version.

    Change Log:

1, Change the column type of "UNLOAD" command to "RAW" (original: "SKIP")
2, Enhance of the column type guess for "SCAN TABLE" command
3, Change the logic of "SCAN TABLE" command to better output

    I will create a demo table in a 2M tablespace, as following :

CREATE TABLE TEST
(
   COL1 CHAR(10),
   COL2 VARCHAR2(10),
   COL3 DATE, COL4 NUMBER,
   COL5 BINARY_FLOAT,
   COL6 BINARY_DOUBLE
);

INSERT INTO TEST VALUES('A','A1',SYSDATE,1.11,1.0,1.0);
INSERT INTO TEST VALUES('B','B1',SYSDATE,2.11,2.0,2.02);
INSERT INTO TEST VALUES('C','C1',SYSDATE,3.11,3.0,3.02);
INSERT INTO TEST VALUES('D','D1',SYSDATE,4.11,4.0,4.02);
INSERT INTO TEST VALUES('E','E1',SYSDATE,5.11,5.0,5.02);

    Then I will test the new "UNLOAD" command without specify any column type information, you will see all column values are printed out in hex code:

AUL> open db10g.cfg
*  ts#  fno  rfn ver bsize     blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------------------
Y    5    5    5 a2   8192        256 C:\ORACLE\ORADATA\AULTEST01.DBF
AUL> SCAN HEADER
2006-08-11 22:59:29
RDBA=0x0140000b(5/11),type=0x23,fmt=0xa2,seq=0x02,flag=0x04
seg/obj=0x000026ef=9967

2006-08-11 22:59:29
AUL> UNLOAD OBJECT 9967;
2006-08-11 22:59:45
41202020202020202020,4131,786a080b17012d,c1020c,bf800000,bff000...
42202020202020202020,4231,786a080b17013c,c1030c,c0000000,c00028...
43202020202020202020,4331,786a080b17020d,c1040c,c0400000,c00828...
44202020202020202020,4431,786a080b17021c,c1050c,c0800000,c01014...
45202020202020202020,4531,786a080b170227,c1060c,c0a00000,c01414...
2006-08-11 22:59:45

    Now I will test the "SCAN TABLE" command, it will tell you a command to unload the data (you could download the 2M tablespace for test), as following:

AUL> SCAN TABLE
2006-08-11 23:02:20
RDBA=0x0140000f(5/15),type=0x06,fmt=0xa2,seq=0x07,flag=0x06
seg/obj=0x000026ef=9967,csc=0x0000.000996dc,itc=2,typ=1 - DATA
tab#=  0     nrow=   5     offs=   0

41202020202020202020,4131,786a080b17012d,c1020c,bf800000,bff000...
42202020202020202020,4231,786a080b17013c,c1030c,c0000000,c00028...
43202020202020202020,4331,786a080b17020d,c1040c,c0400000,c00828...
44202020202020202020,4431,786a080b17021c,c1050c,c0800000,c01014...
45202020202020202020,4531,786a080b170227,c1060c,c0a00000,c01414...
Column Count=6
CMD:UNLOAD OBJECT 9967 CLUSTER 0 COLUMN  VARCHAR VARCHAR DATE NUMBER BINARY_FLOAT BINARY_DOUBLE

2006-08-11 23:02:20
AUL> UNLOAD OBJECT 9967 CLUSTER 0 COLUMN  VARCHAR VARCHAR DATE NUMBER BINARY_FLOAT BINARY_DOUBLE;
2006-08-11 23:07:03
A         ,A1,2006-08-11 22:00:44,1.11,1.000000,1.000000
B         ,B1,2006-08-11 22:00:59,2.11,2.000000,2.020000
C         ,C1,2006-08-11 22:01:12,3.11,3.000000,3.020000
D         ,D1,2006-08-11 22:01:27,4.11,4.000000,4.020000
E         ,E1,2006-08-11 22:01:38,5.11,5.000000,5.020000
2006-08-11 23:07:03

    This is the solution for lost system tablespace in AUL/MyDUL. Enjoy it!

    The basic target of AUL/MyDUL is to recover data, so the dmp file contains the data you required. When you use exp utility to export a table from Oracle database, the dmp file contains the full syntax of recreate the table, indexes, all the constraints, triggers of the target table. But in the dmp file of AUL/MyDUL, it just contains a basic create table SQL, it will not contain the following:

1, The storage definition of the table.
2, The partition & sub-partition definition.
3, The indexes associated with the table.
4, The constraints associated with the table except the "NOT NULL".
5, The default values defined on the columns.
6, The triggers defined on the table.
7, The privileges granted to other schema.

    And the export schema of the dmp file is always "mydul", each table a dmp file, the dmp format version is Oracle 8.1.7, you must use exp version 8.1.7 or higher to import it to new database. And no multiple dmp file support as exp do. Some customers have asked me this question. Maybe I will enhance it in later releases (no recent plan for these feature).

    All the table structure informations are stored in system tablespace, in the 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 from. 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:

RDBA=0x01400015(5/21),type=0x06,fmt=0xa2,seq=0x03,flag=0x06
seg/obj=0x000026fb=9979,csc=0x0000.000999cd,itc=2,typ=1 - DATA
tab#=  0     nrow=  11     offs=   0

545f50415254
545f4f424a454354
545f4c4f42
545f434c4f42
42494e24773455692b46
Column Count=1
CMD:UNLOAD OBJECT 9979 CLUSTER 0 COLUMN  VARCHAR

RDBA=0x0140001d(5/29),type=0x06,fmt=0xa2,seq=0x03,flag=0x06
seg/obj=0x000026fc=9980,csc=0x0000.000999cd,itc=2,typ=1 - DATA
tab#=  0     nrow=  11     offs=   0

545f50415254
545f4f424a454354
545f4c4f42
545f434c4f42
42494e24773455692b46
Column Count=1
CMD:UNLOAD OBJECT 9980 CLUSTER 0 COLUMN  VARCHAR

RDBA=0x01400025(5/37),type=0x06,fmt=0xa2,seq=0x03,flag=0x06
seg/obj=0x000026fd=9981,csc=0x0000.000999cd,itc=2,typ=1 - DATA
tab#=  0     nrow=  11     offs=   0

545f50415254
545f4f424a454354
545f4c4f42
545f434c4f42
42494e24773455692b46
Column Count=1
CMD:UNLOAD OBJECT 9981 CLUSTER 0 COLUMN  VARCHAR

    Then we test the unload commands generated, you will see rows are recovered, in this case the three tables contain same rows:

AUL> UNLOAD OBJECT 9979 CLUSTER 0 COLUMN  VARCHAR;
2006-08-12 13:17:52
T_PART
T_OBJECT
T_LOB
T_CLOB
BIN$w4Ui+F
BIN$JRhlBf
TEST
TEST2
BIN$IRebiY
TEST1
TEST3
2006-08-12 13:17:52
AUL> UNLOAD OBJECT 9980 CLUSTER 0 COLUMN  VARCHAR;
2006-08-12 13:18:01
T_PART
T_OBJECT
T_LOB
T_CLOB
BIN$w4Ui+F
BIN$JRhlBf
TEST
TEST2
BIN$IRebiY
TEST1
TEST3
2006-08-12 13:18:01
AUL> UNLOAD OBJECT 9981 CLUSTER 0 COLUMN  VARCHAR;
2006-08-12 13:18:09
T_PART
T_OBJECT
T_LOB
T_CLOB
BIN$w4Ui+F
BIN$JRhlBf
TEST
TEST2
BIN$IRebiY
TEST1
TEST3
2006-08-12 13:18:09

    Even with this command, the recovery of lost system tablespace is more complex. Enjoy it.

    In Oracle we know that it's possible to contain some corrupted blocks that cannot be read, We could use dbv or rman to check them. But in AUL/MyDUL, it's still possible to recover rows from these corrupted blocks, unless the corruption caused the AUL/MyDUL abort the running, due to the corruption caused the AUL/MyDUL memory access out of bound. I do not want to call them corrupted blocks, I named them as dead blocks. We could get them skipped with some extra steps.

    First we need to identify them out, I will introduce "set verbose 1" command to you, with this setting, AUL/MyDUL will print out the relative data block address (RDBA) of the last processing block, so if program died, we just mark the last block as corrupted, then we restart the processing. If there are more than one dead blocks, we may take some round trips. As following:

AUL> set verbose 1
  Current VERBOSE is : 1
AUL> unload table mydul.t_lob to t_lob.txt;
2006-08-12 14:11:15
Unload OBJD=9946 FILE=4 BLOCK=219 CLUSTER=0 ...
Recover rows from RDBA=16777436 ...
Recover rows from RDBA=16777437 ...
Recover rows from RDBA=16777438 ...
Recover rows from RDBA=16777439 ...
Recover rows from RDBA=16777440 ...
<< program aborted here>>

    Then we mark the last block as corrupted and restart the processing:

AUL> corrupt rdba 16777440
AUL> corrupt list
Corrupted Blocks List:
  RDBA = 16777440 , FILE = 4 , BLOCK = 224
AUL> unload table mydul.t_lob to t_lob.txt;
2006-08-12 14:13:46
Unload OBJD=9946 FILE=4 BLOCK=219 CLUSTER=0 ...
Recover rows from RDBA=16777436 ...
Recover rows from RDBA=16777437 ...
Recover rows from RDBA=16777438 ...
Recover rows from RDBA=16777439 ...
Recover rows from RDBA=16777440 ...
Block check failed at RDBA=0x010000e0=16777440
Recover rows from RDBA=16777553 ...
Recover rows from RDBA=16777554 ...
2006-08-12 14:13:49

    The "Block check failed ..." line means AUL/MyDUL skip this marked block. Enjoy it!