13
Downgrading to Release 8.1

The information in this chapter only applies to release 9.0.1 installations of Oracle. The term downgrading describes transforming an Oracle database into a previous release of the same version, such as transforming a database from release 8.1.7 to release 8.1.5. The term downgrading also describes transforming an Oracle database into a previous version, such as transforming a database from Oracle9i to Oracle7. This chapter describes downgrading to release 8.1 of Oracle. If you want to downgrade to Oracle7 or release 8.0, then see Chapter 15, "Downgrading to an Older Release of Oracle".

This chapter covers the following topics:

• Perform a Full Offline Backup

• Remove Incompatibilities

• Reset Database Compatibility

• Downgrade the Database

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

Perform a Full Offline Backup

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

See Also: Oracle9i User-Managed Backup and Recovery Guide for more information

Remove Incompatibilities

The process of removing incompatibilities depends on the release to which you are downgrading. First, check the compatibility level of your database to see if your database might have incompatibilities with the release to which you are downgrading.

Checking the Compatibility Level of Your Database

If the compatibility level of your database is higher than the release to which you are downgrading, then your database may have incompatibilities with the previous release that must be removed before you downgrade. Your compatibility level matches 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';

You do not need to remove incompatibilities if the COMPATIBLE parameter is set to the release to which you are downgrading or lower. For example, if you are downgrading to release 8.1.6 and the COMPATIBLE parameter is set to 8.1.6 or lower, then you do not need to remove incompatibilities. In this case, no incompatibilities exist in your database with the release to which you are downgrading, and you can skip the rest of this section and procede to "Downgrade the Database".

However, if the COMPATIBLE parameter is set higher than the release to which you are downgrading, then some incompatibilities may exist. For example, if you are downgrading to release 8.1.7 and COMPATIBLE is set to 9.0.0 or higher, then incompatibilities may exist.

Follow the instructions in Chapter 14, "Removing Incompatibilities Before Downgrading to Release 8.1" to remove incompatibilities with the release to which you are downgrading.

Reset Database Compatibility

After you have removed all of the incompatibilities with the release to which you are downgrading, reset the compatibility level of the database to the prior release by completing the following steps:

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

2. If you are using any initialization parameters that were added in a release higher than the release to which you are downgrading, then remove them from your initialization parameter file.

   See Also: Appendix B, "Changes to Initialization Parameters" for lists of parameters added in each release

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

4. Start SQL*Plus.

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

6. Start up the instance in RESTRICT mode:

   SQL> STARTUP RESTRICT
   
   

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

7. If you plan to lower the value of COMPATIBLE from 9.0.0 to 8.1.x, then run utlpitl.sql:

   SQL> @utlpitl.sql
   
   

   The utlpitl.sql script removes PDML ITL incompatibilities.

8. Run ALTER DATABASE RESET COMPATIBILITY:

   SQL> ALTER DATABASE RESET COMPATIBILITY;
   
   

   See Also: "About ALTER DATABASE RESET COMPATIBILITY" for more information.

9. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   
   

10. Exit SQL*Plus.

11. Set the COMPATIBLE initialization parameter in the initialization parameter file to match the release to which you are downgrading.

    For example, if you are downgrading to release 8.1.5, then set the COMPATIBLE parameter to the following:

    COMPATIBLE = 8.1.5
    
    

12. Open the database to ensure that it is compatible with the release you specified with the COMPATIBLE parameter.

    If your database fails to open, then some incompatibilities still exist. If so, then reset the COMPATIBLE parameter to a higher setting. Remove the incompatibilities and attempt to reset database compatibility again. All incompatibilities with the database to which you are downgrading must be removed before you proceed with the downgrade process.

    See Also: "Remove Incompatibilities" for information about removing incompatibilities.

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. See "Remove Incompatibilities" if you have not yet removed incompatibilities.

Complete the following steps to downgrade your release 9.0.1 database to a previous release:

1. Log in to the system as the owner of the Oracle home directory of the release from which you are downgrading.

2. Copy the following files from the ORACLE_HOME/rdbms/admin directory of the release from which you are downgrading to a directory outside of the Oracle home, such as the temporary directory on your system:
   • utlip.sql

   • utlrp.sql

   If you have Java installed, copy one or more of the following files from the ORACLE_HOME/javavm/install directory of the release from which you are downgrading to a directory outside of the Oracle home, such as the temporary directory on your system:

   • jvmd815.sql (if you are downgrading to release 8.1.5)

   • jvmd816.sql (if you are downgrading to release 8.1.6)

   • jvmd817.sql, jisd817.sql, jspd817.sql (if you are downgrading to release 8.1.7)

   Also, if you have XDK for Java installed, copy one of the following files from the ORACLE_HOME/xdk/admin directory of the release from which you are downgrading to a directory outside of the Oracle home, such as the temporary directory on your system:

   • xmld817.sql (if you are downgrading to release 8.1.7)

   Make a note of the new location of these files. You may need them later in the downgrade process.

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

4. Start SQL*Plus.

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

6. Start up the instance in RESTRICT mode:

   SQL> STARTUP RESTRICT
   
   

   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 catoutd.log
   
   

   If you want to see the complete detailed output of the script you will run, then you can also issue a SET ECHO ON statement:

   SQL> SET ECHO ON
   
   

8. Run dold_release.sql, where old_release refers to the release to which you are downgrading. See Table 13-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 13-1  Downgrade Scripts

   Downgrading To	Run Script
   8.1.7	d0801070.sql
   8.1.6	d0801060.sql
   8.1.5	d0801050.sql

   Note: If the release to which you are downgrading is not included in Table 13-1, then see the README files in the new installation for the correct downgrade script to run.

   The following are notes about running the script:

   • You must use the version of the script included with the release from which you are downgrading.

   • You must run the script in the environment of the release from which you are downgrading.

   • You only need to run one script, even if your downgrade spans several releases. For example, if you are downgrading to release 8.1.5, then you need to run only d0801050.sql.

   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.

   See Also: "Running Scripts" for information about the types of errors to look for when you run a script.

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

   If you specified SET ECHO ON, then you may want to SET ECHO OFF now:

   SQL> SET ECHO OFF
   
   

10. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE
    
    

    If you are using Oracle9i Real Application Clusters, then shutdown all instances.

    Note: Oracle9i Real Application Clusters is an new, breakthrough software architecture with scalability and high availability features that exceed the capabilities of previous Oracle cluster-enabled software releases.

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

      Note: For Oracle9i Real Application Clusters, perform this step on all nodes.

      See Also: Your operating system-specific Oracle9i 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 Oracle service OracleServiceSID of the database you are downgrading, where SID is the instance name.

       For example, if your SID is ORCL, then enter the following at an MS-DOS prompt:

       C:\> NET STOP OracleServiceORCL
       
       

       See Also: Your Administrator's Guide for Windows for information about stopping services.

    2. Delete the Oracle service at the MS-DOS command prompt by issuing the ORADIM command. For example, if your SID is ORCL, then enter the following MS-DOS command:

       C:\> ORADIM -DELETE -SID ORCL
       
       

14. Install the release to which you are downgrading using the installation media for that release.

    For example, if you are downgrading to release 8.1.5, then use the release 8.1.5 installation media to install the release 8.1.5 distribution of Oracle.

    You must install the release 8.1 software in a new Oracle home that is separate from the 9.0.1 release.

    Note: Installation is operating system-specific. For installation instructions, see your operating system-specific installation documentation and the README for your operating system.

15. If your operating system is Windows, then complete the following steps:
    1. Shut down and restart your computer.

    2. Create the Oracle database service at the MS-DOS 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:

       SID	is the same SID name as the SID of the release 8.1 database you are downgrading.
       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 8.1.6, 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
       
       

16. If you are using a server parameter file to start up the instance, or if your initialization parameter file has an SPFILE (server parameter file) entry, then complete the following steps:
    1. Export the server parameter file to a traditional initialization parameter file:

       CREATE PFILE[=pfile-name] [FROM spfile-name];
       
       

       The initialization parameter file will be created as a text file. In an Oracle9i Real Application Clusters environment, it will contain all parameter settings of all instances.

    2. If you used the SPFILE parameter to specify a server parameter file, then change the SPFILE parameter to an IFILE parameter in the initialization parameter file used to start up the instance. Make sure the IFILE parameter points to the initialization parameter file that you exported from the server parameter file.

    3. If you are using Oracle9i Real Application Clusters, then create instance-specific initialization parameter files. Remove all instance-specific parameters from the initialization parameter file that you exported from the server parameter file.

       You can use the IFILE parameter in each instance-specific parameter file to point to the initialization parameter file that you exported from the server parameter file.

17. Copy configuration files to a location outside of the Oracle home of the release from which you are downgrading:
    1. If your initialization parameter file resides within the Oracle home of the release from which you are downgrading, then copy the initialization parameter file to a location outside of the Oracle home. By default Oracle looks for the initialization parameter file in ORACLE_HOME/dbs on UNIX and ORACLE_HOME\database on Windows platforms. The initialization parameter file can reside anywhere you wish, but it should not reside in the Oracle home of the release from which you are downgrading.

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

    3. If you have a password file that resides within the Oracle home of the release from which you are downgrading, 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 operating systems, 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 and Windows operating systems, sid is your Oracle instance ID.

       Note: For Oracle9i Real Application Clusters, perform this step on all nodes. Also, set the PARALLEL_SERVER initialization parameter to false. You can change it back to true after the downgrade operation is complete.

