Skip to content

How to Resolve ORA-29283: invalid file operation

  • by

ORA-29283

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.

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.

Leave a Reply

Your email address will not be published.