Re: Archive Log Size

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