question about redo log swich and checkpoints

Printable View

12-01-2004, 10:15 AM
arokh

question about redo log swich and checkpoints

When I look in the alert log I see this:
Current log# 1 seq# 7802300 mem# 0: /u02/oradata/O8I5A/redo01.log
Current log# 1 seq# 7802300 mem# 1: /usr/oracle/redo01.log
Thread 1 advanced to log sequence 7802301
Current log# 4 seq# 7802301 mem# 0: /u02/oradata/O8I5A/redo04.ora
Current log# 4 seq# 7802301 mem# 1: /usr/oracle/redo04.ora
Thread 1 cannot allocate new log, sequence 7802302
Checkpoint not complete
Current log# 4 seq# 7802301 mem# 0: /u02/oradata/O8I5A/redo04.ora
Current log# 4 seq# 7802301 mem# 1: /usr/oracle/redo04.ora
Thread 1 advanced to log sequence 7802302
Current log# 3 seq# 7802302 mem# 0: /u02/oradata/O8I5A/redo03.ora
Current log# 3 seq# 7802302 mem# 1: /usr/oracle/redo03.ora
Thread 1 advanced to log sequence 7802303
Current log# 2 seq# 7802303 mem# 0: /u02/oradata/O8I5A/redo02.log
Current log# 2 seq# 7802303 mem# 1: /usr/oracle/redo02.log
Thread 1 advanced to log sequence 7802304
Current log# 1 seq# 7802304 mem# 0: /u02/oradata/O8I5A/redo01.log
Current log# 1 seq# 7802304 mem# 1: /usr/oracle/redo01.log
Thread 1 advanced to log sequence 7802305
Current log# 4 seq# 7802305 mem# 0: /u02/oradata/O8I5A/redo04.ora
Current log# 4 seq# 7802305 mem# 1: /usr/oracle/redo04.ora
Thread 1 cannot allocate new log, sequence 7802306
Checkpoint not complete
Current log# 4 seq# 7802305 mem# 0: /u02/oradata/O8I5A/redo04.ora
Current log# 4 seq# 7802305 mem# 1: /usr/oracle/redo04.ora
Thread 1 advanced to log sequence 7802306
Current log# 3 seq# 7802306 mem# 0: /u02/oradata/O8I5A/redo03.ora
Current log# 3 seq# 7802306 mem# 1: /usr/oracle/redo03.ora
Thread 1 advanced to log sequence 7802307
Current log# 2 seq# 7802307 mem# 0: /u02/oradata/O8I5A/redo02.log
Current log# 2 seq# 7802307 mem# 1: /usr/oracle/redo02.log
Thread 1 advanced to log sequence 7802308
Current log# 1 seq# 7802308 mem# 0: /u02/oradata/O8I5A/redo01.log
Current log# 1 seq# 7802308 mem# 1: /usr/oracle/redo01.log
Thread 1 advanced to log sequence 7802309

What can be causing this, and how I can solve it?
I have 4 groups with 2 members each on separate drives. Size of the members is 500K. Oracle 8.1.5 on AIX.
12-01-2004, 10:52 AM
DaPi

I'd increase the number of groups.
12-01-2004, 10:55 AM
gopi

....or get faster disks

but DaPi's advice is usually more reasonable :)
12-01-2004, 12:47 PM
Halo

The log switches are taking place too fast, either because of the small size of the redo log files and/or due a high # of transactions/redo. This is the reason for the error:
Thread 1 cannot allocate new log, sequence 7802302
Checkpoint not complete
Increase the size of the redo log files. Start with file size of say, 5M and tune accordingly.
12-02-2004, 11:17 AM
Markelenkov

If you have not Stanby it's better start from 100-200Mb
12-03-2004, 03:29 AM
reydp

Re: question about redo log swich and checkpoints

Quote:

Originally posted by arokh
Size of the members is 500K

It's way too small.
12-03-2004, 03:25 PM
pgamez

Re: Re: question about redo log swich and checkpoints

Quote:

Originally posted by reydp
It's way too small.

Yes, you should increase them accordingly the load you database have. I'd get the timing of the redo switching and increase the size of the log members so the switching time is no less than 30 minutes.

Also, if the problem arises intermittently you should audit the applications or batch processes that might be generating a large amount of redo information. You can use log miner to see a clearer picture (stats) of what's filling your redo logs.
12-14-2004, 04:33 AM
arokh

Thank You for the advices. I increased the size of the redo logs and added two more groups.