Super BLOATED archive log files!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Super BLOATED archive log files!

  1. #1
    Join Date
    Jul 2002
    Posts
    13

    Question

    Hello everyone,

    I have a question about archive log files. I understand that archive log files are generated whenever the redo logs fill up on a group, and the process ARCn is the one that writes to the path/file_format specified in the respective initSID.ora.

    With the principles as stated above, if a database experiences relatively consistent use, you'll have archive log files spawning at relatively regular intervals, and they'll have about the same size.

    I have just taken over the task of administering a database. This database experienced a bad crash recently, and recovery was performed by the previous guy. Now that I have this database in my hands, I went over it in detail, and I found that all instances on this database had only one archive log file per instance, and it was HUGE size. In the order of 3 Gbytes.

    This puzzles and worries me. I was expecting to see a list of archive log files with sequential names, all with relatively even size, but here, what I had was a single obscenely bloated file per instance.

    I can't figure out why it is like this, and I am not sure what settings to change to get it normal again. Can someone help me out? I need information on what settings to check and stuff like that.

    Many thanks in advance.

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Is it a tared Unix file with all the redo logs inside it?
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Jul 2002
    Posts
    13
    Nope. Not a tared file. This is the normal archive file following the normal conventions, except that it was huge size. The name of the file is something like this: /mount_point/oradata/SID/ARCHIVE/arch_056.arc

    As you can see, the sequence is still running, although I saw only one file there, and I believe that was the only file that was spawned since the server crash/database recovery event.

    I am thinking that it could be some settings not done properly after the crash. At first I thought that the log_checkpoint_interval setting was set to false, but seeing as how there WAS in fact one archive log automatically spawned, that can't be right...

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Well the log_checkpoint_interval shouldn't make any difference since it's related to CHECKPOINTING and not log switches. So what is the size and number of groups of REDO LOGS?
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Jul 2002
    Posts
    13
    Hi,

    Sorry for slow reply. Regarding your query, there are three groups, and each group has two members. The size of each member is 1024 Kbytes (1 Mbyte).

    Best Regards,
    Wilbie Poh

  6. #6
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    can you list the values of '%archive%' from v$parameter ?

    Code:
    select name, value from v$parameter where name like '%archive%';
    
    archive log list;
    This will help us see if the current log sequence number is comparable to the 3 G archive log file.

    HTH.

  7. #7
    Join Date
    Jul 2002
    Posts
    13
    Ok, I'll have to do this check tomorrow because I'm at a different site today. But I hope you don't mind, could you please explain to me what the results mean? The select query should return seven rows. What is log_archive_buffers and log_archive_buffer_size? Also, what is log_archive_duplex_dest and log_archive_min_succeed_dest?

    Thanks in advance.

  8. #8
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by wobbie
    What is log_archive_duplex_dest
    http://technet.oracle.com/docs/produ...astru.htm#8155

    What is log_archive_buffers
    What version of Oracle are you using ? This is obsolete in 8i.
    This denotes the number of buffers to allocate for archiving.


    What is log_archive_min_succeed_dest
    ...the minimum number of destinations that must succeed in order for the online logfile to be available for reuse.



    What is log_archive_buffer_size
    This param is obsolete in 8i. This denotes the size of each archival buffer, in redo log blocks (operating system blocks).

    Do keep us posted about the outcome.

    HTH.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width