Recently, I worked on an Oracle database migration for a customer and I faced a strange behavior when accessing a file stored on the file system of the Oracle database. The database has been migrated on a new server, and consolidated with existing databases. Several jobs are running into the database and need to access the file system to read or write some files. A particular file is received daily from a remote job over FTP. This file is owned by the “dc_ftp” user in /home/dc_ftp.

First, this is the directory where the file is located:

1
2
3
4
SQL> select directory_name, directory_path from dba_directories where directory_name='MYDIR';
DIRECTORY_NAME            DIRECTORY_PATH
------------------------- -------------------------------------------------------
MYDIR                     /home/dc_ftp/out

The file is named myfile.txt and exists in /home/dc_ftp/out:

1
2
dc_ftp@srvora01$ ls /home/dc_ftp/out/
myfile.txt

During functional tests, I was able to successfully read the file from the database:

1
2
3
4
5
6
7
8
SQL> declare
  p_file utl_file.file_type;
begin
  p_file := utl_file.fopen ('MYDIR', 'myfile.txt', 'w');
end;
/
  
PL/SQL procedure successfully completed.

But members of the application team, connected from SQL Developer, were not able to read this file:

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> declare
  p_file utl_file.file_type;
begin
  p_file := utl_file.fopen ('MYDIR', 'myfile.txt', 'r');
end;
/  2    3    4    5    6
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4

My first hypotheses, such as misconfiguration in SQL Developer tool, wrong entries in the TNS Names, or wrong permissions on the listener binaries, were quickly eliminated.

Both instance and host are correct, so the TNS entry seems to be OK:

1
2
3
4
SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
DB11G            srvora01

The listener is owned by oracle, as expected:

1
2
oracle@srvora01$  ps -ef | grep tnslsnr
oracle    9684     1  0 16:33 ?        00:00:01 /u00/app/oracle/product/11.2.0/db_3_0/bin/tnslsnr LISTENER -inherit

And to finish, the problem occurs with or without SQL Developer.

The next step was to check permissions. First at the DB level:

1
2
3
4
5
SQL> select grantee, privilege, table_name from dba_tab_privs where table_name='MYDIR';
GRANTEE                        PRIVILEGE                                TABLE_NAME
------------------------------ ---------------------------------------- ---------------
SYSTEM                         WRITE                                    MYDIR
SYSTEM                         READ                                     MYDIR

 

Then at the operating system level:

1
2
3
4
5
oracle@srvora01$ ls -altr /home/dc_ftp/out
total 12
-rw-r-----. 1 dc_ftp dc_ftp   16 Feb 24 16:48 myfile.txt
drwxr-x---. 2 dc_ftp dc_ftp 4096 Feb 24 16:48 .
drwxr-xr-x. 5 dc_ftp dc_ftp 4096 Feb 24 16:54 ..

We can see that files and directory are not directly owned by oracle, but oracle is member of the group dc_ftp. It gets the read privilege on the file from the group dc_ftp:

1
2
oracle @ srvora01$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54325(oper),54330(dc_ftp)

Finally, oracle user can clearly access the file from an operating system point of view, since it can order the “cat” command on the file:

1
2
3
oracle@srvora01$ cat /home/dc_ftp/out/myfile.txt
test
testéàè

Now let’s see which problem occurs when Oracle tries to access the  directory from the session.

1
2
3
4
5
6
SQL> set define #
SQL> column spid new_value unix_pid
SQL> select spid from v$process p join v$session s on p.addr=s.paddr and s.sid=sys_context('userenv','sid');
SPID
------------------------
10914

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> host strace -e trace=open -p #unix_pid & echo $! > .tmp.pid
Process 10914 attached - interrupt to quit
  
SQL> declare
  p_file utl_file.file_type;
begin
  p_file := utl_file.fopen ('MYDIR', 'myfile2.txt', 'w');
end;
/
  2    3    4    5    6 
   
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
  
open("/home/dc_ftp/out/myfile2.txt", O_WRONLY|O_CREAT|O_TRUNC, 0666) = -1 EACCES (Permission denied)

The previous message clearly indicates that it is a permission issue. Oracle has no privilege to access the directory to read or write…

We can verifiy if it works when setting the permission READ to the file mytext.txt for non owner or group (i.e.644):

1
2
3
4
5
6
root@srvora01# chmod 644 /home/dc_ftp/out/myfile.txt
root@srvora01# ls -altr /home/dc_ftp/out
total 12
-rw-r--r--. 1 dc_ftp dc_ftp   16 Feb 24 16:48 myfile.txt
drwxr-xr-x. 2 dc_ftp dc_ftp 4096 Feb 24 16:48 .
drwxr-xr-x. 5 dc_ftp dc_ftp 4096 Feb 24 16:54 ..

 

1
2
3
4
5
6
7
8
9
10
oracle@srvora01$ sqlplus system/****@DB11G
  
SQL> declare
  p_file utl_file.file_type;
begin
  p_file := utl_file.fopen ('MYDIR', 'myfile.txt', 'r');
end;
/  2    3    4    5    6
  
PL/SQL procedure successfully completed.

This is the workaround.

But what if the file must not be read by the rest of the world? The file is generated with 640 permissions for a good readon here. So I had to fix the issue definitely.

Fortunately, I fell on a Metalink note which helped me find the solution to my problem. According to the note ID 832323.1, if the file or directory is accessed through a group on which oracle is member of (here dc_ftp group has read privileges on the file), adding oracle to that group while the database is running may cause the error ORA-29283 to occur.

Fine! But the database was restarted several times during the migration process, because of some initialization parameters to apply. I was totally sure that the database had been restarted after oracle user had been added to dc_ftp group! Moreover, I got no error when connected locally on the server. The error only occured when connected remotely through SQLNET, using sqlplus system/****@DB11G for instance through the listener…

That’s why I had an idea. If the database processes are starting with the oracle user’s permissions and if the database need to be restarted in order to make changes to take effect, the listener should be affected too! And the listener was never restarted during the migration. It was only reloaded to reflect changes from the new instance. This could explain why only sessions opened through the listener are affected…

I restarted the listener and… all problems went away!!

1
2
3
4
5
6
7
8
9
10
11
12
oracle@srvora01$ lsnrctl stop listener
oracle@srvora01$ lsnrctl start listener
oracle@srvora01$ sqlplus system/*****@DB11G
  
SQL> declare
  p_file utl_file.file_type;
begin
  p_file := utl_file.fopen ('MYDIR', 'myfile.txt', 'r');
end;
/  2    3    4    5    6
  
PL/SQL procedure successfully completed.

To summarize, do not forget that the listener can also be impacted when changing permissions while it is running. It would be better to restart it instead of a simple reload when you make these kinds of modifications.

Post Views: 2,750
Thumbnail [60x60]
by
Oracle Team