Re: Archive Log Size

OK, so sending to standby using LGWR, and in MAXIMUM PERFORMANCE mode with real time apply enabled (which seems OK), as does ARCH to FRA.

primary:sys_at_ohcops2> l

  1 select DEST_ID, DEST_NAME, STATUS, TYPE, DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE   2 from v$archive_dest_status

  3* where dest_id in (1,2)

primary:sys_at_ohcops2> /

   DEST_ID DEST_NAME            STATUS    TYPE        DATABASE_MODE
RECOVERY_MODE           PROTECTION_MODE

---------- -------------------- --------- ----------- ---------------
----------------------- --------------------

         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL
OPEN
IDLE                                             MAXIMUM PERFORMANCE

         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL    OPEN_READ-ONLY

On Tue, Mar 9, 2021 at 11:35 AM 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