DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00221 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00221: Error on Write to Control File — Causes, Fixes & Prevention

Oracle error ORA-00221 occurs when the database fails to write to one or more control files during normal operation. The control file is one of the most critical components of an Oracle database, storing metadata about its physical structure — including datafile locations, redo log files, and checkpoint information. When this error appears, immediate action is required to prevent database corruption or an unplanned outage.

Top 3 Causes

1. Disk Space Exhaustion

The most common cause is the filesystem hosting the control file running out of space. Archive logs, trace files, or growing datafiles on the same mount point can quickly consume available disk space.

-- Check control file locations
SELECT NAME, STATUS FROM V$CONTROLFILE;

-- Check recovery area usage
SELECT SPACE_LIMIT/1024/1024/1024   AS LIMIT_GB,
       SPACE_USED/1024/1024/1024    AS USED_GB,
       ROUND(SPACE_USED/SPACE_LIMIT*100, 2) AS USED_PCT
FROM V$RECOVERY_FILE_DEST;
Enter fullscreen mode Exit fullscreen mode

2. File Permission or Ownership Issues

After OS patches, security policy changes, or filesystem remounts, Oracle may lose write permission to the control file. This is especially common in NFS-mounted or shared storage environments.

-- Verify control file parameter
SHOW PARAMETER CONTROL_FILES;

-- Check file status
SELECT NAME, STATUS FROM V$CONTROLFILE;
Enter fullscreen mode Exit fullscreen mode 
# Fix ownership and permissions at OS level
chown oracle:oinstall /u01/oradata/ORCL/control01.ctl
chmod 640 /u01/oradata/ORCL/control01.ctl
Enter fullscreen mode Exit fullscreen mode

3. Storage or I/O Hardware Failure

Physical or logical failures on SAN, NAS, or local disk arrays can cause I/O errors that propagate to control file write failures. These will typically appear alongside hardware errors in the OS system logs and in the Oracle alert log.

-- Query recent alert log errors (Oracle 11g+)
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-00221%'
   OR MESSAGE_TEXT LIKE '%control file%'
ORDER BY ORIGINATING_TIMESTAMP DESC
FETCH FIRST 10 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Free up disk space using RMAN:

-- Delete archived logs older than 7 days
-- Run inside RMAN
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
Enter fullscreen mode Exit fullscreen mode

Step 2 — Recover from a multiplexed control file copy:

-- If one control file is damaged, copy a healthy one at OS level, then:
ALTER SYSTEM SET CONTROL_FILES=
  '/u01/oradata/ORCL/control01.ctl',
  '/u01/fra/ORCL/control02.ctl'
  SCOPE=SPFILE;

SHUTDOWN ABORT;
STARTUP;
Enter fullscreen mode Exit fullscreen mode

Step 3 — Backup control file after recovery:

-- Binary backup
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01_backup.ctl';

-- Trace (DDL script) backup
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
  AS '/backup/recreate_controlfile.sql';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always multiplex control files across separate disks.
Configure at least 3 copies of the control file on different physical disks or ASM disk groups. Enable RMAN autobackup to ensure a current backup is always available.

-- Enable RMAN controlfile autobackup
-- Run inside RMAN
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
  FOR DEVICE TYPE DISK TO '/backup/rman/%F';
Enter fullscreen mode Exit fullscreen mode

2. Monitor disk usage proactively.
Set up alerts at the 80% disk usage threshold for all filesystems hosting Oracle files. Use Oracle Enterprise Manager or a third-party tool (Zabbix, Nagios) to catch space issues before they cause ORA-00221.

-- Handy monitoring query for recovery area
SELECT NAME,
       ROUND(SPACE_USED/SPACE_LIMIT*100,2) AS USED_PCT
FROM V$RECOVERY_FILE_DEST
WHERE SPACE_USED/SPACE_LIMIT > 0.8;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00202 Control file I/O error — accompanies ORA-00221 with the specific filename
ORA-00210 Cannot open the specified control file
ORA-00227 Corrupt block detected in control file
ORA-00257 Archiver error — disk full condition that can indirectly trigger ORA-00221

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)