10 Replies Latest reply: Apr 17, 2012 12:00 PM by 713555 RSS

    How to get rid of huge diag file and why occurred

    713555
      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
          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
            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
              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
                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
                  OK....
                  Try using the "PURGE" option from ADRCI utility?
                  :)
                  • 6. Re: How to get rid of huge diag file and why occurred
                    713555
                    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
                      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
                        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
                          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
                            >
                            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!!!