DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02095 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02095: Specified Initialization Parameter Cannot Be Modified

ORA-02095 occurs when a DBA attempts to dynamically change a static initialization parameter using ALTER SYSTEM while the Oracle instance is running. Unlike dynamic parameters, static parameters require a full database restart to take effect and cannot be modified in memory. This error is one of the most common surprises for DBAs during live performance tuning or configuration changes.

Top 3 Causes

1. Attempting to Change a Static Parameter Dynamically

Some Oracle parameters such as PROCESSES, DB_BLOCK_SIZE, and DB_NAME are classified as static. They can only be written to the SPFILE and take effect after a restart.

-- Check if a parameter is static before changing it
SELECT name,
       value,
       issys_modifiable,
       description
FROM   v$parameter
WHERE  name = 'processes';
-- ISSYS_MODIFIABLE = FALSE means it's a static parameter

-- This will cause ORA-02095:
ALTER SYSTEM SET processes = 500 SCOPE = MEMORY;  -- ERROR!

-- Correct approach (write to SPFILE, restart required):
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;
Enter fullscreen mode Exit fullscreen mode

2. Using SCOPE=MEMORY on a Static Parameter

A common misconception is that using SCOPE=MEMORY can bypass static restrictions by only modifying the running instance. However, Oracle does not allow any in-memory change for static parameters, regardless of the SCOPE option used.

-- All of these will throw ORA-02095 for static parameters:
ALTER SYSTEM SET db_block_size = 16384 SCOPE = MEMORY;  -- ERROR!
ALTER SYSTEM SET db_block_size = 16384 SCOPE = BOTH;    -- ERROR!

-- Only SCOPE=SPFILE is valid for static parameters:
ALTER SYSTEM SET db_block_size = 16384 SCOPE = SPFILE;  -- OK

-- Verify the change is recorded in SPFILE:
SELECT name, value, isspecified
FROM   v$spparameter
WHERE  name = 'db_block_size';
Enter fullscreen mode Exit fullscreen mode

3. Incorrect SCOPE Usage in RAC Environments

In Oracle RAC environments, parameter scope and SID targeting add complexity. Applying the wrong SCOPE to a static parameter across multiple instances is a frequent source of ORA-02095.

-- Wrong: trying to apply immediately in RAC
ALTER SYSTEM SET processes = 300 SCOPE = MEMORY SID = '*';  -- ERROR!

-- Correct: write to SPFILE for all instances
ALTER SYSTEM SET processes = 300 SCOPE = SPFILE SID = '*';

-- Or target a specific instance:
ALTER SYSTEM SET processes = 300 SCOPE = SPFILE SID = 'ORCL1';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Always verify modifiability first:

-- Bulk check for commonly tuned parameters
SELECT name,
       issys_modifiable,
       CASE issys_modifiable
           WHEN 'IMMEDIATE' THEN 'Dynamic - No restart needed'
           WHEN 'DEFERRED'  THEN 'Dynamic - New sessions only'
           WHEN 'FALSE'     THEN 'Static - Restart required'
       END AS change_scope
FROM   v$parameter
WHERE  name IN ('processes', 'sessions', 'sga_target',
                'pga_aggregate_target', 'open_cursors',
                'db_block_size', 'log_buffer')
ORDER BY issys_modifiable;
Enter fullscreen mode Exit fullscreen mode

Step 2 — Back up SPFILE before any change:

-- Always back up before modifying
CREATE PFILE = '/backup/initORCL_backup.ora' FROM SPFILE;

-- Apply static parameter change to SPFILE
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;

-- Confirm the change is staged in SPFILE
SELECT name, value FROM v$spparameter WHERE name = 'processes';

-- Then restart the database to apply
-- SHUTDOWN IMMEDIATE;
-- STARTUP;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Make parameter validation a mandatory pre-change step.
Add a query against V$PARAMETER to your team's change management SOP. Before any ALTER SYSTEM command, confirm ISSYS_MODIFIABLE is not FALSE. This single habit eliminates most ORA-02095 occurrences.

-- Pre-change checklist query (add to your runbook)
SELECT name, value, issys_modifiable, isses_modifiable
FROM   v$parameter
WHERE  name = '&parameter_name';
Enter fullscreen mode Exit fullscreen mode

2. Automate SPFILE backups before maintenance windows.
Schedule a pre-change SPFILE backup as part of every maintenance procedure. If a bad static parameter causes startup failure, having a clean PFILE backup saves critical recovery time.

-- Schedule or run before any parameter change session
CREATE PFILE = '/dba/backup/init_' ||
               TO_CHAR(SYSDATE,'YYYYMMDD_HH24MI') || '.ora'
FROM SPFILE;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-02096 — Parameter is modifiable but the specified SCOPE option is invalid for that parameter.
  • ORA-32001 — Raised when SCOPE=SPFILE is used but the instance was started with a PFILE instead of an SPFILE.
  • ORA-01034 — Instance unavailable; can follow a bad static parameter change that prevents successful startup.

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