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) :
ASQL> set queryonly false
ASQL> var p_lob blob
ASQL> define p_lob=test.jpg
ASQL> update t_blobdemo set blob2=:p_lob, blob3=:p_lob;
1 rows affected.
ASQL> commit;
Commit Succeed.
Checkpoint again, and start recovery with AUL 4, specify LOB_STORAGE option to value 1, each BLOB value will store in it's own file. As following:
AUL> set lob_storage 1
Current LOB_STORAGE is : 1-FILE
AUL> UNLOAD TABLE anysql.t_blobdemo to t_blobdemo.txt;
2006-12-17 21:46:42
Unload OBJD=9966 FILE=4 BLOCK=4859 CLUSTER=0 ...
2006-12-17 21:46:42
Check the contents of T_BLOBDEMO.TXT file:
C:\MYDUL>cat t_blobdemo.txt
1|LOB_010012FF_1F39.dat|LOB_010012FF_1F63.dat|LOB_010012FF_1FB8.dat
Check the first file, it should only contains "01234", and I renamed the other two files as JPG files, and them can be opened by photoshop program.
C:\MYDUL>cat LOB_010012FF_1F39.dat
01234
C:\MYDUL>dir LOB*.jpg
Volume in drive C has no label.
Volume Serial Number is 5CF4-08FE
Directory of C:\MYDUL
2006-12-17 21:46 252,443 LOB_010012FF_1F63.jpg
2006-12-17 21:46 252,443 LOB_010012FF_1FB8.jpg
2 File(s) 504,886 bytes
0 Dir(s) 9,521,508,352 bytes free
Then remove the "DIRECT=TRUE" option, and set ROWS to a samller value (such as 100), and load the recovered rows with SQL*Loader.
C:\MYDUL>sqlldr anysql/anysql control=t_blobdemo_sqlldr.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Dec 17 21:49:54 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
Now check the BLOB data length of BLOB2 and BLOB3, the two rows should match:
ASQL> select dbms_lob.getlength(blob2) len2,
2 dbms_lob.getlength(blob3) len3 from t_blobdemo;
LEN2 LEN3
------ ------
252443 252443
252443 252443
2 rows returned.
It seems AUL 4 successfully recover all the data. I will try different platform, different Oracle version, and also DMP format recovery. If you have interesting, please do some test for me. Thanks.