Every database would have at least 2 log file groups.
- Noarchivelog mode is similar to ‘truncate log at check point” in Sybase or “Simple recovery mode” in MSSQL
- archivelog mode is similar to “Full recovery mode” in MSSQL
When the LGWR write the log buffer to the log file
- Every 3 secs , the LGWR will check the remaining space in the log buffer. if the free space is less than 1/3, the LGWR would write the log buffer to the online redo log file.The “1/3” threshold is control by _LOG_IO_SIZE
- When log buffer has more than 1 MB data
- Online redo log switch
- User issue commit command
Log file status
- current: The Log file is currently used by LGWR.
- active: Check point is ongoing but not yet completed.
- inactive: Checkpoint is completed.
- cleaning: The logfile content is being clean.
- unused: Never used before or clean completed.
When the does the log switch happen?
- Current log file full
- Alter system switch logfile
select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE# from v$log
Below query shows 3 file group. The current log group is 1.
alter system switch logfile;
select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE# from v$log;
After log switch, the File group become current and the sequence number +1. The File group 1 become active as it is undergoing check point process.
After checkpoint completed, the File group 1 become inactive
Clean the logfile group 1. Usually, we only need to clear the log file if the logfile is corrupt.
alter database clear logfile group 1;
Log group 1 become unused and reset the sequence to 0
alter system switch logfile;
select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE# from v$log
Becane the logfile group 1 has the smallest sequence number ( 0 ), therefore the log switch would switch to filegroup 1 instead of 3.
Check the log file physical location ( V$LOGFILE)
select * from v$logfile
Add log file group
alter database add logfile group 4 ('/home/oracle/app/oracle/oradata/orcl/redo04.log') size 10M;
Remove log file group
Prerequisites before removing the log file group
- database has at least 2 logfile group after remove.
- Only status is inactive or unused can be removed.
- If db is in the archived mode, the ARC column must be YES.
alter database drop logfile group 3;
Even the logfile group has already been drop but the log file still exist on the file system. To reclaim the disk space, we have to manually delete the file from OS file system ( rm or delete).
Add log file member
The new log file is same size as other log file in the same group. When oracle instance write to the file group, it would write into both file group member. Ideally, the two different log file should be in the 2 different file system/disk to ensure the availability.
alter database add logfile member '/home/oracle/app/oracle/oradata/orcl/redo04b.log' to group 4;
Remove log file member
Prerequisites before removing the log file group
- The file group member can not be the last one in the file group..
- Only status is inactive or unused can be removed.
- If db is in the archived mode, the ARC column must be YES.
Below demo shows if we try to drop the last file member in the file group, we would get ORA-00361 error.
col member format a50
select GROUP#,member from V$LOGFILE;
select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE#,BYTES from v$log;
alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04b.log';
select GROUP#,member from V$LOGFILE;
select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE#,BYTES from v$log;
Even the logfile member has already been drop but the log file still exist on the file system. To reclaim the disk space, we have to manually delete the file from OS file system ( rm or delete).