Re: Archive Log Size

Wow. I just reread my last response and it reads just like what it was, free form thoughts. Let me try and clarify what I meant.

Initially, Jonathan's supposition that somehow the MTTR setting gets the standby to force these short log switches seems reasonable. However, upon further thought, I have some questions. First, I typically associate fast_start_mttr_target with checkpointing, and that isn't a standby thing. Second, the Standby gets its data from LGWR (the creator for dest_id=2), so the data should be in the SRL without a log switch

This also got me wondering if LNS (LGWR) falling behind in writing from the log buffer to the standby SRL might play a role. So I looked at x$logbuf_readhist, but the HITRATE on all 3 nodes was 98%, so it looks like no backlog there.

  1 select indx, inst_id, bufsize, rdmemblks, rddiskblks, hitrate, bufinfo   2* from x$logbuf_readhist
primary:sys_at_ohcops1> /

      INDX    INST_ID BUFSIZE              RDMEMBLKS
 RDDISKBLKS                  HITRATE BUFINFO
---------- ---------- -------------------- ------------------------
------------------------ ---------- --------------------
         0          1 107812K              53747612
1869757119                        2
         1          1 129374K              361691660
 1561813071                       18
         2          1 150937K              727333789
 1196170942                       37
         3          1 172499K              1303643599
619861132                        67
         4          1 194062K              1834188083
89316648                         95 TARGET-90
         5          1 215624K              1896707348
26797383                         98 CURRENT
         6          1 237186K              1897284975
26219756                         98
         7          1 258749K              1897284975
26219756                         98
         8          1 280311K              1897284975
26219756                         98
         9          1 301874K              1897284975
26219756                         98
        10          1 323436K              1897284975
26219756                         98
        11          1 344998K              1897284975
26219756                         98
        12          1 366561K              1897284975
26219756                         98
        13          1 388123K              1897284975
26219756                         98
        14          1 409686K              1897284975
26219756                         98
        15          1 431248K              1897284975
26219756                         98

16 rows selected.

I'm back to wondering if there is a way to distinguish between a local vs. standby effect here.

Henry

On Thu, Mar 11, 2021 at 10:43 AM Henry Poras <henry.poras_at_gmail.com> wrote:

