?
Solved

Oracle Trace File Full

Posted on 2010-01-11
21
Medium Priority
?
1,590 Views
Last Modified: 2013-12-18
Hi,

I have an oracle database w06eprd which is showing deadlocks are occuring.
The database is 10.2.0.3 (patch will be for july/august this year)
Server: Win2K3 R2 Service Pack 2

Mon Jan 11 14:44:15 2010
ORA-00060: Deadlock detected. More info in file d:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc.

The next thing is to check this alert log file:
I couldn't find any timestamp of that date,
From the bottom of the file I looked for 2010-01-11, couldn't find anything

*** 2009-12-17 11:55:57.551
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030004-000de807        23     528     X             25     524           X
TX-000a001a-000df2ac        25     524     X             23     528           X
----
---- removed lots of lines till end
----
        ----- -------- -------- --------- ---- ------ ---
            0 8f1e5750 8b183670 I/P/A/-/-    0 NONE   00
            4 8b17e334 8a934de8 I/P/A/-/-    5 NONE   00
*** Trace file full ***

The trace file is 5Mb and the max_dump_file_size = 10240 blocks --> i'm still figuring this size out, but that's not the real problem

The next thing i did was to move the trace file w06eprd_j002_148.trc to an other directory
Then i found info about oradebug
so i used this in sqlplus:

oradebug setoraspid 148
oradebug trace_close

oracle is not recreating the trace file in the original directory
and of course neither any info on the deadlock

does anybody have any hints or clues on what to do next ?
0
Comment
Question by:Geert Gruwez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 2
21 Comments
 
LVL 11

Expert Comment

by:it-rex
ID: 26284224
Geert_Gruwez,
few points here with deadlocks there is nothing you can do to fix as oracle will do it for;
What is a TX lock ?
~~~~~~~~~~~~~~~~~~~
  A TX lock is acquired when a transaction initiates its first change and is
  held until the transaction does a COMMIT or ROLLBACK. It is used mainly as
  a queuing mechanism so that other sessions can wait for the transaction to
  complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction
  ID of the active transaction.


all what you need to do is to avoid them ob the app level with good code;
as the app which issues the update statements has no strict ordering of the rows it updates.
apps can avoid row-level lock deadlocks by enforcing some ordering
 of row updates. This is purely an application design issue.


==================
NO 2
as from the trace file d:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc.
name it is a job q process trace file ext j002_148.trc. says that;
what happened that while the job was running the dead lock happened and the trace file was created because of the J process ;in a cse like that there could be timestamp diffrerenece between the alert log and the trace file.

this is different from a dbwr or lgwr cases where you have trace files with the name of the process as the error message was not critical to the life of the instance and a mandatory process was not engaged.

just check why this dead lock happened from the app code side.
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 26284387
If trace file is not in its original location then check for default location for windows installation. for Unix the default log folder is $ORACLE_HOME/rdbms/log
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284460
i used the sqlplus and
oradebug tracefile_name
to find the file of the trace

i am using this trace file and the information in it to find eaxctly what statements are producing the deadlocks

and now i don't have any information any more ...
as the *** trace file full ***

i know what a deadlock is and why it happens
i am trying to show the people developing what code caused the deadlock ...
but i can't, as i don't have the info

i need the info to help them from stopping the deadlocks occuring
i need to find a way to :
restart the info in the trace file

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284498
i get this on a other server where i mark the problematic code:

<alert text>
*** 2010-01-11 15:13:39.052
*** SESSION ID:(45.41541) 2010-01-11 15:13:39.052
DEADLOCK DETECTED
Current SQL statement for this session:DELETE FROM TOHOSTSEGMENTSTAT WHERE SEGMENTID <:B2 AND INSTANCEID = :B1 AND STATE = 'D'
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
A43535EC         9  anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090010-0026cd77       110      45     X            122     115           X
TX-00040005-002645bf       122     115     X            110      45           X
session 45: DID 0001-006E-00001A36    session 115: DID 0001-007A-000003F6
session 115: DID 0001-007A-000003F6    session 45: DID 0001-006E-00001A36
Rows waited on:
Session 115: obj - rowid = 00001B3B - AAABs7AAEAAANRSAAE
  (dictionary objn - 6971, file - 4, block - 54354, slot - 4)
