Logwriter basics

• Find out if database is in archive mode
• Increase the size of log files dynamically
• Are my log files multiplexed
• Get size, filename and groups information about the log file
• Under what conditions does LGWR get trigged

 

• Find out if database is in archive mode

The LGWR writes to both the files in circular manner. When one file is filled, it writes to second one. When second is filled, it writes to first one after the changes recorded in it have been written to datafiles if database is not in archived mode. If the database is in archived mode, then LGWR writes to first file after the changes recorded in it have been written to data files and the files have been archived.

To check if database is in archive mode:

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle11gR2/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     451
Current log sequence           452

• Increase the size of log files

Create two additional groups with desired size.
alter database add logfile group 3 ('+LOG1/log_1_8') size 10000M 
alter database add logfile group 4 ('+LOG2/log_1_9') size 10000M

Find out the status of all groups and drop the old inactive group

 select group#,thread#,status from v$log ;
alter database drop logfile group 2

Switch the logfile so you can drop the old active group as well

alter system switch logfile ;
alter database drop logfile group 1 ;

• Get size, filename and groups information about the log file

 Find out the size of the log file
SQL> select group#,thread#,bytes/(1024*1024) , blocksize ,members from v$log ;

Find out the filenames and the group to which they belong:

  sql>select group#,member,status from v$logfile;

• Are my log files multiplexed

Multiplexing of log files (having multiple identical copies) is implemented by creating groups of redo log file. A group consist of a redo log file and its multiplexed copies. Each identical copy is the member of the group.


IIssue the following commnd to find out how many groups you have. The output shows I have two groups and each group has one member ...Hence multiplexing is enabled.
SQL> select group#,member from v$logfile ;

    GROUP#                                           MEMBER
--------------------------------------------------------------------------------
         1                                                      +TPCC1/log_1_1

         2                                                         +TPCC1/log_1_2

• Under what conditions does LGWR gets triggered

When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:

• Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
• Every 3 seconds; or
• When a DBWn process writes modified buffers to disk (checkpoint).