Oracle provides upgrade paths for most RDBMS versions, however they are not as simple as some would like. I have ran into several Junior to Mid-level DBAâ€™s who appear to have a difficult time grasping the general steps required to perform this task or just want a Senior person present for them to perform sanity checks with. Thus, I have decided to outline the general steps required to perform the upgrade from 9i to 10g.
1. Downloading the following software from www.oracle.com if you do not have the media available:
â€¢ Oracle 10.2.0.1 (this is the base release of Oracle 10g R2)
â€¢ Oracle 10.2.0.1 Companion CD (if needed)
â€¢ Oracle 10.2.0.4 (This is patch set 6810189)
â€¢ Latest Oracle CPU Patch
2. Shutdown your current Oracle 9i database system and listener. Do not delete any of the 9i binaries or database objects.
$ sqlplus ” / as sysdba”
SQL> shutdown immediate
$ cd network/admin
$ lsnrctl stop LISTENER
3. Unpack or Unzip (dependent on OS) your 10.2.0.1 database installation. Execute the runInstaller. Note: It is most likely that your OS will be of a higher version, than is supported by this installer. You may have to use the tags â€œâ€“jreLoc â€“ignoreSysPrereqsâ€ to bypass the OS support version. Example:
./runInstaller -jreLoc /opt/java1.4/jre -ignoreSysPrereqs
4. At the Welcome to the Oracle Database 10g Installation page, check the Advanced Installation option and press Next.
5. At the Select Installation Type page, select Enterprise Addition and press Next.
6. At the Specify Home Details page, enter OraDb10g for Name and /app/oracle/10.2.0 for the path and press Next.
7. At the Product-Specific Prerequisite Checks page the Installer will verify the system is ready for installation.
8. At the Product-Specific Prerequisite Checks, review any warning received and press Next.
9. At the Upgrade an Existing Database page, select No and press next.
10. At the Select Configuration Options page, select Install Database Software Only and press Next.
11. At the Summary page, press Install.
12. Once the installer request for you to execute shell script root.sh, contact the SA support for your system and have them execute, then click OK.
13. Record the following output in a safe place and click Exit.
The following J2EE Applications have been deployed and are accessible at the
URLs listed below.
iSQL*Plus DBA URL:
14. This completes Installation of 10.2.0.1 software.
15. Install Oracle companion component. (if desired)
16. Upgrade to 10.2.0.4, unzip the Oracle patch set 6810189 and execute the runInstaller utility. NOTE: You may again have to user the â€“jreLoc and â€“ignoreSysPrereqs tags. Example
./runInstaller -jreLoc /opt/java1.4/jre -ignoreSysPrereqs
17. At the Welcome page, select Next.
18. At the Specify Home Details page, enter OraDb10g in Name and /app/oracle/10.2.0 in Path and press Next
19. At the Product-Specific Prerequisite Checks page, press Next.
20. At the Oracle Configuration Manager Registration page, press Next.
21. At the Summary page, press Install.
22. Once requested to execute the script: app/oracle/10.2.0/root.sh, contact the SA and have them execute as root, then press OK.
23. At the End of Installation page, record the below information and press Exit.
The iSQL*Plus URL is:
The iSQL*Plus DBA URL is:
24. At the screen ‘Do you really want to exit?’, press Yes.
25. This completes the upgrade to 10.2.0.4
26. Apply latest CPU patch. Logon to Metalink at metalink.oracle.com and download the latest CPU patch for Oracle 10.2.0.4. In this example, we will download CPU Jan 2009 – patch number 7592346
27. Unzip the patch file.
28. Ensure that the ORACLE_HOME parameter is point to 10.2.0.4 install by setting the parameter again.
29. Edit your .profile file to ensure that OPATCH parameter is pointing at the correct OPatch directory and your PATH parameter is specifying the correct $ORACLE_HOME/bin directory. NOTE: The OPatch utility installed with 10.2.0.1 maybe too old for your CPU patch, see the README in your CPU to determine the correct OPatch version to use. You can navigate to $ORACLE_HOME/OPatch and execute the command â€œopatch versionâ€ to determine you current version.
30. Note: you may have to user the -jre tag to make the patch work.
opatch napply -skip_subset -skip_duplicate -jre /opt/java1.4/jre
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate -jre
31. This completes installation of the latest CPU patch.
Post installation Steps
32. Update the .profile file to point to the new Oracle installation and make sure the following environment variables point to the new ORACLE installation.
33. Logout and login to reset your environment variables.
34. Copy the init.ora file from existing 184.108.40.206 $ORACLE_HOME/dbs to 10g $ORACLE_HOME/dbs folder
Linux1:/app/oracle/220.127.116.11.0/dbs:>cp initorcl.ora ../../10.2.0/dbs/.
35. NOTE: See listing at bottom of this article for all non-supported 9i init parameters which must be removed or commented out.
36. Increase large pool size=200M in init.ora file.
37. Change folders to $ORACLE_HOME/rdbms/admin/. This is the folder where the SQL files for upgrade are stored.
$ cd $ORACLE_HOME/rdbms/admin
38. Start the database in upgrade mode
$>sqlplus / as sysdba
39. Create sysaux tablespace
Ex:CREATE TABLESPACE sysaux DATAFILE
‘/orcl/oradata/orcl/data1/sysaux02.dbf’ SIZE 4096M REUSE EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;
40. Issue unlimited quota for system
ALTER USER SYSTEM QUOTA UNLIMITED ON system;
41. Start the upgrade by running catupgrd.sql
SQL> spool /scripts/upgrade.log
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Check the spool file and verify that the packages and procedures compiled successfully. You named the spool file earlier in this step; the suggested name was upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade script if necessary. You can rerun any of the scripts described in this note as many times as necessary.
42. Run utlu102s.sql, specifying the TEXT option:
SQL> @utlu102s.sql TEXT
43. Insure that all temp files from the production system are attached to temporary tablespace in converted database. If not recreate temp files.
alter tablespace TEMP add tempfile â€˜/orcl/oradata/orcl/data1/temp_01.dbf’
44. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.
Verify that all expected packages and classes are valid:
45. If there are still objects which are not valid after running the script run
Select substr(owner,1,12) owner,
substr(object_type,1,30) type, status
from dba_objects where status <>‘VALID’;
46. Copy old listeners.ora and tnsnames.ora from the previous 18.104.22.168 oracle installation and update their ORACLE_HOME references.
â€¢ Copy files to new ORACLE_HOME/network/admin folder
cp listener.ora $ORACLE_HOME/network/admin
cp tnsnames.ora $ORACLE_HOME/network/admin
â€¢ Change folders to $ORACLE_HOME/network/admin
â€¢ Update the newly copied files to point to the new ORACLE_HOME location
47. Restart the database in a normal mode with â€œstartup openâ€
48. It is a good idea to know rebuild all of your indexes and gather statistics.
49. NOTE: You will not get all of the benefits of Oracle 10g until you set the COMPATIBILITY parameter to a minimum of 10.1
Larry J Catt, OCP 9i, 10g
Appendix A: Initialization Parameters Obsolete in 10g
Appendix B: Initialization Parameters Deprecated in 10g
BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)