Oracle9i Database Migration
Release 1 (9.0.1)
Part Number A90191-02
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback

Go to previous page Go to next page

7
i Release

This chapter contains information about upgrading your current release of Oracle to the new Oracle9i release. The information in this chapter only applies to release 8.0 and higher installations of Oracle. If your current release is version 6 or version 7 and you want to migrate to Oracle9i, then follow the instructions at the beginning of this book, starting with Chapter 2, "Overview of Migration".

This chapter covers the following topics:

Upgrade Paths

The path that you must take to upgrade your database to the new release depends on the release you are currently using. Table 7-1 contains the upgrade path required for each old release of Oracle. Use the upgrade path and the specified documentation to upgrade the release you are currently running.

Table 7-1 Upgrade Paths
Old Release  Upgrade Path 

8.0.3

8.0.4

8.0.5 

Direct upgrade is not supported. Complete the following steps to upgrade to the new release:

  1. Upgrade to release 8.0.6 using the instructions in the release 8.0.6 READMEMIG.doc file.

  2. Upgrade the release 8.0.6 database to the new release using the instructions in "Upgrading the Database to the New Oracle9i Release".

 

8.0.6

8.1.5

8.1.6

8.1.7 

Direct upgrade is supported. Upgrade to the new release using the instructions in "Upgrading the Database to the New Oracle9i Release". 

Upgrading the Database to the New Oracle9i Release

This section guides you through the process of upgrading your database to the new Oracle9i release.

Character Set Considerations

In Oracle9i, the SQL NCHAR datatypes (NCHAR, NVARCHAR2, and NCLOB) will be limited to the Unicode character set encoding (UTF8 and AL16UTF16) only. Any other version 8 character sets that were available under the NCHAR data type, including Asian character sets (for example, such as JA16SJISFIXED), will no longer be supported.

Before migrating your SQL NCHAR data to the new Unicode NCHAR, Oracle Corporation recommends that you analyze your SQL NCHAR data, using the Character Set Scanner for the identification of possible invalid character set conversion or data truncation.

See Also:

Oracle9i Globalization Support Guide for more information about the Character Set Scanner 

When you upgrade to Oracle9i, the value of the National Character Set of the upgraded database is set based on the value of the National Character Set of the version 8 database being upgraded.

If the old National Character Set is UTF8, then the new National Character Set will be UTF8. Otherwise, the National Character Set is changed to AL16UTF16.

During the upgrade, the existing NCHAR columns in the data dictionary are changed to use the new Oracle9i format and, if the National Character Set has been changed to AL16UTF16, the dictionary NCHAR columns will be converted to the AL16UTF16 character set.

Note:

NCHAR columns in user tables are not changed during the upgrade. To change NCHAR columns in user tables, see "Upgrade User NCHAR Columns". 

AL24UTFFSS Character Set Desupported

The AL24UTFFSS Unicode character set has been desupported in Oracle9i. AL24UTFFSS was introduced in Oracle7 as the Unicode character set supporting the UTF-8 encoding scheme based on the Unicode 1.1 standard, which is now obsolete. In Oracle9i, The Unicode database character sets AL32UTF8 and UTF8, include the Unicode enhancements based on the Unicode 3.1 standard.

The migration path for existing AL24UTFFSS databases is to upgrade your database character set to UTF8 prior to upgrading to Oracle9i. As with all migrations to a new database character set, Oracle Corporation recommends you use the Character Set Scanner for data analysis before attempting to migrate your existing database character set to UTF8.

See Also:

Oracle9i Globalization Support Guide for more information about the Character Set Scanner 

Considerations for Replication Environments

If you plan to use CHAR column length semantics in Oracle9i, or if your replication database contains tables with NCHAR or NVARCHAR2 columns, then this section contains considerations for upgrading a replication environment to Oracle9i.

CHAR Column Length Semantics

If you plan to use CHAR column length semantics in a replication database after you upgrade it to Oracle9i, then all of the databases participating with that database in the replication environment must also use CHAR column length semantics. In this case, Oracle Corporation recommends that you upgrade all of the databases participating in the replication environment at the same time. This applies to both master sites and materialized view sites in your replication environment.

If you cannot upgrade all of the databases in your replication environment at the same time, then you can only use CHAR column length semantics in your Oracle9i databases if all of the databases prior to Oracle9i are using a single-byte character set. Otherwise, do not switch to CHAR column length semantics in the Oracle9i database until all of the other databases in the replication environment are upgraded to Oracle9i.

NCHAR or NVARCHAR2 Columns

If your replication database contains tables with NCHAR or NVARCHAR2 columns, then Oracle Corporation recommends that you upgrade all of the databases participating in the replication environment at the same time. This applies to both master sites and materialized view sites in your replication environment. In Oracle9i, all columns specified as NCHAR or NVARCHAR2 datatype are stored in Unicode format.

If you cannot upgrade all of the databases in your replication environment at the same time, then interoperability is only supported if all of the databases prior to Oracle9i are using a fixed width national character set. If any of the databases prior to Oracle9i are using a variable width character set, then you must convert these databases to fixed width character sets before you upgrade any of the other databases in the replication environment to Oracle9i.

See Also:

  • Oracle9i Replication for more information about replication support for column length semantics and Unicode

  • Oracle9i Globalization Support Guide for general information about column length semantics and Unicode

  • Oracle8i National Language Support Guide for information about converting character sets in release 8.1

 

Upgrading Oracle Parallel Server

If you are upgrading a system that has Oracle Parallel Server installed, then most of the actions described in this chapter should be performed on only one node of the system. So, perform the actions described in this chapter on only one node unless instructed otherwise in a particular step.

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. 

Prepare to Upgrade

