ORA-29283
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.