Tuesday, October 27, 2020
    Home Oracle Internals Hidden parameter _TRACE_FILES_PUBLIC in Oracle

    Hidden parameter _TRACE_FILES_PUBLIC in Oracle

    Trace files are generated due to some errors in Oracle. We can generate it forcefully after enabling SQL_TRACE=TRUE

    parameter. SQL_TRACE parameter can be modified in the session-level and system level. All trace files are logging in USER_DUMP_DEST parameter or BACKGROUND_DUMP_DEST parameter.

    - Advertisement -

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

    $cd /opt/oracle/ora11g/admin/orcl/udump
    $ls -l
    total 23
    -rw-r—– 1 oracle oinstall 2048 Jul 23 20:43 orcl1_ora_3423.trc
    -rw-r—– 1 oracle oinstall 1024 Jul 24 03:40 orcl1_ora_3517.trc
    -rw-r—– 1 oracle oinstall  965 Jul 24 04:06 orcl1_ora_3518.trc

    Because trace files may have some important information about database security or may have some sensitive details of data. Due to this reason Oracle’s default setting forced to non-readable to other users in USER_DUMP_DEST directory.

    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.

    - Advertisement -

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

    SELECT x.ksppinm name, y.ksppstvl value
    FROM x$ksppi x, x$ksppcv y
    WHERE x.inst_id = userenv(‘Instance’)
    AND y.inst_id = userenv(‘Instance’)
    AND x.indx = y.indx
    AND x.ksppinm=’_TRACE_FILES_PUBLIC’;

    ——————————— ————————————
    _trace_files_public FALSE

    For modifying 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 the public too.

    $cd /opt/oracle/ora11g/admin/orcl/udump
    $ls -l
    -rw-r–r– 1 oracle oinstall 1433 Jul 24 17:32 orcl1_ora_3577.trc

    All the best,

    Thanks and regards,

    Dbametrix Solutions

    - Advertisement -
    - Advertisment -

    Most Popular

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Differences Raw Device, ASM Device and CFS

    The article explains basic differences of ASM device, raw device and Oracle cluster file system cfs with fundamentals

    Recent Comments