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

From: alek <alexandru.tica_at_gmail.com>
Date: 19 Apr 2007 02:05:20 -0700
Message-ID: <1176973520.052638.68830@l77g2000hsb.googlegroups.com>

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. Received on Thu Apr 19 2007 - 04:05:20 CDT