COMPATIBLE - Manage Oracle Database Version Compatibility for Upgrades

COMPATIBLE

Overview

COMPATIBLE specifies the minimum Oracle Database release with which this database must remain compatible. Oracle uses this value to gate access to new features that make irreversible changes to data dictionary structures or data file formats. When COMPATIBLE is set to a particular value, features introduced in later versions are disabled and the database guarantees it can be downgraded to that version.

The critical constraint: once you advance COMPATIBLE to a higher value, you cannot lower it. This is the “point of no return” for database upgrades. After Oracle writes format changes to data files under the new compatibility level, the database cannot be opened by an older software version. Before raising COMPATIBLE, ensure your upgrade testing is complete and the rollback window has closed. There is no SQL command, patch, or support procedure that can lower a COMPATIBLE value after it has been committed.

Parameter Type: Static (requires database restart to take effect) Default Value: Matches the version of the Oracle software installed (e.g., 19.0.0 for Oracle 19c) Valid Range: Minimum version varies by release; typically current_major_version − 2 releases Available Since: Oracle 7 Modifiable: Yes — via ALTER SYSTEM SET ... SCOPE = SPFILE then restart — but only upward, never downward PDB Modifiable: No — CDB-level parameter; applies to the entire CDB

Configuration

Viewing Current Value

-- Current COMPATIBLE value in memory
SELECT name,
       value,
       description,
       isdefault
FROM   v$parameter
WHERE  name = 'compatible';

-- SPFILE value (what will be used on next restart)
SELECT name, value
FROM   v$spparameter
WHERE  name = 'compatible';

-- Cross-check with the actual database version
SELECT version,
       version_full,
       banner
FROM   v$version
WHERE  rownum = 1;

-- Confirm: current COMPATIBLE vs installed software version
SELECT p.value                              AS compatible_setting,
       v.version                            AS software_version,
       CASE
         WHEN p.value = v.version
           THEN 'COMPATIBLE matches software version — fully enabled'
         WHEN p.value < v.version
           THEN 'COMPATIBLE is below software version — some new features disabled'
         ELSE 'WARNING: COMPATIBLE is above software version — unusual'
       END                                  AS status
FROM  (SELECT value FROM v$parameter WHERE name = 'compatible')   p,
      (SELECT version FROM v$version WHERE rownum = 1)            v;

Setting the Parameter (Raising Compatibility — Point of No Return)

-- WARNING: This change is IRREVERSIBLE once the database restarts and writes
-- new-format blocks. Ensure upgrade testing is complete before proceeding.

-- Step 1: Set the new COMPATIBLE value in the SPFILE
ALTER SYSTEM SET compatible = '19.0.0' SCOPE = SPFILE;

-- Step 2: Shut down the database cleanly
SHUTDOWN IMMEDIATE;

-- Step 3: Start up — Oracle will now operate at the new compatibility level
STARTUP;

-- Step 4: Verify
SELECT name, value FROM v$parameter WHERE name = 'compatible';

You cannot execute ALTER SYSTEM SET compatible = '18.0.0' to go back to 18c after this step. If you need to roll back the database software, you must restore from a cold backup taken before the compatibility change.

Tuning Guidance

When to Advance COMPATIBLE After a Software Upgrade

After upgrading the Oracle software (e.g., from 18c to 19c), the database starts with the old COMPATIBLE value intact. This is intentional: it gives you a window to validate the upgrade and roll back if necessary.

The recommended process:

Keep COMPATIBLE at the old value initially. Run the database on the new software for your defined test/validation period (typically 2–4 weeks in production).
Advance COMPATIBLE only after you are confident the upgrade is stable and no rollback is needed.
Once advanced, the rollback path is closed. Document this as a change management milestone.

-- Before advancing: document the current state
SELECT name, value FROM v$parameter WHERE name IN ('compatible', 'optimizer_features_enable');

-- Check for any features already using the new release format
-- (Oracle does not provide a direct query; use the upgrade advisor)
-- Run from SQL*Plus as SYSDBA:
-- @?/rdbms/admin/utlu192i.sql   -- for 19c upgrade utility

-- After advancing: confirm new features are now available
SELECT name, value FROM v$parameter WHERE name = 'compatible';

Relationship with OPTIMIZER_FEATURES_ENABLE

