ARCHIVE LOG files generated very frequently

[samirshah]

Hi all,

We have foll. init.ora parameters

db_block_size = 8192
db_block_buffers = 75000
shared_pool_size = 471859200
LOG_CHECKPOINT_INTERVAL = 104402
LOG_BUFFER = 512000

Our Redo Log file size is 50MB.

We have 3 Redo Log groups with 2 members in each.

Database is in ARCHIVE LOG mode.

DML is also very low. We have about 30 users at a time.

LOG_CHECKPOINT_INTERVAL is set to 104402 which is more than 50M / 512 i.e 102400 so that checkpoint occurs only if Log file fills up.

Still I see that it generates ARCH files like 6 files in 2 hours. I feel that it is still more.

What should I do to reduce its frequncy.

Thanks in Advance.

Sam.

[sambavan]

Increase your redo log file size.

Sam

[samirshah]

Originally posted by samirshah
Hi all,

We have foll. init.ora parameters

db_block_size = 8192
db_block_buffers = 75000
shared_pool_size = 471859200
LOG_CHECKPOINT_INTERVAL = 104402
LOG_BUFFER = 512000

Our Redo Log file size is 50MB.

We have 3 Redo Log groups with 2 members in each.

Database is in ARCHIVE LOG mode.

DML is also very low. We have about 30 users at a time.

LOG_CHECKPOINT_INTERVAL is set to 104402 which is more than 50M / 512 i.e 102400 so that checkpoint occurs only if Log file fills up.

Still I see that it generates ARCH files like 6 files in 2 hours. I feel that it is still more.

What should I do to reduce its frequncy.

Thanks in Advance.

Sam.

Don't we have enough 50MB size of our Redo Log. We have very less amount of transactions and also less no. of users.

How to decide the size of Redo Log?

Thanks,

Sam.

[sambavan]

Check your alert log to see how offen your log switch is occouring? Depending on it tune your redo log files. Remember the bigger the log file the longer the recovery.

You could also use v$thread, v$log, v$logfile, v$loghist views to verify things

Sam

[marist89]

Originally posted by samirshah
Still I see that it generates ARCH files like 6 files in 2 hours. I feel that it is still more.

6 log switches in 2 hours is nothing. I have one instance that switches 128M logs every 10 minutes during HEAVY activity. I wouldn't worry about it...

[Zaggy]

I average 10-15mins for 2048MB Redo Log files. I might have to go larger if the load increases.

[venudba]

Whether the archived log file is taking Full 50 MB space in the Physical Disk?

[julian]

LOG_CHECKPOINT_INTERVAL = 104402

LOG_CHECKPOINT_INTERVAL is set to 104402 which is more than 50M / 512 i.e 102400 so that checkpoint occurs only if Log file fills up.

Now, do you have 8 or 8 i? Because the meaning of LOG_CHECKPOINT_INTERVAL is totally different.

In 8i, LOG_CHECKPOINT_INTERVAL means that the target for checkpoint position cannot lag the end of the log by more than 104402 redo log blocks.

In 8, LOG_CHECKPOINT_INTERVAL shows the number of redo log blocks after which to checkpoint.

[samirshah]

Hi all,

Thanks for your replies.

Today, following files have been generated all of same size 50M. I want this freq. to reduce. I also know that we dont have that much DML operations so that 50M is consumed so fast.

We have Oracle 8.0.5

Pl. help me ASAP.

Thanks.

Sam

Thu Sep 13 08:17:42 2001
Thread 1 advanced to log sequence 3068
Current log# 2 seq# 3068 mem# 0: D:\ORADATA\PROD\LOG2.DBF
Current log# 2 seq# 3068 mem# 1: E:\ORADATA\PROD\LOG2A.DBF
Thu Sep 13 08:18:59 2001
Thread 1 advanced to log sequence 3069
Current log# 3 seq# 3069 mem# 0: D:\ORADATA\PROD\LOG3.DBF
Current log# 3 seq# 3069 mem# 1: E:\ORADATA\PROD\LOG3A.DBF
Thu Sep 13 08:20:12 2001
Thread 1 advanced to log sequence 3070
Current log# 1 seq# 3070 mem# 0: D:\ORADATA\PROD\LOG1.DBF
Current log# 1 seq# 3070 mem# 1: E:\ORADATA\PROD\LOG1A.DBF
Thu Sep 13 08:21:22 2001
Thread 1 advanced to log sequence 3071
Current log# 2 seq# 3071 mem# 0: D:\ORADATA\PROD\LOG2.DBF
Current log# 2 seq# 3071 mem# 1: E:\ORADATA\PROD\LOG2A.DBF
Thu Sep 13 08:32:41 2001
Thread 1 advanced to log sequence 3072
Current log# 3 seq# 3072 mem# 0: D:\ORADATA\PROD\LOG3.DBF
Current log# 3 seq# 3072 mem# 1: E:\ORADATA\PROD\LOG3A.DBF
Thu Sep 13 09:15:27 2001
Thread 1 advanced to log sequence 3073
Current log# 1 seq# 3073 mem# 0: D:\ORADATA\PROD\LOG1.DBF
Current log# 1 seq# 3073 mem# 1: E:\ORADATA\PROD\LOG1A.DBF
Thu Sep 13 09:17:41 2001
Thread 1 advanced to log sequence 3074
Current log# 2 seq# 3074 mem# 0: D:\ORADATA\PROD\LOG2.DBF
Current log# 2 seq# 3074 mem# 1: E:\ORADATA\PROD\LOG2A.DBF
Thu Sep 13 10:02:47 2001
Thread 1 advanced to log sequence 3075
Current log# 3 seq# 3075 mem# 0: D:\ORADATA\PROD\LOG3.DBF
Current log# 3 seq# 3075 mem# 1: E:\ORADATA\PROD\LOG3A.DBF
Thu Sep 13 10:32:45 2001
Thread 1 advanced to log sequence 3076
Current log# 1 seq# 3076 mem# 0: D:\ORADATA\PROD\LOG1.DBF
Current log# 1 seq# 3076 mem# 1: E:\ORADATA\PROD\LOG1A.DBF

[marist89]

Originally posted by sambavan
Increase your redo log file size.

Sam