Re: Archive Log Size

Current parameter settings (same on all nodes) are:

log_archive_dest_1                   LOCATION=USE_DB_RECOVERY_FILE_DEST

log_archive_dest_2                   SERVICE=xxxx LGWR ASYNC

log_archive_format                   ARC%S_%R.%T

> 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_at_gmail.com>
> 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_at_gmail.com> 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_at_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_at_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_at_gmail.com>
>>> 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_at_gmail.com> 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_at_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_at_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
>>>>> ===================================================================
>>>>>
>>>>>

--
http://www.freelists.org/webpage/oracle-l