> Your supposition makes sense.I associate fast_start_mttr with
> checkpointing, which isn't happening on the standby. Just reading directly
> from the SRL. However, on the other hand, it would still be strange for
> this to be the cause of the small archive. We also know from earlier that
> the Standby (log_archive_dest_2) is getting its data from LGWR so the log
> switches shouldn't be necessary to obtain the same mttr on the standby. I
> also tend to get confused by some of the interrelations between
> fsat_start_mttr_target, archive_lag_target, ...
>
> If it is mttr related, I would guess that doubling my
> fast_start_mttr_target should double the size of my archive logs (seeing
> that the current archive sizing is pretty consistent). Not sure if I'll be
> able to do that, but it's worth a try. I think it would be pretty harmless.
>
> Henry
>
>
> On Thu, Mar 11, 2021 at 10:07 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> I wonder if the MTTR gets reflected by something in the Standby.
>> If you're mean time to recover has to be 15 seconds then maybe Oracle
>> does something to ensure that the standby could recover inside the MTTR as
>> well - and archiving log files prematurely might be a mechanism to allow
>> that to happen.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Wed, 10 Mar 2021 at 15:29, Henry Poras <henry.poras_at_gmail.com> wrote:
>>
>>> fast_start_mttr_target integer 15
>>>
>>> *gv$instance_recovery*
>>> INST_ID : 1
>>> RECOVERY_ESTIMATED_IOS : 18594
>>> ACTUAL_REDO_BLKS : 369677
>>> TARGET_REDO_BLKS : 1002837
>>> LOG_FILE_SIZE_REDO_BLKS : 3821985
>>> LOG_CHKPT_TIMEOUT_REDO_BLKS : 1002837
>>> LOG_CHKPT_INTERVAL_REDO_BLKS :
>>> FAST_START_IO_TARGET_REDO_BLKS :
>>> TARGET_MTTR : 15
>>> ESTIMATED_MTTR : 12
>>> CKPT_BLOCK_WRITES : 91402225
>>> OPTIMAL_LOGFILE_SIZE : 457
>>> ESTD_CLUSTER_AVAILABLE_TIME : 3
>>> WRITES_MTTR : 211343278
>>> WRITES_LOGFILE_SIZE : 0
>>> WRITES_LOG_CHECKPOINT_SETTINGS : 0
>>> WRITES_OTHER_SETTINGS : 0
>>> WRITES_AUTOTUNE : 24832295
>>> WRITES_FULL_THREAD_CKPT : 97904
>>>
>>> -----------------
>>>
>>> INST_ID : 2
>>> RECOVERY_ESTIMATED_IOS : 14247
>>> ACTUAL_REDO_BLKS : 268501
>>> TARGET_REDO_BLKS : 1047425
>>> LOG_FILE_SIZE_REDO_BLKS : 3821985
>>> LOG_CHKPT_TIMEOUT_REDO_BLKS : 1047425
>>> LOG_CHKPT_INTERVAL_REDO_BLKS :
>>> FAST_START_IO_TARGET_REDO_BLKS :
>>> TARGET_MTTR : 15
>>> ESTIMATED_MTTR : 7
>>> CKPT_BLOCK_WRITES : 8211142
>>> OPTIMAL_LOGFILE_SIZE : 596
>>> ESTD_CLUSTER_AVAILABLE_TIME : 3
>>> WRITES_MTTR : 6818539
>>> WRITES_LOGFILE_SIZE : 0
>>> WRITES_LOG_CHECKPOINT_SETTINGS : 0
>>> WRITES_OTHER_SETTINGS : 0
>>> WRITES_AUTOTUNE : 37153280
>>> WRITES_FULL_THREAD_CKPT : 134238
>>>
>>> -----------------
>>>
>>> INST_ID : 3
>>> RECOVERY_ESTIMATED_IOS : 7800
>>> ACTUAL_REDO_BLKS : 262743
>>> TARGET_REDO_BLKS : 1035890
>>> LOG_FILE_SIZE_REDO_BLKS : 3821985
>>> LOG_CHKPT_TIMEOUT_REDO_BLKS : 1035890
>>> LOG_CHKPT_INTERVAL_REDO_BLKS :
>>> FAST_START_IO_TARGET_REDO_BLKS :
>>> TARGET_MTTR : 15
>>> ESTIMATED_MTTR : 14
>>> CKPT_BLOCK_WRITES : 6467159
>>> OPTIMAL_LOGFILE_SIZE : 285
>>> ESTD_CLUSTER_AVAILABLE_TIME : 8
>>> WRITES_MTTR : 18266031
>>> WRITES_LOGFILE_SIZE : 0
>>> WRITES_LOG_CHECKPOINT_SETTINGS : 0
>>> WRITES_OTHER_SETTINGS : 0
>>> WRITES_AUTOTUNE : 16983159
>>> WRITES_FULL_THREAD_CKPT : 81827
>>>
>>> -----------------
>>>
>>> That's a table I almost never look at. I need to go back and review it a
>>> bit.
>>> Henry
>>>
>>> On Wed, Mar 10, 2021 at 4:59 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>> I don't think fast_start_mttr_target has been mentioned explicitly yet,
>>>> nor (g)v$instance_recovery.
>>>> Any information there.
>>>>
>>>> Regards
>>>> Jonathan Lewis
>>>>
>>>>
>>>> On Tue, 9 Mar 2021 at 22:34, Henry Poras <henry.poras_at_gmail.com> wrote:
>>>>
>>>>> Jonathan,
>>>>> According to the docs, "A 0 value disables the time-based thread
>>>>> advance feature" so I am going under the assumption that 900 means 900.
>>>>> Of course, sometimes docs lie.
>>>>>
>>>>> Tim, what you suggest makes sense, but it doesn't match up with what I
>>>>> am seeing in this environment. There is some data in the first few entries
>>>>> in this thread. I can summarize over a larger time frame if you like.
>>>>> Basically, each thread is switching every couple of minutes or less, and
>>>>> the archive logs are consistently 40-43M (redo logs are 256M).
>>>>>
>>>>> Henry
>>>>>
>>>>>

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