Oracle Database Discussions

Hello,

When generating a 10053 trace using tracefile_identifier, I see the file generated the first time but on subsequent runs, I don't see files in udump directory.

I have checked in other directories as well. Has anyone faced similar issue ?

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


SQL> show parameter timed_stat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

SQL> show parameter trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace                    integer     0
sec_protocol_error_trace_action      string      TRACE
sql_trace                            boolean     FALSE
trace_enabled                        boolean     TRUE
tracefile_identifier                 string   
SQL> 

SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace
core_dump_dest                       string      /home/oracle/app/oracle/diag/rdbms/orcl/orcl/cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      partial
user_dump_dest                       string      /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace

sqlplus hr/hr@orcl <<EOF
alter session set tracefile_identifier = 'f_id';
alter session set events '10053 trace name context forever,level 1';
select count(*) from employees e, departments d;
exit
EOF

[oracle@localhost trace]$ ls /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl*ora*id*.trc
ls: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl*ora*id*.trc: No such file or directory
[oracle@localhost trace]$ 

I am using the following query to identify the trace file - from Tanel's blog

select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
        (select spid||case when traceid is not null then '_'||traceid else null end
              from v$process where addr = (select paddr from v$session
                                          where sid = (select sid from v$mystat
                                                     where rownum = 1
                                                )
                                     )
        ) || '.trc' tracefile
 from v$parameter where name = 'user_dump_dest';

Rgds,
Gokul

Edited by: Gokul Gopal on Aug 22, 2012 5:53 PM

Edited by: Gokul Gopal on Aug 22, 2012 5:54 PM