Skip Headers

Oracle9i Database Migration
Release 2 (9.2)

Part Number A96530-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

3
Upgrading a Database to the New Oracle9i Release

This chapter guides you through the process of upgrading a database to the new Oracle9i release. This chapter covers the following topics:

Install the Release 9.2 Oracle Software

Complete the following steps to install the release 9.2 software:

  1. If your operating system is UNIX, then make sure you are logged in as a user with write permission to the Oracle home and Oracle base directories, as well as all of their subdirectories.
  2. Follow the instructions in your Oracle operating system-specific documentation to prepare for installation and start the Oracle Universal Installer.

    If you are upgrading a cluster database, then see Oracle9i Real Application Clusters Setup and Configuration for additional installation instructions.

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

    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.

  4. 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. Enter the name of a new Oracle home in the Destination Name 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.


      Note:

      You must install the new 9.2 release in a new Oracle home that is separate from the old release.


    4. Click Next.

    The Available Products screen appears.

  5. At the Available Products screen, select Oracle9i Database. Then, click Next.

    The Installation Types screen appears.

  6. At the Installation Types screen, select Enterprise, Standard, or Custom Installation. Then, click Next.

    If you chose Enterprise or Standard, then the Database Configuration screen appears. Complete the following steps:

    1. Select Software Only.
    2. Click Next.


      Note:

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


    If you chose Custom Installation, then 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 Oracle Utilities to install the Database Upgrade Assistant, and if you are upgrading from Oracle7, the MIG utility.

      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 Text in the previous database, then you should install Oracle Text 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 an Existing Database screen.
    4. If the Create Database screen appears, then select the No option, indicating that you do not want to create a database because you are upgrading an existing database. Then, click Next.
  7. At the Upgrading an Existing Database screen, complete the following steps:
    1. To upgrade a database using the Database Upgrade Assistant, select the Upgrade an Existing Database check box.

      To upgrade a database manually, or to run the Database Upgrade Assistant independently after installation is complete, do not select the Upgrade an Existing Database check box.

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

When installation is complete, one or more assistants may be started. If you chose to run the Database Upgrade Assistant during installation, then you are ready to proceed with the upgrade When the Database Upgrade Assistant is started. See "Upgrade the Database Using the Database Upgrade Assistant".

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

Running the Database Upgrade Assistant Independently

If you installed the new Oracle9i release without specifying that you are upgrading an existing database, then you can run the Database Upgrade Assistant independently after installation is complete.

Complete the following steps to run the Database Upgrade Assistant independently:

  1. Start the Database Upgrade Assistant.

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

    dbua
    
    

    On Windows operating systems, choose:

    Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools >
    Database Upgrade Assistant
    
    

    You are ready to proceed with the upgrade when the Database Upgrade Assistant is started.

Upgrade the Database Using the Database Upgrade Assistant

When the Database Upgrade Assistant starts, its Welcome screen appears.

Figure 3-1 Welcome Screen of the Database Upgrade Assistant

Text description of dbua.gif follows.

Text description of the illustration dbua.gif

