Oracle Log File Switch

Oracle Log File Switch

More often changes are made to the data using DML statements, oracle keeps running log of these changes within “REDO” (Redo Logs). Every database consist of redo log files which are critical in recovery process, since they record changes to the data and provide the input to produce sets of archive logs which are used for point in time recovery. Whenever redo logs are filled to certain capacity and are ready for archival a log switch occurs that changes control from one redo log to the another. Redo Log Buffer – A circular buffer that stores all changes made in the database. It contains are transferred periodically from memory to the online redo log files on disk by the Log Writer (LGWR) background process. The contents of the redo log buffer are essential for instance recovery purposes. The size of the redo log buffer is determined by the LOG_BUFFER initialization parameter.

A single log switch entails stopping further acceptance of generated of redo, reading & writing to control files for redo log status and SCN, (System Change Number), information, flushing memory, and the closing & opening of individual redo log files.

There is no recorded amount of time where in a log switch takes place, not even from oracle. It is only experienced by users or applications and is recorded as a wait event at a session or system level. Switching too many redo logs is not advisable, the thumb rule is to switch a redo log every 25 to 35 minutes.  All you need to do is that to increase the redo log size to hold more redo’s and this will reduce the amount of log switching. Use the below SQL statement which shows how often log switching occur every hour.

SELECT to_char(first_time,’YYYY-MON-DD’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”,
/****************************************/
/* add more hours here to fill out the day */
/****************************************/
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “23”
FROM v$log_history
GROUP by to_char(first_time,’YYYY-MON-DD’);

To remove contention of writing redo and reading redo for archival purpose. It has been noticed that the placement of redo log groups on disk is not optimal for the system, in this case each group member  should be assigned to its own disk in order to avoid contention. Check the status of the current configuration, size, status and use of redo logs using:

SELECT group#, bytes, archived, status, first_change#, first_time FROM v$log ORDER BY first_change#;

and use of redo logs in this SQL stament FIRST_CHANGE# is important which shows the first SCN number that was used for a particular redo log group. Make sure that redo log groups are not on the same disk that are used next to each other in the redo log rotation, then finally determine redo log groups on the disk using:

 

SELECT group#, status, member FROM v$logfile ORDER BY group#;

Once you have pinpointed them use disk monitoring utility such as iostat during high redo activities to determine if you have any contention for read and writes. Another way  you could use is disk cache to speed I/O operations. During the log switch operation, the control file is used quite extensively. It has always been recommended that practitioners multiplex control files through Oracle.

In this way the log switch operation must keep in sync all the control files defined and thus overhead is increased by a factor of the number of control files multiplexed. Mirroring control files at the operating system level will eliminate the reads and writes for each control file. To determine the extra amount of I/O you are incurring for each log switch (and many other operations) use:

 

SELECT group#, bytes, archived, status, first_change#, first_time

 FROM v$log ORDER BY first_change#;

 

If you choose this route, please schedule a backup of your control file to trace often enough for recoverability. Alternatively you could use a disk cache to speed I/O operations. To check location of control file use:

 

SELECT name FROM v$controlfile;

As data is manipulated and altered within an Oracle database, time must be spent recording and archiving these changes through redo log generation. The time spent recording and archiving can be reduced if proper consideration is given to the number of log switches occurring, the size of the redo logs, how they are cyclically used, and taking a look at supporting structures such as the control file or init.ora settings. Remembering to check periodically and around major application changes will safeguard your database from wasting time and waiting for a log file switch.

SELECT name, value  FROM v$parameter  WHERE name = ‘archive_lag_target’;