如何公开Oracle trace文件？

Comments

1. admin says

   2010/08/11 at 21:32

   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

   2010/08/11 at 21:34

   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

   2010/08/11 at 21:36

   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

   回复