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@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@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.

On Apr 19, 1:05 pm, alek <alexandru.t...@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@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@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

On Apr 19, 1:26 pm, "Vladimir M. Zakharychev"
<vladimir.zakharyc...@gmail.com> wrote:
> On Apr 19, 1:05 pm, alek <alexandru.t...@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@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@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.