ORA-32004: Obsolete or Deprecated Parameter - Clean Up SPFILE After Upgrade

ORA-32004: Obsolete or Deprecated Parameter

Error Overview

Error Text: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

The ORA-32004 error (technically a warning, not a hard error) is written to the alert log during database startup when Oracle detects one or more initialization parameters in the SPFILE or PFILE that are obsolete, deprecated, or no longer recognised in the current database version. While the database will still start successfully, the presence of these parameters indicates a configuration hygiene problem that can mask real issues, cause unexpected behaviour, and should be resolved before the next database upgrade.

Common Causes

1. Post-Upgrade Parameter Cleanup Not Performed

• Parameters valid in Oracle 11g or 12c that were removed or renamed in 19c or 21c
• Database upgraded from an older version without reviewing and cleaning the SPFILE
• SPFILE cloned from an older environment without parameter validation
• Parameters flagged as deprecated in one version became fully obsolete in the next

2. Manually Added Obsolete Underscore Parameters

• Hidden parameters (_parameter_name) added for a specific bug workaround in an older version
• Workaround parameters no longer needed but never removed after applying a patch
• Test or diagnostic parameters left in the SPFILE by a previous DBA

3. Version-Specific Parameter Removals

• PARALLEL_SERVER replaced by CLUSTER_DATABASE in RAC
• LOG_ARCHIVE_START removed when archiving became controlled by ARCHIVELOG mode
• MAX_ENABLED_ROLES obsoleted in 10g
• BACKGROUND_DUMP_DEST and USER_DUMP_DEST replaced by DIAGNOSTIC_DEST in 11g
• OPTIMIZER_ADAPTIVE_FEATURES split into multiple granular parameters in 12.2

4. Incorrect Parameter Names

• Typographical errors in parameter names treated as unknown/obsolete
• Parameters from third-party documentation that do not exist in Oracle
• Case sensitivity issues in PFILE-based configurations on case-sensitive OS platforms

5. Parameters Deprecated in the Current Version

• UTL_FILE_DIR deprecated (use Oracle Directory objects instead)
• OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT deprecated for security reasons
• SEC_CASE_SENSITIVE_LOGON deprecated in 12.2 (case-sensitive passwords are now mandatory)

Diagnostic Queries

Identify Obsolete and Deprecated Parameters

-- Query V$OBSOLETE_PARAMETER to find all obsolete parameters
SELECT
  name,
  isspecified,
  isdeprecated,
  isobsolete,
  description
FROM v$obsolete_parameter
WHERE isspecified = 'TRUE'
ORDER BY name;

-- Parameters currently in effect that are deprecated
SELECT
  name,
  value,
  description,
  isdefault,
  ismodified
FROM v$parameter
WHERE name IN (
  SELECT name FROM v$obsolete_parameter WHERE isdeprecated = 'TRUE'
)
ORDER BY name;

Check the SPFILE for All Non-Default Parameters

-- View all parameters explicitly set in the SPFILE
SELECT
  name,
  value,
  display_value,
  description,
  isdefault,
  ismodified
FROM v$spparameter
WHERE isspecified = 'TRUE'
ORDER BY name;

-- Compare SPFILE values against current effective values
SELECT
  sp.name,
  sp.value          AS spfile_value,
  p.value           AS current_value,
  p.description
FROM v$spparameter sp
LEFT JOIN v$parameter p ON sp.name = p.name
WHERE sp.isspecified = 'TRUE'
ORDER BY sp.name;

Review the Alert Log for ORA-32004 Details

-- Find ORA-32004 entries and surrounding context in the alert log
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-32004%'
   OR message_text LIKE '%deprecated%'
   OR message_text LIKE '%obsolete%'
ORDER BY originating_timestamp DESC
FETCH FIRST 100 ROWS ONLY;

-- The alert log typically lists the specific parameter names:
-- ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
-- Deprecated/obsolete parameters in this instance:
--   rdbms_server_dn
--   utl_file_dir

Check for Hidden (Underscore) Parameters in SPFILE

-- List all underscore parameters explicitly set in SPFILE
SELECT
  name,
  value,
  description
FROM v$spparameter
WHERE isspecified = 'TRUE'
  AND name LIKE '\_%' ESCAPE '\'
ORDER BY name;

