Monday, October 29, 2012

0 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

0 comments:

Post a Comment

 

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