INSTANCE_NUMBER - Set Oracle RAC Instance Identity

INSTANCE_NUMBER

Overview

INSTANCE_NUMBER assigns a unique numeric identity to each instance in an Oracle RAC cluster. This number determines which undo tablespace and redo thread an instance uses, influences object allocation for reverse-key indexes and partitions, and serves as the instance’s identifier in cluster-wide operations such as GCS (Global Cache Service) resource mastering, GES (Global Enqueue Service) affinity, and instance recovery. Each instance in a RAC cluster must have a distinct value between 1 and CLUSTER_DATABASE_INSTANCES.

When CLUSTER_DATABASE=FALSE (single instance), INSTANCE_NUMBER defaults to 1 and has no operational significance. In a RAC environment, incorrect or duplicate instance numbers will prevent the cluster from starting properly. Instance numbers also map to default undo tablespace names (typically UNDOTBS1, UNDOTBS2, etc.) and to redo thread numbers, making this parameter a foundational identity setting for every RAC instance.

Parameter Type: Static (requires instance restart to change) Default Value: 1 Valid Range: 1 to value of CLUSTER_DATABASE_INSTANCES (or 1 to 255 maximum) Available Since: Oracle 9i RAC Modifiable: No — SCOPE=SPFILE only; takes effect on next startup PDB Modifiable: No (CDB-level setting)

Configuration

Viewing Current Value

-- Check current INSTANCE_NUMBER setting
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'instance_number';

-- Check SPFILE value
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'instance_number';

-- View instance identity parameters together
SELECT name, value
FROM v$parameter
WHERE name IN (
  'instance_number',
  'instance_name',
  'thread',
  'undo_tablespace',
  'cluster_database',
  'cluster_database_instances'
)
ORDER BY name;

-- Confirm instance identity at runtime
SELECT instance_number, instance_name, host_name,
       thread#, status, database_status
FROM v$instance;

-- View all instances in the cluster
SELECT inst_id, instance_number, instance_name, host_name, status
FROM gv$instance
ORDER BY inst_id;

Setting the Parameter

-- Set instance number for RAC instance 1 (in instance-specific SPFILE section)
-- Typically done via SRVCTL or in a per-instance pfile/SPFILE

-- Using SRVCTL to set instance-specific parameters (recommended method)
-- srvctl modify instance -db mydb -instance mydb1 -stopoption immediate

-- Directly in SPFILE for instance-specific setting
ALTER SYSTEM SET instance_number = 1 SCOPE=SPFILE SID='mydb1';
ALTER SYSTEM SET instance_number = 2 SCOPE=SPFILE SID='mydb2';
ALTER SYSTEM SET instance_number = 3 SCOPE=SPFILE SID='mydb3';

-- View instance-specific SPFILE values
SELECT sid, name, value
FROM v$spparameter
WHERE name = 'instance_number'
ORDER BY sid;

-- Verify after restart
SELECT instance_number, instance_name
FROM v$instance;

Tuning Guidance

Recommended Values

Cluster Configuration	Instance Number Assignment
2-node RAC	Instance 1 = 1, Instance 2 = 2
4-node RAC	Instances numbered 1 through 4 sequentially
RAC One Node	Always 1 (single active instance)
Extended clusters	Numbers must be unique and contiguous from 1
Policy-managed databases	Oracle assigns numbers automatically via SRVCTL

Instance numbers should be assigned sequentially starting at 1 and match the corresponding undo tablespace and thread numbers. Gaps in numbering (e.g., 1, 3, 5) are supported but complicate administration and should be avoided.

How to Size

INSTANCE_NUMBER is an identity parameter, not a performance parameter. Sizing decisions involve CLUSTER_DATABASE_INSTANCES, undo tablespace pre-creation, and redo thread enablement.

-- Verify undo tablespace assignment matches instance number
SELECT i.instance_number,
       p.value AS undo_tablespace
FROM v$instance i,
     v$parameter p
WHERE p.name = 'undo_tablespace';

-- Check that redo thread is enabled for each instance
SELECT thread#, status, enabled, groups, instance
FROM v$thread
ORDER BY thread#;

-- Confirm all undo tablespaces exist and are properly sized
SELECT tablespace_name, status, contents,
       initial_extent, next_extent
FROM dba_tablespaces
WHERE contents = 'UNDO'
ORDER BY tablespace_name;

-- Check object affinity and reverse-key index usage per instance
SELECT inst_id, COUNT(*) AS object_count
FROM gv$bh
GROUP BY inst_id
ORDER BY inst_id;

