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
===================================================================