COMPATIBLE and OPTIMIZER_FEATURES_ENABLE both influence how the optimizer behaves, but they control different things:

COMPATIBLE controls data format and structural compatibility — it gates whether new physical changes can be written.
OPTIMIZER_FEATURES_ENABLE controls the optimizer’s behaviour independently of the format compatibility level.

-- Check both settings
SELECT name, value
FROM   v$parameter
WHERE  name IN ('compatible', 'optimizer_features_enable')
ORDER BY name;

-- OPTIMIZER_FEATURES_ENABLE can be lowered independently to roll back optimizer behaviour
-- without affecting COMPATIBLE
ALTER SYSTEM SET optimizer_features_enable = '18.1.0' SCOPE = BOTH;

If after an upgrade your execution plans regress, you can lower OPTIMIZER_FEATURES_ENABLE to the previous version’s value while leaving COMPATIBLE at the new level. This is the recommended approach for plan stability during upgrades — not lowering COMPATIBLE.

Pre-Upgrade Compatibility Checks

Before advancing COMPATIBLE, run Oracle’s pre-upgrade checks:

-- Identify objects that may be affected by the version change
-- (run these queries on the SOURCE database before upgrade)

-- Invalid objects that must be compiled before upgrade
SELECT owner,
       object_name,
       object_type,
       status,
       last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID'
  AND  owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','ORACLE_OCM')
ORDER BY owner, object_type, object_name;

-- Deprecated parameters that must be removed before raising COMPATIBLE
-- (query varies by target version; example for 19c)
SELECT name, value, description
FROM   v$parameter
WHERE  name IN (
  'utl_file_dir',
  'max_enabled_roles',
  'o7_dictionary_accessibility',
  'parallel_automatic_tuning',
  'standby_archive_dest'
)
  AND  value IS NOT NULL;

-- Tablespace-level compatibility (some features require specific COMPATIBLE values)
SELECT tablespace_name,
       contents,
       status,
       force_logging
FROM   dba_tablespaces
WHERE  status != 'ONLINE'
ORDER BY tablespace_name;

Taking a Pre-Advance Backup

Always take a cold (consistent) backup before advancing COMPATIBLE. This is your last reliable restore point:

-- Verify database is in a clean consistent state before cold backup
SELECT status FROM v$instance;    -- should be OPEN
SELECT count(*) FROM v$recover_file;  -- should be 0 (no files needing recovery)
SELECT count(*) FROM v$backup WHERE status = 'ACTIVE';  -- should be 0

Then from RMAN:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG TAG 'PRE_COMPATIBLE_ADVANCE';
RMAN> BACKUP CURRENT CONTROLFILE TAG 'PRE_COMPATIBLE_ADVANCE';

Common Issues

Issue 1: Database reports ORA-32004 after upgrade — deprecated or obsolete parameters

Symptom: Alert log shows ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance during startup.

Cause: The SPFILE contains parameters that were deprecated or removed in the new version. This can appear when the software is upgraded but the SPFILE was carried over unchanged.

Resolution:

-- Identify deprecated parameters from the alert log or query V$PARAMETER
-- Look for parameters where ISDEPRECATED = TRUE
SELECT name, value, isdeprecated, isbasic
FROM   v$parameter
WHERE  isdeprecated = 'TRUE'
  AND  isdefault = 'FALSE'
ORDER BY name;

-- Remove deprecated parameters from SPFILE
ALTER SYSTEM RESET utl_file_dir SCOPE = SPFILE;
-- Restart to clear the ORA-32004 warning

Issue 2: Attempting to open the database with older software after advancing COMPATIBLE

Symptom: After a failed upgrade rollback where the DBA restored the old Oracle software binaries but not the data files, startup fails with a compatibility error.

Cause: Oracle’s COMPATIBLE value in the control file exceeds what the older software version supports.

Resolution: This scenario requires a full database restore from the cold backup taken before the compatibility was advanced. There is no in-place fix. This is why the cold backup before advancing COMPATIBLE is critical.

Issue 3: New features not available even after upgrading software

Symptom: A feature documented for Oracle 19c is unavailable; Oracle raises an error saying the feature requires a higher COMPATIBLE value.

Cause: COMPATIBLE is still set to the pre-upgrade value (e.g., 12.2.0). Oracle will not write new-format structures until COMPATIBLE is advanced.