Role of Database Redo Log Files

Redo log files record every change the database must be able to replay. They do not store a full "before and after picture" of a row. Undo holds the before-image for rollback and flashback; redo holds change vectors that say how to reconstruct the new version of a block after a crash or restore. That distinction matters more than it might seem: it's the difference between "what the row used to look like" and "what happened to it," and only the second one is what a crash recovery actually needs to replay. Everything else in this lesson, groups, members, status, sizing, comes back to that one job: keeping a reliable, sequential record of change vectors that the instance can trust completely when it has to reconstruct recent history.

Every Oracle AI Database 26ai instance has at least two online redo log groups. The reason is circular reuse: while LGWR writes the current group, the other group can be archived (in ARCHIVELOG mode) and then reused. One group is not enough; if that file filled, the instance would have nowhere to write the next change.

1. A session changes data in the buffer cache.
2. The corresponding redo is copied into the redo log buffer.
3. On commit, log switch, or other trigger, LGWR (the only process that writes online redo) flushes that buffer to the current log group's members.
4. Later, DBWn writes the dirty data blocks to data files.

Commit is durable when the redo is on disk, not when the table's data file is updated. That is why redo exists as a separate, sequential write path: random data-file I/O can lag; recovery can still reconstruct committed work from the log. This ordering, redo first, data blocks later, is also why a database can survive losing power mid-write to a data file but cannot survive losing its only copy of the redo protecting an unwritten change.

A group is one logical log. A member is one physical file in that group. Oracle writes the same redo to every member of the current group. If you put both members on one disk and that disk dies, the group is gone. Multiplex members across disks, ASM failure groups, or controllers.

In RAC, each instance owns a thread of groups. Crash recovery on a surviving instance can read every thread.

Group status is the operational view you will use later:

• CURRENT, LGWR is writing here
• ACTIVE, no longer current, still needed for crash recovery
• INACTIVE, checkpoint has passed this group; it can be archived and reused

Loss of an inactive group is usually recoverable by clearing or dropping it. Loss of the current group is the hard case: crash recovery cannot finish, and you fall back to incomplete recovery and OPEN RESETLOGS if no multiplexed copy exists. Between those two extremes, an active group and an unarchived group each carry their own recovery procedure; the general rule when you've lost more than one group at once is to work the most difficult one first, current, then active, then unarchived, then inactive.

You don't have to guess at any of this; the status of every group is a query away.

SELECT group#, members, status, archived
FROM v$log;

GROUP#     MEMBERS               STATUS        ARCHIVED
------     -------               ---------     -----------
     1     2                     INACTIVE      YES
     2     2                     ACTIVE        NO
     3     2                     CURRENT       NO

That single query tells you which group LGWR is currently writing to, which groups still matter for crash recovery, and which ones have already been safely archived. If you need to know the actual file paths behind a given group, rather than just its status, V$LOGFILE is the companion view:

SELECT group#, status, member
FROM v$logfile;

A blank STATUS there means the member is fine; INVALID means Oracle can't currently access that particular file, which is exactly the first thing you'd check if a disk holding one member of a multiplexed group went offline.

How big to make your redo log files isn't just a storage question, it directly affects two other processes covered elsewhere in this course. Undersized logs force more frequent log switches, and more frequent log switches mean more frequent checkpoints, which pushes DBWn harder than it needs to be pushed. A common rule of thumb is to size your logs so a switch happens no more than about once every twenty minutes under normal load; if you'd rather have Oracle tell you a number instead of relying on a rule of thumb, the OPTIMAL_LOGFILE_SIZE column of V$INSTANCE_RECOVERY reports a sizing recommendation based on your current FAST_START_MTTR_TARGET setting, covered in this course's checkpoint lesson.

Redo log files aren't purely a recovery mechanism sitting idle until disaster strikes. Oracle LogMiner lets you query the contents of online or archived redo log files through an ordinary SQL interface, turning the redo stream into a readable source of historical information about what actually happened in the database and when. That's useful well beyond a recovery scenario: auditing who changed a specific row, reconstructing a sequence of events after an application bug, or simply confirming that a change you expected to see actually made it into the redo stream. It's a reminder that the redo log's job isn't only "protect the database," it's also "remember the database," at least for as long as the relevant logs stick around.

It's worth clearing up a distinction this course will return to later: redo logs and flashback logs are not the same mechanism, even though both live in the same neighborhood and both matter for recovery. Redo protects forward motion, replaying committed changes after a crash or restore. Flashback logs, one of several file types the Fast Recovery Area can hold alongside control file copies, archived redo, and RMAN backups, exist to let you rewind an entire database to an earlier point in time without a full restore. You'll cover Flashback Database properly later in this course; for now, the useful takeaway is that "protects the database's history" and "lets you undo history" are two different jobs, handled by two different kinds of log.

It's a reasonable question once you understand that redo is just a sequential stream: why bother with multiple groups and log switches at all, instead of one enormous continuous file? The answer comes back to archiving and reuse. A single, ever-growing file would have no natural boundary at which ARCn could safely hand a completed segment off to the archive destination while LGWR kept writing new redo elsewhere. Groups give the stream a seam. When one group fills and LGWR switches to the next, the group that just closed becomes a stable, finished unit, safe to archive, and eventually safe to overwrite once that archiving is done. Multiple groups are what let recording and archiving happen concurrently instead of one blocking the other.

One more distinction worth making explicit, since it's easy to assume everything in a pluggable database is scoped per PDB: online redo belongs to the CDB as a whole, not to any individual PDB. Every PDB inside a CDB shares the same redo threads and the same LGWR process, the way they share the same instance and SGA. A PDB contributes its own data files, but it does not get its own private redo stream. When you're troubleshooting redo or planning capacity for a multitenant database, you're always reasoning about the CDB's redo activity in aggregate, not any single PDB's slice of it.

If you create a database with DBCA in 26ai, you typically get three or more groups, often as Oracle-managed files in ASM (for example +DATA and +RECO), not c:\oracle8\database\log1orc1.ora. Those old names and a single ORACLE_HOME folder are from Oracle8-era defaults and should not be copied. Under ASM, online redo logs live in their own ONLINELOG subdirectory with names like +DATA/<db_unique_name>/ONLINELOG/group_3.263.873380343, a group number plus file number plus incarnation identifier, rather than anything you'd hand-name yourself.

Putting every member in one directory on one disk is still a single point of failure. Multiplex groups, run in ARCHIVELOG mode for any database that must survive media loss, and size logs so you do not switch so often that archiving or standby transport lags.

Online redo is also the stream Data Guard and Zero Data Loss Recovery Appliance consume. Protecting that stream, mirroring, archiving, and shipping it, is how 26ai turns "changes in memory" into recoverability.

The next lesson is how LGWR writes to the current group, when a log switch happens, and what Oracle does when a member or an entire group is missing.