Re: Archive Log Size

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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com>
>> wrote:
>>
>>> I liked that idea. Unfortunately,
>>>
>>> *physical standby*:sys_at_ohcopp2> select distinct bytes from v$log;
>>>
>>>
>>>
>>> BYTES
>>>
>>> ----------
>>>
>>> 268435456
>>>
>>>
>>>
>>> 1 row selected.
>>>
>>> *physical standby*:sys_at_ohcopp2> select distinct bytes from
>>> v$standby_log;
>>>
>>>
>>>
>>> BYTES
>>>
>>> ----------
>>>
>>> 268435456
>>>
>>>
>>>
>>> 1 row selected.
>>>
>>>
>>>
>>>
>>>
>>> *primary*:sys_at_ohcops1> select distinct bytes from v$log;
>>>
>>>
>>>
>>> BYTES
>>>
>>> ----------
>>>
>>> 268435456
>>>
>>>
>>>
>>> 1 row selected.
>>>
>>>
>>>
>>> On Tue, Mar 9, 2021 at 12:02 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> 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_at_gmail.com> 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_at_gmail.com>
>>>>> 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_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