AnySQL.net English

    AUL V4 support Oracle BLOB/CLOB data types compared to AUL V3. I started this enhancement since Dec, 2006. And release the AUL V4 beta version at the beginning of Jan, 2007. I supposed to announce the formal release after a successful recovery of LOB data, it's time to announce this great news.

    Few days ago, AUL V4 recovered a LOB table of my customer, totally 12292 images, PDF/DOC files recovered, and the result was perfect, just found some white PDF file, it should not be AUL V4's problem. So I formally announce the retire of AUL V3, and the latest version is 4.0.2.

    I want to give special thanks to my customers, initially we use the beta version for recovery, and get some bugs. My customer gave me enough debug information and enough support for the fixing work. This customer read across my site carefully to avoid asking too much question of the usage of AUL software. Mainly two bugs are fixed :

• 1, Cannot recover LOB value stored in chained or migrated row.
    Reason: did not pass the correct LOB index information to the subroutine.
• 2, Cannot recover LOB value except for the first LOB column of table.
    Reason: did not get correct LOB index information for the LOB columns.

    I have stopped download of AUL V4, the old link has been pointed to the AUL 4 binary. The next version will support compress tables & indexes, but still no time schedule for it.

    When we cannot open the database for access, we can try AUL software. With system available we can use the DMP format for recovery, however TXT format is more robust according to the previous experience. And sometime DBA cannot find out the script to recreate the tables in new database, so generate a script to recreate the tables is useful during recovery. I made this change, when you recover database with TXT format, a new SQL file will be created.

    For example, describe the table structure in AUL.

AUL> desc anysql.emp

Storage(OBJ#=10560 OBJD=10560 TS=4 FILE=4 BLOCK=627 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
  1   1   1 EMPNO                         NUMBER(4) NOT NULL
  2   2   2 ENAME                         VARCHAR2(30)
  3   3   3 JOB                           VARCHAR2(9)
  4   4   4 MGR                           NUMBER(4)
  5   5   5 HIREDATE                      DATE
  6   6   6 SAL                           NUMBER(7,2)
  7   7   7 COMM                          NUMBER(7,2)
  8   8   8 DEPTNO                        NUMBER(2)

    Recover the table by TXT format.

AUL> unload table anysql.emp to emp.txt;
2007-05-31 21:43:35
Unload OBJD=10560 FILE=4 BLOCK=627 CLUSTER=0 ...
2007-05-31 21:43:35
AUL>

    You will find that a new SQL file (tablename_syntax.sql) will be created under the directory where you run the AUL, for example (EMP_syntax.sql) :

CREATE TABLE "EMP" ( "EMPNO" NUMBER(4) NOT NULL , "ENAME" VARCHAR2(30) , "JOB" VARCHAR2(9) , "MGR" NUMBER(4) , "HIREDATE" DATE , "SAL" NUMBER(7,2) , "COMM" NUMBER(7,2) , "DEPTNO" NUMBER(2) );

    Hope this improvement can fasten the database recovery.

    How many files can be stored in on directory? I don't know the answer. But when one directory have a lot of files, it will be very hard to operate, and may slow down the file access speed.

    I performed two times of LOB recovery, all recovered in TXT mode. At this mode, each LOB values is stored as a single file in the directory where you run the AUL binary. For this two times, there are no so many LOB rows, the first time has about 12000 LOB values, the second time has about 15000 LOB values, so there is no problem for all of them are stored in one directory.

    But how about there are millions of LOB values? I add a new option to AUL to distribute the LOB files among different subdirectories. The option name is "MAXLOBDIR" with default value 500 (The maximum value you can give is 2000). Then the LOB files will be stored in different directories, which can support millions of LOB values recovery, the subdirectory name will look like "LOBxxxx" (xxxx is four digit). For example :

AUL> set MAXLOBDIR 1000
  Current MAXLOBDIR is : 1000
AUL>

    Now perform the LOB recovery, and check the current directory.

C:\MYDUL\LOBREC>ls
AULCOL.TXT      LOB0245       LOB0394
AULOBJ.TXT      LOB0246       LOB0395
AULTAB.TXT      LOB0247       LOB0396
AULUSR.TXT      LOB0248       LOB0397
LOB0212         LOB0377       LOB0398
LOB0213         LOB0378       LOB0399
LOB0214         LOB0379       LOB0400
LOB0215         LOB0380       LOB0402
LOB0216         LOB0381       LOB0403
LOB0233         LOB0382       LOB0404
LOB0234         LOB0383       LOB0405
LOB0235         LOB0384       LOB0406
LOB0236         LOB0386       LOB0407
LOB0237         LOB0387       LOB0408
LOB0238         LOB0388       LOB0413
LOB0239         LOB0389       T_LOBTEST_sqlldr.ctl
LOB0240         LOB0390       T_LOBTEST_syntax.sql
LOB0242         LOB0391       aul4b.exe
LOB0243         LOB0392       lobtest.txt
LOB0244         LOB0393       t_lobtest.txt

    I unload about 5000 LOB values in the example, it looks much better than store all of them in one directory.

    Some one used AUL utility to recover millions of LOB values, and told me the recovery was very slow, about 10MB generated per minute. I don't think so, and I want to tell him it's impossible slow. They may choose the wrong method to estimate the recovery speed. There are two storage modes of the LOB values. First mode is inline storage, store the LOB value with other columns value, the second mode is file storage, store each LOB value in a distinct file.

    Inline is the default method (SET LOB_STORAGE 0), so all the LOB value is stored in one file, the file increase should be very fast. When I try to unload the SYS.SOURCE$ table, the speed is usually 6-8MB per second on my notebook. So this is impossible.

    Under file storage mode (SET LOB_STORAGE 1), each LOB value will be stored in distinct file (LOB_xxxxxxxx_xxxx.dat), and the relative LOB column will record the LOB file name. In this mode, the file increase speed maybe slow for the text file which stores the table data, and 10MB per minute is reasonable. When you estimate the recovery speed, you should take the LOB files into account. Or you just count the rows of the text file contains the table data (BLOB Recovery Example).

    If AUL give you such a bad impression, it's really unlucky, for me not a big issue, but for you, it's critical because you are not choosing the right recovery methods, and spend a lot of time in doing nothing. On a Linux platform based Intel x86 CPUs, it will run very fast.

    When you download the AUL binary and extract it, you may find that the execution file name on Windows is aul4b.exe, Linux is aul4b_linux, and Solaris is aul4b_solaris. However it's not a beta version, the "4b" means version 4 with LOB support. Since release 4.0.2, it's a release version after two formal recoveries of LOB data.

    If you see "B" is added to the suffix of the version number as following, then it's beta version.

Register Code: TRBR-CCPF-F6KJ-ALTT-MNGQ
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g, release 4.0.1B

(C) Copyright Lou Fangxin 2005-2007 (AnySQL.net), all rights reserved.

AUL> exit

    Just notice.