Mastering Oracle Database Failure Recovery: Flashback, Instance Restoration, and Best Practices

Learning Objectives

Identify the types of Oracle database errors.

Describe the steps involved in instance recovery.

Explain the role of checkpoints, redo log files, and archive log files.

Configure the Fast Recovery Area (FRA).

Enable ARCHIVELOG mode.

DBA Responsibilities

The DBA must keep the database available and performant by preventing routine errors, increasing Mean Time Between Failures (MTBF), providing hardware redundancy (RAC, Data Guard), reducing Mean Time To Recover (MTTR), and minimizing data loss through archive logs, Flashback, and Data Guard.

Categories of Failure

Statement‑level errors (e.g., syntax or constraint violations).

User‑process errors (session aborts).

Network errors (lost connections).

User errors (accidental DML or DDL).

Instance errors (crash or emergency shutdown).

Media errors (disk or OS‑level file loss).

Statement Failure

Illegal data insertion – validate in the application or correct the data.

Insufficient privileges – DBA must grant the required object or system privileges.

Space allocation failure – increase user quota or add tablespace.

Application logic errors – developers must fix the code.

User Process Failure

When a user session aborts, the instance automatically rolls back uncommitted changes and releases locks. The PMON background process monitors server processes and performs the necessary rollback.

Network Failure

Listener failure – configure a standby listener for failover.

NIC failure – use multiple network interfaces.

Connection loss – provide redundant network paths.

User Error

Accidental data modification – if uncommitted, no action; if committed, use Flashback Query or Flashback Table.

Accidental table drop – recover from the recycle bin or perform point‑in‑time recovery if the table was PURGE‑d.

Flashback Technology

Flashback allows viewing past data states and replaying changes within a time window, aiding error analysis and recovery.

Flashback Query Features

Flashback Query – view committed data at a previous point using the AS OF clause.

Flashback Versions Query – list all versions of committed rows.

Flashback Transaction Query – examine changes made by a specific transaction.

Flashback Recovery Options

Flashback Transaction Backout – revert a specific transaction and its dependents.

Flashback Table – restore one or more tables to a prior point without affecting other objects.

Flashback Drop – recover a dropped table from the recycle bin.

Flashback Database – revert the entire database to a previous point in time.

Instance Failure

Typical causes include power loss, hardware faults, critical background process crashes, or emergency shutdowns. The instance automatically performs recovery on startup.

Instance Recovery: Checkpoint (CKPT) Process

The CKPT background process triggers checkpoint events, causing DBWR to write dirty buffers to data files and updating checkpoint information in control files.

Checkpoints can be incremental or full.

During a log switch, CKPT also writes checkpoint data to data file headers.

Checkpoints ensure (a) dirty buffers are persisted to avoid data loss, (b) recovery time is reduced, and (c) all committed data is on disk at shutdown.

Instance Recovery: Redo Log Files and Log Writer (LGWR)

A transaction must be written to redo logs before COMMIT completes, guaranteeing durability.

Redo log files record every change in the database, grouped by transaction.

Multiplex redo logs across separate disks to protect against media loss.

The LGWR process writes redo entries to online log groups; it is triggered by transaction commit, every three seconds, before DBWR writes, and during a clean shutdown.

Instance Recovery Process

When the database reaches MOUNT, it checks SCN consistency between data file headers and the control file.

If inconsistent, redo log changes (both committed and uncommitted) are applied to bring data files up to date.

After synchronization, the database opens for user access.

UNDO data is then used to roll back uncommitted transactions, leaving only committed data.

Tuning Instance Recovery

Recovery applies changes from the last checkpoint to the end of the online logs.

Optimization focuses on minimizing the distance between the checkpoint SCN and the current log end (i.e., transaction volume).

Dirty buffers are flushed to disk at intervals because DBWR is slower than LGWR.

CKPT writes its position to the control file every 3 seconds, informing the database which redo entries are needed for recovery.

Set fast_start_mttr_target and size online log groups so that the checkpoint‑to‑log‑end distance does not exceed 90 % of the smallest log file.

Using the MTTR Advisor

Specify the desired instance recovery time via Enterprise Manager or SHOW PARAMETER fast_start_mttr_target in SQL*Plus.

The MTTR Advisor (EM → Advisor Central → MTTR Advisor) adjusts related parameters to meet the target.

Maximum allowed value is 3600 seconds.

Setting the target too low increases I/O load; setting it too high prolongs recovery.

Media Failure

Caused by disk or controller damage, or loss/corruption of data files, control files, or online log files. Recovery requires restoring from backup, possibly to a new location.

Configuring for Recoverability

Implement a regular backup strategy (RMAN or user‑managed backups).

Multiplex control files (minimum two copies on separate disks or ASM locations).

Multiplex online log groups (at least two members per group on different disks).

Enable ARCHIVELOG mode to retain copies of online logs before they are overwritten; verify with ARCHIVE LOG LIST.

Configuring the Fast Recovery Area (FRA)

The FRA stores archive logs, backups, flashback logs, and multiplexed control and online log files.

Place the FRA on a disk separate from data files, control files, and online logs.

Size the FRA to at least twice the total size of database files (data, control, online logs).

Oracle automatically manages FRA contents based on RMAN retention policies.

Set DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE accordingly.

Multiplexing Control Files

ALTER SYSTEM SET control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl','/u01/app/oracle/oradata/ORCL/control03.ctl' SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

cp /u01/app/oracle/oradata/ORCL/control01.ctl /u01/app/oracle/oradata/ORCL/control03.ctl

STARTUP;

Redo Log Files

Each online log group can contain one or more log files; multiple members provide redundancy but increase I/O.

Recommended minimum: two members per group, placed on separate disks (or separate ASM disks).

Losing a single member generates a warning; losing an entire group causes media errors and potential data loss.

Multiplexing Redo Log

SELECT * FROM v$logfile;

ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL/redo01A.log' TO GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/ORCL/redo04A.log','/u01/app/oracle/oradata/ORCL/redo04B.log') SIZE 50M;

Archive Log Files

Online logs are cyclic; before they are overwritten, they must be copied to archive logs for recoverability.

Configure archive logs in three steps:

Define a naming convention (e.g., %t_%s_%r.dbf).

Specify one or more archive destinations.

Enable ARCHIVELOG mode (ensure destinations exist).

If the FRA is used, the first two steps can be omitted because USE_DB_RECOVERY_FILE_DEST points to the archive location.

Archiver (ARCn) Process

Optional background process that starts automatically in ARCHIVELOG mode; view with ps -ef | grep arc.

Writes all database changes to archive logs; the number of processes is controlled by log_archive_max_processes.

Invoked on each log switch when in ARCHIVELOG mode; absent otherwise.

Archive Log Naming and Destinations

Set log_archive_format (e.g., %t_%s_%r.dbf) where %t =thread, %s =sequence, %r =resetlogs ID.

When FRA is enabled, USE_DB_RECOVERY_FILE_DEST defines the archive location; otherwise set log_archive_dest_1='location=/path'.

Oracle 11gR2 supports up to 31 different archive destinations via log_archive_dest_n.

Enabling ARCHIVELOG Mode

sqlplus / as sysdba

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ARCHIVE LOG LIST;

ALTER DATABASE OPEN;