Step‑by‑Step Guide to Downgrade Oracle Database 12c to Earlier Releases

Scope and Purpose

This document supplies a detailed checklist and script‑based process for downgrading Oracle Database 12c (Standard or Enterprise Edition, version 12.1.0.1 or later) to earlier releases—specifically 11.2.0.3, 11.2.0.2, or 11.1.0.7. It does not cover export/import, Data Pump, or other migration methods.

Important Preconditions

Target‑version Oracle binaries must be installed on the server before starting the downgrade.

The downgrade can only be performed on a database that was successfully upgraded to 12c; it is not intended for failed upgrades.

Direct upgrades are allowed from 10.2.0.5, 11.1.0.7, and 11.2.0.2 +; downgrades are possible for all those versions except 10.2.0.5.

Patch rollbacks must be completed before the downgrade; see the relevant opatch and SQL rollback scripts.

Pre‑Downgrade Checklist

XML DB component will be removed during downgrade; ensure it is present in the 12c source.

Enterprise Manager is not supported for downgrade—re‑configure EM controls after the process.

Database Control files are removed during upgrade; recreate them after downgrade.

Verify the compatible parameter has not been set beyond 12.1.0.

Disable Database Vault if enabled (see Oracle Note 803948.1).

If Oracle Label Security (OLS) is used, run the olspredowngrade.sql script before downgrading.

Ensure the timezone files ( ORA_TZFILE) point to the 12c home and are reset for the target version.

Copy apxrelod.sql (APEX) from the 12c $ORACLE_HOME/apex directory to a temporary location if APEX is installed.

Drop any objects created on fixed tables to avoid ORA‑00600 errors; they can be recreated later.

For RAC databases, set CLUSTER_DATABASE=FALSE before downgrade and revert it afterward.

Downgrade Procedure

1) Verify component status

col comp_id format a10
col comp_name format a30
col version format a10
col status format a8
select substr(comp_id,1,15) comp_id,
       substr(comp_name,1,30) comp_name,
       substr(version,1,10) version,
       status
from dba_registry;

2) Check for invalid SYS/SYSTEM objects

select owner, count(object_name) "Invalid object count"
from dba_objects
where status!='VALID' and owner in ('SYS','SYSTEM')
group by owner;

If the count is zero, continue; otherwise run utlrp.sql repeatedly or seek assistance.

3) Shut down the database shutdown immediate 4) Back up the 12c database (recommended but not detailed here).

5) Start the database in downgrade mode startup downgrade 6) Execute the downgrade script

spool downgrade.log
@$ORACLE_HOME/rdbms/admin/catdwgrd.sql
spool off

Note: $ORACLE_HOME must point to the 12c home; catdwgrd.sql rolls back components to the original pre‑upgrade version.

After the script finishes, run:

spool off
shutdown immediate
exit

Post‑Downgrade Steps

If APEX is present and the target is 11g, run the copied apxrelod.sql to reload APEX components.

If OLS is used, copy olstrig.sql from the 12c home to the target home and execute it: @olstrig.sql For RAC databases, downgrade the Clusterware configuration:

srvctl downgrade database -d db-unique-name -o oraclehome -t to_version

Replace db-unique-name, oraclehome, and to_version with appropriate values (see version mapping table in the original guide).

Recompile invalid objects:

sqlplus "/ as sysdba"
startup upgrade
spool catout.log
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
spool off
shutdown immediate

Run utlrp.sql again to ensure all PL/SQL modules are valid.

Verification

Execute the Oracle‑provided diagnostic script (e.g., dbupgdiag.sql) to generate a status log ( db_upg_diag_<sid>_<timestamp>.log) and confirm the database is healthy.

References

NOTE 1351112.1 – Upgrading and Migration Oracle Database

NOTE 1520299.1 – Oracle Database 12c Release 1 Installation/Upgrade Guide

Various Oracle Support Notes referenced throughout the checklist (e.g., 870877.1, 876353.1, 803948.1, 556610.1, 1520209.1)