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.
Couldn’t find this information anywhere else. This solved our problem. We have RAC env and could not write to files in one particular directory until we added the grid user to the appropriate groups. Thank you for sharing!!