Upgrading to Oracle Database 10g - Performing a Manual Direct Upgrade
(Page 4 of 4 )

In a manual upgrade, you must perform the steps that DBUA performs. The result will be a direct upgrade of the database in which you are responsible for (and control) each step in the upgrade process.

You should use the Pre-Upgrade Information Tool to analyze the database prior to its upgrade. This tool is provided in a SQL script that is installed with the Oracle Database 10g software; you will need to run it against the database to be upgraded. The file, named utlu 101i.sql, is located in the /rdbms/admin subdirectory under the Oracle 10g software home directory. You should run that file in the database to be upgraded as a SYSDBA-privileged user, spooling the results to a log file. The results will show potential problems that should be addressed prior to the upgrade.

If there are no issues to resolve prior to the upgrade, you should shut down the database and perform an offline backup before continuing with the upgrade process.

Once you have a backup you can restore if needed, you are ready to proceed with the upgrade process. The process is detailed and script-based, so you should consult with the Oracle installation and upgrade documentation for your environment and version. The steps are as follows:

1. Copy configuration files (init.ora, spfile.ora, password file) from their old location to the new Oracle software home directory. By default, the configuration files are found in the /dbs subdirectory on UNIX platforms and the /database directory on Windows platforms.
   
2. Remove obsolete and deprecated initialization parameter from the configuration files. Update the COMPATIBLE parameter for Oracle 10. Make sure your SHARED_POOL_ SIZE parameter is set to at least 96MB for 32-bit platforms and at least 144MB for 64-bit platforms. Set PGA_AGGREGATE_TARGET to at least 24MB, LARGE_POOL_SIZE to at least 8MB, and JAVA_POOL_SIZE to at least 48MB. For Windows parameters, set BACKGROUND_DUMP_DEST to \oradata\database_name\bdump under the Oracle base software directory, and set USER_DUMP_DEST to \oradata\database_name\udump under the Oracle base software directory. Use full pathnames in the parameter files.
   
3. If you are upgrading a cluster database, set the CLUSTER_DATABASE initialization parameter to FALSE. After the upgrade, you must set this initialization parameter back to TRUE.
   
4. Shut down the instance.
   
5. If you are using Windows, stop the service associated with the instance and delete the Oracle service at the command prompt. For Oracle 8.0, use the command ORADIM80              –DELETE –SID instance_name. For Oracle8.1 and higher, use ORADIM –DELETE –SID instance_name. Then create the new Oracle Database 10g service using ORADIM, as shown here:
   
   C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
        -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
   
   The following variables are available for this command:
   
   

    

   Variable	Description
   SID	The name of the SID (instance identifier) of the database you are upgrading.
   PASSWORD	The password for the new release 10.1 database instance. This is the password for the user connected with SYSDBA privileges. If you do not specify INTPWD, 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.1 Oracle home directory. Ensure that you specify the full pathname with the -PFILE option, including the drive letter of the Oracle home directory.

   
   
6. If your operating system is UNIX, make sure the following environment variables point to the new release 10.1 directories: ORACLE_HOME, PATH, ORA_NLS33, and LD_ LIBRARY_PATH.
   
7. Log into the system as the owner of the Oracle Database 10g software.
   
8. Change your directory to the /rdbms/admin subdirectory under the Oracle software home directory.
   
9. Connect to SQL*Plus as a user with SYSDBA privileges.
   
10. Issue the startup upgrade command.
    
11. Use the spool command to log the results of the following steps.
    
12. Create a SYSAUX tablespace via the create tablespace command. You should allocate SYSAUX between 500MB and 5GB of disk space, depending on the number of user objects. SYSAUX must be created with the following clauses: online, permanent, read write, extent management local, and segment space management auto. All those clauses except segment space management auto are the defaults. Here’s an example:

    create tablespace SYSAUX
      datafile '/u01/oradata/db1/sysaux01.dbf'
      size 500m reuse
      extent management local
      segment space management auto
      online;

13. Run the script for the old release. For example, if you are upgrading from Release 8.0.6, run only the u0800060.sql script and then move on to the next step in the upgrade process. Consult the following table for which script to run.

     

    Upgrading From:	Run Script:
    8.0.6	u0800060.sql
    8.1.7	u0801070.sql
    9.0.1	u0900010.sql
    9.2	u0902000.sql

14. Stop spooling (via spool off) and review the spool file for errors. Resolve any problems identified there.

15. Run the utlu101s.sql file, with TEXT as the input parameter:
    
    @utlu101s TEXT
    
    Oracle will then display the status of the upgrade. The upgrade elements should all be listed with a status of “Normal successful completion.”

16. Shut down and restart the instance. 

17. Run the utlrp.sql script to recompile invalid packages. You can then verify that all packages and classes are valid:
    
    select distinct Object_Name from DBA_OBJECTS
      where Status = 'INVALID';

18. Exit SQL*Plus.

19. Shut down the database and perform an offline backup of the database; then restart the database. The upgrade is complete.

NOTE

After the upgrade, you should never start your Oracle 10g database with the software from an earlier release.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Upgrading to Oracle Database 10g

· This article is an excerpt from the book "Oracle Database 10g DBA Handbook,"...    · Hello,I am getting following error while Pre-upgrade check in DBUAError in...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter two of the Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Check it out today at your favorite bookstore. Buy this book now.