Basically that is it. Redo logs are 256M, archive logs are ~40M. There was
a similar issue/thread in this list ~6 weeks ago, but the solution to that
system doesn't fit with this case. Hope that helps.
Henry
On Tue, Mar 9, 2021 at 2:23 PM Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
So what is the issue here exactly? I''ve read through this thread a few
times without clearly understanding the "problem" .
It seems like you're wondering about why the archivelogs are smaller than
the redo logs maybe? But I'm not sure if that is the issue or not.
Chris
On Tue, Mar 9, 2021 at 1:51 PM Henry Poras <henry.poras@xxxxxxxxx> wrote:
I wonder if there is any way to see if the problem is local or from the
standby without temporarily disabling the standby. It would be nice to be
able to eliminate one of those two causes.
Henry
On Tue, Mar 9, 2021 at 12:54 PM Henry Poras <henry.poras@xxxxxxxxx>
wrote:
I liked that idea. Unfortunately,
*physical standby*:sys@ohcopp2> select distinct bytes from v$log;
BYTES
----------
268435456
1 row selected.
*physical standby*:sys@ohcopp2> select distinct bytes from
v$standby_log;
BYTES
----------
268435456
1 row selected.
*primary*:sys@ohcops1> select distinct bytes from v$log;
BYTES
----------
268435456
1 row selected.
On Tue, Mar 9, 2021 at 12:02 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
That explains the LGWR/ARCH oddity.
Next, let me display my ignorance: is it possible to configure your
standby with standby log files that are SMALLER than the primary log files
- I think it used to be necessary to have them matching in size, but maybe
that's changed since the good old days (of 10g). If a standby can survive
with standby log file smaller than primary log files then maybe your 40 -
45MB is being dictated by the size of your standby log files.
Regards
Jonathan Lewis
On Tue, 9 Mar 2021 at 16:35, Henry Poras <henry.poras@xxxxxxxxx> wrote:
Current parameter settings (same on all nodes) are:
log_archive_dest_1
LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=xxxx
log_archive_dest_2 SERVICE=xxxx LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xxxx
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
log_archive_max_processes 4
log_archive_duplex_dest
log_archive_format ARC%S_%R.%T
So log_archive_dest_2 has the deprecated LGWR parameter. It's dest_1,
using FRA which is using ARCH. Huh?
Henry
On Tue, Mar 9, 2021 at 8:45 AM Henry Poras <henry.poras@xxxxxxxxx>
wrote:
Jonathan,
I have to admit I was also not expecting both LGWR and ARCH and only
came across that because you suggested looking for creator. I didn't put
this standby together, so I'll do a bit of digging to see how it is
constructed. One question though (and the reason I didn't head down this
path earlier). If archive_lag_time=900 on all instances, what is the
hypothesis where the standby impacts the archive log size?
Thanks again.
Henry
On Tue, Mar 9, 2021 at 4:43 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Interesting that of the two archived copies one is created by ARCH
and the other by LGWR.
What do your log_archive_dest_1 and log_archive_dest_2 look like ?
For completeness I guess it's also worth checking
log_archive_dest_state_1 and log_archive_dest_state_2
log_archive_max_processes
log_archive_duplex_dest
log_archive_format
log_archive_local_first
What sort of standby setup do you have?
Regards
Jonathan Lewis
On Mon, 8 Mar 2021 at 19:39, Henry Poras <henry.poras@xxxxxxxxx>
wrote:
Hi Jonathan,
Thanks for responding so quickly.
As expected, we have 2 public threads and no private threads (RAC).
The sizes of log_buffer, Redo Size, Fixed Size, public thread size as
documented above are identical for all three nodes.
Looking at v$instance, all nodes have been up for ~10 days (not
identical startup_time, but within a day) with status of OPEN.
I can't find any clues in v$archived_log
primary:sys@ohcops1> l
1 select * from (
2 select dest_id, thread#, sequence#, creator, first_time from
v$archived_log
3 order by first_time desc, thread# asc, dest_id asc
4 )
5* where rownum <=20
primary:sys@ohcops1> /
DEST_ID THREAD# SEQUENCE# CREATOR FIRST_TIME
---------- ---------- ---------- ------- -------------------
1 2 1056302 ARCH 08-03-2021 20:34:11
2 2 1056302 LGWR 08-03-2021 20:34:11
1 1 1126141 ARCH 08-03-2021 20:34:06
2 1 1126141 LGWR 08-03-2021 20:34:06
1 3 1164895 ARCH 08-03-2021 20:33:47
2 3 1164895 LGWR 08-03-2021 20:33:47
1 3 1164894 ARCH 08-03-2021 20:32:26
2 3 1164894 LGWR 08-03-2021 20:32:26
1 2 1056301 ARCH 08-03-2021 20:32:20
2 2 1056301 LGWR 08-03-2021 20:32:20
1 1 1126140 ARCH 08-03-2021 20:32:15
2 1 1126140 LGWR 08-03-2021 20:32:15
1 3 1164893 ARCH 08-03-2021 20:30:50
2 3 1164893 LGWR 08-03-2021 20:30:50
1 2 1056300 ARCH 08-03-2021 20:30:29
2 2 1056300 LGWR 08-03-2021 20:30:29
1 1 1126139 ARCH 08-03-2021 20:30:09
2 1 1126139 LGWR 08-03-2021 20:30:09
1 3 1164892 ARCH 08-03-2021 20:29:32
2 3 1164892 LGWR 08-03-2021 20:29:32
20 rows selected.
I'll keep looking.
Henry
On Sat, Mar 6, 2021 at 3:41 AM Jonathan Lewis <
jlewisoracle@xxxxxxxxx> wrote:
RAC doesn't use private redo - so with 32 CPUs we'd expect two
public threads with (granule - fixed - overheads)/2 as the log buffer
size.
Is this happening on all three instances (redo threads)?
If you report the "first_time" from v$archived_log can you see a
pattern to the timing of the switch.
Is there any clue in the "creator" from v$archived_log ?
One possible explanation for this (assuming you've discounted all
the usual suspects): Are all three instances active when this happens?
I haven't checked recently but when an instance is down the active
instances may be "kicking" it (KK lock) on a regular basis to do a
log file
switch so that all instances have archived log files with SCNs that
are
reasonably close to each other. Maybe there's a side effect (or timing
issue, or bug) related to this that means the kicking is happening too
frequently and you're not getting through a lot of redo before it
happens.
Regards
Jonathan Lewis
On Fri, 5 Mar 2021 at 20:39, Henry Poras <henry.poras@xxxxxxxxx>
wrote:
- I was just rereading this thread and ran into a slightly
different case I am just starting to dig into. My environment is
11.2.0.4
RAC (3 nodes).
The rough breakdown:
Granule size: 256M
select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 2269072
Variable Size 3.9460E+10
Database Buffers 8.8584E+10
Redo Buffers 227807232
cpu_count: 32
log_buffer: 220798976
public threads: 2 public (no private) 105M each
primary:sys@ohcops1> l
1 select
2 indx,
3 total_bufs_kcrfa,
4 strand_size_kcrfa,
5 index_kcrf_pvt_strand,
6 space_kcrf_pvt_strand
7 from
8* x$kcrfstrand
primary:sys@ohcops1> /
INDX TOTAL_BUFS_KCRFA STRAND_SIZE_KCRFA
INDEX_KCRF_PVT_STRAND SPACE_KCRF_PVT_STRAND
---------- ---------------- -----------------
--------------------- ---------------------
0 215624 110399488
0 0
1 215624 110399488
0 0
redo logs: 256M
archive logs: 40-43M
So if each public thread takes 105M, I have ~~46M left over.
Filling one thread and switching, as discussed in Jonathan's article,
should give me archive logs of ~105 M. Instead, I am getting an
archive log
of ~ the rump size in the redo (256-105-105=46M).
I checked the obvious (not a manual log switch, not
archive_lag_target).
I'll chime in if I find something interesting. Also wondering if
any obvious things I'm missing off the top.
Henry
===================================================================