Session 45: obj - rowid = 00001C0D - AAABwNAAFAAANaxAC5
  (dictionary objn - 7181, file - 5, block - 54961, slot - 185)
Information on the OTHER waiting sessions:
Session 115:
  pid=122 serial=5466 audsid=33176306 user: 34/LOCUS
  O/S info: user: apse13, term: SRVAPSE13, ospid: 5088:5744, machine: EMEA\SRVAPSE13
            program: tohora_QSSE_PRD.exe
  application name: tohora_QSSE_PRD.exe, hash value=0
  Current SQL Statement:
 
SELECT NVL (SI.MAXVALUE, SG.MAXVALUE) MAXVALUE, NVL (SI.MINVALUE, SG.MINVALUE) MINVALUE, SI.SEQUENCEITEM, SI.VALUE FROM SEQUENCEGROUP SG, SEQUENCEITEM SI WHERE SI.SEQUENCEGROUP =:B2 AND SI.SEQUENCEITEM =:B1 AND SG.SEQUENCEGROUP =SI.SEQUENCEGROUP FOR UPDATE OF SEQUENCEITEM
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
     process: 9FCBC7CC, call: A76055EC, xact: A11CD680, curses: 9FDB5254, usrses: 9FDB5254
  ----------------------------------------
  SO: 9FCBC7CC, type: 2, owner: 00000000, flag: INIT/-/-/0x00
  (process) Oracle pid=110, calls cur/top: A76055EC/A25EB4B0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 198 0 4
              last post received-location: kslpsr
              last process to post me: 9fca29bc 1 6
              last post sent: 0 0 16
              last post sent-location: ksasnd
              last process posted by me: 9fca29bc 1 6
    (latch info) wait_event=0 bits=10
      holding     21999a8 Parent+children enqueue hash chains level=4
        Location from where latch is held: ksqcmi: kslgpl:
        Context saved from call: 0
        state=busy
        recovery area:
Dump of memory from 0x9FCA195C to 0x9FCA1964
9FCA1950                            00000000              [....]
9FCA1960 00000000                             [....]            
    Process Group: DEFAULT, pseudo proc: 9FD95C70
    O/S info: user: SYSTEM, term: SRVORAE02, ospid: 2888
    OSD pid info: Windows thread id: 2888, image: ORACLE.EXE
    ----------------------------------------
    SO: 9FDB5254, type: 4, owner: 9FCBC7CC, flag: INIT/-/-/0x00
    (session) trans: A11CD680, creator: 9FCBC7CC, flag: (48100041) USR/- BSY/-/-/-/-/-
              DID: 0001-006E-00001A36, short-term DID: 0000-0000-00000000
              txn branch: 00000000
              oct: 7, prv: 0, sql: A434A5B0, psql: A434A5B0, user: 649/HOSTCOMM
    O/S info: user: apse13, term: SRVAPSE13, ospid: 5032:6012, machine: EMEA\SRVAPSE13
              program: tohora_QSSE_PRD.exe


And this info is missing ...
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26284533
Geert_Gruwez:
have you seen my comment?
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284576
yes, i have seen your comment

i renamed the old file
i then moved this file to a other directory

i then searched for the file
and it says :
---------------------------
d:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc
---------------------------
Windows cannot find 'd:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc'. Make sure you typed the name correctly, and then try again. To search for a file, click the Start button, and then click Search.
---------------------------
OK  
---------------------------

so the timestamp difference isn't the problem
there just isn't a new file
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284633
meanwhile, i have set the following

 alter system set max_dump_file_size = 100240;

i'm hoping this will resolve it
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26284672
you have moved the trace file;and the even already happened.
which means the oracle engine is not dumping data into trace ;
why do you expect to find the trace again or that oracle will ever recreate it?
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284682
I recreated the same file in the same location also
and emptied it, i only left in the first line.
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284692
well i expected oracle to recreate it ...
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26284743
just do not worry about the whole thing oracle will rectify dead locks for you.
the code needs to be changed to a good model;a TX lock is a dml as I have mentioned!
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26284755
i know it is caused by the dml
that's not the issue

the problem is there is trillions of dml happening on the database
i wanted the information of which 2 statements out of those trillions of statements ...
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26284927
select s.username, s.status,  s.sid,     s.serial#,
             p.spid,     s.machine, s.process, s.lockwait
      from   v$session s, v$process p
      where  s.process  = 'the process from the trace file'
      and    s.paddr    = p.addr
      /
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26284972
This article shows the steps necessary to identify the offending application code when a deadlock is detected;as it looks like this will happen with you again

http://www.oracle-base.com/articles/misc/Deadlocks.php
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 26286487
But whatever info you are trying to find is already there in your trace file.
Current SQL statement for this session:
DELETE FROM TOHOSTSEGMENTSTAT WHERE SEGMENTID <:B2 AND INSTANCEID = :B1 AND STATE = 'D'
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
A43535EC         9  anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                      ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090010-0026cd77       110      45     X            122     115           X
TX-00040005-002645bf       122     115     X            110      45           X
session 45: DID 0001-006E-00001A36    session 115: DID 0001-007A-000003F6
session 115: DID 0001-007A-000003F6    session 45: DID 0001-006E-00001A36
Rows waited on:
Session 115: obj - rowid = 00001B3B - AAABs7AAEAAANRSAAE
 (dictionary objn - 6971, file - 4, block - 54354, slot - 4)
Session 45: obj - rowid = 00001C0D - AAABwNAAFAAANaxAC5
 (dictionary objn - 7181, file - 5, block - 54961, slot - 185)
Information on the OTHER waiting sessions:
Session 115:
 pid=122 serial=5466 audsid=33176306 user: 34/LOCUS
 O/S info: user: apse13, term: SRVAPSE13, ospid: 5088:5744, machine: EMEA\SRVAPSE13
           program: tohora_QSSE_PRD.exe
 application name: tohora_QSSE_PRD.exe, hash value=0
 Current SQL Statement:
 
SELECT NVL (SI.MAXVALUE, SG.MAXVALUE) MAXVALUE, NVL (SI.MINVALUE, SG.MINVALUE) MINVALUE, SI.SEQUENCEITEM, SI.VALUE FROM SEQUENCEGROUP SG, SEQUENCEITEM SI WHERE SI.SEQUENCEGROUP =:B2 AND SI.SEQUENCEITEM =:B1 AND SG.SEQUENCEGROUP =SI.SEQUENCEGROUP FOR UPDATE OF SEQUENCEITEM
End of information on OTHER waiting sessions.

 
This is enough for you to send to application guys for investigation
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26286554
Fayyaz:
I think the asker@ ID: 26284498 said
i get this on a other server where i mark the problematic code:
and he meant he saw a dead lock trace file before on another server that looked like that!

may be i am wrong but this is what I understood!
0
 
LVL 38

Author Comment

by:Geert Gruwez
ID: 26286927
it-rex,
i actually sent them the same link about the deadlocks

Fayyaz,
that was for a other server as i stated

okay, forget the deadlocks, that's not the problem

the problem is a trace file which is full
if i delete the trace file, oracle doesn't recreate it
i don't know yet, if after recreating the file empty,
if oracle now writes new info into it

i'm still waiting for a new deadlock to occur ...

the deadlocks themselves are not the problem,
it's the info produced by oracle which doesn't get written to the trace file.

am i so unclear ?
0
 
LVL 11

Assisted Solution

by:it-rex
it-rex earned 2000 total points
ID: 26287128
http://translate.google.com/translate?hl=en&sl=zh-CN&u=http://www.pazel.cn/pazel/article.asp%3Fid%3D78&ei=GYlLS7HfM8Tg8Qbi7OX7Ag&sa=X&oi=translate&ct=result&resnum=7&ved=0CBcQ7gEwBjgU&prev=/search%3Fq%3D%2522trace%2Bfile%2Bfull%2522%2B%252Boracle%26hl%3Den%26sa%3DN%26start%3D20

