3 Upgrading to the New Oracle Database 10g Release

Upgrading a Cluster Database

If you are upgrading a cluster database, then most of the actions described in this chapter should be performed on only one node of the system. Actions that need to be performed on more than one node will be indicated in that particular step.

Upgrading With Read-Only and Offline Tablespaces

The Oracle database can read file headers created prior to Oracle 10g, so you do not need to do anything to them during the upgrade. The only exception to this is if you want to transport tablespaces created prior to Oracle 10g, to another platform. In this case, the file headers must be made read-write at some point before the transport. However, there are no special actions required on them during the upgrade.

The file headers of offline datafiles are updated later when they are brought online, and the file headers of read-only tablespaces are updated if and when they are made read-write sometime after the upgrade. In any other circumstance, read-only tablespaces never have to be made read-write.

Upgrading Standby Databases

The methods by which you can perform an upgrade in an Oracle Data Guard configuration are described in the following sections:

• Upgrading Databases in an Oracle Data Guard Configuration

• Upgrading Databases in a Broker Configuration

• Using SQL Apply to Perform a Rolling Upgrade of Oracle Databases

Upgrading Your Operating System

If required, upgrade the operating system before upgrading the Oracle database.

Migrating Data to a Different Operating System

When using the Database Upgrade Assistant or when performing a manual upgrade, you cannot migrate data in a database on one operating system to a database on another operating system. For example, you cannot migrate data in an Oracle9i database on Solaris to an Oracle Database 10g database on Windows 2000 using the Database Upgrade Assistant.

If you need to migrate Oracle Database to a different operating system, the best practice is to follow these steps:

1. Upgrade to the newest Oracle Database 10g release on the current operating system platform following the instructions in this book.

2. Use the Oracle Database 10g cross-platform transportable tablespace feature or the Oracle Data Pump Export and Import utilities to migrate the upgraded database to the different operating system.

Using the Pre-Upgrade Information Tool

The Pre-Upgrade Information Tool is a SQL script that ships with the new Oracle Database 10g release, and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool:

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

2. Copy the following file from the ORACLE_HOME/rdbms/admin directory of the new Oracle Database 10g release to a directory outside of the Oracle home, such as the temporary directory on your system:

   • utlu102i.sql

   Make a note of the new location of this file.

3. Log in to the system as the owner of the Oracle home directory of the database to be upgraded.

4. Change to the directory outside of the Oracle home directory that you copied files to in Step 2.

5. Start SQL*Plus.

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

7. Set the system to spool results to a log file for later analysis:

   SQL> SPOOL info.log
   
   

8. Run utlu102i.sql:

   SQL> @utlu102i.sql
   
   

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

   SQL> SPOOL OFF
   
   

   Then, check the spool file and examine the output of the upgrade information tool. You named the spool file in Step 7; the suggested name was info.log.

The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 10.2 Upgrade Information Utility    05-25-2005 05:19:08
.
**********************************************************************
Database:
**********************************************************************
--> name:       RBX0
--> version:    9.2.0.6.0
--> compatible: 9.2.0
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 583 MB
.... AUTOEXTEND additional space required: 163 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
--> CWMLITE tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 27 MB
.... AUTOEXTEND additional space required: 7 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 150 MB
.... AUTOEXTEND additional space required: 1 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 48 MB
.... AUTOEXTEND additional space required: 3 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 11 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 178499994
WARNING: --> "java_pool_size" needs to be increased to at least 67108864
WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No deprecated parameters found. No changes are required.
.
**********************************************************************
Obsolete Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "hash_join_enabled"
--> "max_enabled_roles"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle Data Mining           [upgrade]  VALID
--> Messaging Gateway            [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  UPGRADED
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  UPGRADED
--> Oracle interMedia            [upgrade]  VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial                      [upgrade]  VALID
--> Oracle Ultra Search          [upgrade]  VALID
... To successfully upgrade Ultra Search, install it from
... the 10g Companion CD.
--> Oracle Label Security        [upgrade]  VALID
... To successfully upgrade Oracle Label Security, perform
... a Custom install and select the OLS option.
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Workspace Manager replication is in use.
.... Drop OWM replication support before upgrading:
.... EXECUTE dbms_wm.DropReplicationSupport;
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   XDB
....   OLAPSYS
....   MDSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER SYS has 1 INVALID objects.
WARNING: --> Database contains globally authenticated users.
.... Refer to the 10g Upgrade Guide to upgrade SSL users.
WARNING: --> OLS requires post-upgrade action to update policy triggers.
.... Run rdbms/admin/olstrig.sql after the upgrade.
.
***************************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

The following sections describe the output of the Pre-Upgrade Information Tool.

Issues Requiring Further Analysis Prior to Upgrading

If the Pre-Upgrade Utility displays a warning about any of the following issues, then further analysis of the database is recommended prior to upgrading to Oracle Database 10g:

• Deprecated CONNECT Role

• Database Links With Passwords

• TIMESTAMP WITH TIMEZONE Datatype

• Release 8.1.7 National Character Set

• Optimizer Statistics

SELECT grantee FROM dba_role_privs
     WHERE granted_role = 'CONNECT' and
                     grantee NOT IN (
                                'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 
                                'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',  
                                'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY', 
                                'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                                'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',  
                                  'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                                 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS:

Using the Database Upgrade Assistant Graphical User Interface

If you installed the new Oracle Database 10g release and specified that you are upgrading an existing database, then the Database Upgrade Assistant is started automatically. However, if you did not specify that you are upgrading an existing database, then you can start the Database Upgrade Assistant independently after installation is complete.

Complete the following steps to start the Database Upgrade Assistant:

1. In the environment of the new Oracle Database 10g release, start the Database Upgrade Assistant.

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

   dbua
   
   

   Note:

   The dbua executable is usually located in ORACLE_HOME/bin.

   On Windows operating systems, choose:

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

When the Database Upgrade Assistant starts, its Welcome screen appears. Figure 3-1 shows the Welcome screen of the Database Upgrade Assistant. Before the upgrade, the DBUA performs the following steps:

• Check for any invalid user accounts or roles

• Check for any invalid datatypes or invalid objects

• Check for any desupported character sets

• Check for adequate resources, including rollback segments, tablespaces, and free disk space

• Check for any missing SQL scripts needed for the upgrade

• Optionally, DBUA backs up all necessary files

The DBUA does not begin the upgrade until all of these pre-upgrade steps are completed.

During the upgrade, the DBUA automatically modifies or creates new required tablespaces and invokes the appropriate upgrade scripts.

While the upgrade is running, the DBUA shows the upgrade progress for each component. The DBUA writes detailed trace and log files and produces a complete HTML report for later reference. To enhance security, the DBUA automatically locks new user accounts in the upgraded database. The DBUA then proceeds to create new configuration files (parameter and listener files) in the new Oracle home.

If you need detailed instructions on using the DBUA graphical user interface, see Appendix D, "Using the Database Upgrade Assistant".

Figure 3-1 Welcome Screen of the Database Upgrade Assistant

Description of the illustration dbua.gif

Using the Database Upgrade Assistant in Silent Mode

When invoked with the -silent command line option, the Database Upgrade Assistant operates in silent mode. In silent mode, the Database Upgrade Assistant does not present a user interface. It also writes any messages (including information, errors, and warnings) to a log file.

For example, the following command upgrades a database named ORCL in silent mode:

dbua -silent -dbName ORCL &

Database Upgrade Assistant Command Line Options

The Database Upgrade Assistant supports several command line options. You can specify all valid options from the command line using the following syntax:

dbua [ -silent ] [ -dbName SID ]
 [ -disableUpgradeScriptLogging ] [ -backupLocation directory ]
 [ -postUpgradeScripts script [, script ] ... ]
 [ -initParam parameter=value [, parameter=value ] ... ]
 [ -emConfiguration { LOCAL | CENTRAL | NOBACKUP | NOEMAIL | NONE }
 -dbsnmpPassword password -sysmanPassword password
 [ -hostUserName hostname -hostUserPassword password -backupSchedule hh:mm ]
 ]
 [ -smtpServer server_name -emailAddress address ]
 -centralAgent location
 ]
 [ -recoveryAreaDestination directory ] [ -h | -help ]

Table 3-2 describes the various options and their parameters that are supported by the Database Upgrade Assistant.

Back Up the Database

After cleanly shutting down the instance following the analysis of the database, you should perform a full backup of the database. Complete the following steps:

1. Sign on to RMAN:

   rman "target / nocatalog"
   
   

2. Issue the following RMAN commands:

   RUN
   {
       ALLOCATE CHANNEL chan_name TYPE DISK;
       BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
       BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
   }
   
   

Prepare the New Oracle Home

After analyzing the database to be upgraded, complete the following steps to prepare the new Oracle home:

1. Copy configuration files from the Oracle home of the database being upgraded to the new Oracle Database 10g Oracle home:

   1. 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 Oracle Database 10g.

      Tip:

      It may be necessary to create a text initialization parameter file (pfile) from the server parameter file (spfile) so that you can edit the initialization parameters.

   2. If your parameter file is a text-based initialization parameter file with either an IFILE (include file) or a SPFILE (server parameter file) entry, and the file specified in the IFILE or SPFILE entry resides within the old environment's Oracle home, then copy the file specified by the IFILE or SPFILE entry to the new Oracle home. The file specified in the IFILE or SPFILE entry contains additional initialization parameters.

   3. 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 Oracle Database 10g 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.

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

2. Adjust your parameter file in the new Oracle Database 10g release by completing the following steps:

   1. Remove obsolete initialization parameters and adjust deprecated initialization parameters. Certain parameters are obsolete in the new Oracle Database 10g release, while other parameters have become deprecated. Remove all obsolete parameters from any parameter file that will start a release 10.2 instance. Obsolete parameters may cause errors in the new Oracle Database 10g release. Also, alter any parameter whose syntax has changed in the new Oracle Database 10g release.

      The Pre-Upgrade Information Tool displays any deprecated parameters and obsolete parameters it finds in the Deprecated Parameters and Obsolete Parameters sections, respectively.

      See Also:

      Appendix A, "Initialization Parameter and Data Dictionary Changes" for a list of initialization parameters that have been deprecated or have become obsolete

   2. Make sure the COMPATIBLE initialization parameter is properly set for the new Oracle Database 10g release. The Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set.

   3. Adjust the values of the initialization parameters to at least the minimum value indicated the Pre-Upgrade Information utility.

   4. Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.

   5. 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 1. b. Then, edit the file specified in the IFILE entry in the same way that you edited the parameter file in Step ` through Step d.

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

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.

Upgrade the Database

After preparing the new Oracle home, you are ready to proceed with the manual upgrade. Complete the following steps to upgrade the database:

1. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   
   

   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.

      If your SID is ORCL, then enter the following command:

      C:\> ORADIM -DELETE -SID ORCL
      
      

   3. Create the new Oracle Database 10g service at a command prompt using the ORADIM command of the new Oracle Database release:

      C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
           -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
      
      

      This syntax includes the following variables:

      Variable	Description
      SID	The same SID name as the SID of the database you are upgrading.
      PASSWORD	The password for the new release 10.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	The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
      ORACLE_HOME	The release 10.2 Oracle home directory. Ensure that you specify the full path name 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
      
      

2. If your operating system is UNIX, then make sure that your ORACLE SID is set correctly and that the following environment variables point to the new release 10.2 directories:

   • ORACLE_HOME

   • PATH

   • ORA_NLS10

   • LD_LIBRARY_PATH

   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 Oracle Database installation documents for information about setting other important environment variables on your operating system.

3. Log in to the system as the owner of the Oracle home directory of the new Oracle Database 10g release.

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

5. Start SQL*Plus.

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

7. Start up the instance by issuing the following command:

   SQL> STARTUP UPGRADE
   

   Note:

   The UPGRADE keyword allows you to open a pre-10.2 database. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.

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

   The following are common errors that may occur when attempting to start the new Oracle Database 10g release. If you receive any of these errors, issue the SHUTDOWN ABORT command to shut down the database and correct the problem.

   • If the COMPATIBLE initialization parameter is set to a value less than "9.2.0":

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

   • If the CLUSTER_DATABASE initialization parameter is set to true instead of false:

     ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
     
     

   • If the STARTUP command was issued without the UPGRADE keyword:

     ORA-39700: database must be opened with UPGRADE option
     
     

   • If a redo log's size is less than 4 MB:

     ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks
     
     

   If errors appear listing obsolete initialization parameters, then make a note of the obsolete initialization parameters and continue with the upgrade. Then, remove the obsolete initialization parameters the next time you shut down the database.

8. If you are upgrading from release 10.1, then skip to step 9. Otherwise, if you are upgrading from release 8.1.7, 9.0.1, or 9.2.0, then create a SYSAUX tablespace. In Oracle Database 10g, the SYSAUX tablespace is used to consolidate data from a number of tablespaces that were separate in previous releases.

   The SYSAUX tablespace must be created with the following mandatory attributes:

   • ONLINE

   • PERMANENT

   • READ WRITE

   • EXTENT MANAGEMENT LOCAL

   • SEGMENT SPACE MANAGEMENT AUTO

   The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section. Table 3-3 can be used to determine an optimal size for the SYSAUX tablespace.

   Table 3-3 Guidelines for Sizing the SYSAUX Tablespace

   Factor	Small	Medium	Large
   Number of CPUs	2	8	32
   Number of concurrently active sessions	5	20	100
   Number of user objects (tables and indexes)	500	5,000	50,000
   Estimated SYSAUX size at steady state with default config	500 MB	2 GB	5 GB

   
   

   The following SQL statement would create a 500 MB SYSAUX tablespace for the database:

   SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
            SIZE 500M REUSE
            EXTENT MANAGEMENT LOCAL
            SEGMENT SPACE MANAGEMENT AUTO
            ONLINE;
   
   

   See Also:

   Oracle Database Administrator's Guide for more information about the SYSAUX tablespace

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

   SQL> SPOOL upgrade.log
   
   

10. Run catupgrd.sql:

    SQL> @catupgrd.sql
    
    

    The catupgrd.sql script determines which upgrade scripts need to be run and then runs each necessary script. You must run the script in the new release 10.2 environment.

    The upgrade script creates and alters certain data dictionary tables. It also upgrades or installs the following database components in the new release 10.2 database:

    • Oracle Database Catalog Views

    • Oracle Database Packages and Types

    • JServer JAVA Virtual Machine

    • Oracle Database Java Packages

    • Oracle XDK

    • Oracle Real Application Clusters

    • Oracle Workspace Manager

    • Oracle interMedia

    • Oracle XML Database

    • OLAP Analytic Workspace

    • Oracle OLAP API

    • OLAP Catalog

    • Oracle Text

    • Spatial

    • Oracle Data Mining

    • Oracle Label Security

    • Messaging Gateway

    • Expression Filter

    • Oracle Enterprise Manager Repository

11. Run utlu102s.sql to display the results of the upgrade:

    SQL> @utlu102s.sql
    
    

    The Post-upgrade Status Tool displays the status of the database components in the upgraded database and the time required to complete each component upgrade. The Upgrade Status Tool displays output similar to the following:

    Oracle Database 10.2 Upgrade Status Utility           04-20-2005 05:18:40
    .
    Component                                Status         Version  HH:MM:SS
    Oracle Database Server                    VALID      10.2.0.1.0  00:11:37
    JServer JAVA Virtual Machine              VALID      10.2.0.1.0  00:02:47
    Oracle XDK                                VALID      10.2.0.1.0  00:02:15
    Oracle Database Java Packages             VALID      10.2.0.1.0  00:00:48
    Oracle Text                               VALID      10.2.0.1.0  00:00:28
    Oracle XML Database                       VALID      10.2.0.1.0  00:01:27
    Oracle Workspace Manager                  VALID      10.2.0.1.0  00:00:35
    Oracle Data Mining                        VALID      10.2.0.1.0  00:15:56
    Messaging Gateway                         VALID      10.2.0.1.0  00:00:11
    OLAP Analytic Workspace                   VALID      10.2.0.1.0  00:00:28
    OLAP Catalog                              VALID      10.2.0.1.0  00:00:59
    Oracle OLAP API                           VALID      10.2.0.1.0  00:00:53
    Oracle interMedia                         VALID      10.2.0.1.0  00:08:03
    Spatial                                   VALID      10.2.0.1.0  00:05:37
    Oracle Ultra Search                       VALID      10.2.0.1.0  00:00:46
    Oracle Label Security                     VALID      10.2.0.1.0  00:00:14
    Oracle Expression Filter                  VALID      10.2.0.1.0  00:00:16
    Oracle Enterprise Manager                 VALID      10.2.0.1.0  00:00:58
    .
    Total Upgrade Time: 00:56:09
    
    

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 9; the suggested name was upgrade.log. Correct any problems you find in this file (see "Troubleshoot the Upgrade") and rerun the catupgrd.sql script, if necessary. You can rerun any of the scripts described in this chapter as many times as necessary. (If you have JAVAVM in your database, but you did not install the companion Oracle Database 10g Products, you will most likely receive error message ORA-29558.)

13. Shut down and restart the instance to reinitialize the system parameters for normal operation.

    SQL> SHUTDOWN IMMEDIATE
    SQL> STARTUP
    
    

    Cleanly shutting down and restarting the instance 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 Oracle Database.

    Also, if you encountered a message listing obsolete initialization parameters when you started the database in Step 7, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the spfile to a pfile so you can edit the file to delete parameters.

14. Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies. (See Oracle Database Enterprise User Administrator's Guide for more information.) This step is only necessary if Oracle Label Security is in your database.

    SQL> @olstrig.sql
    
    

15. 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 distinct object_name FROM dba_objects WHERE status='INVALID';
    
    

16. Exit SQL*Plus.

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

Troubleshoot the Upgrade

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

• SYSTEM tablespace

• Shared memory

• Rollback segments/Undo Tablespace

If you run out of one of these resources during the upgrade, then increase the resource allocation. After increasing the resource allocation, you should perform a SHUTDOWN ABORT and restart the instance (in UPGRADE mode) before rerunning the catupgrd.sql script or restarting the Database Upgrade Assistant.

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

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

ORA-01562: failed to extend rollback segment number string

Abandon the Upgrade

If you completed the steps in "Back Up the Database" to back up your database, then the easiest way to abandon the upgrade is to restore that backup. Complete the following steps:

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

2. Sign on to RMAN:

   rman "target / nocatalog"
   
   

3. Issue the following RMAN commands:

   STARTUP NOMOUNT
   RUN
   {
       REPLICATE CONTROLFILE FROM 'save_controlfile_location';
       ALTER DATABASE MOUNT;
       RESTORE DATABASE FROM TAG before_upgrade
       ALTER DATABASE OPEN RESETLOGS;
   }