UTL_FILE errors are one of those mysterious failures which are quite hard to pin down what is wrong. Recently we had a process failing intermittently on a cluster database. The process was running successfully on some days but failing on some days. It was quite a challenge to find out the reason behind the failure.
Error -
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Below are some of the analysis performed -
1. Checks were performed to check the permissions on the target area.
2. The target area is defined in dba_directories (The target area in this case was ZFS mounted on EBS/DB server side)
3. The code is referring to the correct directory and this is case sensitive
4. The oracle account has access to the target area.
After spending quite some time the DBA has identified that the target area was not mounted on one of the DB nodes.(Note the EBS program runs on the DB server. That was the reason the job was being successful at times.
So in summary we need to check the target area is accessible from all the servers if EBS is running on cluster environment.
