REDO LOG file size

[samdba]

Hi guys,

How to determine the file size of REDO LOGs?

We have DSS kind of system with about 40-50 users and NOT a type of 24X7 environment.

We had 50 MB of redo log file with 3 members with two groups in each.

We were observing frequent log checkpointing and too many Archive log files generation.

Should I increase the size from 50M to 100M?

Pl. advise.

Thanks in Adv.

[jgmagnus]

most would say anywhere from 1 checkpoint every 15-30 minutes or so. It would also depend on how important the data is to retrieve. If very important and you have lots of activity, then you want more frequent checkpoints/log-intervals.

if you take cold-backups every evening, then you don't need the old achived-logs after your cold-backup. Just delete your logs after every cold-backup with a script. I assume you are doing this since you mentioned it is not 24/7.

- Magnus

[samdba]

Originally posted by jgmagnus
most would say anywhere from 1 checkpoint every 15-30 minutes or so. It would also depend on how important the data is to retrieve. If very important and you have lots of activity, then you want more frequent checkpoints/log-intervals.

if you take cold-backups every evening, then you don't need the old achived-logs after your cold-backup. Just delete your logs after every cold-backup with a script. I assume you are doing this since you mentioned it is not 24/7.

- Magnus

Hi Magnus,

I think, we have very weired log generation pattern. Sometimes, every two minutes, Arch log file is created. Sometimes, it takes 1-2 hours too. In Nov, it generated 450 Arch log files of 50 MB each and in Oct, about 470.

We take hot backup everyday and weekly cold backup as we cannot shutdown the database every night due to some schedules concurrent programs running that time.

So, should I increase the size of Redo log file and then change LOG_CHECKPOINT_INTERVAL to the size greater than 100M so that only on filling of Redo log file, log checkpoint occurs?

Thanks,

[samdba]

Originally posted by samdba

Originally posted by jgmagnus
most would say anywhere from 1 checkpoint every 15-30 minutes or so. It would also depend on how important the data is to retrieve. If very important and you have lots of activity, then you want more frequent checkpoints/log-intervals.

if you take cold-backups every evening, then you don't need the old achived-logs after your cold-backup. Just delete your logs after every cold-backup with a script. I assume you are doing this since you mentioned it is not 24/7.

- Magnus

Hi Magnus,

I think, we have very weired log generation pattern. Sometimes, every two minutes, Arch log file is created. Sometimes, it takes 1-2 hours too. In Nov, it generated 450 Arch log files of 50 MB each and in Oct, about 470.

We take hot backup everyday and weekly cold backup as we cannot shutdown the database every night due to some schedules concurrent programs running that time.

So, should I increase the size of Redo log file and then change LOG_CHECKPOINT_INTERVAL to the size greater than 100M so that only on filling of Redo log file, log checkpoint occurs?

Thanks,

Hi Magnus,

Are you there?

Thanks,

[pando]

Well I wonder how a DSS system can generate arvhive logs
Or it's a hybrid system?

If it's DSS you dont really have to put it in archive log mode anyways

[samdba]

Originally posted by pando
Well I wonder how a DSS system can generate arvhive logs
Or it's a hybrid system?

If it's DSS you dont really have to put it in archive log mode anyways

Hi Pando,

I am sorry. It is a kind of hybrid system.

Thanks,

[Ablakios]

Sam,
You should have 3 groups with 2 members each.

You have that type of error because your redo log size is too small to handle the transaction
load.
what you need to do is re create your redo log with larger size.
before you drop your redo log make sure you only drop the redo log with INACTIVE status.

SQL>COL MEMBER FORMAT A40
SQL> select f.group#,
f.member,
g.status,
g.bytes bytes_mb
from v$logfile f, v$log g;

if they are active do the following.
SQL> alter system switch logfile;
SQL> alter database drop logfile group 1;
REM to delete logfiles from OS
SQL> HOST DEL C:\oracle\oradata\db_name\redo01.log

Also LOG_CHECKPOINT_INTERVAL = (set it larger than your redo log) and
LOG_CHECKPOINT_TIMEOUT=0. These 2 parameters influence how often your log switch.

Hope this help

Jean Richard.

[samdba]

Thanks folks for your precious advise.

I have increased the size of Redo logs from 50M to 100M. Now, I am monitoring it.

But, now how can I change the group numbers and name of new log files.

Earliler, there were 3 groups 1,2 and 3 with 2 members each such as log1.dbf, log1a.dbf, log2.dbf, log2a.dbf, log3.dbf, log3a.dbf.

I have dropped old groups and their members, files.

Now, group numbers are 4,5 and 6 and new redo log file names are log4.dbf, log4a.dbf, log5.dbf, log5a.dbf, log6.dbf, log6a.dbf.

How can I restore old group numbers and old log file names?

Thanks,

[samdba]

Any solution to my renaming thing????

After increasing Redo log file size from 50m to 100m and also increasing the value of LOG_CHECKPOINT_INTERVAL appropriately, I still see very inconsistancy in Archive log file creation.

It created some files with 5-10 minutes interval and few files with 1-2 hours.

So, overall I do not see any advantage of increasing the redo log file size.

What is the solution?


Thanks,

[ratcheer]

If it is any consolation to you, my redo logs are 256MB each, and my processing load can still make them swap several times per minute (sometimes).

It is NEVER going to be a constant rate of swapping. Oracle recommends sizing them so that they swap not more than once per hour. It is not a problem if they swap less frequently than that.

However, it is not always possible to achieve this goal.

[Edited by ratcheer on 12-11-2001 at 04:11 PM]