Since Oracle 11g files created by the database belong by default to the Linux group oinstall. Changing the default group after creating the central inventory is difficult. In this Blog I want to show how locally created archived redo can be in group dba instead of oinstall.

One of my customers had the requirement to provide read-access on archived redo to an application for logmining. To ensure the application can access the archived redo, we created an additinal local archive log destination:


LOG_ARCHIVE_DEST_9 = 'LOCATION=/logmining/ARCHDEST/NCEE19C valid_for=(online_logfile,primary_role)'

and provided NFS-access to that directory for the application. To ensure that the application can access the archived redo, the remote user was part of a remote dba-group, which had the same group-id (GID) as the dba-group on the DB-server. Everything worked fine until we migrated to a new server and changed the setup to use oinstall as the default group for Oracle. The application could no longer read the files, because they were created with group oinstall:


oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] ls -ltr
-rw-r-----. 1 oracle oinstall 24403456 Oct 9 21:21 1_32_1017039068.dbf
-rw-r-----. 1 oracle oinstall 64000 Oct 9 21:25 1_33_1017039068.dbf
-rw-r-----. 1 oracle oinstall 29625856 Oct 9 21:27 1_34_1017039068.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C]

One possibility to workaround this would have been to use the id-mapper on Linux, but there’s something better:

With the group-sticky-bit on Linux we can achieve, that all files in a directory are part of the group of the directory.

I.e.


oracle@19c:/logmining/ARCHDEST/ [NCEE19C] ls -l
total 0
drwxr-xr-x. 1 oracle dba 114 Oct 9 21:27 NCEE19C
oracle@19c:/logmining/ARCHDEST/ [NCEE19C] chmod g+s NCEE19C
oracle@19c:/logmining/ARCHDEST/ [NCEE19C] ls -l
drwxr-sr-x. 1 oracle dba 114 Oct 9 21:27 NCEE19C

Whenever an archived redo is created in that directory it will be in the dba-group:


SQL> alter system switch logfile;
 
System altered.
 
SQL> exit
 
oracle@19c:/logmining/ARCHDEST/ [NCEE19C] cd NCEE19C/
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] ls -ltr
-rw-r-----. 1 oracle oinstall 24403456 Oct 9 21:21 1_32_1017039068.dbf
-rw-r-----. 1 oracle oinstall 64000 Oct 9 21:25 1_33_1017039068.dbf
-rw-r-----. 1 oracle oinstall 29625856 Oct 9 21:27 1_34_1017039068.dbf
-rw-r-----. 1 oracle dba 193024 Oct 9 21:50 1_35_1017039068.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C]

To make all files part of the dba-group use chgrp and use the newest archivelog as a reference:


oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] chgrp --reference 1_35_1017039068.dbf 1_3[2-4]*.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C] ls -ltr
-rw-r-----. 1 oracle dba 24403456 Oct 9 21:21 1_32_1017039068.dbf
-rw-r-----. 1 oracle dba 64000 Oct 9 21:25 1_33_1017039068.dbf
-rw-r-----. 1 oracle dba 29625856 Oct 9 21:27 1_34_1017039068.dbf
-rw-r-----. 1 oracle dba 193024 Oct 9 21:50 1_35_1017039068.dbf
oracle@19c:/logmining/ARCHDEST/NCEE19C/ [NCEE19C]

Hope this helps somebody.