Useful shortcuts for vi editor

Tuesday, 11 July 2017

log_buffer, log_checkpoint_timeout and log_checkpoint_interval parameters

Purpose
We are able to tune CKPT process with these paramaters and they should be configured together.

Usage
e.g.
Check current parameter:
SQL> show parameter log

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------------
log_buffer                           integer     5361664
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1200

Check log file size of one group
SQL> select group#, bytes, blocksize, members from v$log;

    GROUP#      BYTES  BLOCKSIZE    MEMBERS
---------- ---------- ---------- ----------
         1   52428800        512          2
         2   52428800        512          2

Log file size -> 52428800 bytes -> 50 MB
Log buffer should be -> 50 MB * 3 =  150 MB -> 157286400
Log checkpoint timeout could be -> 600 -> 10 minutes
Log checkpoint interval could be -> 0 or 102400 (102400 * 512(OS Block size) -> 52428800 bytes -> 50 MB is equal to log file size)

Apply the setting:
init.ora file:
*.log_buffer=157286400
*.log_checkpoint_interval=102400
*.log_checkpoint_timeout=600

Then restart the instance to perform.

Check lastly:
SQL> show parameter log
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
log_buffer                           integer     157286400
log_checkpoint_interval              integer     102400
log_checkpoint_timeout               integer     600

Deep notes
Log group members should be at least 2, same size, same amount and located in different paths to take backup. (log members are exactly same, 1-> 1)
Log checkpoint timeout parameter means that at most 600 seconds later, dirty blocks are written to database files from SGA.
Log checkpoint interval parameter means that after active log file size exceeds to 50 MB, CKPT is triggered for DBWR. If this parameter is "0", Oracle decides CKPT running time automatically.