Re: how to read a file from ASM using BFILENAME and DBMS_LOB.FILEOPEN?

On Apr 19, 1:05 pm, alek <alexandru.t..._at_gmail.com> wrote:
> Hi,
>
> I need your help concerning the following issue. I have generated a
> dump file using datapump into an ASM diskgroup. Now I want to read the
> resulting dump file using DBMS_LOB.FILEOPEN into a BLOB and to send it
> afterwards to a ftp location. The problem is that DBMS_LOB.FILEOPEN
> procedure raises an exception complaining that:
>
> ORA-22288: file or LOB operation FILEOPEN failed
> No such file or directory
>
> Below is the steps I followed:
>
> 1. create a directory which points out to the directory into the ASM
> diskgroup which contains the dump:
>
> connect admin/xxx
> create directory dumps_dir as '+DG_DATA/dumps';
> GRANT READ, WRITE ON DIRECTORY DUMPS_DIR TO FITS_DUMPMAN;
>
> 2. verify if the dump really exist:
>
> [oracle_at_rac1 ~]$ ORACLE_SID=+ASM1 asmcmd
> ASMCMD> ls -al DG_DATA/dumps
> Type Redund Striped Time Sys Name
> N
> fits_common_repository.dmp =>
> +D
> G_DATA/RACDB/DUMPSET/FITS_DUMPMANEXPORT_TEST_86502_1.638.620306271
>
> 3. try loading the file:
>
> fits_dumpman_at_racdb> declare
> 2 l_bfile bfile;
> 3 begin
> 4 l_bfile := BFILENAME('DUMPS_DIR',
> 'fits_common_repository.dmp');
> 5 DBMS_LOB.fileopen(l_bfile,
> DBMS_LOB.file_readonly);
> 6 dbms_lob.filecloseall;
> 7 end;
> 8 /
>
> declare
> l_bfile bfile;
> begin
> l_bfile := BFILENAME('DUMPS_DIR', 'fits_common_repository.dmp');
> DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
> dbms_lob.filecloseall;
> end;
>
> ORA-22288: file or LOB operation FILEOPEN failed
> No such file or directory
> ORA-06512: at "SYS.DBMS_LOB", line 523
> ORA-06512: at line 5
>
> Any idea why this file is not accesible to DBMS_LOB.fileopen? Are
> there any issues in connection with using DBMS_LOB and ASM storage?
>
> Many thanks in advance!
> alec.

I am not sure how you got the impression that BFILEs on ASM storage are supported for they don't seem to be. BFILEs can only reside in OS file systems, not on ASM volumes. Afaik, ways you can get a file out of ASM are: RMAN CONVERT command; XML DB FTP/WebDAV interfaces, when enabled; and API in DBMS_FILE_TRANSFER package.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Apr 19 2007 - 05:26:27 CDT