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

On Apr 19, 1:26 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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

Hi Vladimir,

Thanks for your fast reply and for clarifications. I am going to use DBMS_FILE_TRANSFER package in order to move the dump from ASM into a samba share locally mounted.

alec. Received on Thu Apr 19 2007 - 06:15:46 CDT