-- Check if any of these underscore parameters correspond to known bug workarounds
-- Cross-reference with My Oracle Support before removing
SELECT
  name,
  value,
  isdefault
FROM v$parameter
WHERE name LIKE '\_%' ESCAPE '\'
  AND isdefault = 'FALSE'
ORDER BY name;

Identify Parameters Changed Across Oracle Versions

-- Parameters in the SPFILE that are not in v$parameter (truly obsolete)
SELECT
  sp.name,
  sp.value
FROM v$spparameter sp
WHERE sp.isspecified = 'TRUE'
  AND sp.name NOT IN (SELECT name FROM v$parameter)
  AND sp.name NOT LIKE '\_%' ESCAPE '\'
ORDER BY sp.name;

-- Deprecated parameters with non-default values
SELECT
  p.name,
  p.value,
  p.description,
  p.ismodified
FROM v$parameter p
WHERE p.isdeprecated = 'TRUE'
  AND p.isdefault = 'FALSE'
ORDER BY p.name;

Step-by-Step Resolution

1. Generate a Complete List of Parameters to Remove

Before making any changes, document all currently set parameters:

-- Export current SPFILE to a PFILE for review and backup
CREATE PFILE='/tmp/pfile_backup_YYYYMMDD.ora' FROM SPFILE;

-- Review the file at OS level:
-- cat /tmp/pfile_backup_YYYYMMDD.ora

-- Identify which parameters appear in v$obsolete_parameter with isspecified=TRUE
SELECT name, isdeprecated, isobsolete, description
FROM v$obsolete_parameter
WHERE isspecified = 'TRUE'
ORDER BY isobsolete DESC, name;

2. Remove Obsolete Parameters from the SPFILE

-- Remove a fully obsolete parameter from the SPFILE
ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE;
ALTER SYSTEM RESET background_dump_dest SCOPE=SPFILE;
ALTER SYSTEM RESET user_dump_dest SCOPE=SPFILE;
ALTER SYSTEM RESET utl_file_dir SCOPE=SPFILE;
ALTER SYSTEM RESET os_authent_prefix SCOPE=SPFILE;
ALTER SYSTEM RESET remote_os_authent SCOPE=SPFILE;
ALTER SYSTEM RESET sec_case_sensitive_logon SCOPE=SPFILE;

-- Verify the parameter was removed
SELECT name, isspecified
FROM v$spparameter
WHERE name IN ('log_archive_start', 'background_dump_dest', 'utl_file_dir')
ORDER BY name;

3. Migrate Functionality from Deprecated Parameters

UTL_FILE_DIR Migration

-- Before (deprecated): utl_file_dir = /tmp:/u01/files
-- After: Create Oracle Directory objects

-- Create a directory object to replace each path in UTL_FILE_DIR
CREATE OR REPLACE DIRECTORY tmp_dir   AS '/tmp';
CREATE OR REPLACE DIRECTORY files_dir AS '/u01/files';

-- Grant access to application users
GRANT READ, WRITE ON DIRECTORY tmp_dir   TO app_user;
GRANT READ, WRITE ON DIRECTORY files_dir TO app_user;

-- Remove the deprecated parameter
ALTER SYSTEM RESET utl_file_dir SCOPE=SPFILE;

BACKGROUND_DUMP_DEST / USER_DUMP_DEST Migration

-- These are replaced by DIAGNOSTIC_DEST (introduced in 11g)
-- Set DIAGNOSTIC_DEST to the ADR base
ALTER SYSTEM SET diagnostic_dest = '/u01/app/oracle' SCOPE=BOTH;

-- Remove the obsolete parameters
ALTER SYSTEM RESET background_dump_dest SCOPE=SPFILE;
ALTER SYSTEM RESET user_dump_dest SCOPE=SPFILE;
ALTER SYSTEM RESET core_dump_dest SCOPE=SPFILE;

4. Handle Hidden (Underscore) Parameter Removal Safely

-- Research each underscore parameter before removing
-- Check My Oracle Support for the bug number and whether the patch is applied

-- Example: remove a workaround parameter after confirming the patch is applied
-- ALTER SYSTEM RESET "_fix_control" SCOPE=SPFILE;
-- ALTER SYSTEM RESET "_optimizer_use_feedback" SCOPE=SPFILE;

-- Always bounce a non-production instance first to confirm stability
-- Then apply to production during the next maintenance window

