How To Downgrade From Database 11.2 To Previous Release

How To Downgrade From Database 11.2 To Previous Release [ID 883335.1]

Purpose:

This document is created for use as a guideline and checklist for downgrading a previously upgraded database from Oracle 11gR2 back to the previous release:  Oracle 10gR1, Oracle 10gR2, or Oracle 11gR1.

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.

Please note the following:

1. This process is intended to downgrade a database that was successfully upgraded to 11gR2.  It is not intended to back out from a failed upgrade.
2. You can only downgrade to the release and patch level you upgraded from. For example, if you upgraded from Oracle 10gR1 (10.1.0.5) to Oracle 11gR2 (11.2), then you can not downgrade to Oracle 10gR2 (10.2).  You can only downgrade to Oracle 10gR1 (10.1.0.5)
3. Downgrades to 9iR2 are not supported.  This is because in the upgrade process the compatible parameter is set to a minimum 10.1.0.  This prevents downgrades.  See Note 388604.1 : ORA-00201 while downgrading from 10gR2 to 10gR1 or 9iR2 for additional information.

Solution

Pre-Downgrade Steps:

1: Verify that all components and dictionary objects are valid and at the correct version for 11gR2.

set pagesize500
set linesize 100
       
select substr(comp_name,1,40) comp_name, status, substr version,1,10) version from dba_registry order by comp_name;
       
select substr(object_name,1,40) object_name,substr(owner,1,15) owner, object_type from  dba_objects where status='INVALID' order by owner,object_type;
       
select owner,object_type,count(*) from dba_objects where status='INVALID'  group by owner,object_type order by owner,object_type;

If any components or the Oracle provided objects are invalid then you will need to run utlrp.sql to re-validate them. This script may need to be run multiple times to validate all objects.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

When completed re-run the queries above to verify that any invalid objects are now valid.
2: If you have enabled Oracle Database Vault on your database, then you must:

a: Grant the Database Vault DV_PATCH_ADMIN role for the SYS account.
b: Disable Database Vault before downgrading the database.
From Note.761983.1: How to Install Database Vault Patches on top of 11.1.0.7

Disable the Oracle Database Vault triggers: 
      SQL> CONNECT DVSYS/DVSYS 
      SQL> ALTER TRIGGER DV_BEFORE_DDL_TRG DISABLE; 
      SQL> ALTER TRIGGER DV_AFTER_DDL_TRG DISABLE; 

3: If you set the Operating System Parameter ORA_TZFILE then unset it. If you do not unset the ORA_TZFILE variable, then connecting to the database might produce the following errors:

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

4: If you have run DBMS_DST package to upgrade the timezone version to a newer one, e.g., version 11 during post-upgrade steps of the 11gR2 upgrade, before you downgrade to your previous database release, you must install version 11 timezone files into the directory of  $ORACLE_HOME/ORACORE/ZONEINFO on your old Oracle database release.
For patches of new timezone version files on Oracle Database Release 11.1 or older, please refer to metalink  Note. 412160.1. Updated DST transitions and new Time Zones in Oracle Time Zone File patches.
5: Check the compatibility level of your database to see if the database might have incompatibilities that prevent you from downgrading.

If the compatibility level of your database is 11.2.0 or higher, then you are not able to downgrade.
If you are downgrading your database to 11gR1, then the COMPATIBLE initialization parameter must already be set to 11.0.0 or lower.
If you are downgrading your database to 10gR2, then the COMPATIBLE initialization parameter must already be set to 10.2.0 or lower.
If you are downgrading your database to 10gR1, then the COMPATIBLE initialization parameter must already be set to 10.1.0.

6: If you have Oracle Application Express on your database, then you must copy the apxrelod.sql file from the Oracle 11gR2 (11.2) ORACLE_HOME/apex/ directory to a directory outside of the Oracle home, such as the temporary directory on your system. Make a note of the new location of this file.

7: Perform a backup of your 11gR2 database before you downgrade.

Downgrade Steps for the Database

1: Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

2: If the Enterprise Manager Database Control is configured and running for the database then stop Database Control, as follows:

a: Set the ORACLE_UNQNAME environment variable to the database unique name.
b: Run the following command:ORACLE_HOME/bin/emctl stop dbconsole

3: If the database being downgraded is an Oracle Real Application Clusters (Oracle RAC) database, then perform this step on all instances.

a: If you are downgrading an Oracle RAC database to 10g Release 1 (10.1), then you must remove extra voting disks before shutting down the Oracle Clusterware stack.
To see the number of voting disks used and to list voting disk paths, run the following command: Oracle_Clusterware_Home/bin/crsctl query css votedisk
b: Remove each additional voting disk you find in the previous step by running the following command, where path is a voting disk path discovered in the previous step:                  Oracle_Clusterware_Home/bin/crsctl delete css votedisk path
Note: 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.
SET CLUSTER_DATABASE=FALSE

4: At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

5: Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges.

6: If the instance is currently running, shut it down
     SQL> SHUTDOWN IMMEDIATE

7: Start up the instance in DOWNGRADE mode:
     SQL> STARTUP DOWNGRADE
If needed specify the the location of your initialization parameter file.

8a: If you have Enterprise Manager configured in your database and a backup Enterprise Manager Database Control Data WAS TAKEN pre-upgrade, then:

Drop the SYSMAN schema: 
DROP USER sysman CASCADE; 

Note: After this step, MGMT* synonyms may be invalid. Follow the steps for "Restoring Oracle Enterprise Manager" in the post-upgrade section to restore the backup and validate the synonyms.

