如何公开Oracle trace文件?

隐式参数_trace_files_public决定了Oracle产生的trace文件是否公开,该参数默认值为FALSE,也就是非DBA/OINSTALL组的用户是没有权限读取数据库产生的trace文件的;在某些场合中我们需要让非DBA组的用户也能访问trace文件,就可以通过修改该参数实现。请看下面的例子:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> col name for a20
SQL> col value for a20
SQL> col describ for a40
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_trace_files_public%'
  7  order by x.ksppinm;

NAME                 VALUE                DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public  FALSE                Create publicly accessible trace files

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written to trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
SQL> !ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
-rw-r----- 1 maclean oinstall 4206 Aug 11 20:51 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
/*所产生的trace文件权限为640,非oinstall组用户无权限读取该文件*/

SQL> alter system set "_trace_files_public"=true;
alter system set "_trace_files_public"=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
/*修改该参数需要重启实例*/

SQL> alter system set "_trace_files_public"=true scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             922747480 bytes
Database Buffers          637534208 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written to trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
SQL> ! ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
-rw-r--r-- 1 maclean oinstall 5471 Aug 11 20:54 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
/*other组用户也具有了读权限*/

SQL> ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:35 cdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:36 dpdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:37 pfile
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:54 udump
/*请注意修改_trace_files_public为true,并不会修改trace所在目录的权限,Oracle默认建立bdump/udump等trace目录时分配的权限为750,other组用户无法进入这些目录,需要修改目录权限为755,即o+r+x*/

SQL> ! chmod o+r+x /s01/10gdb/admin/YOUYUS/*dump

SQL>  ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-xr-x 2 maclean oinstall 4096 Aug  5 21:35 cdump
drwxr-xr-x 2 maclean oinstall 4096 Aug  5 21:36 dpdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:37 pfile
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 udump

/*需要注意的另一点是修改_trace_files_public参数并不会引起既有的trace文件的权限被修改,典型的例子是alert log告警日志*/
[maclean@rh2 bdump]$ ls -l
total 20
-rw-r----- 1 maclean oinstall 12971 Aug 11 21:17 alert_YOUYUS.log
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:12 youyus_lgwr_10514.trc

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_trace_files_public%'
  7  order by x.ksppinm;

NAME                 VALUE                DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public  FALSE                Create publicly accessible trace files

