Wednesday 18 February 2015

parameter _TRACE_FILES_PUBLIC in Oracle


Generally all trace files have read and write permission for Oracle software owner and group of Oracle installation has permission of read only. Other users don’t have privilege of read.

$cd /oradump/diag/rdbms/ABCPROD1/trace
$ls -lrt
-rw-r----- 1 ora11g dba      156 Feb 18 13:35 ABCPROD1_lmhb_37332.trm
-rw-r----- 1 ora11g dba     1892 Feb 18 13:35 ABCPROD1_lmhb_37332.trc
-rw-r----- 1 ora11g dba    72499 Feb 18 13:39 ABCPROD1_lms0_37322.trm
-rw-r----- 1 ora11g dba   448097 Feb 18 13:39 ABCPROD1_lms0_37322.trc
-rw-r----- 1 ora11g dba    71272 Feb 18 13:40 ABCPROD1_mmon_37350.trm

Because trace files may have some important information about database security or may have some sensitive details of data. 
Oracle applies this permission using hidden parameter called _TRACE_FILES_PUBLIC. 
Default value of _TRACE_FILES_PUBLIC is FALSE. Due to this parameter trace files don’t have read permission to other users or public. It is not recommended to change value of this parameter because it is security risk. But in some scenario it has needed to change permission to make it readable trace files to other users or PUBLIC.

We can change this parameter using parameter file or spfile and make it TRUE.

select value from v$parameter where name = '_trace_files_public';

VALUE
----------
FALSE

For modifing this parameter login as SYSDBA and execute ALTER SYSTEM command as follows.

SQL> ALTER SYSTEM SET “_trace_files_public” = TRUE SCOPE=SPFILE;

After changing _TRACE_FILES_PUBLIC parameter to TRUE. Newly generated trace has readable permission to other users and public too.

select value from v$parameter where name = '_trace_files_public';

VALUE
----------
TRUE

$cd /oradump/diag/rdbms/ABCPROD1/trace
$ls -lrt
-rw-r--r-- 1 ora11g dba     2247 Feb 18 13:48 ABCPROD1_lmhb_1907.trc
-rw-r--r-- 1 ora11g dba      156 Feb 18 13:48 ABCPROD1_dbrm_1885.trm
-rw-r--r-- 1 ora11g dba     1496 Feb 18 13:48 ABCPROD1_dbrm_1885.trc