Control Files, SCNs, Checkpoints, Datafile Headers, and Recovery Metadata

Learning outcomes

After a storage incident, an engineer still has datafiles and redo but no usable control file. Another proposes rebuilding it from memory because “the control file is just the file list.” In Oracle, the control file is critical CDB-level recovery metadata: database identity and structure, online redo history, checkpoint information, RESETLOGS/incarnation state, and reusable backup/recovery records.

1. The CDB has the control file; PDBs do not have separate ones

Control files must be available to mount/open the CDB. They record database name/DBID, datafile and redo identities, log/checkpoint history, RESETLOGS state, and backup/recovery records. Multiple physical copies can represent the same logical control file.

SHOW PARAMETER control_filesSELECT name,status,is_recovery_dest_file,block_size,file_size_blksFROM v$controlfile ORDER BY name;SELECT type,record_size,records_total,records_used,first_index,last_indexFROM v$controlfile_record_section ORDER BY type;

Multiplex copies across independent failure domains where feasible. Two files on the same failed volume are not independent protection.

2. SCNs order database changes

A System Change Number is Oracle's logical ordering value for changes and consistency positions. It appears in redo, transaction snapshots, control-file metadata, datafile headers, and recovery targets. An SCN is not itself a wall-clock timestamp.

SELECT dbid,name,checkpoint_change#,resetlogs_change#,resetlogs_time,       controlfile_type,controlfile_sequence#FROM v$database;

3. Checkpoints advance the crash-recovery starting position

Database Writer (DBWR) writes dirty buffers while checkpoint metadata advances. Checkpointing reduces the amount of redo that crash/instance recovery must process. It does not mean “write every buffer in cache immediately.”

SELECT checkpoint_change# FROM v$database;SELECT file#,checkpoint_change#,checkpoint_time,fuzzy,recoverFROM v$datafile_header ORDER BY file#;

A datafile can be FUZZY=YES during normal open operation because writes/checkpointing are in flight; that flag alone is not corruption evidence.

4. Control-file and datafile headers define recovery position

SELECT d.file#,d.name,h.checkpoint_change#,h.checkpoint_time,h.recover,h.fuzzyFROM v$datafile dJOIN v$datafile_header h ON h.file#=d.file#ORDER BY d.file#;

After a crash, Oracle uses checkpoint positions to determine the redo range needed for cache recovery. After restoring an older datafile, media recovery applies redo beyond that file's checkpoint to advance it toward the requested recovery point.

5. One checkpoint can illustrate the mechanism

SELECT checkpoint_change# FROM v$database;ALTER SYSTEM CHECKPOINT;SELECT checkpoint_change# FROM v$database;

Scheduling forced checkpoints as “cleanup” can increase DBWR write pressure and compete with foreground workload. Fast-start recovery controls exist to manage the recovery/write tradeoff deliberately.

6. Back up control-file metadata through supported recovery tools

rman target /RMAN> SHOW CONTROLFILE AUTOBACKUP;RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;RMAN> BACKUP CURRENT CONTROLFILE TAG 'SH14_CONTROLFILE';

Changing RMAN configuration is persistent policy; record the previous setting before altering it in a disposable lab. A textual BACKUP CONTROLFILE TO TRACE reconstruction script can be useful, but it does not preserve the same full binary control-file recovery record history.

7. Deliberately wrong: recreate a control file from a guessed list

CREATE CONTROLFILE is a recovery operation, not ordinary maintenance. Recreation can discard control-file recovery records, change creation metadata, misdescribe datafiles/redo, and require recovery or OPEN RESETLOGS. The safe repair is to restore a valid control-file backup or use a verified, rehearsed documented recreation only when necessary.

8. Mandatory read-only recovery metadata card

SELECT name,log_mode,checkpoint_change#,controlfile_type FROM v$database;SELECT name,status FROM v$controlfile ORDER BY name;SELECT group#,sequence#,bytes,members,status,archived FROM v$log ORDER BY group#;SELECT file#,checkpoint_change#,recover,fuzzy FROM v$datafile_header ORDER BY file#;

9. Production judgment

Keep current control-file copies on resilient independent storage and include control-file/autobackup in recovery drills. Do not edit/copy live files casually or reconstruct the control file from memory. Correlate database checkpoint, datafile header, redo sequence, and backup metadata during incidents.

No special option, pack, or COMPATIBLE change is required. Lesson 4 uses these structures during a controlled instance crash: redo rolls forward from the checkpoint position and undo removes uncommitted work.

Authoritative references

• Managing Control Files — control-file multiplexing, views, backup and recovery
• Database System Files — control files and multitenant scope
• V$DATABASE — checkpoint and RESETLOGS metadata
• V$DATAFILE_HEADER — datafile header recovery/checkpoint state
• Database Concepts — Instance Recovery — checkpoint and recovery relationship