LOGFILE, MEMBER & ARCHIVELOG


Logfile (Online Redo Logfile)

  • Records every change (redo entry) made to the database — used for instance recovery and media recovery.
  • Written by the LGWR (Log Writer) background process.
  • Works in OPEN stage — mandatory for the database to function.
  • A database needs a minimum of 2 logfile groups to operate (circular writing).
  • LGWR writes to groups in a cyclic (round-robin) manner: Group1 → Group2 → Group3 → Group1 ...
  • A log switch happens when LGWR moves from one group to the next.
Logfile Contains
  • SCN (System Change Number)
  • Redo entries of DML & DDL changes
  • Group number & Sequence number
  • Thread number (for RAC databases)
Logfile Status (V$LOG)
  • CURRENT — group actively being written by LGWR
  • ACTIVE — needed for instance recovery, not yet archived
  • INACTIVE — not needed for instance recovery
  • UNUSED — newly created group, never used yet
Quick DBA Commands — Logfile
SQL
SELECT GROUP#, SEQUENCE#, MEMBERS, STATUS FROM V$LOG;
SELECT GROUP#, MEMBER FROM V$LOGFILE;

-- Add a new logfile group
ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/app/oracle/oradata/mac/redo04a.log',
 '/u01/app/oracle/oradata/mac/redo04b.log') SIZE 50M;

-- Drop a logfile group (must not be CURRENT/ACTIVE)
ALTER DATABASE DROP LOGFILE GROUP 4;

-- Force a log switch
ALTER SYSTEM SWITCH LOGFILE;

Member

  • A member is a physical copy of an online redo logfile that belongs to a group.
  • Multiplexing the redo logfile = keeping more than one member per group, ideally on different disks.
  • Minimum 1 member per group; 2 or more members recommended for safety.
  • All members of the same group are written simultaneously by LGWR and contain identical (mirrored) content.
  • If one member of a group is lost/corrupted, the database continues working using the surviving member(s).
  • Used for safety purpose — same concept as multiplexing the control file.
Quick DBA Commands — Member
SQL
SELECT GROUP#, MEMBER, STATUS FROM V$LOGFILE;

-- Add a member to an existing group
ALTER DATABASE ADD LOGFILE MEMBER
'/u01/app/oracle/fast_recovery_area/mac/redo01b.log' TO GROUP 1;

-- Drop a member (group must have more than 1 member)
ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/fast_recovery_area/mac/redo01b.log';

-- Rename / move a member (OS copy first, then DB level)
-- OS level: cp redo01b.log /new_location/redo01b.log
ALTER DATABASE RENAME FILE
'/u01/app/oracle/oradata/mac/redo01b.log'
TO '/new_location/redo01b.log';

Archivelog (Archive Log Mode)

  • An archived logfile is a saved copy of a filled online redo logfile, made before that logfile is reused.
  • Required for complete media recovery and point-in-time recovery.
  • Copied by the ARCn (Archiver) background process to the archive destination.
  • Two database modes: NOARCHIVELOG (default) and ARCHIVELOG.
  • Mode can only be changed at the MOUNT stage (database must be mounted, not open).
  • In NOARCHIVELOG mode, only instance recovery is possible — old redo data is lost when overwritten.
  • In ARCHIVELOG mode, both instance recovery and media recovery are possible — no data loss.
How to Enable Archivelog Mode
SQL
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
How to Disable Archivelog Mode (Back to NOARCHIVELOG)
SQL
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;
Quick DBA Commands — Archivelog
SQL
ARCHIVE LOG LIST;
SELECT LOG_MODE FROM V$DATABASE;
SHOW PARAMETER LOG_ARCHIVE_DEST;

-- Set archive destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/fast_recovery_area/mac/archive'
SCOPE=SPFILE;

-- Manually archive the current logfile
ALTER SYSTEM ARCHIVE LOG CURRENT;

-- Check archived log history
SELECT SEQUENCE#, NAME, COMPLETION_TIME FROM V$ARCHIVED_LOG;

Easy Memory

LOGFILE
|
+-- Group (min 2 groups)
|     +-- Member (min 1, safety = 2+)
|
+-- Status: CURRENT / ACTIVE / INACTIVE / UNUSED
|
+-- Log Switch --> ARCn copies filled logfile
      |
      +-- ARCHIVELOG mode --> saved as Archivelog (Media Recovery)
      +-- NOARCHIVELOG mode --> overwritten, lost (Instance Recovery only)