Monitoring

-- Monitor instance recovery assignments (each instance recovers its own undo)
SELECT recovery_status, standby_became_primary_scn
FROM v$database;

-- Check which instance is mastering which resources (GCS)
SELECT inst_id,
       gc_cr_block_receive_time,
       gc_current_block_receive_time
FROM gv$instance_cache_transfer
ORDER BY inst_id;

-- View active session distribution across instances
SELECT inst_id, COUNT(*) AS active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id
ORDER BY inst_id;

-- Check instance-specific wait events
SELECT inst_id, event, total_waits, time_waited
FROM gv$system_event
WHERE event NOT LIKE 'SQL*Net%'
  AND event NOT IN ('Streams AQ: waiting for messages in the queue',
                    'wait for unread message on broadcast channel')
ORDER BY inst_id, time_waited DESC
FETCH FIRST 20 ROWS ONLY;

Common Issues

Issue 1: Duplicate Instance Numbers Prevent Cluster Startup

If two instances have the same INSTANCE_NUMBER, the second instance to start will fail because the cluster registry already has that number registered.

Resolution: Set unique instance numbers in the SPFILE using instance-specific SID qualifiers. Use SRVCTL to manage RAC instance configurations to avoid manual errors.

-- Check for duplicate assignments in SPFILE
SELECT sid, name, value
FROM v$spparameter
WHERE name = 'instance_number'
ORDER BY TO_NUMBER(value), sid;

-- Correct a duplicate using SRVCTL (preferred)
-- srvctl modify instance -db mydb -instance mydb2 -stopoption immediate
-- Then update instance number via SRVCTL parameter management

Issue 2: Instance Number Mismatch with Undo Tablespace

If INSTANCE_NUMBER=2 but UNDO_TABLESPACE=UNDOTBS1 (which belongs to instance 1), the instance will use the wrong undo tablespace. This causes contention when both instances run simultaneously.

Resolution: Align instance numbers with dedicated undo tablespaces. Each RAC instance should have its own undo tablespace named consistently (e.g., UNDOTBS1 for instance 1, UNDOTBS2 for instance 2).

-- Create a dedicated undo tablespace if missing
CREATE UNDO TABLESPACE undotbs2
DATAFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 500M;

-- Assign the correct undo tablespace for instance 2
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE=SPFILE SID='mydb2';

Issue 3: Redo Thread Not Enabled for Instance

Each RAC instance requires its own redo thread. If the thread corresponding to the instance number is not enabled, the instance cannot start.

Resolution: Enable the required redo thread and add redo log groups for it.

-- Check thread status
SELECT thread#, status, enabled, groups
FROM v$thread
ORDER BY thread#;

-- Enable a thread (requires exclusive database access)
ALTER DATABASE ENABLE PUBLIC THREAD 2;

-- Add redo log groups to the thread
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 5 ('+DATA/mydb/redo05.log', '+FRA/mydb/redo05b.log') SIZE 500M;

Related Parameters

• CLUSTER_DATABASE — Must be TRUE for INSTANCE_NUMBER to have cluster significance
• CLUSTER_DATABASE_INSTANCES — Defines the expected total instance count; INSTANCE_NUMBER must be within this range
• PARALLEL_MAX_SERVERS — Parallel query resource limit per instance; set consistently across all instances
• UNDO_TABLESPACE — Each RAC instance’s dedicated undo tablespace; must align with INSTANCE_NUMBER

Related Errors

• ORA-01034: Oracle Not Available — Startup failures when instance number conflicts or redo thread issues prevent startup
• ORA-01555: Snapshot Too Old — Undo tablespace misassignment causes undo contention and premature undo overwrite
• ORA-00600: Internal Error — Cache Fusion and GCS resource mastering errors can surface as internal errors when instance identity is misconfigured

Version Notes

Version	Notes
Oracle 9i	INSTANCE_NUMBER introduced with RAC
Oracle 10g	Grid infrastructure improvements made instance number management more automated
Oracle 11g R2	Policy-managed databases introduced; Oracle can assign instance numbers automatically
Oracle 12c+	CDB/PDB architecture; INSTANCE_NUMBER remains a CDB-level identity parameter
Oracle 19c+	No functional changes; SRVCTL remains the recommended tool for managing instance-specific parameters
Oracle 21c / 23ai	Behavior unchanged; applies to all RAC configurations including Exadata and Cloud RAC