Skip Headers
Oracle® Database Upgrade Guide
10g Release 2 (10.2)

Part Number B14238-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Downgrading a Database Back to the Previous Oracle Database Release

This chapter guides you through the process of downgrading a database back to the previous Oracle Database release. This chapter covers the following topics:

See Also:

Some aspects of downgrading are operating system-specific. See your operating system-specific Oracle documentation for additional instructions about downgrading on your operating system.

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.

Note:

You do not need to first upgrade your previous database to release 9.2.0.6.0 or later, but the release 9.2.0.6.0 or later software must be installed before the downgrade from release 10.2.

Likewise, you do not need to first upgrade your previous database to release 10.1.0.4 or later, but the release 10.1.0.4 or later software must be installed before the downgrade from 10.2.

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.

See Also:

Oracle Database Backup and Recovery Basics for more information

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:

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: