7 Downgrading a Database Back to the Previous Oracle Database Release

Supported Releases for Downgrading

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

In addition, if the database being downgraded has Messaging Gateway installed, then you should install Messaging Gateway Bundled Patch 2 or later.

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.1 database is 10.0.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 9.2 and the COMPATIBLE initialization parameter is set to 9.2.0, then you will be able to downgrade.

Perform a Full Offline Backup

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

Downgrade the Database

Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section.

Complete the following steps to downgrade your release 10.1 database to the previous Oracle Database release:

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

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

3. Start SQL*Plus.

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

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

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

   SQL> SPOOL downgrade.log
   
   

7. Run dold_release.sql, where old_release refers to the release to which you are downgrading. See Table 7-1 to choose the correct script. Each script provides a direct downgrade to the release specified in the "Downgrading To" column.

   To run a script, enter the following:

   SQL> @dold_release.sql
   
   

   Table 7-1 Downgrade Scripts

   Downgrading To	Run Script
   9.2	d0902000.sql

   
   

   Note: This is the only release supported for downgrading.

   The following are notes about running the script:

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

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

   • The script downgrades all Oracle Database components in the database.

   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.

8. 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 6; the suggested name was downgrade.log. Correct any problems you find in this file and rerun the appropriate downgrade script if necessary.

9. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   
   

   If you are downgrading a cluster database, then shut down all instances.

10. Exit SQL*Plus.

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

    
    

    Note: If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.

    
    

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

12. 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.1 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 pathname 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
       
       

13. Copy configuration files from the release 10.1 Oracle home directory to the Oracle home of the release to which you are downgrading:

    1. Copy your parameter file from the release 10.1 Oracle home to the Oracle home of the release to which you are downgrading. By default Oracle looks for the parameter file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on Windows operating systems. The parameter file can reside anywhere you wish, but it should not reside in the release 9.2 Oracle home.

    2. If your parameter file has an IFILE (include file) entry and the file specified in the IFILE entry resides within the release 10.1 Oracle home directory, then copy the file specified by the IFILE entry to the Oracle home of the release to which you are downgrading. The file specified in the IFILE entry contains additional initialization parameters. After you copy this file, edit the parameter file to point to its new location.

    3. If you have a password file that resides within the release 10.1 Oracle home directory, then move or copy the password file to the Oracle home of the release to which you are downgrading. The name and location of the password file are operating system-specific. On UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid. On Windows operating systems, the default password file is ORACLE_HOME\database\pwdsid.ora. On both UNIX platforms and Windows operating systems, sid is your Oracle instance ID.

    
    

    Note: If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured. Set the CLUSTER_DATABASE initialization parameter to false. After the downgrade, you must set this initialization parameter back to true.

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

15. Start SQL*Plus.

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

17. Start up the instance:

    SQL> STARTUP MIGRATE
    
    

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

    SQL> SPOOL reload.log
    
    

19. Run catrelod.sql:

    SQL> @catrelod.sql
    
    

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

20. 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 18; 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.

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

    
    

    Note: If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.

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

23. Exit SQL*Plus.

Your database is now downgraded.