Preserving Oracle Redo: Multiplexing, Archiving and Monitoring

Redo is your durability guarantee. If you lose online or archived redo at the wrong moment, you may forfeit committed data or be unable to recover a restored backup. Preservation rests on two pillars:

• Multiplexing online redo (groups & members on separate devices/failure domains)
• Archiving and retaining redo (ARCHIVELOG + backups + sensible retention)

Goals: at least two members per group; members on different disks/paths (different LUNs, controllers, or ASM failure groups).

Create/resize groups:

-- Add three groups sized for your workload
ALTER DATABASE ADD LOGFILE GROUP 4 ('/redo/redo04a.log','/redo_mirror/redo04b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/redo/redo05a.log','/redo_mirror/redo05b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/redo/redo06a.log','/redo_mirror/redo06b.log') SIZE 2048M;

-- Add an extra member to an existing group
ALTER DATABASE ADD LOGFILE MEMBER '/redo_mirror/redo01b.log' TO GROUP 1;

-- Drop an unhealthy member (after adding a healthy replacement)
ALTER DATABASE DROP LOGFILE MEMBER '/redo/redo01a.log';

ASM deployments: Prefer a dedicated +REDO disk group with NORMAL/HIGH redundancy. Multiplexing via ASM mirroring covers media loss; many sites still add multiple members for defense-in-depth or operational flexibility.

• Avoid too-small logs (excessive log switches & checkpoints) and too-large logs (slow archiving & long crash recovery).
• Target a steady switch interval that fits backup/RPO needs (often on the order of 15–30 minutes during peak load-tune by measuring).
• Watch V$LOG_HISTORY to validate cadence and adjust SIZE accordingly.

SELECT sequence#, first_time, next_time
FROM   v$log_history
ORDER  BY first_time DESC
FETCH  FIRST 30 ROWS ONLY;

Turn on ARCHIVELOG so each full online log is persisted before reuse, then point archiving to the Fast Recovery Area (FRA):

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- FRA sizing and destination
ALTER SYSTEM SET db_recovery_file_dest_size = 200G SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest      = '/u02/app/oracle/fast_recovery_area' SCOPE=BOTH;

-- Archive to FRA
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;

Tip: FRA saturation halts archiving and can stall DML. Monitor usage and purge obsolete/expired logs with RMAN.

Back up archived logs frequently and keep them long enough to meet RPO/RTO.

-- RMAN retention & routine
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
RMAN> DELETE NOPROMPT OBSOLETE;

For DR, ship archived redo to a remote site (Data Guard) and verify gaps regularly.

-- Groups, status, sizes
SELECT l.group#, l.sequence#, l.bytes/1024/1024 AS size_mb,
       l.members, l.archived, l.status
FROM   v$log l
ORDER  BY l.group#;

-- Members and paths (watch for STATUS != VALID)
SELECT lf.group#, lf.member, lf.status
FROM   v$logfile lf
ORDER  BY lf.group#, lf.member;

-- Archive destinations & errors
SELECT dest_id, destination, status, error
FROM   v$archive_dest
ORDER  BY dest_id;

-- Gaps (Data Guard/standby scenarios)
SELECT * FROM v$archive_gap;

• Alerts: notify on invalid redo members, rapid switch bursts, FRA > 85% full, or archiver errors.
• Change control: log any redo topology change (sizes, paths, groups) and test recovery regularly.