ORA-00096 Invalid Value for Parameter - Parameter Configuration Guide

ORA-00096: Invalid Value for Parameter

Error Overview

Error Text: ORA-00096: invalid value parameter_value for parameter parameter_name, must be from among: valid_values

This error occurs when attempting to set an Oracle initialization parameter to an invalid value. The parameter value doesn’t match the expected data type, range, or enumerated list of valid values for that specific parameter.

Understanding Parameter Validation

Parameter Types

Oracle Parameter Types
├── Boolean (TRUE/FALSE)
├── Integer (with min/max ranges)
├── String (with pattern validation)
├── Enumerated (fixed list of values)
├── Size (with unit suffixes: K, M, G)
└── Time (with unit suffixes: s, m, h)

Parameter Scopes

• MEMORY - Current instance only (until restart)
• SPFILE - Persistent (survives restart)
• BOTH - Both memory and SPFILE

Common Causes

Invalid Boolean Values

-- Incorrect boolean parameter values
ALTER SYSTEM SET recyclebin=YES;     -- Should be TRUE/FALSE
ALTER SYSTEM SET audit_trail=ON;     -- Should be TRUE/FALSE/EXTENDED/etc.

Out-of-Range Numeric Values

-- Values outside acceptable ranges
ALTER SYSTEM SET processes=0;        -- Below minimum (6)
ALTER SYSTEM SET sessions=5;         -- Too low for processes setting
ALTER SYSTEM SET sga_target=100;     -- Too small for SGA

Invalid Enumerated Values

-- Invalid options for enumerated parameters
ALTER SYSTEM SET optimizer_mode=BEST;           -- Invalid mode
ALTER SYSTEM SET undo_management=MANUAL_AUTO;   -- Invalid option
ALTER SYSTEM SET audit_trail=EXTENDED_DB;       -- Invalid combination

Incorrect Size Format

-- Invalid size specifications
ALTER SYSTEM SET sga_target='1GB';   -- Should be 1G (no 'B')
ALTER SYSTEM SET pga_aggregate_target=256MB;  -- Should be 256M

Diagnostic Queries

Check Current Parameter Values

-- View current parameter settings
SELECT
    name,
    value,
    isdefault,
    ismodified,
    description
FROM v$parameter
WHERE name = LOWER('&parameter_name')  -- Replace with parameter name
ORDER BY name;

Parameter Validation Information

-- Check parameter constraints and valid values
SELECT
    name,
    type,
    value,
    default_value,
    isdefault,
    ismodified,
    update_comment
FROM v$parameter
WHERE name LIKE '%&search_pattern%'  -- Replace with search pattern
ORDER BY name;

SPFILE vs Memory Values

-- Compare SPFILE and memory parameter values
SELECT
    name,
    value as memory_value,
    display_value,
    isdefault,
    ismodified
FROM v$parameter
WHERE name IN (
    SELECT name
    FROM v$spparameter
    WHERE value IS NOT NULL
)
INTERSECT
SELECT
    name,
    value as spfile_value,
    display_value,
    'SPFILE' as source,
    'YES' as in_spfile
FROM v$spparameter
WHERE value IS NOT NULL
ORDER BY name;

Parameter Dependencies

-- Check for parameter dependencies
SELECT
    p1.name as parameter,
    p1.value as current_value,
    p2.name as dependent_parameter,
    p2.value as dependent_value
FROM v$parameter p1, v$parameter p2
WHERE p1.name IN ('processes', 'sessions', 'transactions')
AND p2.name IN ('processes', 'sessions', 'transactions')
AND p1.name != p2.name
ORDER BY p1.name, p2.name;

Resolution Steps

Common Parameter Fixes

1. Boolean Parameters

-- Correct boolean parameter syntax
ALTER SYSTEM SET recyclebin=TRUE SCOPE=BOTH;
ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;  -- Requires restart
ALTER SYSTEM SET sql_trace=FALSE SCOPE=MEMORY;

2. Numeric Parameters

-- Check valid ranges first
SELECT
    name,
    value,
    description
FROM v$parameter
WHERE name IN ('processes', 'sessions');

-- Set with proper values
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=555 SCOPE=SPFILE;  -- Must be > processes

3. Size Parameters

-- Correct size parameter format
ALTER SYSTEM SET sga_target=4G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;
ALTER SYSTEM SET db_cache_size=512M SCOPE=BOTH;

4. String Parameters

-- String parameters with proper quoting
ALTER SYSTEM SET global_names=TRUE SCOPE=BOTH;
ALTER SYSTEM SET db_domain='company.com' SCOPE=SPFILE;
ALTER SYSTEM SET instance_name='PROD1' SCOPE=SPFILE;

Parameter Validation Script

-- Create parameter validation procedure
CREATE OR REPLACE PROCEDURE validate_parameter(
    p_name VARCHAR2,
    p_value VARCHAR2
) AS
    l_sql VARCHAR2(1000);
    l_error_count NUMBER := 0;
BEGIN
    -- Test parameter setting
    l_sql := 'ALTER SYSTEM SET ' || p_name || '=' || p_value || ' SCOPE=MEMORY';

    BEGIN
        EXECUTE IMMEDIATE l_sql;
        DBMS_OUTPUT.PUT_LINE('SUCCESS: Parameter ' || p_name || ' can be set to ' || p_value);

        -- Revert to original value
        EXECUTE IMMEDIATE 'ALTER SYSTEM RESET ' || p_name || ' SCOPE=MEMORY';

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
            l_error_count := 1;
    END;

    IF l_error_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Parameter validation successful');
    END IF;
END;
/

Common Parameter Issues

Memory Management Parameters

-- SGA parameters must be consistent
SELECT
    name,
    value,
    CASE
        WHEN name = 'sga_target' AND TO_NUMBER(value) > 0
        THEN 'Automatic SGA Management Enabled'
        WHEN name IN ('db_cache_size', 'shared_pool_size', 'large_pool_size')
             AND TO_NUMBER(value) > 0
        THEN 'Manual SGA Component Sizing'
        ELSE 'Default/Automatic'
    END as management_mode
FROM v$parameter
WHERE name IN (
    'sga_target', 'sga_max_size', 'db_cache_size',
    'shared_pool_size', 'large_pool_size'
)
ORDER BY name;

Process and Session Parameters

-- Calculate proper session values
SELECT
    'processes' as parameter,
    value as current_processes,
    CEIL(TO_NUMBER(value) * 1.1) + 5 as recommended_sessions
FROM v$parameter
WHERE name = 'processes'
UNION ALL
SELECT
    'sessions' as parameter,
    value as current_sessions,
    'Should be >= ' || (
        SELECT CEIL(TO_NUMBER(value) * 1.1) + 5
        FROM v$parameter
        WHERE name = 'processes'
    ) as recommendation
FROM v$parameter
WHERE name = 'sessions';

Optimizer Parameters

-- Valid optimizer parameter values
SELECT
    'optimizer_mode' as parameter,
    'FIRST_ROWS_n, FIRST_ROWS, ALL_ROWS, CHOOSE' as valid_values
FROM dual
UNION ALL
SELECT
    'optimizer_index_cost_adj',
    '1-10000 (default: 100)'
FROM dual
UNION ALL
SELECT
    'optimizer_index_caching',
    '0-100 (default: 0)'
FROM dual;

Prevention Strategies

Parameter Change Process

-- Safe parameter change procedure
CREATE OR REPLACE PROCEDURE safe_parameter_change(
    p_parameter VARCHAR2,
    p_value VARCHAR2,
    p_scope VARCHAR2 DEFAULT 'BOTH'
) AS
    l_original_value VARCHAR2(4000);
    l_sql VARCHAR2(1000);
BEGIN
    -- Get current value
    SELECT value INTO l_original_value
    FROM v$parameter
    WHERE name = LOWER(p_parameter);

    DBMS_OUTPUT.PUT_LINE('Original value: ' || l_original_value);

    -- Build ALTER SYSTEM command
    l_sql := 'ALTER SYSTEM SET ' || p_parameter || '=' || p_value ||
             ' SCOPE=' || p_scope;

    -- Log the change
    INSERT INTO parameter_change_log VALUES (
        SYSDATE,
        p_parameter,
        l_original_value,
        p_value,
        p_scope,
        USER
    );
    COMMIT;

    -- Execute change
    EXECUTE IMMEDIATE l_sql;
    DBMS_OUTPUT.PUT_LINE('Parameter changed successfully');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
        RAISE;
END;
/

Parameter Documentation

-- Create parameter documentation table
CREATE TABLE parameter_standards (
    parameter_name VARCHAR2(80),
    valid_values VARCHAR2(4000),
    recommended_value VARCHAR2(1000),
    description VARCHAR2(4000),
    last_updated DATE
);

-- Example entries
INSERT INTO parameter_standards VALUES (
    'processes',
    '6-2147483647',
    'Based on concurrent user load + background processes',
    'Maximum number of OS processes that can connect to Oracle',
    SYSDATE
);

Emergency Recovery

SPFILE Recovery

-- If SPFILE is corrupted due to invalid parameter
-- 1. Start with PFILE
STARTUP PFILE='/path/to/backup/init.ora';

-- 2. Create new SPFILE
CREATE SPFILE FROM PFILE='/path/to/backup/init.ora';

-- 3. Restart with SPFILE
SHUTDOWN IMMEDIATE;
STARTUP;

Reset Invalid Parameters

-- Reset parameter to default value
ALTER SYSTEM RESET parameter_name SCOPE=SPFILE;

-- For immediate reset (if possible)
ALTER SYSTEM RESET parameter_name SCOPE=BOTH;

Monitoring and Alerting

Parameter Change Monitoring

-- Monitor parameter changes
SELECT
    name,
    value,
    ismodified,
    update_comment,
    CASE
        WHEN ismodified = 'TRUE'
        THEN 'Modified from default'
        ELSE 'Default value'
    END as status
FROM v$parameter
WHERE ismodified = 'TRUE'
ORDER BY name;

Automated Parameter Validation

#!/bin/bash
# Parameter validation script
sqlplus -s / as sysdba << EOF
SET PAGESIZE 0 FEEDBACK OFF
SELECT 'INVALID: ' || name || ' = ' || value
FROM v$parameter
WHERE name IN ('processes', 'sessions')
AND (
    (name = 'processes' AND TO_NUMBER(value) < 6) OR
    (name = 'sessions' AND TO_NUMBER(value) < 10)
);
EOF

Related Errors

• ORA-02097: Parameter cannot be modified
• ORA-00980: Synonym translation no longer valid
• ORA-01078: Failure in processing system parameters

Best Practices

1. Always validate parameters in test environment first
2. Use SCOPE=MEMORY for testing, SCOPE=BOTH for production
3. Document parameter changes and reasons
4. Keep backup of working SPFILE
5. Monitor parameter changes regularly
6. Follow Oracle documentation for valid ranges

Troubleshooting Checklist

• Verify parameter name spelling and case
• Check valid value ranges/options
• Validate parameter dependencies
• Test with SCOPE=MEMORY first
• Review Oracle documentation
• Check for parameter-specific syntax
• Verify sufficient privileges
• Document changes made