Blog - comments

Hi goog article can we install avdf firewall with flat network if it's possible please let me know ?

Thilina
First, thank you for your interrest in this blog.Yes, the byte code will be interpreted each time bu...
BIEHLER Stephane
Pretty sure this is wrong:> already said that JVMs interprets the generated byte code - that's true...
Gs
Michael, great article, however, I would disagree on DRS/Host Affinity. You are legally only requir...
David Bradshaw
Hi lauri, db_file_multiblock_read_count is still used in exadata smartscan because it defines the si...
Blog Michael Schwalm Troubleshooting: ORA-29283 when oracle is member of a group with read/write privileges

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Troubleshooting: ORA-29283 when oracle is member of a group with read/write privileges

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:

 

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:

 

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

 

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

 

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:

 

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:

 

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

 

The listener is owned by oracle, as expected:

 

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:

 

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:

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:

 

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:

 

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.

 

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

 

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):

 

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

 

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!!

 

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.

Rate this blog entry:
2

Michael Schwalm is Consultant at dbi Services and has more than two years of experience in Oracle database administration. He has a broad knowledge in the realization of virtualization infrastructures such as vMware vSphere. He took his first steps in database administration as an integrator of a web applications on Unix, Oracle, and Websphere environments. Michael Schwalm is Oracle Certified Professional 11g and RAC Implementation Specialist 11g. Prior to joining dbi services, Michael Schwalm was application administrator at SOGETI Est (F) on behalf of PSA Peugeot Citroen and responsible for the realization and managing of Unix environments and Oracle databases in the context of migration projects. Michael Schwalm holds a BTS diploma in Information System Management from Belfort (F) and a TSAR diploma in advanced network administration from Strasbourg (F). His branch-related experience covers Automotive, Software industry, Financial Services / Banking, etc.

Comments

  • Guest
    Mark Monday, 19 May 2014

    You hit the nail on the head. Awesome, you saved me hours of banging my head against the wall.

Leave your comment

Guest Saturday, 25 October 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter