Instance Recovery, Roll Forward/Roll Back, Fast-Start Recovery, and MTTR Tuning

Learning outcomes

A disposable ServiceHub database loses power after one transaction commits and another modifies a row without committing. With files intact, this is an instance/crash recovery problem—not automatically a backup restore. Oracle rolls forward redo from the checkpoint position, reconstructs undo as needed, then rolls back transactions without commit records.

1. Commit does not require every changed data block to be written

Committed durability depends on required redo reaching durable online redo. Dirty table/index/undo blocks can remain in the buffer cache after commit. If the instance stops abruptly, instance recovery applies redo from the checkpoint position toward the end of the redo thread.

2. Roll forward can include uncommitted changes

Redo contains changes from transactions that may not have committed. Cache recovery can therefore reapply both committed and uncommitted changes. Because redo also reconstructs the undo needed for permanent-object changes, transaction recovery can then roll back work without a commit record.

3. Fast-start recovery is a checkpoint/I/O tradeoff

FAST_START_MTTR_TARGET is an instance-level target in seconds for expected crash recovery. In current 26ai it defaults to 0, is dynamically modifiable with ALTER SYSTEM, is not PDB-modifiable, and accepts 0–3600. Lower targets can increase incremental DBWR checkpoint activity.

SELECT name,value,isdefault,issys_modifiable,ispdb_modifiableFROM v$parameterWHERE name IN ('fast_start_mttr_target','log_checkpoint_interval','log_checkpoint_timeout')ORDER BY name;SELECT target_mttr,estimated_mttr,recovery_estimated_ios,       actual_redo_blks,target_redo_blks,writes_mttrFROM v$instance_recovery;

These are recovery/checkpoint estimates—not an end-to-end application restart SLA. Storage, instance startup, PDB/services, transaction cleanup, listener/pool reconnection, and application health are additional steps.

4. Controlled abort/restart lab

SHOW CON_NAME-- FREEPDB1BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_recovery_case PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_recovery_case(  marker_id NUMBER PRIMARY KEY,  marker_text VARCHAR2(80) NOT NULL);INSERT INTO servicehub_recovery_case VALUES(1,'baseline committed');COMMIT;

5. Session A: create uncommitted redo

INSERT INTO servicehub_recovery_caseVALUES(3,'uncommitted before abort');SELECT marker_id,marker_textFROM servicehub_recovery_case ORDER BY marker_id;-- Do not COMMIT row 3. Keep this session open.

The uncommitted transaction has generated redo but has no commit record.

6. Session C: commit later work so LGWR flushes through earlier redo

INSERT INTO servicehub_recovery_caseVALUES(2,'committed before abort');COMMIT;

The later commit forces LGWR durability through its commit position, which includes earlier redo records in the stream. This makes the uncommitted row's redo more likely to participate visibly in roll-forward before transaction recovery removes it.

7. SYSDBA session: abort and restart

SELECT checkpoint_change# FROM v$database;SELECT target_mttr,estimated_mttr,actual_redo_blks,target_redo_blksFROM v$instance_recovery;SHUTDOWN ABORT;STARTUP;SHOW PDBS-- If FREEPDB1 is MOUNTED rather than already open by saved state:-- ALTER PLUGGABLE DATABASE FREEPDB1 OPEN;

SHUTDOWN ABORT skips the normal clean shutdown path. Startup performs instance recovery when required.

8. Verify transaction semantics after recovery

SELECT marker_id,marker_textFROM servicehub_recovery_caseORDER BY marker_id;-- Expected logical result:-- 1 baseline committed-- 2 committed before abort-- row 3 is absent because it was never committed.

This demonstrates instance recovery with intact system files. It does not prove media recovery when a datafile, redo member, or control file is lost.

9. Observe ADR/alert recovery evidence

SELECT name,valueFROM v$diag_infoWHERE name IN ('ADR Home','Diag Trace','Diag Alert');

adrci exec="show alert -tail 120 -term"

Look for startup and recovery messages around the abort/restart timestamp. Exact wording varies; correlate alert evidence with database state rather than building brittle automation around one line of text.

10. Deliberately wrong: FAST_START_MTTR_TARGET=1 means a one-second outage

An unrealistically low target can increase DBWR writes and can still be constrained by system capabilities. Oracle's effective target/estimate can differ from the requested extreme. It controls crash-recovery/checkpoint behavior, not listener startup, PDB service state, connection-pool recovery, or business availability.

11. Cleanup and production judgment

SHOW CON_NAME-- FREEPDB1DROP TABLE servicehub_recovery_case PURGE;

Use clean shutdown for planned maintenance. Use abort for emergencies or controlled recovery testing. Instance recovery protects transaction durability when system files remain usable; media recovery is a different procedure involving backups and redo.

No pack or COMPATIBLE change is required. Lesson 5 turns the same mechanisms into a practical diagnostic workflow for commit latency, log switches, archive backlog, checkpoint incomplete waits, and recovery risk.

Authoritative references

• Database Concepts — Instance Recovery — roll forward/rollback and checkpoint position
• FAST_START_MTTR_TARGET — parameter scope/default/range
• V$INSTANCE_RECOVERY — recovery/checkpoint estimates
• Database Administrator’s Guide — startup/shutdown administration
• Automatic Diagnostic Repository — alert/ADR evidence