Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted (Oracle 10.2.0.3.0; MS Windows Server 2003)
| UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558212] |
Wed, 20 June 2012 05:20  |
 |
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I'm not sure whether this is the right place to ask a question. Moreover, I'm not sure I'll be able to ask this question the smart way. Anyway, I'll try ...
It is related to a problem we discussed some time ago (Run external job with DBMS_SCHEDULER). Shortly: I managed to upload images to a database server, resize them, copy to the application server and everything worked just fine - the Apex page successfully displayed images.
Since last week, things have broken. This is how: there's a directory object which points to application server's directory:
SQL> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ------------------------------ -----------------------------------
SYS SLIKE_4005_UPLOAD d:\gis\slike_4005_upload --> on a database server
SYS SLIKE_4005 \\my-ias\d$\home\gis\slike_4005 --> on an application server
SQL>
I can use a directory located on a database server:
D:\GIS\Slike_4005_upload>dir photo_resize.*
Volume in drive D is RAID
Volume Serial Number is 88F2-69D2
Directory of D:\GIS\Slike_4005_upload
15.05.2012 09:16 3.414 photo_resize.bat
20.06.2012 10:18 5.763 photo_resize.log
2 File(s) 9.177 bytes
0 Dir(s) 13.683.204.096 bytes free
SQL> create or replace function f_exists (par_directory in varchar2,
2 par_filename in varchar2
3 )
4 return varchar2
5 is
6 l_postoji boolean;
7 l_flen number;
8 l_bsize number;
9 begin
10 utl_file.fgetattr(par_directory, par_filename, l_postoji, l_flen, l_bsize);
11
12 if l_postoji then
13 return ('It exists');
14 end if;
15
16 return ('It does not exist');
17 end f_exists;
18 /
Function created.
SQL> select f_exists('SLIKE_4005_UPLOAD', 'photo_resize.log') res_1,
2 f_exists('SLIKE_4005_UPLOAD', 'photo_resize.xxx') res_2
3 from dual;
RES_1 RES_2
-------------------- --------------------
It exists It does not exist
SQL>
However, I can not use a directory located on an application server (i.e. a network disk):
D:\Home\GIS\Slike_4005>dir ts3032-1.jpg
Volume in drive D is Oracle
Volume Serial Number is 3608-E11E
Directory of D:\Home\GIS\Slike_4005
25.04.2012 13:58 60.389 TS3032-1.jpg
1 File(s) 60.389 bytes
0 Dir(s) 5.090.762.752 bytes free
SQL> select f_exists('SLIKE_4005', 'TS3032-1.jpg') res_1
2 from dual;
RES_1
--------------------
It does not exist
SQL>
Well, that's not true - TS3032-1.JPG actually exists ...
How come it doesn't work? I was absent last week (this is not an excuse, I'm just saying that I wasn't there when it happened); database server was restarted for some reason (there were Windows' updates which required restarting). After that, all applications (lucky us, just two of them, but in multiple procedures/functions) return FALSE for UTL_FILE.FGETATTR.
We recreated directory objects, but that didn't help (UNC or not, no difference). I Googled quite a lot, read Metalink notes - nothing I did solved the problem.
I understand that you can't reproduce this situation so I'm not very optimistic about the outcome. However, I was hoping that maybe, somehow, someone might have experienced that and knows to to fix it. I really don't know what these OS updates were about; maybe they are not to be blamed at all.
Both servers (database & application) run MS Windows Server 2003 Standard Edition Service Pack 2.
What additional information should I provide?
Any ideas, please?
P.S. In the meantime, a colleague developed a workaround (it uses UTL_HTTP) which works, but it is MUCH slower than the previous UTL_FILE.FGETATTR option.
P.P.S. Why don't we keep these images on the database server (instead of the application server)? I really know nothing about this technology, but I was told that Apache is incapable of accessing mapped network directories so we used what we could.
|
|
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558283 is a reply to message #558260] |
Wed, 20 June 2012 10:17  |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
joy_division wrote on Wed, 20 June 2012 08:58ok, this is probably not that helpful, but just guessing. I work in unix, so windows is not my forte.
Isn't my forte either, but one of the issues on windows is security. Oracle runs on windows as a service. By default service is started as local system account which obviously has no access to network drive. You need to start oracle services as account that has proper access to network drive.
SY.
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558324 is a reply to message #558283] |
Wed, 20 June 2012 16:11 |
|
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Thank you, both of you.
@Joy: this directory is mapped as a network drive. However, I don't think that this is the case because I didn't set oracle directory object to a mapped drive (which would be something like "Z:\slike_4005"); instead, I used UNC and set it to "\\my-ias\d$\home\gis\slike_4005". That *used to work*.
@Solomon: security, eh? Yes, it is started as a local system account. What would I set it to, instead? I tried "Administrator", provided valid password and restarted the service. That was a huge failure - I couldn't even connect to the database via SQL*Plus, so I set it back to local system account.
It is quite late over here so tomorrow I'll have a closer look to what you, guys, said.
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558327 is a reply to message #558283] |
Wed, 20 June 2012 16:33 |
 |
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
|
Senior Member |
|
|
This won't resolve the problem, but might help with diagnosis.
Create the following function on your system. It uses DBMS_LOB to access files rather than UTL_FILE:
FUNCTION load_clob_from_file(p_file VARCHAR2,
p_directory VARCHAR2)
RETURN CLOB
IS
dest_clob CLOB;
src_clob BFILE := BFILENAME(p_directory, p_file);
BEGIN
DBMS_LOB.CREATETEMPORARY(dest_clob, true);
DBMS_LOB.OPEN(src_clob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile(
DEST_LOB => dest_clob,
SRC_LOB => src_clob,
AMOUNT => DBMS_LOB.GETLENGTH(src_clob)
);
DBMS_LOB.CLOSE(src_clob);
RETURN dest_clob;
END load_clob_from_file;
Run the following query (from your note, it looks like file is in the problem directory. If not, any text file will do):
SELECT load_clob_from_file('photo_resize.log', 'SLIKE_4005') FROM dual;
If that comes back with the text of the log file, then the problem is likely specific to UTL_FILE. If it doesn't come back with the text, then the problem is likely with accessing the directory itself. From your notes, I suspect it the second issue, but I've seen flaky problems with UTL_FILE sometimes...
|
|
|
|
|
|
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558360 is a reply to message #558336] |
Thu, 21 June 2012 05:43 |
|
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Problem solved!
Me, some time ago
@Solomon: security, eh? Yes, it is started as a local system account. What would I set it to, instead? I tried "Administrator", provided valid password and restarted the service. That was a huge failure - I couldn't even connect to the database via SQL*Plus, so I set it back to local system account.
Additional search was rather disappointing; everyone (including Tom Kyte) said that Oracle + network issue on MS Windows is a painful issue. Finally, I stumbled over Metalink note ID 428130.1 which resulted with success (and opened another problem).
Yesterday evening, I was close to the solution - I did change "Log on as" information for both the database and the listener; my steps weren't correctly sorted.
So, here's what we did: - Creating an Oracle directory object with the UNC convention is a MUST
- shut down the database, stop the listener
- modify "Log on as" information for both the instance service and the listener service to "Administrator". See the screenshot; these two are marked red. Read about blue ones later.
- start both database and listener. After that, I was able to connect to SQL*Plus asbut not asbecause of ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (BlackSwan already wrote a whole book about it here, on the forum)
- reboot the database server. After that, SQL*Plus works as expected
Then, I tested my function again:
SQL> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ------------------------------ ----------------------------------------
SYS SLIKE_4005_UPLOAD d:\gis\slike_4005_upload
SYS SLIKE_4005 \\my-ias\d$\Home\GIS\Slike_4005
SQL> select f_exists('SLIKE_4005' , 'TS3032-1.jpg' ) ias,
2 f_exists('SLIKE_4005_UPLOAD', 'photo_resize.log') dbs
3 from dual;
IAS DBS
-------------------- --------------------
It exists It exists
SQL>
I was really excited with the success. However, bad news were just waiting behind the corner.
Our local, intranet homepage (sorry if I'm inaccurate, but I know close to nothing about this technology) uses Apache web server. It just refused to connect to the database and display the homepage (written in PL/SQL). Message we got was "Service Temporarily Unavailable".
We (actually, he, as I was completely useless here) modified the DADS.CONF file, restarted all system components found in Enterprise Manager (Forms, HTTP Server, Web cache, ...), rebooted the Application server, ... Nothing helped.
Then we modified "Log on as" information in the Application server as well - from "Local system account" to the same "Administrator" user, just like we did on the Database server:
Additionally, we did the same for the rest of started services on the Database server (painted in blue; see the first screenshot).
That was the moment when "everything" started to work normally. I put "everything" under the quotes because I have no idea what else might stop working; our homepage is back here, UTL_FILE.FGETATTR works just fine, Apex runs OK - too good to be true.
I'll post new information if something unexpected surprises us. Hopefully, I won't have to.
Once again, thanks to everyone who participated this discussion. Looking forward to another one 
|
|
|
|
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558445 is a reply to message #558419] |
Fri, 22 June 2012 05:35 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Just one other observation:
\\server\d$\ usually is a hidden administrative share for the D: drive. To access that the user must be either administrator on the box or domain administrator.
There are some circumstances I have run into (not related to Oracle, though) where such hidden administrative shares didn't work, a "real", "visible" network share had to be created with the proper permissions. (for instance you should be able to set the permission of the network share so that the oracle account of that other box is allowed to access it)
Then the Oracle service wouldn't have to run under an domain administrator account.
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558546 is a reply to message #558445] |
Sat, 23 June 2012 12:48 |
|
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Thank you, Thomas!
Thomas
for instance you should be able to set the permission of the network share so that the oracle account of that other box is allowed to access it
"The network share" would be - in my case - a directory located on the application server (d:\Home\GIS\Slike_4005).
"Other box" would be my database server.
I managed to follow what you are saying until now. Then you said:
"Oracle account of that other box is allowed to access it" - what is "Oracle account"? As far as I know, this is how Oracle (software - database in this case) was installed: a colleague sat in front of the computer, switched it on. Username/password appeared - he entered "Administrator", its password, "This computer". Ran the Oracle Universal Installer and ... well, installed Oracle.
Does it mean that "Administrator" is "Oracle account" you mentioned?
I can't check it now, but I think that there's no "explicit" share of the application server's D disk (or the directory that holds images I'm interested in); I suppose that it means that we access it "implicitly" ("hidden administrative share", as you put it).
I'll try to - if I'm allowed to - share only that directory and see what happens. Unfortunately, I can't just play with it as it is a "live" system. Unfortunately, we don't have a testing environment.
|
|
|
|
| Re: UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted [message #558561 is a reply to message #558546] |
Sat, 23 June 2012 18:36 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
OK, first with "the Oracle user" I meant the user that the Oracle service is running under. Which in this case seems to be the Administrator anyway. So in that case "Oracle account" = "Administrator"
I have never encountered these share problems with Oracle, since that is completely under Unix in our case, but some *other* services running on Windows had the same problems accessing shares.
We basically now create a domain user to run these services (not an *Administrator*) though, and give those domain users access to the needed shares.
Although I must confess:
We only went through the trouble of setting up separate domain users for different services after a quite annoying bout with "Locked domain administrator accounts" where we never knew which application caused them to lock up. Only after we split off each service into a different user were we able to pin down the problem.
|
|
|
|
Goto Forum:
Current Time: Fri Aug 15 01:08:37 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
