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)