DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00304 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00304: Requested INSTANCE_NUMBER is Busy — Causes, Fixes & Prevention

ORA-00304 is an Oracle RAC (Real Application Clusters) specific error that occurs when a database instance attempts to start using an INSTANCE_NUMBER that is already claimed or not yet properly released by another instance. This typically surfaces during instance restarts after a crash, or when RAC parameter files are misconfigured with duplicate instance numbers. Resolving this promptly is critical, as it prevents an entire RAC node from coming online.

Top 3 Causes

1. Stale Instance Lock After Abnormal Shutdown

When an Oracle RAC instance crashes unexpectedly (e.g., OS panic, process kill), the lock on the INSTANCE_NUMBER may not be properly released in the cluster registry. The Clusterware still considers that number "in use," blocking any restart attempt.

-- Check currently active instances across the cluster
SELECT inst_id, instance_number, instance_name, status, host_name
FROM gv$instance
ORDER BY inst_id;

-- Check for GES enqueue locks related to instance locking
SELECT resource_name1, grant_level, request_level, blocked
FROM v$ges_enqueue
WHERE resource_name1 LIKE '%BL%'
ORDER BY resource_name1;
Enter fullscreen mode Exit fullscreen mode

2. Duplicate INSTANCE_NUMBER in SPFILE/PFILE

If two or more instances share the same INSTANCE_NUMBER in the parameter file — often caused by copying a parameter file when adding a new node — Oracle will throw ORA-00304 at startup.

-- Check INSTANCE_NUMBER for all instances via SPFILE
SELECT name, value, sid
FROM v$spparameter
WHERE name = 'instance_number'
ORDER BY value;

-- Fix: assign a unique instance number to the conflicting instance
ALTER SYSTEM SET INSTANCE_NUMBER = 3
  SCOPE = SPFILE
  SID = 'ORCL3';
-- Restart the instance after this change
Enter fullscreen mode Exit fullscreen mode

3. Clusterware and Database Configuration Mismatch

After a Clusterware upgrade, node reconfiguration, or manual OCR edits, the metadata stored in the Oracle Cluster Registry (OCR) may conflict with the actual database parameter settings, causing ORA-00304 when the instance tries to register its number.

-- Verify parameter consistency across all live instances
SELECT inst_id, name, value
FROM gv$parameter
WHERE name IN ('instance_number', 'instance_name', 'cluster_database_instances')
ORDER BY name, inst_id;

-- Check SPFILE for any duplicate instance_number values
SELECT name, value, COUNT(*) AS duplicates
FROM v$spparameter
WHERE name = 'instance_number'
GROUP BY name, value
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Force stop the stuck instance (OS level):

-- Run via OS shell on the affected node
-- srvctl stop instance -d ORCL -i ORCL2 -o abort
-- If SRVCTL fails, use crsctl:
-- crsctl stop resource ora.orcl.db -f
Enter fullscreen mode Exit fullscreen mode

Step 2 — Correct the INSTANCE_NUMBER in SPFILE:

-- Connect to another running instance as SYSDBA
ALTER SYSTEM SET INSTANCE_NUMBER = 2
  SCOPE = SPFILE
  SID = 'ORCL2';

-- Verify the fix
SELECT name, value, sid
FROM v$spparameter
WHERE name = 'instance_number';
Enter fullscreen mode Exit fullscreen mode

Step 3 — Resync Clusterware configuration:

-- Confirm instance-to-node mapping (run on OS)
-- srvctl config database -d ORCL -a
-- srvctl modify instance -d ORCL -i ORCL2 -n node2

-- Check alert log location for further diagnostics
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Validate parameters before adding RAC nodes.
Always run a pre-deployment check to ensure no INSTANCE_NUMBER duplicates exist before starting a new instance.

-- Pre-deployment validation query
SELECT name, value, COUNT(*) AS cnt
FROM v$spparameter
WHERE name = 'instance_number'
GROUP BY name, value
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

2. Always use graceful shutdown procedures.
Enforce SHUTDOWN IMMEDIATE or srvctl stop instance as the standard shutdown method. Avoid OS-level kills unless absolutely necessary. After any crash, review Clusterware logs at $GRID_HOME/log/<hostname>/crsd/crsd.log before restarting.

Related Errors

  • ORA-00303 — Invalid INSTANCE_NUMBER value specified
  • ORA-00305 — Redo log thread inconsistency, often co-occurs during RAC reconfiguration
  • ORA-29701 — Unable to connect to Cluster Manager, indicating deeper Clusterware issues
  • CRS-5017 — Clusterware-level instance start failure, frequently logged alongside ORA-00304

📖 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)