Complete the following steps to begin the upgrade process:

  1. Review upgrade issues relating to SQL*Net, Net8, and Oracle Net.

    See Also:

    Appendix F, "Migration and Compatibility for Oracle Net Services", for information. 

  2. Make sure your DB_DOMAIN initialization parameter is set properly.

    See Also:

    "The DB_DOMAIN Parameter" for more information about setting this initialization parameter. 

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

  4. Start SQL*Plus.

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

  6. If you are upgrading from release 8.0.6, then make sure no user or role has the name OUTLN, because this schema is created automatically when you install Oracle9i. If you have a user or role named OUTLN, then you must drop the user or role and recreate it with a different name.

    Note:

    If you are upgrading from an 8.1 release, then you do not need to perform this check because the OUTLN user should have been created when you installed the 8.1 release. Therefore, if you are upgrading from an 8.1 release, then go to Step 7. Do not drop the OUTLN user if you are upgrading from an 8.1 release. 

    To check for a user with the name OUTLN, enter the following SQL statement: 

    SELECT username FROM dba_users WHERE username = 'OUTLN';

    If you do not have a user named OUTLN, then zero rows are selected.

    To check for a role with the name OUTLN, enter the following SQL statement: 

    SELECT role FROM dba_roles WHERE role = 'OUTLN';

    If you do not have a role named OUTLN, then zero rows are selected.

  7. Add space to your SYSTEM tablespace and to the tablespaces where you store rollback segments, if necessary.

    Upgrading to a new release requires more space in your SYSTEM tablespace and in the tablespaces where you store rollback segments. If you have enough space on your system, then consider adding more space to these tablespaces. In general, you need at least 50 MB of free space in your SYSTEM tablespace to upgrade. If you run out of space during the upgrade, then you will need to perform the upgrade again.

    The following SQL statement illustrates how to add more space to a tablespace: 

    ALTER TABLESPACE system
   ADD DATAFILE '/home/user1/mountpoint/oradata/db1/system02.dbf'
   SIZE 50M
   AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

ALTER ROLLBACK SEGMENT rb
   STORAGE (MAXEXTENTS UNLIMITED);
  8. Determine the files that you will back up in Step 11 by issuing the following SQL statements: 

    SPOOL v8files.log
SELECT member FROM v$logfile;
    
SELECT name FROM v$datafile;
    
SELECT value FROM v$parameter WHERE name = 'control_files';
    SPOOL OFF

    The v8files.log spool file lists all of the files that you must back up in Step 11.

  9. Run SHUTDOWN IMMEDIATE on the database: 

    SQL> SHUTDOWN IMMEDIATE

    If you are using Oracle Parallel Server, then shutdown all instances.

  10. If your operating system is Windows, then ensure all Oracle services are stopped.

    See Also:

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

  11. Perform a full offline backup of the database. Make sure you back up the files listed in the v8files.log spool file that you generated in Step 8.

    Caution:

    If you encounter any problems with the upgrade, then you will need to restore the database from this backup. Therefore, make sure you back up your database now as a precaution. 

    See Also:

    • Oracle8i Backup and Recovery Guide for more information about backing up a release 8.1 database

    • Oracle8 Backup and Recovery Guide for more information about backing up a release 8.0 database

     
  12. Exit SQL*Plus.

Upgrade the Database

Choose an upgrade method and then follow the instructions for upgrading using the method you have chosen.

See Also:

Some aspects of upgrading are operating system-specific. See your operating system-specific Oracle documentation for additional information about migrating. 

Choose an Upgrade Method

There are two ways to upgrade your database to release 9.0.1. You can either use the Oracle Data Migration Assistant to complete the upgrade, or you can perform the upgrade manually.

The Oracle Data Migration Assistant provides a completely automated upgrade of your database. You use a graphical user interface (GUI), which guides you through each step of the process. In addition, the Oracle Data Migration Assistant includes extensive online help. The Oracle Data Migration Assistant runs the appropriate upgrade script for your current release, deletes any obsolete initialization parameters from your initialization parameter file, and optionally configures your listener.ora file. See Appendix B, "Changes to Initialization Parameters", for lists of obsolete initialization parameters.

On the other hand, you lose some flexibility and control by using the Oracle Data Migration Assistant. If you want complete control over the upgrade process, especially with regard to setting initialization parameters, then you may want to perform the upgrade manually.

Caution:

The Oracle Data Migration Assistant cannot upgrade systems with Oracle Parallel Server installed. If you have Oracle Parallel Server installed, then you must upgrade the database manually. 

Decide which method you want to use to upgrade your database, and then complete the steps in one of the following sections accordingly:

Upgrade the Database Using the Oracle Data Migration Assistant

