How to determine the name of the trace file to be generated

In many cases we need to find out the name of the latest trace file generated in the USER_DUMP_DEST directory. What we usually do is go to physically to the USER_DUMP_DEST with the operating system browser and sort all the files by date and look for latest files. We can remove this hassle easily if we know what would be the trace file name in advance.

For example issuing,
D:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 10 21:58:15 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
will generated a trace file inside USER_DUMP_DEST. Let's see the location of USER_DUMP_DEST.

If you use Sql*plus then issue,
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP

Querying from v$parameter,
SQL> select value from v$parameter where name='user_dump_dest';

VALUE
-------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

On windows changes to directory E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP and issue,
E:\oracle\product\10.2.0\admin\orcl\udump>dir /OD

The latest files are for latest trace.

Now it would be quite easy task if we knew the name of the trace file to be generated by ALTER DATABASE command.

In advance we can get the trace file name by,

SQL> set linesize 130
SQL> COL trace_file FOR A60
SQL> SELECT s.sid,
          s.serial#,
          pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
          '_ora_' || p.spid || '.trc' AS trace_file
   FROM   v$session s,
          v$process p,
          v$parameter pa
   WHERE  pa.name = 'user_dump_dest'
   AND    s.paddr = p.addr
   AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

      SID    SERIAL# TRACE_FILE
---------- ---------- ------------------------------------------------------------
      146         11 E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc

So the trace file to be generated now will be named as orcl_ora_4968.trc

So now issuing, "alter database backup controlfile to trace" will generate the file named E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc.

On windows you can issue like this,
SQL> alter database backup controlfile to trace;

Database altered.

SQL> host notepad E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc

Comments

  1. AnonymousJuly 8, 2021 at 4:58 PM

    Great blog. we also share information about amazing USA Dedicated Server Hosting plans with prime benefits according to your business needs so if anyone interested visit this link.

    ReplyDelete
  2. AnonymousJuly 8, 2021 at 5:13 PM

    Hello Everyone, First of all, thank you so much for sharing your blog post. every online business user uses a computer and a lot of software, So if you have got an error on your system or on your business then I hope Onlive Server does your best domain name checking opportunity.

    Book Domain Name Online

    ReplyDelete
  3. AnonymousAugust 14, 2021 at 12:17 PM

    This comment has been removed by the author.

    ReplyDelete
  4. AnonymousAugust 14, 2021 at 12:22 PM

    Firstly you are explain very well in this article. This type of article you are sharing is good for this generation and I surely share this article to my friends and near once. If you want to know about server hosting, I can help you. You must know about
    India VPS and how this could be important for this modern world. Thanks once again.

    ReplyDelete
  5. AnonymousAugust 14, 2021 at 12:45 PM

    This comment has been removed by the author.

    ReplyDelete
  6. AnonymousAugust 14, 2021 at 12:47 PM

    This comment has been removed by the author.

    ReplyDelete
  7. AnonymousAugust 14, 2021 at 6:28 PM

    Here are I received got of valuable information that is helpful in my online business branding. So thanks for sharing your great info and I like your posting.
    Book Domain Name Online

    ReplyDelete
  8. AnonymousSeptember 4, 2021 at 4:37 PM

    Wah! what a post I have to get a piece of excellent information about the web hosting services, such a good explanation. I appreciate your post. Thanks for sharing your experience.
    USA Dedicated Server Hosting

    ReplyDelete
  9. Divyanshu RajputSeptember 18, 2021 at 2:36 PM

    Thank you for your great post. This is so informative for me. Keep sharing this type of informative blog post.
    Dubai Dedicated Server Hosting

    ReplyDelete
  10. Ritz Media WorldFebruary 1, 2024 at 5:37 PM

    This blog post is really very informative. Where you are providing the information about “v How to determine the name of the trace file to be generated” That is really such nice information for those who need it. Here in this post, you are providing one of the most important things that is “We can remove this hassle easily if we know what would be the trace file name in advance. ”. But here my opinion is that you should make a website for this as well as you need to promote also. So that you can choose Ritz Media World.

    ReplyDelete

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more