here waht this link has:

User wants to trace a session, so he starts a sesson and I open a trace for him. But he says, he finds
*** Trace file full *** *** Trace file full ***
in the end of the trace file. in the end of the trace file.

I check the file size of the trace, it's 5120KB, so i believe there is a parameter in oracle database which limits the trace file size. from the metalink, i get the parameter -- MAX_DUMP_FILE_SIZE. I check the file size of the trace, it's 5120KB, so i believe there is a parameter in oracle database which limits the trace file size. From the metalink, i get the parameter - MAX_DUMP_FILE_SIZE.

You can use below command to find the value of this parameter,

SHOW PARAMETER MAX_DUMP_FILE_SIZE SHOW PARAMETER MAX_DUMP_FILE_SIZE

--here is the output - here is the output
NAME                                 TYPE        VALUE NAME TYPE VALUE
------------------------------------ ----------- ---------- ------------------------------------ ----------- --- -------
max_dump_file_size                   string      10240 max_dump_file_size string 10240

so I need to increase the size to a larger value. so I need to increase the size to a larger value.
First, i use below command to increase the value, First, i use below command to increase the value,
Alter SESSION SET MAX_DUMP_FILE_SIZE =102400 Alter SESSION SET MAX_DUMP_FILE_SIZE = 102400

but it doesn't work, the trace file doesn't grow up once it's 5120KB. but it doesn't work, the trace file doesn't grow up once it's 5120KB.
Since I use Since I use

exec dbms_system.set_sql_trace_in_session(28,18792,FALSE); exec dbms_system.set_sql_trace_in_session (28,18792, FALSE);

to catch trace file, I think the trace file may be generated by another session, so I change the parameter by to catch trace file, I think the trace file may be generated by another session, so I change the parameter by

Alter SYSTEM SET MAX_DUMP_FILE_SIZE =102400; Alter SYSTEM SET MAX_DUMP_FILE_SIZE = 102400;

and this time , it does work ! the trace file is 14.7MB when the session finishes.. and this time, it does work! the trace file is 14.7MB when the session finishes ..

So , here is the summary, So, here is the summary,

the values of MAX_DUMP_FILE_SIZE in database uses the default unit which is OS blocks, so for example, if your logical file system block size is 512 bytes and you do not want to exceed 5 Mg for the trace file size, you would set the MAX_DUMP_FILE_SIZE to 10000. And if you want to have larger trace file, you would set the MAX_DUMP_FILE_SIZE to a larger value , for example, 102400. the values of MAX_DUMP_FILE_SIZE in database uses the default unit which is OS blocks, so for example, if your logical file system block size is 512 bytes and you do not want to exceed 5 Mg for the trace file size, you would set the MAX_DUMP_FILE_SIZE to 10000. And if you want to have larger trace file, you would set the MAX_DUMP_FILE_SIZE to a larger value, for example, 102400.
0
 
LVL 11

Assisted Solution

by:it-rex
it-rex earned 2000 total points
ID: 26287140
also from
Jeremiah Wilton the infamouse first amazaon.com DBA

http://www.mail-archive.com/oracle-l@fatcity.com/msg62967.html
0
 
LVL 11

Assisted Solution

by:it-rex
it-rex earned 2000 total points
ID: 26287232
mostly as OS block is 512 bytes which means ur size is 512*10240/1000/1000= almost 5 mb that is why ur trace file stopped after hitting the 5 limit;
you can set max_dump_file_size to unlimited  or a numberwith a suffix of K or M for kilo or megabytes or increase this size according to your block size like 20480 will result in 10 mg trace file!
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 0 total points
ID: 26293439
there were 2 solutions to this problem

1: setting max_dump_file_size higher
2: deleting contents from the trace file and saving it (the file size will shrink)
why does nobody suggest the second in clear words ?

i now get the information of the deadlocks in the trace files
next step is to automate the info extraction and shrinking of the trace files

thanks for all the help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question