Complete the following steps to upgrade the database using the Oracle Data Migration Assistant:

  1. Follow the instructions in your operating system-specific Oracle documentation to prepare for installation and start the Oracle Universal Installer.

    If you need help at any screen or want to consult more documentation about the Oracle Universal Installer, then click the Help button to open the online help.

  2. At the Welcome screen of the Oracle Universal Installer, click Next. The File Locations screen appears.

  3. At the File Locations screen, complete the following steps:

    1. Do not change the text in the Source field. This is the location of files for installation.

    2. If there is a Destination Name field, enter the name of a new Oracle home in this field.

    3. Enter the complete path of the Oracle home directory where you want to install the new release in the Destination Path field. Click the Browse button to navigate to the directory.

    4. Click Next.

    The Available Products screen appears.

  4. At the Available Products screen, select the Oracle9i server. The Oracle9i server is either Oracle9i Enterprise Edition or Oracle9i, depending on your installation medium. Then, click Next.

  5. At the Installation Types screen, choose Custom. Do not choose Standard Edition or Enterprise Edition unless you want to install a starter database along with your Oracle software. You can avoid installing a starter database if you select Custom.

    Note:

    Normally, you should not install a starter database if you are upgrading an existing database. 

    After you make your selection, click Next.

    If you chose Custom, the Available Product Components screen appears. Complete the following steps:

    1. Choose the product components you want to install. Then, click Next.

      Make sure you install all of the options you installed with the previous database, assuming you do not want to discontinue use of a particular option. For example, if you installed Oracle Parallel Server in the previous database, then you should install Oracle9i Real Application Clusters in the new Oracle9i database.

    2. Respond to the remaining screens that enable you to specify your custom installation settings, until you reach the Upgrading or Migrating an Existing Database screen.

  6. At the Upgrading or Migrating an Existing Database screen, complete the following steps:

    1. Select the Upgrade or Migrate an Existing Database check box.

    2. Choose the database to upgrade.

    3. Click Next.

  7. At the Create Database screen, select the No option, indicating that you do not want to create a database because you are upgrading an existing database. Then, click Next.

  8. At the Summary screen, make sure all of the settings and choices are correct for your installation. Then, click Install. The Oracle Universal Installer performs the installation, which may take some time.

    When installation is complete, one or more assistants may be started. When the Oracle Data Migration Assistant is started, you are ready to proceed with the upgrade.

  9. At the Before You Migrate or Upgrade screen of the Oracle Data Migration Assistant (shown in Figure 7-1), make sure the database that you are upgrading meets the conditions specified. Then, click Next.

    If you need help at any screen or want to consult more documentation about the Oracle Data Migration Assistant, then click the Help button to open the online help.

    Figure 7-1 Before You Migrate or Upgrade Screen


    Text description of odmaint.gif follows.
    Text description of the illustration odmaint.gif
  10. At the Select a Database Instance screen, select the database instance of the database you are upgrading. Then, click Next.

    Note:

    The database you choose must be release 8.0 or higher. If the database is an Oracle7 or lower database, then you must complete a migration of the database, not an upgrade. If the database is an Oracle7 or lower database, then exit the Oracle Data Migration Assistant, and see Chapter 2 to start the migration process. 

  11. At the Database Password and INIT.ORA File screen, complete the following steps:

    1. Make sure the specified new Oracle home is correct.

    2. Make sure the location of the initialization parameter file specified is the complete path to the initialization parameter file of the database that you are upgrading. If the location is incorrect, click the Browse button to navigate to the correct directory.

    3. Make sure the old Oracle home specified is the complete path to the Oracle home of the database you are upgrading.

    4. Click Next. The Choose Migration or Upgrade Type screen appears.

  12. Choose an upgrade type. Then, click Next.

    If you chose Custom, then respond to the screens that enable you to specify your custom migration settings until you reach the Backup Your Database screen. If you need help with any of the custom screens, click the Help button.

  13. At the Backup Your Database screen, you have two options:

    • Choose "I have already backed up my database" if you completed a backup before running the Oracle Data Migration Assistant.

    • Choose "I would like this tool to back up the database" if you did not complete a backup. If you choose this option, then you can select the backup directory by clicking the Browse button.

    After you have made your choice, click Next.

  14. At the Start the Migration or Upgrade screen, make sure all of the specifications are correct. If anything is incorrect, then click Back until you can correct the specification. If everything is correct, then click Next.

    The Oracle Data Migration Assistant performs the upgrade. During the upgrade, the assistant sets the COMPATIBLE initialization parameter to 8.1.0. See Chapter 9, "Compatibility and Interoperability" for information about setting the COMPATIBLE initialization parameter after the upgrade.

    You may encounter the following error message: 

    An error occurred while upgrading your Oracle database. Check the log files to 
determine if the upgrade was successful.

    If you encounter this message, then complete the following steps:

    1. Click the OK button.

    2. Click the View Summary in the subsequent box that appears. If the errors are similar to the following, then you can ignore the messages: 

      ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYSTEM.AQ$_QUEUES_CHECK) violated
ORA-06512: at "SYS.DBMS_AQADM", line 2023
ORA-06512: at line 2

    If other errors appear, then you must address them accordingly. For example, if you receive the error message "error accessing package DBMS_APPLICATION_INFO", it means that the migration utility does not recognize that your old database is not release 9.0.1. The solution to this is to run the appropriate upgrade script, located in the ORACLE_HOME/rdbms/admin directory. For example, if your old database is release 8.1.5, run u0801050.sql.

    Caution:

    If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new release 9.0.1 installation directory. 

  15. At the Listener.ora Migration Confirmation screen, click the Yes button if you want the assistant to modify your listener.ora file automatically, or click the No button if you do not want the assistant to modify the listener.ora file.

    Certain modifications are required to the listener.ora file for your database to work properly with Oracle Enterprise Manager. If you plan to use Oracle Enterprise Manager, then you should click the Yes button to automatically modify the listener.ora file. However, if you do not plan to use Oracle Enterprise Manager, then click the No button.

    If you click the Yes button, then the Oracle Data Migration Assistant modifies the listener.ora file in the following way:

    1. The assistant shuts down the old listener and the new Oracle9i listener.

    2. The assistant modifies the SID_DESC entry for the migrated database in the Oracle9i listener.ora file in one of the following ways:

      A simple case: Suppose the old listener.ora has the following SID_DESC entry: 

      ...
   (SID_DESC = 
      (SID_NAME = ORCL) 
   ) 
...

      If the database name is SAL, the domain name is COM, and the Oracle home is /oracle/product/9.0, then the assistant adds the following entry: 

      ...
   (SID_DESC = 
       (GLOBAL_DBNAME = sal.com) 
          (ORACLE_HOME = /oracle/product/9.0) 
          (SID_NAME = SAL) 
    ) 
...

      A more complicated case: Suppose the old listener.ora has the following SID_DESC entry: 

      ...
   (SID_DESC = 
       (GLOBAL_DBNAME = an_entry) 
       (SID_NAME = ORCL) 
    ) 
...

      If an_entry does not match the GLOBAL_DBNAME of the migrated database, and if the database name is SAL, the domain name is COM, and the Oracle home is \oracle\product\9.0 on the D drive, then the assistant adds the following entry: 

      ...
   (SID_DESC = 
       (GLOBAL_DBNAME = sal.com) 
          (ORACLE_HOME = d:\oracle\product\9.0) 
          (SID_NAME = SAL) 
    ) 
...

      This entry is the same as the entry in the simple case, but the assistant also adds the entry an_entry to the SERVICE_NAMES parameter in the listener.ora file. Therefore, the assistant changes the SERVICE_NAMES parameter to the following: 

      SERVICE_NAMES = sal.com, an_entry
    3. On Windows platforms, the assistant removes the entry of the migrated database from the old listener.ora file. The assistant does not perform this action on UNIX operating systems.

    4. The assistant starts up the Oracle9i listener.

  16. Complete the procedures described in "Upgrading Specific Components" and in Chapter 8, "After Migrating or Upgrading the Database".

Caution:

If you retain the old Oracle software, then never start the upgraded database with the old Oracle software. Only start the database with the executables in the new Oracle9i installation. Also, before you remove the old Oracle environment, make sure you relocate any datafiles in that environment to the new Oracle9i environment. See the Oracle9i Database Administrator's Guide for information about relocating datafiles. 

Running the Oracle Data Migration Assistant Independently

If you installed Oracle9i without specifying that you are migrating or upgrading an existing database, then you can run the Oracle Data Migration Assistant independently after the Oracle9i installation is complete.

Complete the following steps to run the Oracle Data Migration Assistant independently:

  1. Start the Oracle Data Migration Assistant.

    On UNIX, enter the following command at a system prompt: 

    odma

    On Windows platforms, choose: 

    Start > Programs > Oracle - HOME_NAME > Migration Utilities > 
Oracle Data Migration Assistant

    When you start the Oracle Data Migration Assistant, its Before You Migrate or Upgrade screen appears (see Figure 7-1).

  2. Respond to questions in each Oracle Data Migration Assistant window, and click Next when you are ready to continue to the next window.

    See Also:

    Step 9 to Step 16 in "Upgrade the Database Using the Oracle Data Migration Assistant" for more information. 

Upgrade the Database Manually

Complete the following steps to upgrade the database manually using SQL scripts:

  1. Follow the instructions in your operating system-specific Oracle documentation to prepare for installation and start the Oracle Universal Installer.

    If you are upgrading a system with Oracle Parallel Server installed, then see Oracle9i Real Application Clusters Installation and Configuration for additional installation instructions.

  2. At the Welcome screen of the Oracle Universal Installer, click Next. The File Locations screen appears.

  3. At the File Locations screen, complete the following steps:

    1. Do not change the text in the Source field. This is the location of files for installation.

    2. If there is a Destination Name field, enter the name of a new Oracle home in this field.

    3. Enter the complete path of the Oracle home directory where you want to install the new release in the Destination Path field. Click the Browse button to navigate to the directory.

    4. Click Next.

    The Available Products screen appears.

  4. At the Available Products screen, select the Oracle9i server. The Oracle9i server is either Oracle9i Enterprise Edition or Oracle9i, depending on your installation medium. Then, click Next.

  5. At the Installation Types screen, choose Custom. Do not choose Standard Edition or Enterprise Edition unless you want to install a starter database along with your Oracle software. You can avoid installing a starter database if you select Custom.

    Note:

    Normally, you should not install a starter database if you are upgrading an existing database. 

    After you make your selection, click Next.

    If you chose Custom, the Available Product Components screen appears. Complete the following steps:

    1. Choose the product components you want to install. Then, click Next.

      Make sure you install all of the options you installed with the previous database, assuming you do not want to discontinue use of a particular option. For example, if you installed Oracle Parallel Server in the previous database, then you should install Oracle9i Real Application Clusters in the new Oracle9i database.

    2. If you are installing Oracle9i Real Application Clusters, then, at the Cluster Node Selection screen, select the nodes onto which you want the software installed. Then, click Next.

    3. Respond to the remaining screens that enable you to specify your custom installation settings, until you reach the Upgrading or Migrating an Existing Database screen.

  6. At the Upgrading or Migrating an Existing Database screen, leave the Upgrade or Migrate an Existing Database check box unselected. Then, click Next.

    If you select the Upgrade or Migrate an Existing Database check box, then the Oracle Data Migration Assistant is started automatically after installation. Because you are following the instructions for upgrading the database manually, you should not start the Oracle Data Migration Assistant.

    Note:

    The Oracle Data Migration Assistant does not support Oracle Parallel Server migrations. 

  7. At the Summary screen, make sure all of the settings and choices are correct for your installation. Then, click Install. The Oracle Universal Installer performs the installation, which may take some time.

    When installation has completed successfully, click the Exit button to close the Oracle Universal Installer.

  8. If your operating system is Windows, then complete the following steps:

    1. Shut down and restart your computer.

    2. Stop the Oracle service OracleServiceSID of the database you are upgrading, 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
    3. Delete the Oracle service at the MS-DOS command prompt using ORADIM. The following table lists the MS-DOS command to run for each Oracle9i release:

      Oracle Release...  Enter at the MS-DOS Command Prompt... 

      8.0 

      C:\> ORADIM80 -DELETE -SID SID 

      8.1 

      C:\> ORADIM -DELETE -SID SID 

      9.0 

      C:\> ORADIM -DELETE -SID SID 

      For example, if your Oracle release is release 8.0.6 and your SID is ORCL, then enter the following MS-DOS command: 

      C:\> ORADIM80 -DELETE -SID ORCL

      If your Oracle release is release 8.1.7 and your SID is ORCL, then enter the following MS-DOS command: 

      C:\> ORADIM -DELETE -SID ORCL
    4. Create the Oracle9i database service at the MS-DOS command prompt: 

      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 database you are upgrading.

      PASSWORD

      is the password for the new release 9.0.1 database instance. This is the password for the user connected AS SYSDBA. 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 release 9.0.1 Oracle home directory. Ensure that you specify the full pathname with the -PFILE option, including drive letter of the Oracle home directory.

      For example, if your SID is ORCL, your PASSWORD is TWxy579, the maximum number of USERS is 10, and the ORACLE_HOME directory is C:\ORA900, then enter the following command: 

      C:\> ORADIM -NEW -SID ORCL -INTPWD TWxy579 -MAXUSERS 10
     -STARTMODE AUTO -PFILE C:\ORA900\DATABASE\INITORCL.ORA
  9. Copy configuration files to a location outside of the old Oracle home:

    1. If your initialization parameter file resides within the old environment's Oracle home, then copy it to a location outside of the old environment's Oracle home. By default Oracle looks for the initialization parameter file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on Windows platforms. The initialization parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you upgrade to the new release.

    2. If your initialization parameter file has an IFILE (include file) entry and the file specified in the IFILE entry resides within the old environment's Oracle home, then copy the file specified by the IFILE entry to a location outside of the old environment's Oracle home. The file specified in the IFILE entry has additional initialization parameters.

    3. If you have a password file that resides within the old Oracle home, then move or copy the password file to the Oracle9i Oracle home. 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.

  10. Adjust the initialization parameter file for use with the new release. Specifically, complete the following steps:

    1. Remove obsolete parameters and adjust changed parameters. Certain initialization parameters are obsolete in the new 9.0.1 release. Remove all obsolete parameters from any initialization parameter file that will start a new release 9.0.1 instance. Obsolete parameters may cause errors in the new 9.0.1 release. Also, alter any parameter whose syntax has changed in the new 9.0.1 release; refer to Appendix B, "Changes to Initialization Parameters", for lists of new, renamed, and obsolete parameters.

      Also, if you are using Oracle Parallel Server, then see Oracle9i Real Application Clusters Installation and Configuration for more information about obsolete Oracle Parallel Server initialization parameters.

    2. If you are updating materialized views automatically by using the JOB_QUEUE_PROCESSES initialization parameter, then set this parameter to 0 (zero) in the initialization parameter file. Also, if you are using Advanced Queuing and have propagation schedules, then set both the JOB_QUEUE_PROCESSES and AQ_TM_PROCESSES initialization parameters to 0 (zero).

    3. Make sure the OPTIMIZER_MODE initialization parameter is set to choose.

    4. If you are using a password file, then set REMOTE_LOGIN_PASSWORDFILE to NONE in the initialization parameter file. After upgrading your database, you can change the settings for these parameters back to their normal settings.

    5. On Windows platforms, change the BACKGROUND_DUMP_DEST and USER_DUMP_DEST initialization parameters that point to RDBMS80 or any other environment variable to point to the following directories instead:

      Initialization Parameter  Change Setting To 

      BACKGROUND_DUMP_DEST 

      ORACLE_BASE\oradata\DB_NAME 

      USER_DUMP_DEST 

      ORACLE_BASE\oradata\DB_NAME\archive 

      In the settings, substitute the complete ORACLE_BASE path for ORACLE_BASE and substitute the database name for DB_NAME.

    6. If the initialization parameter file contains an IFILE entry, then change the IFILE entry in the initialization parameter file to point to the new location you copied it to in Step 9. b. Then, edit the file specified in the IFILE entry in the same way that you edited the initialization parameter file in sub-steps a to e.

    7. Make sure the COMPATIBLE initialization parameter is properly set for Oracle9i. If COMPATIBLE is set below 8.1.0, then you will encounter the following error when you attempt to start up your release 9.0.1 database later in step 16: 

      ORA-00401: the value for parameter compatible is not supported by this release

      Either leave COMPATIBLE unset in your initialization parameter file or set COMPATIBLE to 8.1.x.

    8. If you are using Oracle Parallel Server, then modify the initdb_name.ora file in the same way that you modified the initialization parameter file.

    Make sure you save all of the files you modified after making these adjustments.

    Note:

    For Oracle Parallel Server, perform this step on all nodes. Also, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the upgrade operation is complete. 

  11. If your operating system is UNIX, then make sure that the following environment variables point to the new release 9.0.1 directories:

  12. Log in to the system as the owner of the Oracle home directory of the new release.

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

  14. Start SQL*Plus.

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

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

    You may see error messages listing obsolete initialization parameters. If so, then make a note of the obsolete initialization parameters and continue with the upgrade normally. Then, remove the obsolete initialization parameters the next time you shut down the database (Step 20).

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

    SQL> SPOOL catoutu.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
  18. Run uold_release.sql, where old_release refers to the release you had installed prior to upgrading. See Table 7-2 to choose the correct script. Each script provides a direct upgrade from the release specified in the "Old Release" column. The "Old Release" is the release from which you are upgrading.

    To run a script, enter the following: 

    SQL> @uold_release.sql
    Table 7-2 Upgrade Scripts
    Old Release  Run Script 

    8.0.6 

    u0800060.sql 

    8.1.5 

    u0801050.sql 

    8.1.6 

    u0801060.sql 

    8.1.7 

    u0801070.sql 

    Note:

    If the old release you had installed prior to upgrading is not listed in Table 7-2, then see the readme files in the new installation for the correct upgrade script to run. 

    Make sure you follow these guidelines when you run the script:

    • You must use the version of the script supplied with the new release 9.0.1 installation.

    • You must run the script in the new release 9.0.1 environment.

    • You only need to run ONE script, even if your upgrade spans several releases. For example, if your old release was 8.1.5, then you need to run only u0801050.sql.

    The script you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the release to which you are upgrading, which create the system catalog views and all the necessary packages for using PL/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. 

    You may encounter a series of messages similar to the following during the upgrade: 

    ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYSTEM.AQ$_QUEUES_CHECK) violated
ORA-06512: at "SYS.DBMS_AQADM", line 2023
ORA-06512: at line 2

    You can ignore these messages.

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

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

    SQL> SET ECHO OFF
  20. Run SHUTDOWN on the Oracle9i database: 

    SQL> SHUTDOWN IMMEDIATE

    Caution:

    Use SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. Do not use SHUTDOWN ABORT. 

    Executing this clean shutdown flushes all caches, clears buffers, and performs other DBMS housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle9i database.

    Also, if you encountered a message listing obsolete initialization parameters when you started the database in Step 16, then remove the obsolete initialization parameters from the initialization parameter file now.

    Your database is now upgraded to the new 9.0.1 release.

    Caution:

    If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new release 9.0.1 installation directory. Also, before you remove the old Oracle environment, make sure you relocate any datafiles in that environment to the new Oracle9i environment. See the Oracle9i Database Administrator's Guide for information about relocating datafiles. 

  21. Complete the procedures described in "Upgrading Specific Components" and in Chapter 8, "After Migrating or Upgrading the Database".

Upgrading Specific Components

Some components of the Oracle database server require an upgrade operation separate from the general database upgrade operation. Complete the actions in the following sections to upgrade specific components:

Upgrading JServer

If the Oracle system has the JServer component installed, then complete the following steps:

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

  2. At a system prompt, change to the ORACLE_HOME/javavm/install directory.

  3. Start SQL*Plus.

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

  5. If the instance is running, shut it down using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE

    Note:

    For Oracle9i Real Application Clusters, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the upgrade operation is complete. 

  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 catoutjava.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 the appropriate upgrade script depending on the release from which you are upgrading.

    If you are upgrading from release 8.1.5, run jvmu815.sql: 

    SQL> @jvmu815.sql

    If you are upgrading from release 8.1.6, run jvmu816.sql: 

    SQL> @jvmu816.sql

    If you are upgrading from release 8.1.7, run jvmu817.sql: 

    SQL> @jvmu817.sql

    After you run any one of these scripts, user classes that were present before the upgrade are left in place but are typically invalid. These classes are implicitly revalidated when they are used. You can explicitly revalidate any class by issuing an ALTER ANY CLASS statement. For example, to revalidate a class named MyClass, issue the following statement: 

    ALTER JAVA CLASS MyClass RESOLVE;
  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 catoutjava.log. Correct any problems you find in this file and rerun the appropriate upgrade script if necessary.

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

    SQL> SET ECHO OFF
  10. Run ALTER SYSTEM DISABLE RESTRICTED SESSION: 

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
  11. Shut down the instance: 

    SQL> SHUTDOWN IMMEDIATE
  12. Exit SQL*Plus.

The JServer component is upgraded to the new release.

Upgrading XDK for Java

If the Oracle system has the XDK for Java component installed, then complete the following steps:

  1. Make sure you have successfully upgraded JServer.

    See Also:

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

  2. Log in to the system as the owner of the Oracle home directory of the new release.

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

  4. Start SQL*Plus.

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

  6. If the instance is running, shut it down using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE

    Note:

    For Oracle9i Real Application Clusters, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the upgrade operation is complete. 

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

  8. Run the appropriate upgrade script depending on the release from which you are upgrading.

    If you are upgrading from release 8.1.5, run xmlu815.sql: 

    SQL> @xmlu815.sql

    If you are upgrading from release 8.1.6, run xmlu816.sql: 

    SQL> @xmlu816.sql

    If you are upgrading from release 8.1.7, run xmlu817.sql: 

    SQL> @xmlu817.sql
  9. Shut down the instance: 

    SQL> SHUTDOWN
  10. Exit SQL*Plus.

The XDK for Java component is upgraded to the new release.

Upgrading Session Namespace, CORBA, and OSE

  1. Make sure you have successfully upgraded JServer and XDK for Java.

  2. Log in to the system as the owner of the Oracle home directory of the new release.

  3. At a system prompt, change to the ORACLE_HOME/javavm/install directory.

  4. Start SQL*Plus.

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

  6. If the instance is running, shut it down using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE

    Note:

    For Oracle9i Real Application Clusters, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the upgrade operation is complete. 

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

  8. Make sure you have at least 100 MB of free rollback segment space.

    See Also:

    Oracle9i Database Administrator's Guide for information about managing rollback segments. 

  9. Start the listener.

  10. Run the appropriate upgrade script depending on the release from which you are upgrading.

    If you are upgrading from release 8.1.5, run jisu815.sql: 

    SQL> @jisu815.sql

    If you are upgrading from release 8.1.6, run jisu816.sql: 

    SQL> @jisu816.sql

    If you are upgrading from release 8.1.7, run jisu817.sql: 

    SQL> @jisu817.sql
  11. Shut down the instance: 

    SQL> SHUTDOWN
  12. Exit SQL*Plus.

    Note:

    Upgrade of Enterprise JavaBeans is not supported. If you deployed Enterprise JavaBeans in a past release, then you need to redeploy it for release 9.0.1. See the Oracle9i Enterprise JavaBeans Developer's Guide and Reference for information. 

Upgrading JSP

If the Oracle system has JSP installed, then complete the following steps:

  1. Make sure you have successfully upgraded JServer, XDK for Java, and Session Namespace, CORBA, and OSE.

  2. Log in to the system as the owner of the Oracle home directory of the new release.

  3. At a system prompt, change to the ORACLE_HOME/javavm/install directory.

  4. Start SQL*Plus.

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

  6. If the instance is running, shut it down using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE

    Note:

    For Oracle9i Real Application Clusters, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the upgrade operation is complete. 

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

  8. Make sure you have at least 100 MB of free rollback segment space.

    See Also:

    Oracle9i Database Administrator's Guide for information about managing rollback segments. 

  9. Run the appropriate upgrade script depending on the release from which you are upgrading.

    If you are upgrading from release 8.1.5, run jspu815.sql: 

    SQL> @jspu815.sql

    If you are upgrading from release 8.1.6, run jspu816.sql: 

    SQL> @jspu816.sql

    If you are upgrading from release 8.1.7, run jspu817.sql: 

    SQL> @jspu817.sql
  10. Shut down the instance: 

    SQL> SHUTDOWN
  11. Exit SQL*Plus.

Upgrading Oracle Spatial

If the Oracle system has Oracle Spatial installed, then see the Oracle Spatial User's Guide and Reference for instructions about upgrading Oracle Spatial to release 9.0.1.

Upgrading interMedia

Manual upgrade instructions for interMedia can be found in ORACLE_HOME/ord/im/admin/README.txt on UNIX platforms and in ORACLE_HOME\ord\im\admin\README.txt on Windows platforms.

Manual upgrade instructions for customers with existing Visual Information Retrieval applications who wish to upgrade to Oracle9i Visual Information Retrieval-compatible API can be found in ORACLE_HOME/ord/vir/admin/README.txt on UNIX platforms and in ORACLE_HOME\ord\vir\admin\README.txt on Windows platforms.

Upgrading Oracle Text

If the Oracle system has Oracle Text installed, then complete the following steps:

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

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

  3. Start SQL*Plus.

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

  5. If the instance is running, shut it down using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE
  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 catouttext.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. If you are upgrading from release 8.1.5, then complete the following steps. Skip to Step 9 if you are upgrading from release 8.1.6. Skip to step 10 if you are upgrading from release 8.1.7.

    1. Run s0801060.sql: 

      SQL> @s0801060.sql
    2. Connect to the database instance as user CTXSYS.

    3. Run u0801060.sql: 

      SQL> @u0801060.sql
    4. Connect to the database instance as a user with SYSDBA privileges.

  9. If you are upgrading from release 8.1.6, then complete the following steps. Skip to Step 10 if you are upgrading from release 8.1.7.

    1. Run s0801070.sql: 

      SQL> @s0801070.sql

      This script grants new, required database privileges to user CTXSYS.

    2. Connect to the database instance as user CTXSYS.

    3. Run u0801070.sql: 

      SQL> @u0801070.sql

      This script upgrades the CTXSYS schema to release 8.1.7.

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

  10. If you are upgrading from release 8.1.7, then complete the following steps.

    1. Run s0900010.sql: 

      SQL> @s0900010.sql

      This script grants new, required database privileges to user CTXSYS.

    2. Connect to the database instance as user CTXSYS.

    3. Run u0900010.sql: 

      SQL> @u0900010.sql

      This script upgrades the CTXSYS schema to release 9.0.1.

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

  11. Check for any invalid CTXSYS objects and alter compile as needed.

  12. 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 catouttext.log. Correct any problems you find in this file and rerun the appropriate upgrade scripts if necessary.

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

    SQL> SET ECHO OFF
  13. Run ALTER SYSTEM DISABLE RESTRICTED SESSION: 

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
  14. Shut down the instance: 

    SQL> SHUTDOWN IMMEDIATE
  15. Exit SQL*Plus.

Oracle Text is upgraded to the new release.

Upgrading Oracle Label Security

If the Oracle system has Oracle Label Security installed, and you did not choose to upgrade Oracle Label Security using the Oracle Data Migration Assistant, then complete the following steps:

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

  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. If the instance is running, shut it down using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE
  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 catoutols.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 the appropriate upgrade script depending on the release from which you are upgrading.

    If you are upgrading from release 8.1.7, run olsu817.sql: 

    SQL> @olsu817.sql
  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 catoutols.log. Correct any problems you find in this file and rerun the appropriate upgrade 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
  11. Exit SQL*Plus.

Oracle Label Security is upgraded to the new release.

Upgrading Oracle9i Real Application Clusters

If the Oracle system has Oracle9i Real Application Clusters installed, then complete the following steps:

  1. Shut down all instances using SHUTDOWN IMMEDIATE: 

    SQL> SHUTDOWN IMMEDIATE

    Note:

    For Oracle9i Real Application Clusters, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the upgrade operation is complete. 

  2. Log in to the system as the owner of the Oracle home directory of the new 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. Run STARTUP RESTRICT: 

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

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

    SQL> SET ECHO ON
  8. Run catclust.sql: 

    SQL> @catclust.sql
  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 catoutclust.log. Correct any problems you find in this file.

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

    SQL> SET ECHO OFF
  10. Run ALTER SYSTEM DISABLE RESTRICTED SESSION: 

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle9i Real Application Clusters is upgraded to the new release.

Upgrading Materialized Views

Note:

The word "snapshot" is synonymous with the word "materialized view". 

Materialized views upgraded from release 8.0 or imported from a release 8.0 database cannot use the new summary management features available in release 8.1 and higher. If you want to use these new features, then complete the following steps for each materialized view and for each materialized view imported from release 8.0:

  1. GRANT QUERY REWRITE privileges to the owner of the materialized view. Only local materialized views are available for query rewrite.

    If the materialized view references any schema objects outside its owner's schema, then you must issue a GRANT GLOBAL QUERY REWRITE statement.

  2. Issue the ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITE statement on the materialized views you want to upgrade.

    For example, on a materialized view named SSORDERS, issue the following statement: 

    ALTER MATERIALIZED VIEW ssorders ENABLE QUERY REWRITE;

In addition, if you do not ENABLE QUERY REWRITE on a materialized view, then the ATOMIC=FALSE option of the DBMS_MVIEW.REFRESH procedure may not work unless you issue an ALTER MATERIALIZED VIEW ... COMPILE statement on the materialized view. For example, for a materialized view named SSCUST, issue the following statement: 

ALTER MATERIALIZED VIEW sscust COMPILE;

You do not need to issue this statement if you have issued any other ALTER MATERIALIZED VIEW statement on the materialized view, such as the ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITE statement.

Upgrading the Advanced Queuing Option

The following sections describe the actions required to upgrade the Advanced Queuing (AQ) option.

Upgrade Your Queue Tables

The following release 8.1 and higher AQ enhancements are available only if you upgrade your existing queue tables:

To upgrade an existing queue table, run the DBMS_AQADM.MIGRATE_QUEUE_TABLE procedure, specifying 8.1 for the option. For example, for a queue table named tb_queue owned by user scott, run the following procedure: 

EXECUTE dbms_aqadm.migrate_queue_table (
    queue_table => 'scott.tb_queue',
    compatible => '8.1');

