Oracle : Redolog Files

Share via:

Post Views: 0

Dear Readers,

In this article, we will see the following Oracle : Redolog Files.

Redo log Files are Recovery options whenever Database crashes or Down.
LGWR will take care of redo content that needs to be written to redolog files, Based on SCN a record can be identified from redo log files.
Redo records can also be written to a redo log file before the corresponding transaction is committed.
If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed.
If necessary, the database can roll back these changes. Database needs minimum of 2 redo log files to handle redo content. User will get acknowledgement from LGWR.

Scenarios where LGWR writes data to redo log files :-
👉 1/3 of log buffer filled
👉 Data reaches 1mb
👉 User Commits
👉 For every 3sec.
Sessions committing wait for LGWR which is busy.

SELECT * FROM V$LOG;

GROUP# THREAD#   SEQ   BYTES  MEMBERS  ARC STATUS     FIRST_CHANGE#
------ ------- ----- -------  -------  --- ---------  -------------
     1       1 10605 1048576        1  YES ACTIVE          11515628
     2       1 10606 1048576        1  NO  CURRENT         11517595
     3       1 10603 1048576        1  YES INACTIVE        11511666
     4       1 10604 1048576        1  YES INACTIVE        11513647

SELECT * FROM V$LOG;

GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE#

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

1 1 10605 1048576 1 YES ACTIVE 11515628

2 1 10606 1048576 1 NO CURRENT 11517595

3 1 10603 1048576 1 YES INACTIVE 11511666

4 1 10604 1048576 1 YES INACTIVE 11513647

Select * from v$seststat where name='redo size';

1	Select * from v$seststat where name='redo size';

👉 Log Buffer Space
👉 Log File SYNC
👉 Log File Switch(Checkpoint Incomplete)
👉 Log File Switch(Archiving Needed)
Depends on the redo generation for 1 hour(depends on environment) better keep redo log file size to get rid of to many switch issues.

Log Buffer Space :-
Space not available in log buffer of SGA because all the buffers are active(waiting for LGWR to flush the data which are written to log files)
Solution :-
👉 Increase log_buffer parameter in init.ora
Above 3M(1/3 of size fills leads to LGWR writes) is not preferable
👉 Increase disk IO for redo
Reading from Buffer is faster coming to writing to the disk by LGWR
Faster disk is preferable
Instead of common disk better use dedicated disk for Logfiles
Raw File System(ASM) leads better performance.

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS