AnySQL.net English

    Last year, I have said that I will increase AUL version by resolving LOB or compress block. Today I made a great step of supporting LOB type, so I released AUL 4.0.0 Beta.

    You can download the beta release to test LOB recovery. Some new set option introduced to support LOB recovery, I will introduce then in later chapters. Why beta? I have been hoping to support LOB or compress block for a long time, and I have not made enough test on this feature, so I called it beta release. The following is some limits:

• No enough test made.
• Tested on on 10g version.
• Not all character set conversion supported for CLOB.
• Using index full scan to access LOB Index.
• Ignore chunk size now, cheat it as one block size always.

    Compare other tools such as jDUL or Oracle DUL, AUL can recovery lob data into DMP format.

    If you want to recover LOB data, AUL dictionary must exists (get from system tablespace or manually create them). I will try to do more test and make some improvement in the coming two or three months. Hope I can release it in two months (not beta version).

    AUL version 4 will support LOB data type, including inline LOB, in row LOB and out of row LOB. I need to introduce few new options of AUL, the following two are only related to CLOB data type:

    1, SET CLOB_EDIAN {BIG | LITTLE}

    If you use fixed length character set in your database (single byte character or those name suffixed with FIXED), the content in CLOB is stored in the same character set with your database's character set. If you use var-length character set in you database, the content in CLOB is stored in unicode format. One unicode char consists of two bytes, so there is byte edian problem, in 9i or previous release, the byte edian of CLOB keeps the same with CPU, in 10g or above, it's always using big edian. This option is used to specify the CLOB byte edian, default is little edian.

    2,SET LOB_CONVERT {0:NONE | 1:GBK | 2:UTF8}

    According to previous description of CLOB, the value maybe stored in unicode format, it will bring some troubles to us when recovery, so I design a feature to convert them from unicode to GBK or UTF8 format. This option have three option: 0, do no conversion, 1, convert to GBK encode, 2 convert to UTF8 encode. I haven't write out the code of other character set conversion.

    The third option is to decide where the LOB content to be recovered for both CLOB and BLOB.

    3,SET LOB_STORAGE {0:INLINE | 1:FILE | 2:NONE}

    AUL 4 still support two recover format: text and dmp. When recover to text format, lob value can be stored with other columns in the same file (value 0), or store in seperate file (value 1), only the file name is stored with other columns. If you specify the value 2, AUL will not recover LOB data, treat it as a NULL. The default value is 0, store in the same file with other columns.

    NCLOB is not supported, and no plan to support it.

    Following is a example of recover CLOB data of Oracle Windows 10g R2 using the AUL 4, let's create the demo table first:

SQL> CREATE TABLE T_CLOBDEMO
  2  (
  3    ID NUMBER,
  4    CLOB1 CLOB,
  5    CLOB2 CLOB,
  6    CLOB3 CLOB
  7  )
  8  LOB(CLOB3) STORE AS (DISABLE STORAGE IN ROW)
  9  /

Table created.

SQL> INSERT INTO T_CLOBDEMO VALUES (1,'INLINE CLOB', NULL,NULL);

1 row created.

    Then connect to sys and issue an checkpoint command to make sure the data actually written to data files. For column CLOB2 and CLOB3, they contain no data (null value). column CLOB1 contains a very small value, I am testing the inline CLOB now, let's store the CLOB data with the same file (output to screen) of other columns:

AUL> set lob_convert 1
  Current LOB_CONVERT is : 1-GBK
AUL> set lob_storage 0
  Current LOB_STORAGE is : 0-INLINE
AUL> set clob_edian big
  Current CLOB_EDIAN is : BIG
AUL> unload table anysql.t_clobdemo;
2006-12-17 19:38:08
Unload OBJD=9956 FILE=4 BLOCK=4611 CLUSTER=0 ...
1|INLINE CLOB
2006-12-17 19:38:08

    Seems we have successfully recovered the data in CLOB1 column. Now we will start larger LOB values, I will using AnySQL to populate CLOB2/CLOB3 columns with a 64KB text file, as following:

    Following is a example of recover BLOB data of Oracle Windows 10g R2 using the AUL 4, let's create the demo table first:

SQL> CREATE TABLE T_BLOBDEMO
  2  (
  3    ID NUMBER,
  4    BLOB1 BLOB,
  5    BLOB2 BLOB,
  6    BLOB3 BLOB
  7  )
  8  LOB(BLOB3) STORE AS (DISABLE STORAGE IN ROW)
  9  /

Table created.

SQL> INSERT INTO T_BLOBDEMO VALUES (1, '3031323334', NULL, NULL);

1 row created.

    Then connect to sys and issue an checkpoint command to make sure the data actually written to data files. For column BLOB2 and BLOB3, they contain no data (null value). column BLOB1 contains a very small value (string "01234"), I am testing the inline BLOB now, let's store the BLOB data with the same file (output to screen) of other columns:

AUL> UNLOAD TABLE anysql.t_blobdemo;
2006-12-17 21:43:27
Unload OBJD=9966 FILE=4 BLOCK=4859 CLUSTER=0 ...
1|01234
2006-12-17 21:43:27

    Then I will use AnySQL to populate BLOB2 and BLOB3 with an image file (test.jpg) :

    Now I will recover the two demo table in DMP format, to check whether it will succeed. As following:

AUL> set clob_edian big
  Current CLOB_EDIAN is : BIG
AUL> set lob_convert 1
  Current LOB_CONVERT is : 1-GBK
AUL> set charset 852
  Current CHARSET is : 0x0354
AUL> set output_style dmp
  Current OUTPUT_STYLE is : DMP
AUL> unload table anysql.t_clobdemo to t_clobdemo.dmp;
2006-12-17 22:27:40
Unload OBJD=9956 FILE=4 BLOCK=4611 CLUSTER=0 ...
2006-12-17 22:27:40
AUL> unload table anysql.t_blobdemo to t_blobdemo.dmp;
2006-12-17 22:27:46
Unload OBJD=9966 FILE=4 BLOCK=4859 CLUSTER=0 ...
2006-12-17 22:27:46

    Now import data into T_CLOBDEMO table:

C:\MYDUL>imp system/oracle file=t_clobdemo.dmp fromuser=mydul touser=anysql ignore=y

Import: Release 10.2.0.1.0 - Production on Sun Dec 17 22:28:33 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by MYDUL, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing MYDUL's objects into ANYSQL
. . importing table                   "T_CLOBDEMO"          2 rows imported
Import terminated successfully without warnings.

    And check the CLOB column length in SQL*Plus with DBMS_LOB package: