8
Upgrading and Downgrading

This chapter contains information about upgrading from your current version 8 release to a new version 8 release. This chapter also contains information about downgrading a version 8 database system to version 7.

The information in this chapter only applies to version 8 installations of Oracle. If your current release is pre-version 8, such as version 7 or version 6, and you want to migrate to version 8, follow the instructions at the beginning of this book, starting with Chapter 1, "Migration Overview".

The following topics are covered in this chapter:

• Upgrading to a New Version 8 Release
• Product Configurations and Upgrading
• Upgrading the Advanced Queuing Option
• Downgrading

  See Also: Some aspects of upgrading and downgrading are platform-specific. See your platform-specific Oracle documentation for additional instructions about these operations on your platform. In addition, this chapter does not discuss using the Installation Utility to upgrade, but that is an option for you. Again, see your platform-specific Oracle documentation for details.

Upgrading to a New Version 8 Release

This section guides you through the process of upgrading to the new version 8 release from release 8.0.3.

WARNING: If you are upgrading from Oracle8 Enterprise Edition to Oracle8 (formerly Workgroup Server), before you upgrade, you must modify any applications that use the advanced features of Oracle8 Enterprise Edition so that they do not use these advanced features. See Getting to Know Oracle8 and the Oracle8 Enterprise Edition and "Product Configurations and Upgrading" on page 8-4 for more information about the differences between the editions.

Complete the following steps to upgrade your current version 8 database:

1. Start Server Manager by entering the following:

   SVRMGRL
   

1. Run SHUTDOWN NORMAL on the version 8 database:

   SVRMGR> SHUTDOWN NORMAL
   

1. Perform a full offline backup of the database.
2. Install the new version 8 release.

   Note: Installation is platform-specific. For installation instructions, see your version 8 platform-specific installation documentation and the version 8 README for your platform.

3. Set the COMPATIBLE parameter in your INIT.ORA file.

   See Also: "COMPATIBLE Parameter" on page C-2 for information.

4. Run CONNECT INTERNAL:

   SVRMGR> CONNECT INTERNAL
   

5. Run STARTUP RESTRICT:

   SVRMGR> STARTUP RESTRICT
   

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

   SVRMGR> SPOOL CATOUTU.LOG 
   

1. Run CAT8004.SQL:

   SVRMGR> @CAT8004.SQL 
   

The CAT8004.SQL script creates and alters certain dictionary tables. It also runs the CATALOG.SQL and CATPROC.SQL scripts, which create the system catalog views and all the necessary packages for using PL/SQL.

See Also: Oracle8 Reference for a complete list and descriptions of available scripts, if you want to create additional data dictionary structures.

1. If the Oracle system has Advanced Replication installed, run the following catalog script supplied with your new release:

   SVRMGR> @CATREP.SQL
   

1. If the Oracle system has Parallel Server installed, run the following catalog script supplied with your new release:

   SVRMGR> @CATPARR.SQL
   

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

   SVRMGR> SPOOL OFF
   

Then, check the spool file and verify that every package and procedure compiled successfully. You named the spool file in Step 8; the suggested name was CATOUTU.LOG. Correct any problems you find in this file.

1. Run ALTER SYSTEM DISABLE RESTRICTED SESSION:

   SVRMGR> ALTER SYSTEM DISABLE RESTRICTED SESSION
   

2. Run UTLCONST.SQL to check for bad date constraints.

   Note: If you already ran UTLCONST.SQL after you migrated to a previous version 8 release, you need not run it again. However, running the script many times will not damage your system; therefore, if you are unsure about whether it has been run on your system, you should run it now.

   To run UTLCONST.SQL, issue the following commands:

   SVRMGRL> SPOOL UTLRESULT.LOG 
   SVRMGRL> @UTLCONST.SQL 
   SVRMGRL> SPOOL OFF
   

A bad date constraint involves invalid date manipulation. An invalid date manipulation is one that implicitly assumes the century in the date, causing problems at the year 2000. The UTLCONST.SQL script runs through all of the check constraints in the database and sets constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end.

After you run the script, the UTLRESULT.LOG log file includes all the constraints that have invalid date constraints.

Note: UTLCONST.SQL does not correct bad constraints, but it does disable them. You should either drop the bad constraints or recreate them after you make the necessary changes.

Product Configurations and Upgrading

Release 8.0.4 of the Oracle server is offered in two product configurations:

• Oracle8
• Oracle8 Enterprise Edition

  See Also: Getting to Know Oracle8 and the Oracle8 Enterprise Edition for information about the differences between Oracle8 and the Oracle8 Enterprise Edition and the options and features that are available to you.

If you are upgrading to Oracle8, instead of Oracle8 Enterprise Edition, you may lose certain options and features that were available to you in your previous release. The following table describes the functionality changes in such an upgrade:

Lost Option or Feature	Functionality Change
Partitioning	PARTITION clause in CREATE TABLE / INDEX / CLUSTER is disabled. PDML is turned off so that all operations are done serially, not in parallel.
Objects	CREATE TYPE and ALTER TYPE REPLACE are disabled. CREATE VIEW ... OF (i.e. object view) also is disabled.
Parallel Server	Startup SHARED is disabled.
Advanced Replication	Updateable snapshots are disabled. N-way master snapshots are disabled.
Bit-mapped Indexes	CREATE BITMAP INDEX statement is disabled. Bit-mapped indexes are not used. Run-time subquery optimization is turned off.
Connection Multiplexing	An Oracle error occurs at startup if the INIT.ORA file specifies multiplexing.
Connection Pooling	An Oracle error occurs at startup if the INIT.ORA file specifies pooling.
Database Queuing	Queue and dequeue procedures are disabled.
Instead-of Triggers	INSTEAD OF clause in CREATE TRIGGER statement is disabled.
Incremental Backup and Recovery	Recovery Manager (rman) disables incremental backup and recovery.
Parallel Backup and Recovery	Recovery Manager (rman) disables parallel backup and recovery.
Parallel Execution	Parallel CREATE INDEX and CREATE TABLE AS SELECT are disabled.
Parallel Load	Parallel load is disabled.
Point-in-time Tablespace Recovery	ALTER DATABASE MOUNT CLONE statement is disabled.

Upgrading the Advanced Queuing Option

The operational interface in Oracle Advanced Queuing (AQ) 8.0.4 is backward compatible with 8.0.3 Oracle AQ interface.

Note: If you have not started the release 8.0.4 database with a setting of to 8.0.4.0.0 for the COMPATIBLE parameter, do so now to use the features described in this section. See "COMPATIBLE Parameter" on page C-2 for more information.

New Fields Enabled for the AQ$_AGENT Data Type

In the latest release, the address field is enabled for the AQ$_AGENT datatype. Consequently, it is now possible for this field to be specified wherever an interface takes an Agent as an argument - such as in the recipient list of the message properties, and the DBMS_AQADM.ADD_SUBSCRIBER administrative interface.

The Extended Address Field

The address field in the AQ$_AGENT datatype has been extended to 1024 bytes. To use the extended address field, complete the following steps:

1. Export the contents of the existing queues using the Export Utility.

1. Run CATNOQUEUE.SQL to drop the existing dictionary and queue tables:

   SVRMGRL> @CATNOQUEUE.SQL 
   

1. Run CATQUEUE.SQL to redefine the new types and dictionary tables:

   SVRMGRL> @CATQUEUE.SQL 
   

1. Import the queues you exported using the Import Utility.

   Note: If your application does not require you to extend the address field, you need not complete these steps.

New Dictionary Tables

The upgrade script for release 8.0.4 (CAT8004.SQL) creates the additional dictionary tables:

• SYSTEM.AQ$_SCHEDULES
• SYS.AQ$_MESSAGE_TYPES
• SYS.AQ$_QUEUE_STATISTICS

Downgrading

The term downgrading is used to describe transforming an Oracle database into a previous release of the same version, such as transforming a database from release 8.0.4 to release 8.0.3, and downgrading also is used to describe transforming an Oracle database into a previous version, such as transforming a database from version 8 to version 7. The following sections contain instructions for both cases.

WARNING: If you are downgrading from Oracle8 Enterprise Edition to Oracle8 (formerly Workgroup Server), before you downgrade, you must modify any applications that use the advanced features of Oracle8 Enterprise Edition so that they do not use these advanced features. See Getting to Know Oracle8 and the Oracle8 Enterprise Edition for more information about the differences between the editions.

Downgrading from Release 8.0.4 to Release 8.0.3

Complete the following steps to downgrade your release 8.0.4 database to release 8.0.3:

1. Shutdown the release 8.0.4 database by issuing the following command:

   SVRMGR> SHUTDOWN NORMAL
   

Issue the command for all instances if you are running Parallel Server.

1. Perform a full offline backup.

   See Also: Oracle8 Backup and Recovery Guide for more information.

2. Open the release 8.0.4 database.
3. Check your COMPATIBLE parameter setting by issuing the following command:

   SVRMGR> SELECT name, value, description FROM v$parameter
   
   WHERE name="compatible";
   
   

If COMPATIBLE is set to 8.0.3.0.0 or 8.0.0.0.0, skip to Step 10, but, if COMPATIBLE is set to 8.0.4.0.0, continue with Step 5.

1. If you are using propagation in the Advanced Queuing Option, disable propagation; otherwise, move on to Step 6.

   See Also: "Propagation in Advanced Queuing (AQ)" on page C-3 for more information about this feature.

   To disable propagation in the Advanced Queuing Option, complete one of the following procedures:

   • Wait for all messages to be propagated successfully; then, use DBMS_AQDM.UNSCHEDULE_PROPAGATION() to unschedule all propagations.
   • Use DBMS_AQDM.UNSCHEDULE_PROPAGATION() to unschedule all propagations; then, delete all messages that are supposed to be propagated from the queue tables.

     See Also: Oracle8 Application Developer's Guide for information about DBMS_AQDM.UNSCHEDULE_PROPAGATION().

2. Run ALTER DATABASE RESET COMPATIBILITY:

   SVRMGR> ALTER DATABASE RESET COMPATIBILITY
   

1. Run SHUTDOWN NORMAL:

   SVRMGR> SHUTDOWN NORMAL
   

WARNING: Do not open the database using COMPATIBLE=8.0.4.0.0 after completing the step.

1. Set the COMPATIBLE parameter in the INIT.ORA file to the following:

   COMPATIBLE=8.0.0.0.0
   

1. Open the database to ensure that it is compatible with release 8.0.3.
2. Run CAT8004D.SQL:

   SVRMGR> @CAT8004D.SQL 
   

1. Run SHUTDOWN NORMAL:

   SVRMGR> SHUTDOWN NORMAL
   

1. Install the 8.0.3 release using your release 8.0.3 installation media.

   Note: Installation is platform-specific. For installation instructions, see your release 8.0.3 platform-specific installation documentation and the release 8.0.3 README for your platform.

2. Run CONNECT INTERNAL:

   SVRMGR> CONNECT INTERNAL
   

1. Run STARTUP RESTRICT:

   SVRMGR> STARTUP RESTRICT
   

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

   SVRMGR>SPOOL CATOUTD.LOG 
   

1. Run CATALOG.SQL:

   SVRMGR>@CATALOG.SQL 
   

1. Run CATPROC.SQL:

   SVRMGR>@CATPROC.SQL 
   

1. If the Oracle system has Advanced Replication installed, run the following catalog script supplied with the 8.0.3 release:

   SVRMGR>@CATREP.SQL
   

1. If the Oracle system has Parallel Server installed, run the following catalog script supplied with the 8.0.3 release:

   SVRMGR>@CATPARR.SQL
   

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

   SVRMGR> SPOOL OFF 
   

Then, check the spool file and verify that every package and procedure compiled successfully. You named the spool file in Step 15; the suggested name was CATOUTD.LOG. Correct any problems you find in this file.

Downgrading Version 8 to Release 7.x

A version 8 database can be downgraded to a version 7 database (such as release 7.3). However, few downgrade paths are available, and the necessary procedures may require a great deal of time and effort.

Oracle does not support downgrading from version 8 to version 7 using the version 8 Migration Utility, and version 8 provides no other facilities specifically for downgrading.

The procedure for downgrading depends on whether the version 8 database contains new data that must be preserved. Use the procedure that applies to your version 8 database:

• Downgrading a Version 8 Database That Contains No New Data
• Downgrading a Version 8 Database That Contains New Data

Downgrading a Version 8 Database That Contains No New Data

If the version 8 database contains no new data that needs to be preserved, simply restore the complete backup of the previous release 7.x source database and open it again. Make sure the restore includes the initialization parameters that were used in the previous release 7.x database.

Downgrading a Version 8 Database That Contains New Data

If the version 8 database contains new data that needs to be preserved, complete the following steps:

1. Use version 7 Export Utility with the CATEXP7.SQL-created views installed to export the part of the version 8 database containing the new data.

   See Also: Oracle8 Utilities for more information about performing a version 7 export from version 8.

1. Restore the complete backup of the previous version 7 database, making sure the restore includes the previous initialization parameters.
2. Open the restored database.
3. Use the version 7 Import Utility to import the file previously exported from the version 8 database into the restored version 7 database.

Several other methods are available for sending table data from the version 8 database back to the version 7 database. These methods of returning data to version 7 are relatively simple if only a few tables have been updated using version 8. However, copying an entire database of tables can be a long and complicated task; therefore, you should decide whether you need to return to version 7 before you update many tables using version 8.

The following alternate methods are available for downgrading a version 8 database to version 7:

• Use SQL*Plus to create non-Oracle text files from the new version 8 data and then use SQL*Loader to load the data back into the version 7 database.
• Use the SQL*Plus COPY command to copy the new data from the version 8 database tables into the tables in the earlier version 7 database.

  See Also: The SQL*Plus User's Guide and Reference for more information about the COPY command.

• If you are still running the earlier version 7 database, re-create the table where data has been added or modified (using CREATE TABLE ... AS SELECT) by selecting the data in the version 8 database table through a distributed query from that version 7 database using a database link.

  See Also: Oracle8 SQL Reference for more information on the AS clause of the CREATE TABLE command.

---