We have DG in place. With that in mind we have created16 groups. Not sure if
this is too many??
SQL> show parameter log_check
NAME TYPE
VALUE------------------------------------ -----------
------------------------------log_checkpoint_interval integer
0log_checkpoint_timeout integer 1800log_checkpoints_to_alert
boolean FALSESQL>
On Wednesday, 23 August 2017, 14:26, "Powell, Mark" <mark.powell2@xxxxxxx>
wrote:
#yiv6216703968 #yiv6216703968 -- P
{margin-top:0;margin-bottom:0;}#yiv6216703968
Just because you have defined the redo logs at 1.5G does not mean you use all
the space before you switch depending on how you have configured your system
and if RAC, what are your database settings forlog_checkpoint_interval and
log_checkpoint_timeout?
How many redo log groups do you have? From experience we have normally needed
4 to 6 redo log groups.You appear to have Data Guard in use. With Data Guard
you normally want to use smaller redo logs than when DG is not in use, but
would likely need more groups.
Mark PowellDatabase Administration(313) 592-5148
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Zabair Ahmed <dmarc-noreply@xxxxxxxxxxxxx>
Sent: Wednesday, August 23, 2017 6:58:53 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Redo log size on a 4 node RAC cluster. Hello,
11.2.0.4 EE on 4 node RAC Redhat 6.6 Cluster.
We are constantly seeing "Checkpoint not complete" message in the alert log:
Thread 2 cannot allocate new log, sequence 109658Checkpoint not complete
Current log# 26 seq# 109657 mem# 0:
+ARCH01/prd/onlinelog/group_26.393.904013409 Current log# 26 seq# 109657 mem#
1: +ARCH01/prd/onlinelog/group_26.634.904013411Thread 2 advanced to log
sequence 109658 (LGWR switch)Tue Aug 22 00:03:06 2017LNS: Standby redo logfile
selected for thread 2 sequence 109658 for destination LOG_ARCHIVE_DEST_2
Current log# 3 seq# 109658 mem# 0: +ARCH01/prd/onlinelog/group_3.263.877611157
Current log# 3 seq# 109658 mem# 1:
+ARCH01/prd/onlinelog/group_3.273.877680615Tue Aug 22 00:03:11 2017Archived Log
entry 510017 added for thread 2 sequence 109657 ID 0x1b7b9f46 dest 1:Tue Aug 22
00:11:39 2017Thread 2 cannot allocate new log, sequence 109659Checkpoint not
complete Current log# 3 seq# 109658 mem# 0:
+ARCH01/prd/onlinelog/group_3.263.877611157 Current log# 3 seq# 109658 mem# 1:
+ARCH01/prd/onlinelog/group_3.273.877680615Thread 2 advanced to log sequence
109659 (LGWR switch) Current log# 22 seq# 109659 mem# 0:
+ARCH01/prd/onlinelog/group_22.744.904009995 Current log# 22 seq# 109659 mem#
1: +ARCH01/prd/onlinelog/group_22.931.904009999Tue Aug 22 00:11:44 2017LNS:
Standby redo logfile selected for thread 2 sequence 109659 for destination
LOG_ARCHIVE_DEST_2Tue Aug 22 00:11:50 2017Archived Log entry 510019 added for
thread 2 sequence 109658 ID 0x1b7b9f46 dest 1:Tue Aug 22 00:21:55 2017Thread 2
cannot allocate new log, sequence 109660Checkpoint not complete Current log#
22 seq# 109659 mem# 0: +ARCH01/prd/onlinelog/group_22.744.904009995 Current
log# 22 seq# 109659 mem# 1: +ARCH01/prd/onlinelog/group_22.931.904009999Thread
2 advanced to log sequence 109660 (LGWR switch) Current log# 4 seq# 109660
mem# 0: +ARCH01/prd/onlinelog/group_4.265.877611161 Current log# 4 seq# 109660
mem# 1: +ARCH01/prd/onlinelog/group_4.274.877680623Tue Aug 22 00:22:00 2017LNS:
Standby redo logfile selected for thread 2 sequence 109660 for destination
LOG_ARCHIVE_DEST_2Tue Aug 22 00:22:09 2017Archived Log entry 510026 added for
thread 2 sequence 109659 ID 0x1b7b9f46 dest 1:Tue Aug 22 00:30:36 2017Thread 2
cannot allocate new log, sequence 109661Checkpoint not complete Current log# 4
seq#
Also ASH Report listing "log file switch (checkpoint incomplete)" as the Top
User Event.
All this is indicating that we have redo logs which are incorrectly sized.
My question is how do you determine the correct redo log size? Currently the
redo log size 1.5GG in all 4 instances.
Can you mix and match different redo log size for different instances? Is this
recommended? We are getting the "Checkpoint not complete" message only on
Instance 2!
Are there any disadvantages in having different redo log sizes for different
instances? Do other people do this? Or should we keep the redo log size the
same across all 4 instances?
We are also running this in a DataGuard environment.
Appreciate any feedback.
Thanks