Oracle 12c - Downgrade Oracle Database 12c to 11g

Downgrade Oracle Database 12c Release 1 (12.1) To Previous Versions

This document is created for use as a guideline and checklist for downgrading a previously upgraded database from Oracle 12c back to the previous release: 11.2.0.3, 11.2.0.2. 11.1.0.7

An important note is that when downgrading a database instance from the current version to the version prior to the upgrade, the database IS NOT returned to the same state as it was pre-upgrade.  Depending on the versions involved, the upgrade process makes changes that are not reversible. The downgrade process allows users to open and access the database instance in the previous version. This is usually sufficient.
Additional corrective actions (such as de-install / re-install or re-upgrade to current patchset levels) may be needed to settle left-over issues after a downgrade. If the goal is to have the instance back EXACTLY as it was pre-upgrade then other processes including a recovery to just before the upgrade should be used.

The process discussed in this article is a script based downgrade.  This article does not discuss the use of Export/Import, Data Pump or other methods to move data from one version to another.

Oracle binaries of the release/version to which you are downgrading should be available/installed on the server before you start the downgrade process.   If you have uninstalled the Oracle executable's to which you want to downgrade, please re-install the oracle binaries to the correct version/patch level for the downgrade.

This process is intended to downgrade a database that was successfully and upgraded to 12c.  It is not intended to back out from a failed upgrade. You can only downgrade to the release and patch level you upgraded from.

Direct upgrade is possible for release 10.2.0.5, 11.1.0.7, or release 11.2.0.2 or higher. Downgrade is possible to these versions except 10.2.0.5.

For example, if you upgraded from Oracle 11.1.0.6 to Oracle 12c (12.1.0) by applying intermediate patch 11.1.0.7, then you can not downgrade to Oracle 11.1.0.6.   Downgrade can be done only to Direct upgrade version

Exception:

Even though direct upgrade is possible for 10.2.0.5, downgrade is not applicable to 10.2.0.5.

This is because in the upgrade process the compatible parameter is set to a minimum 11.0.0.  This prevents downgrade to 10.2.0.5.  The possible downgrade versions are 11.1.0.7, 11.2.0.2, 11.2.0.3 or higher

If there are any patches applied on the source database running from the upgraded home, they need to be rolled back before beginning the downgrade process.
The steps to un-install and roll back patches are documented in the readme for the patch in question.
Failure to un-install and roll back patches can result in a failure to downgrade including dictionary objects that cannot be re-validated.

Example for an Exadata Bundle Patch, the procedure is to:

Un-install the patch
                 Example:   $ opatch auto /u01/app/oracle/patches/14103267 -rollback
Roll back any SQL applied as part of the patch application:    
                 Example:   SQL> @rdbms/admin/catbundle_EXA_<database SID>_ROLLBACK.sql to rollback SQL changes.

SOLUTION

Pre-downgrade steps

• - XML db component is mandatory in 12c.
  During an upgrade to 12c, XML db component would have been installed if it was not present.
  Downgrade from 12c will remove installed XDB component
• - Downgrade is not supported for Enterprise Manager. Before the downgrade reconfigure Oracle EM controls. Refer to
  
  Oracle Database Upgrade Guide 12c Release 1 (12.1) E17642-10
  6 Downgrading Oracle Database to an Earlier Release
  6.6.5 Restoring Oracle Enterprise Manager after Downgrading Oracle Database
  
  - During upgrade to 12c, the database control repository is removed. After downgrade reconfigure DB control.      
            
  Note 870877.1 How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release ?         
  Note 876353.1 How To Restore The Oracle Enterprise Manager Data To Downgrade The Single Instance Database To Previous/Source Release ?
   
• - Compatible parameter should not have changed to 12.1.0.
• - Disable data vault if it is enabled.
  
  
  Note 803948.1  How To Uninstall Or Reinstall Database Vault in 11g (UNIX)
  Note 453902.1 Enabling and Disabling Oracle Database Vault in WINDOWS
   
• - If your database uses Oracle Label Security, then run the Oracle Label Security (OLS) preprocess downgrade olspredowngrade.sql script (available at $ORACLE_HOME/rdbms/admin) in the new Oracle Database 12c Oracle home.
• - Timezone version should be same.
• - Unset ORA_TZFILE if it is set and points to 12c home.
• - If you have Oracle Application Express on your database, then you must copy the apxrelod.sql file from the Oracle Database 12c $ORACLE_HOME/apex/ directory to a directory outside of the Oracle home, such as the temporary directory on your system to execute it later
• - If you have created objects based on fixed objects, then drop these objects to avoid possible ORA-00600 errors. You can re-create these objects after the downgrade.
• - If you are downgrading a cluster database, then shut down the instance completely and change the CLUSTER_DATABASE initialization parameter to FALSE.
  After the downgrade, you must set this parameter back to TRUE.

