This discussion is archived
10 Replies Latest reply: Apr 17, 2012 10:00 AM by 713555 RSS

How to get rid of huge diag file and why occurred

713555 Pro
Currently Being Moderated
11.2.0.2.0 windows 2008 2 node RAC.

In my diag/trace I have a diag file 20gb in size. Nothing is currently writing to it as its been a static size for some time. However, the Oracle server PID itself has a lock on it so I cant delete it. (procexp > handle find)

If this was linux I know a way but Im unable to delete or echo blank to it to wipe it in windows.

How did this get there? dont know. there is a bug on oracle about large diag files which was supposed to have been fixed after 11.1.0.7
Large Amount of Trace Information Written to DIAG Trace Files [ID 750773.1]

I dumped system system events with oradebug and queried all open events with dbms_system.read_ev but nothing is reported to be running.

thoughts on how to remove and why it occurred? I would rather remove online but can bring the instance later tonight so not too bothered but it would be nice to know how to remove it if I was in an urgent situation and needed that space.

Edited by: deebee_eh on 17-Apr-2012 07:38
  • 1. Re: How to get rid of huge diag file and why occurred
    931190 Newbie
    Currently Being Moderated
    Set max_dump_file_size parameter to a lower value. A small note is given below. This parameter controls the maximum size, the trace file can be generated.


    max_dump_file_size
    ====================
    * A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
    * A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.
    * The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.

    SQL> show parameter dump_file

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    max_dump_file_size string unlimited
    SQL> alter system set max_dump_file_size='500M' scope=both sid='*';

    System altered.

    SQL> show parameter dump_file

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    max_dump_file_size string 500M
    SQL>
  • 2. Re: How to get rid of huge diag file and why occurred
    713555 Pro
    Currently Being Moderated
    yes, the max dump is a good idea and I have set since I seen this large file occured.

    doesnt help me delete now though. Is there a way within oracle itself to identify internal process that has lock on the diag trace and release that?

    as I said, the OS thinks the database server PID has the lock so I cant delete that from the OS without crashing instance.
  • 3. Re: How to get rid of huge diag file and why occurred
    LKBrwn_DBA Expert
    Currently Being Moderated
    On WinDoze you could reduce the size using:
    C:\> echo . >hugeDumpfile.trc
    :p
  • 4. Re: How to get rid of huge diag file and why occurred
    713555 Pro
    Currently Being Moderated
    note the bit above where I say I cant. already tried. :)

    CMD as administrator
    D:\app\oracle\diag\rdbms\prd\prd2\trace>dir 1.trc
     Volume in drive D is Local Data
     Volume Serial Number is 3A92-6F76
    
     Directory of D:\app\oracle\diag\rdbms\prd\prd2\trace
    
    17/04/2012  11:42    17,703,682,000 1.trc
                   1 File(s) 17,703,682,000 bytes
                   0 Dir(s)   5,823,459,328 bytes free
    
    D:\app\oracle\diag\rdbms\prd\prd2\trace>echo . > 1.trc
    Access is denied.
    
    D:\app\oracle\diag\rdbms\prd\prd2\trace>
    it had a proper diag trace file but in an effort to remove it I tried to rename it which it allowed me to do but am unable since to rename or "more" it now when I could before the rename. procexp > handle file shows "1.trc" is locked by oracle server OS process.

    Both nodes in the RAC have the same issue. 20gb on 1 node, 17.7gb on the second as above.
  • 5. Re: How to get rid of huge diag file and why occurred
    LKBrwn_DBA Expert
    Currently Being Moderated
    OK....
    Try using the "PURGE" option from ADRCI utility?
    :)
  • 6. Re: How to get rid of huge diag file and why occurred
    713555 Pro
    Currently Being Moderated
    and again..already tried... :).... if I try to purge any trace file older then 1 minute

    node 1.
    D:\app\oracle\diag\rdbms\prd\prd1\trace>dir prd1_diag_11368.trc
     Volume in drive D is Local Data
     Volume Serial Number is E8AD-B67E
    
     Directory of D:\app\oracle\diag\rdbms\prd\prd1\trace
    
    17/04/2012  12:08    19,997,904,896 prd1_diag_11368.trc
                   1 File(s) 19,997,904,896 bytes
                   0 Dir(s)   3,492,835,328 bytes free
    
    D:\app\oracle\diag\rdbms\prd\prd1\trace>
    adrci> purge -age 1 -type TRACE
    DIA-48119: error encountered when attempting to remove a file [D:\app\oracle\diag\rdbms\prd\prd1\trace\prd1_diag_11368.trc] [0]
    DIA-48190: OS unlink system call failure
    OSD-00001: additional error information
    O/S-Error: (OS 5) Access is denied.
    
    adrci> exit
    and same on node 2, my 1.trc
    adrci> purge -age 1 -type TRACE
    DIA-48119: error encountered when attempting to remove a file [D:\app\oracle\diag\rdbms\prd\prd2\trace\1.trc] [0]
    DIA-48190: OS unlink system call failure
    OSD-00001: additional error information
    O/S-Error: (OS 5) Access is denied.
    
    adrci> exit
    
    D:\app\oracle\diag\rdbms\prd\prd2\trace>dir 1.trc
     Volume in drive D is Local Data
     Volume Serial Number is 3A92-6F76
    
     Directory of D:\app\oracle\diag\rdbms\prd\prd2\trace
    
    17/04/2012  11:42    17,703,682,000 1.trc
                   1 File(s) 17,703,682,000 bytes
                   0 Dir(s)   5,821,325,312 bytes free
    
    # and try an OS force delete
    
    D:\app\oracle\diag\rdbms\prd\prd2\trace>del /F 1.trc
    D:\app\oracle\diag\rdbms\prd\prd2\trace\1.trc
    Access is denied.
    
    D:\app\oracle\diag\rdbms\prd\prd2\trace>
  • 7. Re: How to get rid of huge diag file and why occurred
    LKBrwn_DBA Expert
    Currently Being Moderated
    WOW!

    Perhaps you need to use the three finger Windoze salute: Ctl-Alt-Del?
  • 8. Re: How to get rid of huge diag file and why occurred
    713555 Pro
    Currently Being Moderated
    srvctl stop instance and just stopping the instance removed the file, I didnt need to manually remove it afterwards. space freed up instantly.

    restarted instance fine,

    same on node2, file removed and space freed up automatically on instance stop.

    thanks for efforts, havent seen before nor could find anything out there on how to do so if anyone gets again...
  • 9. Re: How to get rid of huge diag file and why occurred
    jgarry Guru
    Currently Being Moderated
    http://www.oracloid.com/2006/05/closing-trace-file-with-oradebug/
  • 10. Re: How to get rid of huge diag file and why occurred
    713555 Pro
    Currently Being Moderated
    >
    http://www.oracloid.com/2006/05/closing-trace-file-with-oradebug/
    >

    DOH!!!!!! 15 minutes too late to test, trace already gone with instance bounce.... googled me head off for last 2-3 hours on that and couldnt find it.

    I'll keep that in the back pocket for next occurence... if its happened before, sure to happen again.

    thanks!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points