To create a new queue table that is compatible with release 8.1 and higher, connect as the owner of the queue table and run the DBMS_AQADM.CREATE_QUEUE_TABLE procedure, specifying 8.1 for the COMPATIBLE option, as in the following example: 

EXECUTE dbms_aqadm.create_queue_table(
    queue_table => 'scott.tkaqqtpeqt', 
    queue_payload_type =>'message', 
    sort_list => 'priority,enq_time', 
    multiple_consumers => true, 
    comment => 'Creating queue with priority and enq_time sort order', 
    compatible => '8.1');

Note:

The COMPATIBLE initialization parameter must be set to 8.1.0 or higher to upgrade your queue tables and to create new release 8.1 compatible queue tables. 

Upgrading the Recovery Catalog

Your recovery catalog schema for the upgraded database may reside in a database that is separate from the database you upgraded. If you upgraded the Recovery Manager executable to release 8.1, then you must upgrade the recovery catalog to release 8.1 as well.

Also, if you have multiple databases of different releases managed by a single recovery catalog, then you need to consider compatibility issues between a particular Recovery Manager release and the recovery catalog release. For example, release 8.1.3 and 8.1.4 of Recovery Manager cannot access a release 8.1.5 or higher recovery catalog. Therefore, in this case, you must upgrade all of the databases managed by the recovery catalog to release 8.1.5 or higher. For more information about recovery catalog compatibility with Recovery Manager, see "Recovery Manager".

Complete the following steps to upgrade the recovery catalog:

  1. Log in to Recovery Manager and connect to the recovery catalog.

    For example, if RCAT/RCAT is the user name and password for the recovery catalog owner, and RECDB is the network service name, then enter the following: 

    rman rcvcat rcat/rcat@recdb

    The first time you connect to an older recovery catalog with the 8.1 release of Recovery Manager, you will see message RMAN-06186, indicating that the recovery catalog must be upgraded.

  2. Use the UPGRADE CATALOG command to upgrade the recovery catalog to the most current release. Recovery Manager prompts you to enter the command twice to confirm the catalog upgrade. If any errors are encountered while upgrading, then they are displayed in the Recovery Manager log.

    Here is the log from a session that upgrades the recovery catalog from release 8.0.4: 

    Recovery Manager: Release 8.1.5.0.0 

RMAN-06008: connected to recovery catalog database 
RMAN-06186: PL/SQL package rcat.DBMS_RCVCAT version 08.00.04 in RCVCAT 
database is too old 

RMAN> upgrade catalog 

RMAN-06435: recovery catalog owner is rcat 
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade 

RMAN> upgrade catalog 

RMAN-06408: recovery catalog upgraded to version 08.01.05

Upgrading Statistics Tables Created by the DBMS_STATS Package

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure: 

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table');

where SCOTT is the owner of the statistics table and STAT_TABLE is the name of the statistics table. Execute this procedure for each statistics table.

Recompiling Invalid PL/SQL Modules

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.

To run the utlrp.sql script, complete the following steps:

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

  2. Start SQL*Plus.

  3. Connect to the database instance AS SYSDBA.

  4. Run utlrp.sql: 

    SQL> @utlrp.sql

Oracle Corporation highly recommends running utlrp.sql.

Changing the Word-Size of Your Current Release

The instructions in this section guide you through changing the word-size of your current release (switching from 32-bit software to 64-bit software or vice versa).

See Also:

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

Complete the following steps to change the word-size of your current release:

  1. Start SQL*Plus.

  2. Connect to the database instance AS SYSDBA.

  3. Run SHUTDOWN IMMEDIATE on the database: 

    SQL> SHUTDOWN IMMEDIATE

    Note:

    NCHAR columns in user tables are not changed during the upgrade. To change NCHAR columns in user tables, see "Upgrade User NCHAR Columns". 

  4. Perform a full offline backup of the database.

    See Also:

    Oracle9i User-Managed Backup and Recovery Guide for more information. 

  5. If you are using the same Oracle home for your current release and the release to which you are switching, then deinstall your current release using the Oracle Installer. You do not need to deinstall your current release if you are using separate Oracle home directories.

  6. If you currently have a 32-bit installation, then install the 64-bit version of the same release. Or, if you currently have a 64-bit installation, then install the 32-bit version of the same release.

    Note:

    Installation and deinstallation are operating system-specific. For installation and deinstallation instructions, see your Oracle9i operating system-specific installation documentation and the Oracle9i README for your operating system. 

  7. Copy configuration files to a location outside of the old Oracle home:

    1. If your initialization parameter file resides within the old environment's Oracle home, then copy it to a location outside of the old environment's Oracle home. The initialization parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you switch to the new release.

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

    3. If you have a password file that resides within the old Oracle home, then move or copy the password file to the Oracle9i Oracle home. The name and location of the password file are operating system-specific; for example, on UNIX operating systems, the default password file is ORACLE_HOME/dbs/orapwsid, but on Windows platforms, the default password file is ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.

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

  9. Start SQL*Plus.

  10. Connect to the database instance AS SYSDBA.

  11. Run STARTUP RESTRICT: 

    SQL> STARTUP RESTRICT

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

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

    SQL> SPOOL catoutw.log

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

    SQL> SET ECHO ON
  13. Run utlirp.sql: 

    SQL> @utlirp.sql

    The utlirp.sql script recompiles existing PL/SQL modules in the format required by the new database. This script first alters certain dictionary tables. Then, it reloads package STANDARD and DBMS_STANDARD, which are necessary for using PL/SQL. Finally, it triggers a recompile of all PL/SQL modules, such as packages, procedures, types, and so on.

  14. 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 12; the suggested name was catoutw.log. Correct any problems you find in this file.

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

    SQL> SET ECHO OFF
  15. Run ALTER SYSTEM DISABLE RESTRICTED SESSION: 

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

The word-size of your database is changed. You can open the database for normal use.

Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback