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

shows which releases to use when exporting data from releases earlier than 10.2 and then importing that data into release 10.2.