5. Validate the Cleaned SPFILE and Restart

-- After removing obsolete parameters, create a new PFILE snapshot
CREATE PFILE='/tmp/pfile_clean_YYYYMMDD.ora' FROM SPFILE;

-- Review the clean PFILE at OS level to confirm no obsolete parameters remain

-- Bounce the database to apply SPFILE changes and confirm ORA-32004 is gone
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- Verify the alert log no longer contains ORA-32004 after the restart
SELECT message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1/24
  AND message_text LIKE '%32004%';

Prevention Strategies

1. Pre-Upgrade Parameter Review

-- Before upgrading, run Oracle's pre-upgrade utility which flags obsolete parameters
-- $ORACLE_HOME/rdbms/admin/preupgrade.sql (12.1)
-- Or use the pre-upgrade jar: java -jar preupgrade.jar TERMINAL TEXT

-- Also query this view on the target version to identify what will be obsolete
SELECT name, isobsolete, isdeprecated, description
FROM v$obsolete_parameter
WHERE isobsolete = 'TRUE' OR isdeprecated = 'TRUE'
ORDER BY name;

2. Scheduled Parameter Audit

-- Incorporate into a quarterly DBA audit procedure
CREATE OR REPLACE PROCEDURE audit_init_parameters AS
BEGIN
  FOR rec IN (
    SELECT name, isdeprecated, isobsolete, description
    FROM v$obsolete_parameter
    WHERE isspecified = 'TRUE'
    ORDER BY name
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(
      CASE WHEN rec.isobsolete = 'TRUE' THEN 'OBSOLETE' ELSE 'DEPRECATED' END
      || ': ' || rec.name || ' -- ' || rec.description
    );
  END LOOP;
END;
/

EXEC audit_init_parameters;

3. SPFILE Version Control

-- After any parameter change, export the SPFILE to a versioned PFILE
-- and commit it to your configuration management repository

CREATE PFILE='/u01/dba/spfile_exports/pfile_' ||
  TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.ora'
FROM SPFILE;

-- Automate this with a DBMS_SCHEDULER job after any ALTER SYSTEM SET

4. Version-Specific Parameter Change Reference

Key parameters to review when upgrading to Oracle 19c or 23ai:

-- Check these specifically when upgrading from 11g/12c to 19c+
SELECT sp.name, sp.value
FROM v$spparameter sp
WHERE sp.isspecified = 'TRUE'
  AND sp.name IN (
    'utl_file_dir',
    'background_dump_dest',
    'user_dump_dest',
    'core_dump_dest',
    'log_archive_start',
    'max_enabled_roles',
    'os_authent_prefix',
    'remote_os_authent',
    'sec_case_sensitive_logon',
    'optimizer_adaptive_features',
    'parallel_server',
    'parallel_server_instances'
  )
ORDER BY sp.name;

Diagnostic Scripts

These Oracle Day by Day scripts can help with parameter and configuration management:

• db.sql — Database configuration overview including version and parameters
• health.sql — Database health check including configuration warnings

Related Errors

• ORA-00096 - Invalid value for parameter
• ORA-01034 - Oracle not available (startup failures)
• ORA-00845 - MEMORY_TARGET not supported on this system

Emergency Response

Quick Fixes

1. Identify all obsolete parameters in one query

   SELECT name, isdeprecated, isobsolete
   FROM v$obsolete_parameter
   WHERE isspecified = 'TRUE'
   ORDER BY isobsolete DESC, name;

2. Remove a specific obsolete parameter immediately

   ALTER SYSTEM RESET parameter_name SCOPE=SPFILE;

3. Create a clean PFILE backup before making changes

   CREATE PFILE='/tmp/pfile_pre_cleanup.ora' FROM SPFILE;

Post-Resolution Cleanup

-- Confirm no obsolete parameters remain in the SPFILE
SELECT COUNT(*)
FROM v$obsolete_parameter
WHERE isspecified = 'TRUE';

-- Restart the database and confirm ORA-32004 no longer appears
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- Verify the alert log is clean
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 30/1440
  AND (message_text LIKE '%32004%'
    OR message_text LIKE '%obsolete%'
    OR message_text LIKE '%deprecated%')
ORDER BY originating_timestamp;

-- Document the cleanup in your change management system
-- and update the SPFILE backup in version control