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-00923: FROM keyword not found where expected

Problem Symptom While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below. SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected Cause of the problem The ORA-00923 can be caused by two different reasons. 1)Oracle itself expect FROM keyword in the position but it could not find any. Like below, SQL> select 48*1024 om dual; select 48*1024 om dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected As no from keyword found so error arises. Oracle expected from in place of keyword dual. 2)The secong cause can sometimes mislead you. Like in case of SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where exp
Read more

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur
Read more

Copy files between Unix and Windows with rcp

There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task. The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service. Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer. you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service. The following steps will help you. Step 01: Turn on RCP service on UNIX Host Turn on the RCP service on unix machine by using inetd: 1. Log on as root. 2. Edit the file /etc/Inetd.conf. 3. Uncomment the lines that start with shell and that start with exec. 4. Save the file. 5. Use ps -ef |grep inetd and determine inetd service p
Read more