8b: If you have Enterprise Manager configured in your database and a backup Enterprise Manager Database Control Data WAS NOT TAKEN pre-upgrade, then:

Drop the Enterprise Manager users: 


Drop the repository user: 
DEFINE EM_REPOS_USER=SYSMAN @?/sysman/admin/emdrep/sql/core/latest/admin/admin_drop_repos_user.sql SYSMAN 

Drop roles and synonyms: 
DEFINE EM_REPOS_USER=SYSMAN @?/sysman/admin/emdrep/sql/core/latest/admin/admin_drop_synonyms.sql 

Drop the SYSMAN schema: 
DROP USER sysman CASCADE; 


Note: After this step the Enterprise Manager component is removed from the database instance.  The Enterprise Manager components can be re-created post-downgrade.  For additional information refer to Note 278100.1 :How To Drop, Create And Recreate DB Control In A 10g Database

9: Set the system to spool results to a log file for later verification of success:
     SQL> SPOOL downgrade.log
10: Run catdwgrd.sql:
     SQL> @catdwgrd.sql

The following are notes about running the script:
The script downgrades all Oracle Database components in the database to the major release or Oracle Database 11g patch release from which you originally upgraded.

If you encounter any problems when you run the script, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the script. You can rerun any of the scripts described in this chapter as many times as necessary.
If the downgrade for a component fails, then an ORA-39709 error is displayed and the SQL*Plus session terminates without downgrading the Oracle Database data dictionary. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded. You must identify and fix the problem before rerunning the catdwgrd.sql script.

11: Turn off the spooling of script results to the log file:
     SQL> SPOOL OFF 
Check the spool file and verify that there were no errors generated during the downgrade. You named the spool file in Step 9; the suggested name was downgrade.log. Correct any problems you find in this file and rerun the downgrade script if necessary.

12: Shut down the instance:
     SQL> SHUTDOWN IMMEDIATE

13: Exit SQL*Plus.

14: If your operating system is Linux or UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:
     ORACLE_HOME
     PATH
You should also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the downgraded Oracle home.

15: 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 11gR2 (11.2) 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. 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

For additional information on the ORADIM utility please refer to Note 61621.1 WINNT/WIN2000 Recreating Oracle Services and Instances from the Command Line

16: Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.

17: At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the previous release.

18: Start SQL*Plus.

19: Connect to the database instance as a user with SYSDBA privileges.

20: Start up the instance:
     SQL> STARTUP UPGRADE
21: Set the system to spool results to a log file for later verification of success:
     SQL> SPOOL reload.log
22: Run catrelod.sql:
     SQL> @catrelod.sql

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

23: If you are downgrading to Oracle Database 11g Release 1 (11.1.0.6), run the xsrelod.sql script:
     SQL> @xsrelod.sql

Running the xsrelod.sql script avoids the following error:
PLS-00306: wrong number or types of arguments in call to 'INVALIDATE_DSD_CACHE' DBMS_XS_DATA_SECURITY_EVENTS PL/SQL: Statement ignored

24: If you are downgrading to Oracle Database 10g Release 1 (10.1.0.5) and you have XDB in your database, then run the following script after running catrelod.sql:
     SQL> @dbmsxdbt.sql
25: If this is an Oracle RAC database, execute the following command to return the database to RAC mode:
     SQL> SET CLUSTER_DATABASE=TRUE
26: Turn off the spooling of script results to the log file:
     SQL> SPOOL OFF
27: Check the spool file and verify that the packages and procedures compiled successfully.
28: Shut down and restart the instance for normal operation:
     SQL> SHUTDOWN IMMEDIATE
     SQL> STARTUP 
29: Perform this step if the database is configured for Oracle Label Security and you are downgrading to Oracle Database 10g Release 1 (10.1).

a. Copy the olstrig.sql script from the Oracle Database 11g Release 2 (11.2) Oracle home to the Oracle home of the version to which the database is to be downgraded.
b. Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.
     SQL> @olstrig.sql See the Oracle Label Security Administrator's Guide for more information.

30: Run the utlrp.sql script:
     SQL> @utlrp.sql

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.

31: Some installed components may show earlier versions then were shown in the instance pre-upgrade. If you are downgrading to a version with a patchset installed, review and compare the patchset readme.txt file to the current component versions. If the downgraded components are at an earlier version then:

Re-start the instance in upgrade mode.
Run the catupgrd.sql script
Recheck the component versions to those shown in the patchset readme.

32: Exit SQL*Plus.
     Your database is now downgraded.
Post-Downgrade Steps
33: Restoring Oracle Enterprise Manager backups.
If a backup of Oracle Enterprise Manager data was taken pre-upgrade it can be restored. Please refer to Note 876353.1 How To Restore The Oracle Enterprise Manager Data To Downgrade The Single Instance Database To Previous/Source Release ?
If a backup was not taken, the Oracle Enterprise Manager can be re-created if desired.  Please refer to the following article to create/recreate the DB control Data. Note 278100.1 :How To Drop, Create And Recreate DB Control In A 10g Database
34: Enable Database Vault.
To enable Data Vault after a downgrade:

Connect to a SQL*Plus as a user who has been granted the DV_OWNER role and  issue the following statements:
ALTER TRIGGER DVSYS.DV_BEFORE_DDL_TRG ENABLE;
ALTER TRIGGER DVSYS.DV_AFTER_DDL_TRG ENABLE;

35: Reload Oracle Application Express
Change to the directory to which you copied the apxrelod.sql file (in step 3 of the downgrade steps), and manually reload Oracle Application Express by running the apxrelod.sql file. Please note: the database must be started in the UPGRADE mode to run the apxrelod.sql script.