Once the above prerequisites have been met the downgrade can be proceed.

Downgrade steps for the database

1) Check all the db components are valid. Downgrade is only possible from a successfully upgraded database. To verify db component status, kindly execute below query

Connect as SYS user to the database 

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) Verify there are no sys/system invalid objects

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

If count is zero, it is ok to proceed with downgrade.

If there are invalid objects execute utlrp.sql multiple times, if the objects cannot be resolved into a valid state then you cannot proceed with the downgrade.  Open an SR or post a thread to the DBA community for assistance.

Alternatively for 1 and 2 run the script in:

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

3) Shutdown the database

Shutdown immediate

4)  Take a backup of 12c database

5)  Start the database in downgrade mode

Startup downgrade;

6)  Execute downgrade scripts

Sql> Spool downgrade.log
Sql> @$ORACLE_HOME/rdbms/admin/catdwgrd.sql

Note:
$ORACLE_HOME should be pointing to 12c home

The catdwgrd.sql script downgrades all Oracle Database components in the database to the supported major release or patch release from which you originally upgraded

Sql> spool off
Sql> shutdown immediate
Exit SQL Plus
Sql> exit;

7) If operating system is LINUX/UNIX:

Change the following environment variables to source database to which it is downgrading:

ORACLE_HOME

PATH

Edit /etc/oratab or /var/opt/oracle/oratab to change

Map the database to source database oracle home

If your operating system is Windows, then complete the following steps:

a. Stop all Oracle services, including the OracleServiceSID Oracle service of the Oracle Database 12c database, where SID is the instance name.

For example, if your SID is ORCL, then enter the following at a command prompt:

C:\> NET STOP OracleServiceORCL

b. Delete the Oracle service at a command prompt by issuing the ORADIM command. If prompted, enter the password for the standard user account that is active on this Windows system.

For example, if your SID is ORCL, then enter the following command:

C:\> ORADIM -DELETE -SID ORCL

c. Create the Oracle service of the database that you are downgrading at a command prompt using the ORADIM command.

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

8) Restore configuration files

Restore the configuration files (Password file, parameter file and so on) to downgrade release ORACLE_HOME.

9) If this is an Oracle RAC database, execute the following command to return the database to single instance mode:

SET CLUSTER_DATABASE=FALSE

10) Execute catrelod scripts from the downgrade release $ORACLE_HOME/rdbms/admin directory.

Start sqlplus and connect to the database instance as user SYS with sysdba privileges and start the database in upgrade mode:

: cd $ORACLE_HOME/rdbms/admin
: sqlplus
sql> connect sys as sysdba
sql> startup upgrade
sql> spool catrelod.log
sql> @?/rdbms/admin/catrelod.sql
sql> spool off

The catrelod.sql script reloads the appropriate version for each of the database components in the downgraded database.

11) Run the utlrp.sql script:

SQL> @utlrp.sql
Sql> exit;

The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on.

12) Check the status of the downgraded database:

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) 

This sql script is a set of select statements intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade.
The script will create a file called db_upg_diag_<sid>_<timestamp>.log.

13) After downgrade, you may find invalid QT views under sys schema. This is because views have selected the wrong columns from the base table. You need to recreate these views.

Refer to note:

Note 1520209.1 QT_*BUFER Views Invalid after downgrade from 12C 

Post Downgrade Steps:

1) If you are downgrading to Oracle Database 11g Release 1 (11.1.0.7) and you have Oracle Application Express in your database, then change to the directory to which you had copied the apxrelod.sql script (in pre-downgrade steps).
Manually reload Oracle Application Express by running the apxrelod.sql script:

SQL> @apxrelod.sql 

Running the apxrelod.sql script avoids package APEX_030200.WWV_FLOW_HELP being INVALID due to the following error:

PLS-00201: identifier 'CTX_DDL' must be declared 

2) If Oracle Label security was enabled in database, execute below scripts

  a. Copy the olstrig.sql script from the Oracle home under Oracle Database 12c to the Oracle home of the release to which the database is to be downgraded.

  b. From the Oracle home of the release you are downgrading to, run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies:     

SQL> @olstrig.sql 

3) If you are downgrading a cluster database, then you must run the following command to downgrade the Oracle Clusterware database configuration:

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

where db-unique-name is the database name (not the instance name), oraclehome is the location of the old Oracle home for the downgraded database, and to_version is the database release to which the database is downgraded

******************************************************************************