Super BLOATED archive log files!

[wobbie]

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.

[grjohnson]

Is it a tared Unix file with all the redo logs inside it?

[wobbie]

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...

[grjohnson]

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?

[wobbie]

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

[ggnanaraj]

can you list the values of '%archive%' from v$parameter ?

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.

[wobbie]

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.

[ggnanaraj]

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.