Complete the following steps to upgrade a database using the Database Upgrade Assistant:

  1. At the Welcome screen of the Database Upgrade Assistant (shown in Figure 3-1), make sure the database being upgraded meets the specified conditions. Then, click Next.

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

  2. At the Select a Database screen, select the database you want to upgrade. Then, click Next.

    You may need to provide a user name and password with SYSDBA privileges if you do not have operating system authentication.

  3. If you are upgrading an Oracle7 database, then, at the Database Name screen, you can specify a new database name. Then, click Next.
  4. At the Password screen, enter a valid password for each user listed. Then, click Next.

    This screen only appears if the Database Upgrade Assistant requires a password for any user for the upgrade.

  5. At the Backup screen, you have two options:
    • Choose "I have already backed up my database" if you completed a backup before running the Database Upgrade 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.

  6. At the Network Configuration for the database screen, you have two tabs:

    The Listeners tab is displayed if you have more than one listener in the release 9.2 Oracle home. Select the listeners in the release 9.2 Oracle home for which you would like to register the upgraded database.

    The Directory Service tab shows up if you have directory service is configured in the release 9.2 Oracle home. You can select to either register or not register the upgraded database with the directory service.

  7. At the Summary 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 Finish.

    The Database Upgrade Assistant lists the initialization parameters that will be set for the database during the upgrade. The COMPATIBLE initialization parameter will be set to at least 8.1.0.

    See Also:

    Chapter 5, "Compatibility and Interoperability" for information about setting the COMPATIBLE initialization parameter after the upgrade

  8. A Progress dialog appears and the Database Upgrade Assistant begins to perform the upgrade.

    You may encounter error messages with Ignore, Abort, and Skip the Step choices. If other errors appear, then you must address them accordingly. If an error is severe and cannot be handled during the upgrade, then you have the following choices:

    • If Skip the step is presented as a choice in the message, then clicking the button will skip the current upgrade step.

      This causes the Database Upgrade Assistant to move on to the next step in the upgrade, skipping this and any dependent steps. After the upgrade is complete, you can fix the problem, restart the Database Upgrade Assistant, and complete the skipped steps.

    • If Skip the step is not presented as a choice in the message, then you need to abort the process by clicking the Abort button.

      This will abort the upgrade process. The Database Upgrade Assistant prompts you to restore the database if the database backup was taken by the Database Upgrade Assistant.

      After the database has been restored, you need to correct the cause of the error and restart the Database Upgrade Assistant to perform the upgrade again.

      If you do not want to restore the database, then the Database Upgrade Assistant leaves the database in its present state so that you can proceed with a manual upgrade.

    After the upgrade has completed, the following message is displayed on the Progress dialog:

    Upgrade has been completed. Click the "OK" button to see the results of the 
    upgrade.
    
    

    Click the OK button.

  9. At the Results dialog, you can view the details of the upgrade. You can also unlock and set passwords to the user accounts.

    If you are not satisfied with the upgrade, then you can restore the database back to the previous release.

    If you are satisfied with the upgrade, then click the Done button. The Database Upgrade Assistant removes the entry of the upgraded database from the old listener.ora file and reloads the listener of the old database.

    1. The Database Upgrade Assistant modifies the SID_DESC entry for the upgraded 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.2, then the assistant adds the following entry:

      ...
         (SID_DESC =
             (GLOBAL_DBNAME = sal.com)
                (ORACLE_HOME = /oracle/product/9.2)
                (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.2, then the assistant adds the following entry:

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

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

      SERVICE_NAMES = sal.com, an_entry
      
      
    2. The Database Upgrade Assistant removes the entry of the upgraded database from the old listener.ora file.
    3. The Database Upgrade Assistant reloads the listener.ora file in both the old and new Oracle9i environments.
  10. Complete the procedures described in Chapter 4, "After Upgrading a 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.


Upgrade the Database Manually

The following sections guide you through the process of performing a manual upgrade:

System Considerations and Requirements

The following sections discuss system considerations and requirements.

Upgrading a Cluster Database

If you are upgrading a cluster database, then most of the actions described in this section should be performed on only one node of the system. So, perform the actions described in this section on only one node unless instructed otherwise in a particular step.

Migrating to a Different Operating System

You cannot migrate a database to a computer system that has a different operating system during the upgrade process. For example, you cannot migrate a database from Oracle7 on Solaris to Oracle9i on Windows 2000. However, you normally can use Export/Import to migrate a database to a different operating system.


Note:

A change in word size is supported during the upgrade process. A change in word size involves switching between 32-bit and 64-bit architecture within the same operating system. See "Changing Word Size" for more information.


Considerations for Release 8.1.7 and Lower Database Character Sets

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 (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 Database 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".


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

Prepare the Database to be Upgraded

Several preparatory steps are required before you upgrade your database to the new Oracle9i release. Depending on the release number of the database being upgraded, you may need to complete some or all of the following steps:

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

    See Also:

    Appendix B, "Upgrade Considerations for Oracle Net Services" for information

  2. Log in to the system as the owner of the Oracle home directory of the database being upgraded.
  3. Start SQL*Plus.


    Note:

    If the database being upgraded is release 8.0.6 or lower, then start Server Manager. Do not start SQL*Plus.


  4. Connect to the database instance as a user with SYSDBA privileges.
  5. If the database being upgraded is release 8.0.6 or lower, then complete the following steps. Skip to Step 6 if the database being upgraded is release 8.1.7 or higher:
    1. 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 re-create it with a different name.

      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.

    2. If the database being upgraded is release 7.3.4, then complete the additional preparatory steps in "Prepare the Oracle7 Database to be Upgraded".
  6. 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. Table 3-1 identifies the amount of additional space in the SYSTEM tablespace required to upgrade to the new Oracle9i release from each supported Oracle release. If you run out of space during the upgrade, then you will need to perform the upgrade again.

    Table 3-1  SYSTEM Tablespace Requirements
    Release Additional SYSTEM Tablespace Additional SYSTEM Tablespace (with JServer)

    9.0.1

    16 MB

    30 MB

    8.1.7

    52 MB

    80 MB

    8.0.6

    70 MB

    N/A

    7.3.4

    85 MB

    N/A

    The following example illustrates how to add more space to the SYSTEM tablespace:

    ALTER TABLESPACE system
        ADD DATAFILE '/home/user1/mountpoint/oradata/db1/system02.dbf'
        SIZE 16M
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
    
    ALTER ROLLBACK SEGMENT rb1
        STORAGE (MAXEXTENTS UNLIMITED);
    
    

    A large PUBLIC rollback segment is necessary to upgrade databases with a large number of database objects (packages, tables, types, and so on). A rollback segment of at least 70 MB is recommended when the total number of database objects exceeds 5000.

    To determine the total number of database objects, issue the following SQL statement:

    SQL> SELECT COUNT(*) FROM dba_objects;
    
    
  7. Determine the files that you will back up in Step 10 by issuing the following SQL statements:
    SQL> SPOOL files.log
    SQL> SELECT member FROM v$logfile;
    SQL> SELECT name FROM v$datafile;
    SQL> SELECT name from v$controlfile; SQL> SPOOL OFF

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

  8. Run SHUTDOWN IMMEDIATE on the database:
    SQL> SHUTDOWN IMMEDIATE
    
    

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

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

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


    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
  11. Exit SQL*Plus.

Upgrade the Database

Complete the following steps to upgrade the database:

  1. If you are upgrading from release 7.3.4, then complete the following steps:
    1. Complete the steps in "Review MIG Utility Command-Line Options" and in "Run the MIG Utility".
    2. Either remove or rename the database's control files, or use the CONTROL_FILES initialization parameter to specify new control file names. The CONTROL_FILES initialization parameter is typically set in the initialization parameter file, but, if you are upgrading a cluster database, then it may be set in the initdb_name.ora file instead.

      You will issue the ALTER DATABASE CONVERT statement later in the upgrade process. This statement automatically creates new control files. If you do not use the CONTROL_FILES initialization parameter, then this statement uses the control file names of your previous database (derived from the convert file) and returns an error if the control files already exist. Therefore, in this case, you must remove or rename the control files.

      However, if you use the CONTROL_FILES initialization parameter to specify new control file names, then the ALTER DATABASE CONVERT statement creates the new control files with the names you specify, and you do not need to remove the old control files. For a complete list of your existing control files, check the files.log spool file you created in Step 10.

      Control files are considerably larger in Oracle9i than in Oracle7. For example, Oracle7 control files in the hundreds of kilobytes may expand into tens of megabytes in Oracle9i. The larger size in Oracle9i results from the storage of more information in the control file, such as backup and tablespace records. This size increase could be important if a control file is on a raw device or if its available disk space is restricted.


      Note:

      The CONTROL_FILES initialization parameter specifies one or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices or mirroring the file at the operating system level. See the Oracle9i Database Administrator's Guide for more information



      Note:

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


  2. If your operating system is Windows, then complete the following steps:
    1. Stop the OracleServiceSID Oracle service of the database you are upgrading, where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt:
      C:\> NET STOP OracleServiceORCL
      
      
    2. Delete the Oracle service at a command prompt using ORADIM. The following table lists the command to run for each Oracle release:

      Oracle Release... Enter at a Command Prompt...

      7.3

      C:\> ORADIM73 -DELETE -SID SID

      8.0

      C:\> ORADIM80 -DELETE -SID SID

      8.1 and higher

      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 command:

      C:\> ORADIM80 -DELETE -SID ORCL
      
      

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

      C:\> ORADIM -DELETE -SID ORCL
      
      
    3. Create the new Oracle9i database service at a command prompt using the ORADIM command of the new Oracle9i release:
      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.2 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 release 9.2 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:\ORA92, then enter the following command:

      C:\> ORADIM -NEW -SID ORCL -INTPWD TWxy579 -MAXUSERS 10
           -STARTMODE AUTO -PFILE C:\ORA92\DATABASE\INITORCL.ORA
      
      
  3. Copy configuration files from the previous Oracle home to the new Oracle9i Oracle home:
    1. If you are upgrading from release 7.3.4 on a UNIX platform, then move or copy the convert file from the Oracle7 Oracle home directory to the new Oracle9i Oracle home directory. On most UNIX platforms, the convert file, convsid.dbf (where sid is the Oracle9i database name), should reside in ORACLE_HOME/dbs in both the Oracle7 and the new Oracle9i environments.

      On Windows operating systems, the convert file, convert.ora, should reside in ORACLE_HOME\rdbms in the new Oracle9i environment. It is automatically placed in this directory by the MIG utility, and you do not need to move it.

    2. If your parameter file resides within the old environment's Oracle home, then copy it to the new Oracle home. By default, Oracle looks for the parameter file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on Windows operating systems. The parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you upgrade to the new Oracle9i release.
    3. If your 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 the new Oracle home. The file specified in the IFILE entry contains additional initialization parameters.
    4. If you have a password file that resides within the old environment's Oracle home, then move or copy the password file to the new Oracle9i Oracle home.

      The name and location of the password file are operating system-specific. On UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid. On Windows operating systems, the default password file is ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.

    5. If you are upgrading a cluster database and your initdb_name.ora file resides within the old environment's Oracle home, then move or copy the initdb_name.ora file to the new Oracle home.


      Note:

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


  4. Adjust your parameter file for use with the new Oracle9i release by completing the following steps:
    1. Remove obsolete initialization parameters and adjust deprecated initialization parameters. Certain initialization parameters are obsolete in the new Oracle9i release. Remove all obsolete parameters from any parameter file that will start a release 9.2 instance. Obsolete parameters may cause errors in the new Oracle9i release. Also, alter any parameter whose syntax has changed in the new Oracle9i release; refer to Appendix A, "Changes to Initialization Parameters and the Data Dictionary" for lists of initialization parameters that have been deprecated or have become obsolete.

      Also, if you are upgrading a cluster database, then see Oracle9i Real Application Clusters Setup and Configuration for more information about obsolete cluster database initialization parameters.

    2. Make sure the SHARED_POOL_SIZE initialization parameter is set to at least 48 MB.
    3. Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to at least 24 MB.
    4. Make sure the LARGE_POOL_SIZE initialization parameter is set to at least 8 MB.
    5. Make sure the COMPATIBLE initialization parameter is properly set for the new Oracle9i release. If COMPATIBLE is set below 8.1.0, then you will encounter the following error when you attempt to start up your release 9.2 database later in Step 11:
      ORA-00401: the value for parameter compatible is not supported by this 
      release
      
      

      Depending on your old release, set COMPATIBLE as follows:

      Old Release Set COMPATIBLE To

      7.3.4

      8.0.6

      Either remove COMPATIBLE from your parameter file, or set COMPATIBLE to 8.1.0.

      8.1.7

      If COMPATIBLE is set to 8.0.x, then either remove COMPATIBLE from your parameter file, or set COMPATIBLE to 8.1.0.

      If COMPATIBLE is set to 8.1.x, then leave the setting as is.

      9.0.1

      If one or more automatic segment-space managed tablespaces exist in the database, then set COMPATIBLE to 9.0.1.3. Otherwise, leave the setting as is.

    6. Make sure the DB_DOMAIN initialization parameter is set properly.

      See Also:

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

    7. On Windows operating systems, 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.

    8. Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.
    9. If the parameter file contains an IFILE entry, then change the IFILE entry in the parameter file to point to the new location of the include file that you specified in Step 3c. Then, edit the file specified in the IFILE entry in the same way that you edited the parameter file in Step a through Step h.
    10. If you are upgrading a cluster database, then modify the initdb_name.ora file in the same way that you modified the parameter file.

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


    Note:

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


  5. Adjust the following initialization parameters for the duration of the upgrade. After the upgrade is complete, you must change the settings for these parameters back to their pre-upgrade settings:
    1. Remove obsolete initialization parameters and adjust deprecated initialization parameters.
    2. If you are using a password file, then set REMOTE_LOGIN_PASSWORDFILE to NONE in the parameter file.
    3. If you are upgrading a cluster database, then set the CLUSTER_DATABASE initialization parameter to false. After the upgrade, you must set this initialization parameter back to true.
    4. If the NLS_LENGTH_SEMANTICS initialization parameter is set to CHAR, then set it to BYTE. This initialization parameter must be set back to CHAR after the upgrade is complete.
  6. If your operating system is UNIX, then make sure that the following environment variables point to the new release 9.2 directories:
    • ORACLE_HOME
    • PATH
    • ORA_NLS33
    • LD_LIBRARY_PATH

    If you are upgrading from release 7.3.4 and ORACLE_HOME points to the Oracle7 executables, then the following error is displayed when you issue the ALTER DATABASE CONVERT statement later in the upgrade process:

    ORA-00223: convert file is invalid or incorrect version
    
    

    Note:

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


    See Also:

    Your operating system-specific Oracle9i installation documents for information about setting other important environment variables on your operating system.

  7. Log in to the system as the owner of the Oracle home directory of the new release.
  8. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
  9. Start SQL*Plus.
  10. Connect to the database instance as a user with SYSDBA privileges.
  11. If the database being upgraded is release 8.0.6 or higher, then start up the database by issuing the following command:
    SQL> STARTUP MIGRATE
    
    

    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.

  12. If the database being upgraded is release 7.3.4, then perform the following steps to start up the database:
    1. Start an Oracle9i database instance without mounting the new Oracle9i database:
      SQL> STARTUP RESTRICT NOMOUNT
      
      

      Caution:

      Starting the database instance in any other mode might corrupt the database.


      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.

    2. Create a new Oracle9i database control file and convert the file headers of all online tablespaces to Oracle9i format by issuing the following statement:
      SQL> ALTER DATABASE CONVERT;
      
      

      Successful execution of this statement is the "point of no return" to Oracle7 for this database. However, if necessary, you can restore the Oracle7 database from backups.

      If errors occur during this step, then correct the conditions that caused the errors and rerun the MIG utility. Otherwise restore the backup you performed after you ran the MIG utility.

      See Also:

      "Problems at the ALTER DATABASE CONVERT Statement" for information about common errors encountered at this step and the actions required to resolve them.

    3. Open the Oracle9i database with the following statement:
      SQL> ALTER DATABASE OPEN RESETLOGS MIGRATE;
      
      

      When the Oracle9i database is opened, all rollback segments that are online are converted to the new Oracle9i format.

      If you encounter errors when you issue this statement, then start the migration process over from the beginning, ensuring the database is not opened in the Oracle7 environment after the Migration utility completes. Start from the beginning of this chapter, but make sure you completed all of the pre-migration steps described in Chapter 2.

  13. Set the system to spool results to a log file for later verification of success:
    SQL> SPOOL upgrade.log
    
    
  14. Run uold_release.sql, where old_release refers to the release you had installed prior to upgrading. See Table 3-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 3-2  Upgrade Scripts
    Old Release Run Script

    7.3.4

    u0703040.sql

    8.0.6

    u0800060.sql

    8.1.7

    u0801070.sql

    9.0.1

    u0900010.sql

    See Also:

    "Determine Your Upgrade Path to the New Release" if the old release you had installed prior to upgrading is not listed in Table 3-2

    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.2 installation.
    • You must run the script in the new release 9.2 environment.
    • You only need to run one script, even if your upgrade spans more than one release. For example, if your old release was 8.1.7, then you only need to run u0801070.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 new 9.2 release, which create the system catalog views and all the necessary packages for using PL/SQL.

    The following components are upgraded by running the uold_release.sql script:

    Oracle9i Catalog Views

    Oracle9i Packages and Types

  15. Display the contents of the component registry to determine which components need to be upgraded:
    SQL> SELECT comp_name, version, status
             FROM dba_registry;
    
    

    The following is an example of the output you will see when issuing this query:

    COMP_NAME                      VERSION          STATUS
    ------------------------------ ---------------- -----------
    Oracle9i Catalog Views         9.2.0.1.0        VALID
    Oracle9i Packages and Types    9.2.0.1.0        VALID
    JServer JAVA Virtual Machine   9.0.1            LOADED
    Java Packages                  9.0.1            LOADED
    Oracle XDK for Java            9.0.1            LOADED
    Oracle Text                    9.0.1            LOADED
    Oracle Workspace Manager       9.0.1.0.0        LOADED
    Oracle interMedia              9.0.0.0.0        LOADED
    Oracle Spatial                 9.0.0.0.0 BETA   LOADED
    Ultrasearch                    9.0.1.0.0        LOADED
    OLAP Catalog                   9.0.1.0.0        LOADED
    
    11 rows selected.
    
    
  16. Run the cmpdbmig.sql script to upgrade components that can be upgraded while connected with SYSDBA privileges:
    SQL> @cmpdbmig.sql
    
    

    The following components are upgraded by running the cmpdbmig.sql script:

    JServer JAVA Virtual Machine

    Oracle9i Java Packages

    Oracle XDK for Java

    Messaging Gateway

    Oracle9i Real Application Clusters

    Oracle Workspace Manager

    Oracle Data Mining

    OLAP Catalog

    OLAP Analytic Workspace

    Oracle Label Security

  17. Display the contents of the component registry to determine which components were upgraded:
    SQL> SELECT comp_name, version, status
             FROM dba_registry;
    
    

    The following is an example of the output you will see when issuing this query:

    COMP_NAME                      VERSION         STATUS
    ------------------------------ --------------- -----------
    Oracle9i Catalog Views         9.2.0.1.0       VALID
    Oracle9i Packages and Types    9.2.0.1.0       VALID
    JServer JAVA Virtual Machine   9.2.0.1.0       VALID
    Oracle9i Java Packages         9.2.0.1.0       VALID
    Oracle XDK for Java            9.2.0.2.0       UPGRADED
    Oracle Text                    9.0.1           LOADED
    Oracle Workspace Manager       9.2.0.1.0       VALID
    Oracle interMedia              9.0.0.0.0       LOADED
    Oracle Spatial                 9.0.0.0.0 BETA  LOADED
    Ultrasearch                    9.0.1.0.0       LOADED
    OLAP Catalog                   9.2.0.1.0       VALID
    OLAP Analytic Workspace        9.2.0.1.0       LOADED
    
    12 rows selected.
    
    
  18. 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 13; the suggested name was upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade scripts if necessary. You can rerun any of the scripts described in this chapter as many times as necessary.

  19. Shut down and restart the instance to reinitialize the system parameters for normal operation. The restart will also perform release 9.2 initialization for JServer JAVA Virtual Machine and other components.
    SQL> SHUTDOWN IMMEDIATE
    
    

    Executing this clean shutdown flushes all caches, clears buffers, and performs other 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 11, then remove the obsolete initialization parameters from the initialization parameter file now.

  20. Upgrade any remaining components that existed in the previous database. See "Upgrading Specific Components".

    The following components require separate upgrade steps:

    Oracle Text

    Oracle Ultra Search

    Oracle Spatial

    Oracle interMedia

    Oracle Visual Information Retrieval

  21. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.
    SQL> @utlrp.sql
    
    

    Verify that all expected packages and classes are valid:

    SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
    SQL> SELECT destinct object_name FROM dba_objects WHERE status='INVALID';
    
    

    Verify that all components are valid and have been upgraded to release 9.2:

    SQL> SELECT comp_name, version, status
             FROM dba_registry;
    
    

Your database is now upgraded to the new 9.2 release. Complete the procedures described in Chapter 4, "After Upgrading a Database".


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


Upgrading Specific Components

Some components of the Oracle database server require an upgrade separate from the general database upgrade operation. Complete the actions in the following sections to upgrade components that were not automatically upgraded.


Note:

You should perform the actions described in these sections only after you have upgraded the database by following the instructions earlier in this chapter.


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

Upgrading Oracle interMedia

Upgrade instructions for Oracle 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.

Upgrading Oracle Visual Information Retrieval

Upgrade instructions for Oracle Visual Information Retrieval 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 text_upgrade.log
    
    
  8. If you are upgrading from release 8.1.7, then complete the following steps. Skip to Step 9 if you are upgrading from release 9.0.1.
    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
      
      
    4. Connect to the database instance as a user with SYSDBA privileges.
  9. If you are upgrading from release 8.1.7 or release 9.0.1, then complete the following steps.
    1. Run s0902000.sql:
      SQL> @s0902000.sql
      
      

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

    2. Connect to the database instance as user CTXSYS.
    3. Run u0902000.sql:
      SQL> @u0902000.sql
      
      

      This script upgrades the CTXSYS schema to release 9.2.

    4. Connect to the database instance as a user with SYSDBA privileges.
  10. Check for any invalid CTXSYS objects and alter compile as needed.
  11. 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 text_upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade scripts if necessary.

  12. Shut down the instance:
    SQL> SHUTDOWN IMMEDIATE
    
    
  13. Exit SQL*Plus.

Oracle Text is upgraded to the new release.

Upgrading Oracle Ultra Search

If the Oracle system has Oracle Ultra Search installed, then see the Oracle Ultra Search Online Documentation for instructions about upgrading Oracle Ultra Search to release 9.2.

Troubleshooting Manual Upgrades

There are three resources that generally require increases for a new Oracle release:

If you run out of one of these resources during the upgrade, then increase the resource allocation and re-run the appropriate upgrade scripts.

SYSTEM Tablespace

Typically you will receive one of the following messages during the upgrade if your SYSTEM tablespace size is insufficient:

ORA-01650: unable to extend rollback segment string by string in tablespace 
string
ORA-01651: unable to extend save undo segment by string for tablespace string
ORA-01652: unable to extend temp segment by string in tablespace string
ORA-01653: unable to extend table string.string by string in tablespace string
ORA-01654: unable to extend index string.string by string in tablespace string
ORA-01655: unable to extend cluster string.string by string in tablespace 
string

Shared Memory

You will require larger shared memory pool sizes, particularly if you have JServer in the database. The error message will indicate which shared memory initialization parameter needs to be increased.

ORA-04031: unable to allocate string bytes of shared memory 
("string","string","string","string")

Refer to Oracle9i Database Reference for information about shared memory initialization parameters.

Public Rollback Segment

If you are using rollback segments, then you need to have a single large (70 MB) PUBLIC rollback segment online while the upgrade scripts are being run. Smaller public rollback segments should be taken offline during the upgrade. Typically you will get the following error if your rollback segment size is insufficient:

ORA-01562: failed to extend rollback segment number string