18. Copy the following files into the ORACLE_HOME/rdbms/admin directory of the release to which you are downgrading:
    • utlip.sql

    • utlrp.sql

    If you have Java installed, copy one or more of the following files to the ORACLE_HOME/javavm/install directory of the release to which you are downgrading:

    • jvmd815.sql (if you are downgrading to release 8.1.5)

    • jvmd816.sql (if you are downgrading to release 8.1.6)

    • jvmd817.sql, jisd817.sql, jspd817.sql (if you are downgrading to release 8.1.7)

    Also, if you have XDK for Java installed, copy one of the following files to the ORACLE_HOME/xdk/admin directory of the release to which you are downgrading:

    • xmld817.sql (if you are downgrading to release 8.1.7)

    You copied these files to a directory outside of the Oracle home of the release from which you are downgrading in Step 2.

19. Add the following initialization parameter to your initialization parameter file:

    _SYSTEM_TRIG_ENABLED = false
    
    

    This initialization parameter should be removed from your initialization parameter file after the downgrade is complete.

20. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the release to which you are downgrading.

21. If you are downgrading to an 8.1 release, start Server Manager. Otherwise, start SQL*Plus.

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

23. Start up the instance in RESTRICT mode:

    STARTUP RESTRICT
    
    

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

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

    SPOOL catoutd2.log
    
    

    If you want to see the complete detailed output of the script you will run, then you can also issue a SET ECHO ON statement:

    SET ECHO ON
    
    

25. Run utlip.sql:

    @utlip.sql
    
    

    The utlip.sql script invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will happen in the format required by the database. It also reloads packages STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations.

    See Also: "Changing Word-Size" for more information about changing word-size.

26. Run catalog.sql:

    @catalog.sql
    
    

27. Run catproc.sql:

    @catproc.sql
    
    

28. Run catrep.sql:

    @catrep.sql
    
    

29. If the Oracle system has Oracle Parallel Server installed, then run the following catalog script supplied with the release to which you downgraded:

    @catparr.sql
    
    

30. If the Oracle system has Java installed, then run the appropriate downgrade scripts (copied to this directory in Step 18) to downgrade the Java component. When you run any of the scripts, replace ORACLE_HOME with the full path of the Oracle home directory of the release to which you downgraded.

    If you are downgrading to release 8.1.5, then run the following script:

    @ORACLE_HOME/javavm/install/jvmd815.sql
    
    

    If you are downgrading to release 8.1.6, then run the following script:

    @ORACLE_HOME/javavm/install/jvmd816.sql
    
    

    If you are downgrading to release 8.1.7, then run the following scripts:

    @ORACLE_HOME/javavm/install/jvmd817.sql
    @ORACLE_HOME/xdk/admin/xmld817.sql
    @ORACLE_HOME/javavm/install/jisd817.sql
    @ORACLE_HOME/javavm/install/jspd817.sql
    
    

31. Run utlrp.sql. This step is optional and can be done regardless of whether there was a change in word-size.

    @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. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future.

    Oracle Corporation highly recommends running utlrp.sql.

    If you receive compile-time errors while running utlrp.sql, you may need to run one or more catalog scripts supplied with the release to which you downgraded. For example, to re-create Heterogeneous Services data dictionary views, tables, and packages, run caths.sql:

    @caths.sql
    
    

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

    SPOOL OFF
    
    

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

    If you specified SET ECHO ON, then you may want to SET ECHO OFF now:

    SET ECHO OFF
    
    

33. Shut down the instance:

    SHUTDOWN IMMEDIATE
    
    

    Note: For Oracle Parallel Server, set the PARALLEL_SERVER initialization parameter to false. You can change it back to true after the downgrade operation is complete.

34. Exit Server Manager or SQL*Plus, depending on which you used to complete the downgrade.

35. Remove the following initialization parameter from your initialization parameter file:

    _SYSTEM_TRIG_ENABLED = false
    
    

    You added this initialization parameter to your initialization parameter file in Step 19.

Your database is now downgraded. Complete the procedures described in the following sections to finish downgrading specific components.

Regenerating Replication Support

If you are using Oracle Replication, then complete the actions described below based on whether the downgraded database is a master site or a materialized view site.

Master Site

If the downgraded database is a master site for one or more object groups, then complete the following steps to regenerate replication support:

1. Quiesce each object group.

2. Generate replication support for each replicated table in the group.

3. Resume master activity for the object group. If the masterdef site is at release 8.1, then make sure you specify GENERATE_80_COMPATIBLE=>TRUE in the GENERATE_REPLICATION_SUPPORT calls.

Materialized View Site

If the downgraded database is a materialized view site, then generate replication support for each updatable materialized view.

See Also: Oracle9i Replication for more information about generating replication support