SQL> alter system set "_trace_files_public"=true scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             922747480 bytes
Database Buffers          637534208 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> !ls -l
total 32
-rw-r----- 1 maclean oinstall 21189 Aug 11 21:20 alert_YOUYUS.log
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:12 youyus_lgwr_10514.trc
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:20 youyus_lgwr_11136.trc

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. admin says:

    Hdr: 5950180 10.2.0.3 RDBMS 10.2.0.3 OSD PRODID-5 PORTID-59
    Abstract: _TRACE_FILES_PUBLIC DIDN’T WORK APPROPRIATELY EVEN THOUGH UMASK IS 022 IN HP-UX

    PROBLEM:
    ——–
    Customer applied 10.2.0.3 patchset recently. and then ct found the permission
    of trace files is -rw-r—– even though _TRACE_FILES_PUBLIC is set to TRUE.
    Ct said there were no changes in the system. It’s no RAC system.

    DIAGNOSTIC ANALYSIS:
    ——————–
    I checked the value of UMASK according as Note.303604.1. But umask was
    already 022. so I tested it in house. _TRACE_FILES_PUBLIC worked fine in
    LINUX platform. but I could see the same as the symptom of customer in HP-UX
    platform.

    WORKAROUND:
    ———–
    No

    RELATED BUGS:
    ————-
    None

    REPRODUCIBILITY:
    —————-
    Always

    TEST CASE:
    ———-

    2) Check the value of umask

    3) Execute sqlplus

    sqlplus “/ as sysdba”

    4) Check the value of parameter “_TRACE_FILES_PUBLIC”

    5) And force to generate the trace file like this:

    SQL> alter session set sql_trace=true;
    SQL> select * from dual;
    SQL> exit

    6) check the permission of trace file from udump.

    You can see the following type of trace files.

    -rw-r—– 1 aprdbms aprdbms 809 Mar 21 11:13
    apra20k6_ora_9209.trc

    STACK TRACE:
    ————
    N/A

    SUPPORTING INFORMATION:
    ———————–
    N/A

    24 HOUR CONTACT INFORMATION FOR P1 BUGS:
    —————————————-
    N/A

    DIAL-IN INFORMATION:
    ——————–
    N/A

    IMPACT DATE:
    ————
    N/A

    Tests:
    – Database release: 10.1.0.5.0
    Box …………: rmtdchp4 – HP-UX B.11.11
    Status ………: Not able to reproduce

    [rmtdchp4]EMB101W6> umask
    022

    Before setting ‘_trace_files_public’:


    -rw-r—– 1 embde embde 810 Mar 28 11:12 emb101w6_ora_25377.trc

    After setting ‘_trace_files_public’:

    SQL> show parameters trace

    NAME TYPE VALUE
    ——————————- ———– ——
    _trace_files_public boolean TRUE

    -rw-r–r– 1 embde embde 2214 Mar 28 11:17 emb101w6_ora_26424.trc
    -rw-r–r– 1 embde embde 1484 Mar 28 11:42 emb101w6_ora_970.trc

    [rmtdchp4]EMB101W6> date
    Wed Mar 28 11:42:09 BST 2007

    – Database release: 10.2.0.2.0
    Box …………: rmtdchp4 – HP-UX B.11.11
    Status ………: Reproduced

    [rmtdchp4]EMB102W6> umask
    022

    SQL> show parameters trace

    NAME TYPE VALUE
    ————————– ———– —–
    _trace_files_public boolean TRUE

    -rw-r—– 1 embde embde 1543 Mar 28 11:37 emb102w6_ora_856.trc
    -rw-r—– 1 embde embde 517 Mar 28 11:47 emb102w6_ora_2334.trc
    -rw-r—– 1 embde embde 517 Mar 28 11:52 emb102w6_ora_3284.trc

    [rmtdchp4]EMB102W6> date
    Wed Mar 28 11:53:28 BST 2007

    – Database release: 10.2.0.3.0
    Box …………: rmtdchp4 – HP-UX B.11.11
    Status ………: Reproduced

    Please review first action in the bug if required.

    – Database release: 10.2.0.3.0
    Status ………: Not able to reproduce
    *** 03/28/07 07:44 am ***
    Further research …

    From the trace file uploaded:

    —> open(“/dev/async”, O_RDWR|O_LARGEFILE, 0) ERR#2 ENOENT

    So it seems to me the customer sees something similar to the following
    message in the trace files:

    —> Unix process pid: 14177, image: oracle@rmtdchp4

    File ‘/dev/async’ not present : errno=2

    Now trying to deactivate ‘asynch_io’ …

    SQL> show parameters asynch

    NAME TYPE VALUE
    ——————————- ———– —–
    disk_asynch_io boolean FALSE

    -rw-r–r– 1 embde embde 2627 Mar 28 15:30 emb102w6_ora_15308.trc
    -rw-r–r– 1 embde embde 1577 Mar 28 15:31 emb102w6_ora_15900.trc

    [rmtdchp4]EMB102W6> date
    Wed Mar 28 15:31:37 BST 2007

    Now the files are generated as expected.

    There are no trace files uploaded, so I can not check ‘/dev/async’.
    Could you review and talk to the Ct. to see if, indeed, that can be
    the issue ?

    If that is the case, it seems to me the Ct. will have to configure
    asynch_io or he/she will have to disable it at database level.

    Could you review/check this with the Ct. ?
    If asynch_io is not enabled see if the Ct. can disable it at db level
    to see if the issue with the trace files still persists.
    About your questions:
    1) By now, I’m not totally sure if this is a missconfiguration or a defect.

    2) The Ct. should not see issues having this deactivated. Please review these
    notes, if needed:

    – Note 394870.1 – Getting “File ‘/dev/async’ not present: errno=2” …
    – Note 186083.1 – HP-UX: Is I/O Asynchronous?
    – Note 382674.1 – /Dev/Async Traces After Upgrade To 10.2.0.2 On HP-UX PA-RISC

  2. admin says:

    Hdr: 3885641 10.1.0.3 PCW 10.1.0.3 GEN PRODID-5 PORTID-59
    Abstract: WRONG TRACE FILE PERMISSION VIA SQLNET CONNECTION EVEN _TRACE_FILES_PUBLIC=TRUE
    PROBLEM:
    ——–
    Even database has _trace_files_public=true set, sessions connected via sqlnet
    (TCP) still have trace file generated with: -rw-r—– in udump. But sessions
    connected via BEQ have the right permission, eg: -rw-r–r–.

    Here is the output:
    [oracle@aulnx1 udump]$ sqlplus aa/aa
    SQL*Plus: Release 10.1.0.2.0 – Production on Mon Sep 13 11:17:23 2004
    Copyright (c) 1982, 2004, Oracle. All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
    With the Partitioning, Real Application Clusters, OLAP and Data Mining
    options

    SQL> select spid from v$process a, v$session b where a.addr=b.paddr and b.sid
    = (select unique sid from v$mystat);
    SPID
    ————
    23463

    SQL> alter session set sql_trace=true;
    Session altered.

    SQL> select sysdate from dual;
    SYSDATE
    ———
    13-SEP-04
    SQL> exit
    [oracle@aulnx1 udump]$ ls -l
    -rw-r–r– 1 oracle dba-1564 Sep 13 11:20 r101_ora_23463.trc

    [oracle@aulnx1 udump]$ sqlplus aa/aa@R101
    SQL*Plus: Release 10.1.0.2.0 – Production on Mon Sep 13 11:20:37 2004
    Copyright (c) 1982, 2004, Oracle. All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
    With the Partitioning, Real Application Clusters, OLAP and Data Mining
    options

    SQL> select spid from v$process a, v$session b where a.addr=b.paddr and b.sid
    = (select unique sid from v$mystat);
    SPID
    ————
    24090

    SQL> alter session set sql_trace=true;
    Session altered.

    SQL> select sysdate from dual;
    SYSDATE
    ———
    13-SEP-04

    SQL> exit

    [oracle@aulnx1 udump]$ ls -l
    -rw-r–r– 1 oracle dba-1564 Sep 13 11:20 r101_ora_23463.trc
    -rw-r—– 1 oracle dba-1576 Sep 13 11:21 r101_ora_24090.trc
    < << wrong [oracle@aulnx1 udump]$ sqlplus '/ as sysdba' SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 13 11:21:15 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> show parameter _trace_files_public
    NAME TYPE VALUE
    ———————————— ———–
    ——————————
    _trace_files_public boolean TRUE
    SQL> exit

    Ct is using Two nodes RAC cluster 10.1.0.3 on HP 11.11.

    This problem is reproducible at will.
    To reproduce the problem, make sure listener is started by command:
    srvctl start nodeapps -n
    Or automatically started after node reboot.

    With this problem, users can not view their sql trace files.

    DIAGNOSTIC ANALYSIS:
    ——————–

    Apparently when srvctl talks to crsd to startup instance, it uses correct
    oracle user umask (022), so there is no problem for sessions connect via BEQ.
    But when srvctl talks to crsd to startup listener, it uses default umask
    (027), thus sessions spawned by listener inherit this uamsk (027) setting and
    overwrite _trace_files_public=true setting. So the trace files generated with

    -rw-r—–.

    WORKAROUND:
    ———–
    1. restart listener manually as oracle user each time listener is started by
    srvctl command
    or
    2. modify lsnrctl command as follows:
    cd $ORACLE_HOME/bin
    mv lsnrctl lsnrctl.bin
    vi lsnrctl, add followings:

    #!/bin/bash

    umask 0022
    exec $ORACLE_HOME/bin/lsnrctl.bin $*

    save the file.
    chmod +x lsnrctl
    Then next time when srvctl command calls lsnrctl, it will reset umask to 022
    before start listener.

    RELATED BUGS:
    ————-
    Bug 511129 – which has good explanation

    REPRODUCIBILITY:
    —————-
    Always, on other 10g platforms (reproducible on Linux) and 10.1.0.2 as well.

    TEST CASE:
    ———-
    start listener using:
    srvctl start nodeapps -n
    or let listener and instance startup automatically after reboot.
    Instance has _trace_files_public=true set. See above output.

    STACK TRACE:
    ————
    None

    SUPPORTING INFORMATION:
    ———————–
    test.log which is a script output of above testcase.

    24 HOUR CONTACT INFORMATION FOR P1 BUGS:
    —————————————-

    DIAL-IN INFORMATION:
    ——————–

    IMPACT DATE:
    ————
    So the fix for this issue is:
    in /etc/init.d/init.crsd, add (at the top):

    _CRSD_UMASK=022
    export _CRSD_UMASK

    Then restart CRS (This setting is to overwrite CRSD default umask of 027).
    Solution has been verified by ct. Note 283379.1 is created for this issue.
    The init.crsd location varies depending on OS platform.

  3. admin says:

    Cannot Read User Trace File Even ”_trace_files_public”=True In 10G RAC
    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.3
    This problem can occur on any platform.
    This issue happened on all platforms with 10G release when listener is started via CRS stack.
    Symptoms
    User can not read user trace files in udump even with “_trace_files_public”=TRUE set in 10g RAC when connection is made via sqlnet TCP protocol.
    When connection is made via BEQ protocol, the trace file permission is correct.

    eg:
    perform:
    alter session set sql_trace=true;
    select sysdate from dual;
    exit

    trace file generated by sqlplus scott/tiger:
    -rw-r–r– 1 oracle dba 1538 Sep 16 16:57 r101_ora_23637.trc

    trace file generated by sqlplus scott/tiger@R101:
    -rw-r—– 1 oracle dba 2381 Sep 16 16:58 r101_ora_23640.trc
    Changes
    There is no changes, everything is installed by default or with correct configuration.
    _trace_files_public=true set in initSID.ora or spfile.
    listener is started by CRS stack during node startup time
    or
    srvctl start nodeapps -n

    root and oracle user all have umask of 022 set.
    If listener is restarted by oracle user using lsnrctl , then the problem does not happen.
    Cause
    _trace_files_public just changes the permissions on open() and creat() from 0660 to 0664, which can still be restricted by umask. If umask is 007, then the trace files are still not readable to others. If umask is 002, then the trace files should be readable to others.

    The umask of the user that starts the listener affects the trace files of the shadow processes that are started by the listener. The umask of the user that runs a local process that forks a shadow process (sqlplus using BEQ adapter) affects the trace file of that shadow process. The umask of the user that starts up the instance affects the trace files of the background processes.

    The default umask for CRS stack start is 027. The listener was started with this umask during CRS startup phase or via crs_start/srvctl start command, thus it overwrites the _trace_files_public setting in database level for user session which connected via sqlnet, eg: session spawned by listener.
    Solution
    CRS has inbuilt environment setting _CRSD_UMASK which can change the default setting of 027.
    To setup this evnironment variable, depend on the platform, modify init.crsd file as root user, add following at the top:

    _CRSD_UMASK=022
    export _CRSD_UMASK

    Save the file. Then restart CRS stack by reboot the server. After this, the trace file should be generated with read permission to others.

    For different platform, the init.crsd file is under:

    Linux /etc/init.d/init.crsd
    HP-UX /sbin/init.d/init.crsd
    SunOS /etc/init.d/init.crsd
    AIX /etc/init.crsd
    OSF1 /sbin/init.d/init.crsd
    Others /etc/init.d/init.crsd

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569