Below is the code i use to extract data from a table to a flat file.
BEGIN
DECLARE
file_name VARCHAR2(50);
file_handle utl_file.file_type;
BEGIN
file_name := 'table.txt';
file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
SELECT column 1
||'~'||column 2
||'~'||column 3 out_line
FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;
This code is working fine in our development database but its throwing the below error if i execute in a new DB.
Error starting at line 1 in command:
BEGIN
DECLARE
file_name VARCHAR2(50);
file_handle utl_file.file_type;
BEGIN
file_name := 'table.txt';
file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
SELECT column 1
||'~'||column 2
||'~'||column 3 out_line
FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;
Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
29283. 00000 - "invalid file operation"
*Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.
*Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.
Oracle directory 'SEND' points to some UNIX directory which has rights as
'rwxrwsr-x' (Octal 2775)
Oracle Version:11g
Please help me to solve this issue.
Guys please do let me know if you require more data from me to solve this question.
UTL_FILE is used to work with local files on the database server side. In this post I’ll explain why you might get ORA-29283 in RAC (but not only) and how to fix it.
The ORA-29283 Problem
First, I’ll present the problem. Here is the code and the error:
[oracle@ora19 admin]$ sqlplus liron/liron@pdb
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 13 11:13:01 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sun Apr 12 2020 23:09:21 -07:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create directory app as '/tmp/app';
Directory created.
SQL> DECLARE
2 app_file UTL_FILE.FILE_TYPE;
3 BEGIN
4 app_file := UTL_FILE.FOPEN('APP','local.txt','w');
5 UTL_FILE.FCLOSE(app_file);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation: cannot open file [29435]
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4
ORA-29283 usually occurs when the directory on the server doesn’t exist or the database owner doesn’t have permissions to read/write to it, so let’s check that:
[oracle@ora19 tmp]$ cd /tmp/app [oracle@ora19 app]$ touch a [oracle@ora19 app]$ ls -l total 0 -rw-r--r--. 1 oracle oinstall 0 Aug 13 11:46 a [oracle@ora19 app]$ cd .. [oracle@ora19 tmp]$ ls -ld app drwxrwxr-x. 2 app app 15 Aug 13 11:46 app [oracle@ora19 tmp]$ id oracle uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54331(app)
We see here something interesting. First, we do have permissions to create files. Second, note that the directory is owned by the app user and app group. So how do we have permissions? oracle user is part of the app group. Great, so everything looks fine, what is the problem then?
Solution
The problem here lies in the listener, and this is why this might be more common in RAC, but can occur in other environments as well. In some configurations, listeners are started with a different user than the database. For example, in RAC, the listener is part of the GI and may be started using the “grid” user while the database uses the “oracle” user. In other cases, if you have different users for different databases or versions on the server, one of them can start the listener that will serve all databases.
In my example environment, I have only one database and one server, but I started the listener using a different user called “listener” (on purpose):
[oracle@ora19 tmp]$ ps -ef|grep LISTENER listener 4005 1 0 11:23 ? 00:00:00 /oracle/product/19/db_home/bin/tnslsnr LISTENER -inherit [oracle@ora19 tmp]$ ps -ef|grep LOCAL oracle 3443 1 0 11:13 ? 00:00:00 oracletst19 (LOCAL=NO)
The thing with Oracle is that once you connect to the database, the listener is the one getting your request and spawning the server process for you. What happens here is that the server process actually has the listener user’s permissions and not the database user’s, even though the OS shows that “oracle” is the user of the server process.
Do you remember that the directory was owned by the “app” user and group? the “listener” user doesn’t have permission on the /tmp/app directory. Let’s verify that and fix the problem:
[listener@ora19 ~]$ id uid=54323(listener) gid=54321(oinstall) groups=54321(oinstall),54322(dba) [listener@ora19 ~]$ cd /tmp/app [listener@ora19 app]$ touch b touch: cannot touch ‘b’: Permission denied [listener@ora19 app]$ exit logout [root@ora19 ~]# usermod listener -G oinstall,dba,app [root@ora19 ~]# su - listener Last login: Thu Aug 13 12:01:03 PDT 2020 on pts/1 [listener@ora19 ~]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-AUG-2020 12:01:47 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully [listener@ora19 ~]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-AUG-2020 12:01:48 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /oracle/product/19/db_home/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production Log messages written to /oracle/product/19/db_home/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA19)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 13-AUG-2020 12:01:48 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /oracle/product/19/db_home/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA19)(PORT=1521))) The listener supports no services The command completed successfully
I added the “app” group to “listener” user. Now I have to restart the listener and reconnect to the database in order to create a new server process with the new permissions. Let’s do this and try running the UTL_FILE again:
[oracle@ora19 tmp]$ sqlplus liron/liron@pdb
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 13 12:04:26 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Aug 13 2020 12:04:23 -07:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> DECLARE
2 app_file UTL_FILE.FILE_TYPE;
3 BEGIN
4 app_file := UTL_FILE.FOPEN('APP','local.txt','w');
5 UTL_FILE.FCLOSE(app_file);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
ls [oracle@ora19 tmp]$ ls -l /tmp/app
total 0
-rw-r--r--. 1 oracle oinstall 0 Aug 13 11:46 a
-rw-r--r--. 1 oracle oinstall 0 Aug 13 12:04 local.txt
Now, that the “listener” user is part of the “app” group, we can see that the session is able to create the file without any problem.
One last comment, if you are able to connect to the database without the listener (i.e. locally with “sqlplus liron/liron”), you’ll see that when you connect NOT through the listener, you can create the file regardless of the listener user groups.
Issue 1 — The directory referenced is incorrect.
Issue 2 — Privileges were not granted to the username to access the directory.
Issue 3 — Attempting to read from a file with no data.
Issue 4 — The input file has a linesize over 1000 bytes and that was not indicated in:
UTL_FILE.FOPEN(). The UTL_FILE.FOPEN is improprly set up. There is no max_linesize parameter in the UTL_FILE.FOPEN method in the provided code. If there is no max_linesize parameter specified for this optional parameter, a default value between 1 and 1000 is assumed. The max_linesize parameter is needed if the file line size size exceeds 1000 as indicated by the documentation of the UTL_FILE package is in:
Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)
Part Number A96612-01
FOPEN Function
This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously. See also «FOPEN_NCHAR Function».
Syntax
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;
FOPEN Function Parameters
Parameter Description
location — Directory location of file
filename — File name, including extension (file type), without directory
path In Unix, the filename cannot end with /.
open_mode — Specifies how the file is opened. Modes include:
r—read text , w—write text, a—append text
If you try to open a file that does not exist using a value for open_mode, then the file is created in write mode.
max_linesize —
Maximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767). The default is approximately 1000 bytes.
There’re several error patterns of ORA-29283.
ORA-29434
Someone tried to use UTL_FILE.FOPEN in a stored procedure to operate a file, but it sometimes failed with ORA-29283.
ORA-29283: invalid file operation: nonexistent file or path [29434]
ORA-06512: at "SYS.UTL_FILE", line 536
...
ORA-29283 means that the given file path you tried to operate with is not valid, more specifically, the file path does not exit. You should make sure the directory is in existence on the server.
ORA-29433
We saw an uncommon error during file operation.
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation: path traverses a symlink [29433]
It seems that we cannot use file system which contains any symbolic link for DIRECTORY object anymore.
Solutions
Ownership and Permission
If the directory does exist on the server, you should also check the permission set of it and make sure it’s writable to user oracle. If there’s any necessary, please change the ownership to oracle:oinstall.
[root@primary01 ~]# chown -R oracle:oinstall /path/to/folder
[root@primary01 ~]# chmod -R 755 /path/to/folder
Please do above commands on all nodes, if you’re in a cluster envionment.
RAC Databases
In this case, it’s a newly created RAC database, applications usually connect to the database through SCAN VIP to balance the server loading between nodes. Which means, the store procedure may be executed on node 1 or node 2, it’s unpredictable.
After investigating both DB servers, I found the file path /reports that user tried to open is not existing on the second node (DB server), which made file operation invalid. That why it sometimes (50% chances) failed to operate the file.
Solution
To solve ORA-29283 in a RAC system is to share the folder among all DB servers in the same cluster, and make sure all instances are able to see the same directory.
In our case, the directory object RPT_PATH points to /reports, we should make both nodes share /reports folder, either using ACFS (ASM Cluster File System), NFS (Network File System) or GPFS (General Parallel File System).
To prevent ORA-29283, please make sure the folder has already shared on all nodes before we create a directory object in a RAC database.
We get the below error while writing file on OS with utl_file package from our application in our PROD environment. The application (which is hosted on IBM WAS) connects to database via JDBC which in turn use the sys.utl file package to write the files on OS.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
The same thing works fine in UAT which has the same setup as PROD
The same operation works on backend from the sql prompt or the batch jobs running. We already checked the below
- UTL_FILE is working with backend call on the database server.
- Server User/Group Privileges check between PROD & UAT which is in sync.
- LDAP User/Group Privileges check between PROD & UAT which is in sync.
- Directory level permission check between UAT & PROD server, which are identical.
- Database User Permission & Privileges.
It gives the same error with 777 privileges.