Redo, Undo, Control Files and Archiving

37.1 Online Redo Logs

Records all changes for recovery. Organized into groups (≥ 2) with members (multiplexed copies on different disks).

LGWR writes them circularly. A log switch triggers a checkpoint and archiving.

Size redo logs for about 3–4 log switches per hour at peak load. Check v$log_history.

Statuses: CURRENT, ACTIVE, INACTIVE, UNUSED.

SELECT group#, thread#, sequence#, bytes/1024/1024 mb, members, status, archived FROM v$log;
SELECT group#, member FROM v$logfile;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u03/redo04a.log', '/u04/redo04b.log') SIZE 1G;
ALTER DATABASE ADD LOGFILE MEMBER '/u04/redo01b.log' TO GROUP 1;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
ALTER DATABASE DROP LOGFILE GROUP 4;

37.2 ARCHIVELOG Mode

Production databases should always run in ARCHIVELOG mode. It enables point-in-time recovery, online backups, and Data Guard.

ARCHIVE LOG LIST
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE = BOTH;
ALTER SYSTEM SET log_archive_format = '%t_%s_%r.arc' SCOPE = SPFILE;
SELECT name, sequence#, completion_time FROM v$archived_log ORDER BY completion_time DESC;

37.3 Undo

Used for: rollback, read consistency, Flashback, and instance recovery.

Automatic Undo Management: UNDO_MANAGEMENT=AUTO, UNDO_TABLESPACE, UNDO_RETENTION, RETENTION GUARANTEE.

Temporary undo (12c, TEMP_UNDO_ENABLED): undo for GTTs goes to TEMP.

Local undo per PDB (12.2, recommended).

SELECT * FROM v$undostat;
SELECT tablespace_name, status, SUM(bytes)/1024/1024 mb FROM dba_undo_extents GROUP BY tablespace_name, status;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

37.4 Control Files

A small binary file holding the DB name, datafile and redo log locations, checkpoint SCN, RMAN backup metadata, and archive log history.

Multiplex at least 2–3 copies on different disks.

SELECT name FROM v$controlfile;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/ctl.sql';
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control.bkp';
-- To multiplex: ALTER SYSTEM SET control_files=... SCOPE=SPFILE; shutdown; copy file; startup

37.5 Checkpoints and Instance Recovery

Checkpoint: DBWn writes dirty buffers. CKPT updates headers with the checkpoint SCN.

Instance recovery: roll forward (redo, cache recovery) → open → roll back uncommitted work (transaction recovery, done by SMON/parallel processes).

FAST_START_MTTR_TARGET controls recovery time. Check v$instance_recovery.

37.6 Fast Recovery Area (FRA)

A central location for archived logs, RMAN backups, flashback logs, and control file autobackups.

ALTER SYSTEM SET db_recovery_file_dest_size = 200G;
ALTER SYSTEM SET db_recovery_file_dest = '/u05/fra';
SELECT * FROM v$recovery_area_usage;
SELECT * FROM v$recovery_file_dest;

37.7 Supplemental Logging and LogMiner

Force logging (ALTER DATABASE FORCE LOGGING) for Data Guard. NOLOGGING operations.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
EXEC DBMS_LOGMNR.START_LOGMNR(STARTTIME => SYSDATE - 1/24, ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
SELECT scn, operation, sql_redo, sql_undo FROM v$logmnr_contents WHERE seg_name = 'EMPLOYEES';
EXEC DBMS_LOGMNR.END_LOGMNR;