7 Downgrading a Database Back to the Previous Oracle Database Release

Supported Releases for Downgrading

In Oracle Database 10g release 10.2, downgrading is supported back to release 10.1 and release 9.2. However, if the release number of your Oracle9i database is lower than release 9.2.0.6.0, then you should install the latest patch release for release 9.2; downgrading is not supported to releases prior to release 9.2.0.6.0. Likewise, if the release number of your Oracle10g database is lower than 10.1.0.4, then you should install the latest patch for release 10.1.

If you have Messaging Gateway or Workspace Manager in your database, be aware that neither of them are part of Oracle Database patch sets. Therefore, you will need to separately apply all relevant patches to release 9.2 or 10.1 before downgrading.

Check for Incompatibilities

Check the compatibility level of your database to see if your database might have incompatibilities that prevent you from downgrading. If the compatibility level of your release 10.2 database is 10.2.0 or higher, then you will not be able to downgrade. Your compatibility level is determined by the setting of the COMPATIBLE initialization parameter. Check your COMPATIBLE initialization parameter setting by issuing the following SQL statement:

SQL> SELECT name, value, description FROM v$parameter
         WHERE name='compatible';

If you are downgrading to release 10.1, the COMPATIBLE initialization parameter must be set to 10.1.0 or lower.

If you are downgrading to release 9.2, the COMPATIBLE initialization parameter must be set to 9.2.0.

Perform a Full Offline Backup

Perform a full offline backup of your release 10.2 database before you downgrade.

Downgrade the Database

Complete the following steps to downgrade your release 10.2 database to the Oracle Database release from which you originally upgraded:

1. Log in to the system as the owner of the release 10.2 Oracle home directory.

2. Note: This step is required only if the Enterprise Manager Database Control is already configured for the database.

   Stop the Database Control, as follows:

   1. Set the ORACLE_SID environment variable to the databaseSid

   2. Execute the following command: ORACLE_HOME/bin/emctl stop dbconsole

   If the database being downgraded is a Real Application Clusters (RAC) database, this step should be performed for all the instances.

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

   Note:

   If you are downgrading a cluster database, shut down the instance completely and change the CLUSTER_DATABASE parameter to false. After the downgrade, you must set this parameter back to true.

4. Start SQL*Plus.

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

6. Start up the instance in DOWNGRADE mode:

   SQL> STARTUP DOWNGRADE
   

   You may need to use the PFILE option to specify the location of your initialization parameter file.

7. Set the system to spool results to a log file for later verification of success:

   SQL> SPOOL downgrade.log
   

8. Run catdwgrd.sql:

   SQL> @catdwgrd.sql
   

   The following are notes about running the script:

   • You must use the version of the script included with release 10.2.

   • You must run the script in the release 10.2 environment.

   • The script downgrades all Oracle Database components in the database to the major 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 will be displayed and the downgrade will not complete. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded.

9. Turn off the spooling of script results to the log file:

   SQL> SPOOL OFF
   

   Then, check the spool file and verify that there were no errors generated during the downgrade. You named the spool file in Step 7; the suggested name was downgrade.log. Correct any problems you find in this file and rerun the downgrade script if necessary.

10. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE
    

11. Exit SQL*Plus.

12. If your operating system is UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:

    • ORACLE_HOME

    • PATH

    • ORA_NLS33

    • LD_LIBRARY_PATH

    See Also:

    Your operating system-specific Oracle Database 10g installation documents for information about setting other important environment variables on your operating system

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

    1. Stop all Oracle services, including the OracleServiceSID Oracle service of the release 10.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
       

       See Also:

       Your Administrator's Guide for Windows for information about stopping services

    2. 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
       

    3. Create the Oracle service of the database to which 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
       

       This syntax includes the following variables:

       Variable	Description
       SID	is the same SID name as the SID of the database being downgraded.
       PASSWORD	is the password for the database instance. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.
       USERS	is the maximum number of users who can be granted SYSDBA and SYSOPER privileges.
       ORACLE_HOME	is the Oracle home directory of the database to which you are downgrading. Ensure that you specify the full path name with the -PFILE option, including drive letter of the Oracle home directory.

       
       

       For example, if you are downgrading to release 9.2, if your SID is ORCL, your PASSWORD is TWxy579, the maximum number of USERS is 10, and the ORACLE_HOME directory is C:\ORANT, then enter the following command:

       C:\> ORADIM -NEW -SID ORCL -INTPWD TWxy579 -MAXUSERS 10
            -STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA
       

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

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

16. Start SQL*Plus.

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

18. Start up the instance:

    SQL> STARTUP MIGRATE
    

19. Set the system to spool results to a log file for later verification of success:

    SQL> SPOOL reload.log
    

20. Run catrelod.sql:

    SQL> @catrelod.sql
    

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

21. Turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

    Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 19; the suggested name was reload.log. Correct any problems you find in this file and rerun the appropriate script if necessary.

    ORA-22308: operation not allowed on evolved type errors in the spool file may safely be ignored.

22. Shut down and restart the instance for normal operation:

    SQL> SHUTDOWN IMMEDIATE
    SQL> STARTUP
    

    You may need to use the PFILE option to specify the location of your initialization parameter file.

23. Do this step if the database is configured for Oracle Label Security. Copy the olstrig.sql script from the 10.2 Oracle Home to the version to which the database will be downgraded. Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies. (See Oracle Database Enterprise User Administrator's Guide for more information.)

    SQL> @olstrig.sql
    

24. Run utlrp.sql:

    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.

25. Exit SQL*Plus.

Your database is now downgraded.

Perform Post-Downgrade Tasks

Note: This step is required only if you are downgrading to 10.1 and some form of Enterprise Manager is configured on the host.

Execute the emca -restore command with the appropriate options to restore the 10.1 environment. The options that you specify depend on whether the database being downgraded is a Real Application Clusters (RAC) database or an Automatic Storage Management (ASM) database, as follows:

• Non-RAC, Non-ASM database - 102Home/bin/emca -restore db

• RAC, Non-ASM database - 102Home/bin/emca -restore db -cluster

• Non-RAC, ASM instance - 102Home/bin/emca -restore asm

• RAC ASM instance(s) - 102Home/bin/emca -restore asm -cluster

• Non-RAC, Database and ASM instance - 102Home/bin/emca -restore db_asm

• RAC, Database and ASM instance(s) - 102Home/bin/emca -restore db_asm -cluster

When you execute these commands, you will be prompted to enter some or all of the following information, depending on what needs to be restored:

• Oracle Home (ORACLE_HOME): Current Oracle Home

• Source Oracle Home (SRC_OH): Oracle Home from where the database will be running after downgrade

• Port (PORT): Database port after downgrade

• ASM Port (ASM_PORT): ASM instance port after downgrade

• SID (SID): SID for the non-RAC database

• Database unique name (DB_UNIQUE_NAME): Database unique name for the RAC database

• ASM Oracle Home (ASM_OH): Oracle Home from where the ASM instance will be running after downgrade

• ASM SID (ASM_SID): ASM instance SID for non-RAC ASM instance