Monday, October 29, 2012

3 Log File

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.

image

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.

image

After checkpoint completed, the File group 1 become inactive

image

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

image

Lest switch the log file again.
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.

image

Check the log file physical location ( V$LOGFILE)

select * from v$logfile

image

Add log file group

alter database add logfile group 4 ('/home/oracle/app/oracle/oradata/orcl/redo04.log') size 10M;

image

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;

image

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;

image

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.

image

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;

image

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).

 

Reference

3 comments:

  1. It is amazing that you share your knowledge with us.
    Emergency Dentist in Clapham Junction

    ReplyDelete
  2. International Watch Company, replica watches or IWC, was first introduced to the world in 1868 when an American Engineer, replica iwc portofino watches Florentine Ariosto Jones, decided to open his own factory in Switzerland. replica iwc pilot watchesIWC has earned its reputation as one of the world's finest manufacturers of Swiss luxury watches. IWC only manufactures automatic and manual timepieces which include features such as the chronograph and GMT dual time zone. IWC has definitely made its mark in the world of watches with its dedication to excellence in form and function. Using a range of supplies a large selection of IWC watches at the best sale prices online so buy yours today!

    ReplyDelete
  3. Err on the side of caution by assessing authorizations and why they are required so you know whether they have a legitimate explanation or they are dubious. 토토먹튀

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates