ORA-00235: Control File Fixed Table Inconsistent - Fix Corruption

ORA-00235: Control File Fixed Table Inconsistent

Error Overview

Error Text: ORA-00235: control file fixed table inconsistent due to concurrent update

The ORA-00235 error indicates that Oracle detected an inconsistency in the fixed (metadata) tables within the control file during a read operation. Oracle’s control file contains both variable-length records (for datafiles, redo logs, archived logs, and backup pieces) and fixed-length records that hold structural database metadata. When Oracle reads these fixed records and finds them inconsistent — typically because another process was updating them at the same moment — it raises ORA-00235.

In most cases this error is transient and caused by a legitimate concurrent update to the control file during an intensive backup or high-checkpoint-frequency workload. However, when it recurs frequently or appears during startup, it can indicate genuine control file corruption that requires immediate investigation.

Common Causes

1. Concurrent Control File Access During Backup

• RMAN backup or ALTER DATABASE BACKUP CONTROLFILE running concurrently with other operations that update control file records
• Hot backup mode (ALTER DATABASE BEGIN BACKUP) combined with heavy checkpoint activity
• Multiple RMAN channels simultaneously reading and writing control file metadata

2. Control File Block Corruption

• Storage hardware wrote bad data to the control file location (bit rot, failing disk)
• An unclean shutdown or system crash left the control file in a partially-written or half-updated state
• SAN or NAS storage returning stale reads due to cache coherency issues

3. Checkpoint Frequency Too High

• Very aggressive LOG_CHECKPOINT_INTERVAL or FAST_START_MTTR_TARGET settings causing rapid, frequent control file updates
• High DML workloads generating frequent LGWR writes and checkpoint completions
• Multiple foreground processes competing to update control file checkpoint records simultaneously

4. Control File on Shared Storage with Write Caching Issues

• Write-back cache on a storage array masking a failed write to the control file
• NFS storage with async mount option allowing Oracle to believe writes succeeded when they did not
• ASM rebalancing or disk addition activity during peak DML period

5. Oracle Bug or Memory Corruption

• An Oracle kernel bug causing incorrect control file writes (check MOS for relevant patches)
• SGA corruption affecting the control file buffer in memory
• Platform-specific issue with very large control files

Diagnostic Queries

Check the Alert Log for Error Context

-- Search the alert log for ORA-00235 and surrounding messages
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 7
  AND (message_text LIKE '%ORA-00235%'
       OR message_text LIKE '%control file%'
       OR message_text LIKE '%ORA-00236%'
       OR message_text LIKE '%ORA-00237%')
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

-- Count frequency by day — a single occurrence is likely transient,
-- multiple occurrences per day suggest a real problem
SELECT
  TRUNC(originating_timestamp) AS error_day,
  COUNT(*)                     AS occurrence_count
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 30
  AND message_text LIKE '%ORA-00235%'
GROUP BY TRUNC(originating_timestamp)
ORDER BY error_day DESC;

Verify Control File Health

-- Check all control file copies (run when database is MOUNT or OPEN)
SELECT
  name,
  status,
  is_recovery_dest_file,
  block_size,
  file_size_blks,
  file_size_blks * block_size AS file_size_bytes
FROM v$controlfile
ORDER BY name;

-- Check control file record section integrity
SELECT
  type,
  record_size,
  records_total,
  records_used,
  first_index,
  last_index,
  last_recid
FROM v$controlfile_record_section
ORDER BY type;

-- Verify control file sequence matches across all copies
-- (Inconsistencies here indicate a copy fell behind)
SELECT
  type,
  record_size,
  records_used
FROM v$controlfile_record_section
WHERE type = 'DATABASE';

Check for Active RMAN or Backup Operations

-- Identify active RMAN sessions that may be updating the control file
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status,
  s.event,
  s.sql_id,
  s.module,
  s.action
FROM v$session s
WHERE s.program LIKE '%rman%'
   OR s.program LIKE '%RMAN%'
   OR s.module  LIKE '%RMAN%'
ORDER BY s.sid;

-- Check for active backup jobs in RMAN catalog
SELECT
  session_key,
  session_recid,
  session_stamp,
  command_id,
  status,
  input_type,
  start_time,
  end_time
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 1
ORDER BY start_time DESC;

-- Check for RMAN jobs currently running
SELECT
  sid, serial#, context, sofar, totalwork,
  ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) AS pct_complete,
  message
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
  AND sofar < totalwork
ORDER BY elapsed_seconds DESC;

Check Checkpoint and LGWR Activity

-- Check checkpoint frequency (high CKPT activity correlates with heavy CF updates)
SELECT
  name,
  value,
  description
FROM v$sysstat
WHERE name IN (
  'background checkpoints completed',
  'background checkpoints started',
  'log switches (derived)',
  'redo writes',
  'redo write time'
)
ORDER BY name;

-- Check current FAST_START_MTTR_TARGET and related parameters
SELECT name, value
FROM v$parameter
WHERE name IN (
  'fast_start_mttr_target',
  'log_checkpoint_interval',
  'log_checkpoint_timeout',
  'db_recovery_file_dest'
)
ORDER BY name;

-- Review redo log switch frequency (high frequency = frequent CF updates)
SELECT
  TO_CHAR(first_time, 'DD-MON-YYYY HH24') AS hour,
  COUNT(*) AS log_switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'DD-MON-YYYY HH24')
ORDER BY hour DESC;

Examine the Trace File Generated by ORA-00235

-- ORA-00235 typically generates a trace file; find its location
SELECT name, value FROM v$diag_info
WHERE name IN ('Diag Trace', 'Default Trace File');

-- List recent trace files
-- (Run from OS: ls -lt $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/trace/*.trc | head -10)

Step-by-Step Resolution

1. Determine if the Error is Transient

If ORA-00235 appeared once during a heavy workload or backup and has not recurred, it is likely a transient concurrency issue:

-- Retry the failing operation (it should succeed on the next attempt)
-- For example, if the error occurred during a backup:
-- Simply re-run the RMAN backup command

-- Confirm current control file state is consistent
SELECT
  type,
  records_total,
  records_used
FROM v$controlfile_record_section
WHERE type IN ('DATAFILE', 'REDO LOG', 'ARCHIVED LOG', 'BACKUP SET')
ORDER BY type;

If the retry succeeds and the error does not recur, no further action is required beyond monitoring.

2. Copy a Known-Good Multiplexed Control File

If the error is recurring and suggests one control file copy is corrupted, replace the suspect copy with a good one:

-- Step 1: Identify which control file copy is problematic
-- (The alert log will typically name the specific file path)
SELECT name, status FROM v$controlfile;

# Step 2: Shut down the database
# SQL> SHUTDOWN IMMEDIATE;

# Step 3: Copy the known-good control file to replace the bad copy
cp /u02/oradata/orcl/control02.ctl /u01/oradata/orcl/control01.ctl

# Verify file sizes match (they should be identical)
ls -la /u01/oradata/orcl/control01.ctl
ls -la /u02/oradata/orcl/control02.ctl

-- Step 4: Restart the database
STARTUP;

-- Step 5: Verify no errors and all control files are consistent
SELECT name, status FROM v$controlfile;
SELECT type, records_total, records_used FROM v$controlfile_record_section ORDER BY type;

3. Restore Control File from RMAN Backup

If all control file copies are suspect or corrupted:

# Connect to RMAN
rman target /

# Restore the control file from the most recent autobackup
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;

# Recover the database to apply any changes since the backup
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

4. Recreate the Control File

As a last resort when no backups are available and all copies are corrupt:

-- Step 1: Generate a trace-based recreation script from ANY surviving copy
-- (If the database can be mounted, even briefly, use this to extract the structure)
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- Step 2: Find the trace file and extract the CREATE CONTROLFILE statement
-- Modify MAXDATAFILES, MAXLOGFILES as needed

-- Step 3: Shut down
SHUTDOWN ABORT;
STARTUP NOMOUNT;

-- Step 4: Execute the CREATE CONTROLFILE script
CREATE CONTROLFILE REUSE DATABASE "ORCL"
  NORESETLOGS ARCHIVELOG
  MAXLOGFILES 32
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 200M,
  GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 200M,
  GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 200M
DATAFILE
  '/u01/oradata/orcl/system01.dbf',
  '/u01/oradata/orcl/sysaux01.dbf',
  '/u01/oradata/orcl/undotbs01.dbf',
  '/u01/oradata/orcl/users01.dbf'
CHARACTER SET AL32UTF8;

-- Step 5: Recover the database
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

-- Step 6: Open with RESETLOGS
ALTER DATABASE OPEN RESETLOGS;

-- Step 7: Re-add tempfiles (not in control file)
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/orcl/temp01.dbf' SIZE 4G AUTOEXTEND ON;

5. Reduce Concurrency Pressure on the Control File

If the error is caused by heavy concurrent access rather than physical corruption:

-- Option A: Increase LOG_CHECKPOINT_INTERVAL to reduce checkpoint frequency
-- (Set to a large value to rely on FAST_START_MTTR_TARGET instead)
ALTER SYSTEM SET log_checkpoint_interval = 0 SCOPE=BOTH;

-- Option B: Set FAST_START_MTTR_TARGET to a reasonable value
-- (This controls the automatic checkpoint tuning algorithm)
ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=BOTH;  -- 5 minutes

-- Option C: If RMAN is causing conflicts, run backups during off-peak hours
-- Schedule RMAN jobs using DBMS_SCHEDULER to avoid peak DML windows

-- Verify changes
SHOW PARAMETER fast_start_mttr_target;
SHOW PARAMETER log_checkpoint_interval;

6. Verify Storage Layer Integrity

When ORA-00235 is caused by storage issues:

# Check the filesystem for errors (Linux)
dmesg | grep -i "error\|fault\|corrupt" | tail -50

# Check for I/O errors on the control file device
iostat -x 5 3

# Verify NFS mount options if control files are on NFS
# (Should use 'sync' not 'async' for Oracle data files)
mount | grep nfs

# On ASM, check diskgroup health
# SQL> SELECT name, state, total_mb, free_mb FROM v$asm_diskgroup;

Prevention Strategies

1. Multiplex Control Files Across Different Storage

-- Verify control file multiplexing
SELECT name, status FROM v$controlfile ORDER BY name;

-- Best practice: 3 copies on 3 different physical storage paths
-- /u01 = primary storage
-- /u02 = secondary storage (different disk/array)
-- FRA  = Flash Recovery Area (third location)

-- Check current CONTROL_FILES parameter
SHOW PARAMETER control_files;

2. Enable RMAN Control File Autobackup

rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
      TO '/u03/backup/%F';
RMAN> SHOW CONTROLFILE AUTOBACKUP;

3. Use Synchronous I/O for Control Files

-- Verify FILESYSTEMIO_OPTIONS (synchronous I/O improves integrity)
SELECT name, value FROM v$parameter
WHERE name IN ('filesystemio_options', 'disk_asynch_io');

-- For maximum reliability on non-ASM storage:
-- Set filesystemio_options = SETALL in the spfile (requires restart)
-- This enables direct I/O and asynchronous I/O optimally

4. Schedule RMAN Backups During Low-Activity Windows

-- Create a DBMS_SCHEDULER job that runs RMAN during off-peak hours
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'NIGHTLY_RMAN_BACKUP',
    job_type        => 'EXECUTABLE',
    job_action      => '/u01/scripts/rman_backup.sh',
    repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Nightly RMAN backup at 2AM to reduce CF contention'
  );
END;
/

5. Monitor Control File Size and Record Usage

-- Alert when control file record sections are heavily used
SELECT
  type,
  records_total,
  records_used,
  ROUND(records_used / NULLIF(records_total, 0) * 100, 1) AS pct_full
FROM v$controlfile_record_section
WHERE records_total > 0
  AND ROUND(records_used / NULLIF(records_total, 0) * 100, 1) > 70
ORDER BY pct_full DESC;

Diagnostic Scripts

These Oracle Day by Day scripts can assist with control file and storage health monitoring:

• health.sql — Full database health check including storage and control file status
• db.sql — Database instance information and structural overview

Related Errors

• ORA-00205 - Error in identifying control file (control file missing at startup)
• ORA-01578 - Oracle data block corrupted
• ORA-00257 - Archiver error (can leave control file in inconsistent state)
• ORA-00600 - Internal error (may accompany control file corruption)

Emergency Response

Quick Fixes

1. If the error is transient — retry the failing operation

   -- Simply re-run the backup or operation that triggered ORA-00235
   -- A single occurrence during a backup is almost always harmless

2. Copy a good multiplexed control file over the bad one

   # Shut down first
   # SQL> SHUTDOWN IMMEDIATE;
   cp /u02/oradata/orcl/control02.ctl /u01/oradata/orcl/control01.ctl
   # Then restart: SQL> STARTUP;

3. Restore from RMAN if all copies are bad

   rman target /
   RMAN> STARTUP NOMOUNT;
   RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
   RMAN> ALTER DATABASE MOUNT;
   RMAN> RECOVER DATABASE;
   RMAN> ALTER DATABASE OPEN RESETLOGS;

Post-Resolution Cleanup

-- Confirm control file is consistent
SELECT type, records_total, records_used
FROM v$controlfile_record_section
ORDER BY type;

-- Verify all datafiles are accounted for and online
SELECT file#, name, status FROM v$datafile
WHERE status NOT IN ('SYSTEM', 'ONLINE')
ORDER BY file#;

-- Take a fresh RMAN backup immediately
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- Verify RMAN autobackup is enabled
-- RMAN> SHOW CONTROLFILE AUTOBACKUP;

-- Generate a fresh trace-based text backup of the control file
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';