Re: archivelog mode question - size of logs?

kaz_at_sis.bms.com wrote:

: We've recently turned on archivelog mode for one of our instances, but
: had to turn it off, due to the amount of archive log files being generated.
: During our normal prime-time hours, we were generating between 125Mb and
: 325Mb of offline redo logs per hour.

I wuold IMHO say that it is quite much, but not unlikely if your database has a large transaction rate. I have seen something like 1 Gb per day, but most of our data is not updated any more, only selected (it is a long historical timeserie).

: The total space in use by this instance is about 3.5 Gb, so the amount of redo
: logs being generated seems extremely high. I realize that this depends
: heavily on how active the database is, but it still sounds too high for us.

Our database is of same size, but like I said, most of it is not to be updated. And like you said, amount of redo logs depends on database activity. A redo log is in fact a log of *every* change in the database, including every write in system tablespace, rollback segments and temporary segments. This is because they are used recovery to re-do (thus the name) everything that has happened in the database after the last backup.

So, not only inserts and updates generate redo logs, also everything that uses temporary segments like sort or join produce them. Is there large queries with order by and joined tables?

: My questions are: Do other people run into a similar situation when
: running in archivelog mode? If so, how do you handle the large volume
: of data - lots of disk space, archive directly to tape? If not,
: any ideas what's going on here?

I have given about half of one disk, that is 500 Mb for archive logs. They are cleaned to tapes once a day. On an average day there is more than enough space, but sometimes additional cleaning runs are needed to prevent freezing of database. Copying logs to tape and then deleting them is done purely by operating system commands, no need to touch the database in any way.

: Some information about our configuration:
 

: RDBMS: v6.0.37.0
: OS: VMS v5.5-1
: There are 4 redo logs, each 25M in size.

Quite alike to ours, but our logs are only 1 Mb by size. I have seen comments that anything more than that would not give much speed, but I think it might be different for large transaction rates.

: Some related parameters:
 

: NAME TYPE VALUE
: ----------------------------------- ------- ------------------------------
: db_block_size integer 2048
: log_allocation integer 10050
: log_archive_buffer_size integer 127
: log_archive_buffers integer 4
: log_blocks_during_backup boolean TRUE
: log_buffer integer 32768
: log_buffers_debug boolean FALSE
: log_checkpoint_interval integer 1000000
: log_entry_prebuild_threshold integer 0
: log_files integer 255
: log_io_size integer 0
: log_simultaneous_copies integer 2
: log_small_entry_max_size integer 800

I do not notice anything strange, but check the Performance Tuning Guide and DBA Guide anyway.

: ----------------
: Joe Kazimierczyk
: kaz_at_bms.com

Mikko Lahti (Mikko.Lahti_at_fmi.fi)
Finnish Meteorological Institute
Helsinki, Finland, Europe Received on Fri Oct 15 1993 - 17:18:30 CET