Upgradation Oracle9i database to Oracle10g Release2 (10.2)

    Oracle® Database

    Upgradation Installation Guide

            Oracle9i database to Oracle10g Release2 (10.2)

Version TEST 1.0

       

Document Details

	Prepared by / Last Updated By	Reviewed by	Approved by
Name
Role
Signature
Date

NOTES 

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

Overview

This guide provides step-by-step instructions for upgrading Oracle 9i database  to Oracle 10g database

Scope

This document is intended to serve as an Upgradation installation guide for the Oracle 9i database to 10g.

You can upgrade to Oracle Database 10g in two ways:
• The traditional manual mode
• By using the Database Upgrade Assistant (DBUA)Note: The DBUA is a GUI tool, but you can also run it in the silent mode, by using the following command at the operating system level: dbua

Using New Utility to Perform Pre-Upgrade Validation Checks

Oracle now includes a brand-new tool, called the Upgrade Information Tool, to help you collect various pieces of critical information before you start the upgrade process.

The Upgrade Information Tool provides important information and actions you should do before upgrading the existing database.

If you are performing a manual upgrade, you need to invoke the tool by running the SQL script utlu10*i.sql. The DBCA automatically runs it as part of the pre-upgrade check.Note: In Oracle 10g Release 2, the Pre-Upgrade Information Utility (utlu102i.sql) has been enhanced to provide improved resource estimations for tablespace space usage and elapsed upgrade runtime.

The Post-Upgrade Status Tool

Oracle Database 10g also provides a Post-Upgrade Status Tool (utlu10*s.sql), which gives you an accurate summary of the upgrade process and any necessary corrective steps to be taken.

You can restart a failed database upgrade job from the point where you failed.
If you use the DBUA to upgrade, the script runs automatically. If you are performing a manual upgrade, you need to run the script yourself, after the upgrade process is finished.

Using the Simplified Upgrade Process

Oracle provides the DBUA to facilitate the database upgrade process. You can use the DBUA to upgrade any database configuration, including RAC and standby databases.

The DBUA takes care of the following tasks for you:

• Deletes all obsolete initialization parameters
• Changes the ORACLE_HOME settings automatically
• Runs the appropriate upgrade scripts for your current release
• Configures your listener.ora fileStarting DBUA
On Windows: Programs | Oracle | Configuration and Migration Tools | Database Upgrade Assistant.

On a UNIX system: simply type dbua
Silent startup: dbua -silent –dbName nina

Manual Upgrade Process
Steps in the Manual Upgrade Process

1. Start a Spool File
SQL> spool upgrade.log

2. Run the Upgrade Information Tool
SQL> @$ORACLE_HOME/rdbms/admin/utlu101i.sql
SQL> spool off

3. Back Up Your Database
At this point, shut down and back up your current database, by using either the RMAN or by using user-managed backup techniques.

4. Copy Your init.ora File
Copy your present init.ora file to the new Oracle Database 10g default location:
o %ORACLE_HOME%\database on Windows with the name: init%ORACLE_SID%.ora
o $ORACLE_HOME/dbs under UNIX with the name: init$ORACLE_SID.ora

Make all the necessary changes in your init.ora parameter file, as per the Upgrade Information Tool’s recommendations.

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. Make modifications in the file in the same way as made in the init.ora file.

6. 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.
 
7. Shut down the instance:
SQL> SHUTDOWN IMMEDIATE

8. Completely remove any Windows-Based Oracle Instances
C:\>net stop oracleservicefinance
C:\>oradim -delete -sid finance
C:\>oradim -new -sid finance -intpwd finance1 -startmode auto –pfile c:\oracle\product\10.1.0\Db_1\database\initfinance.ora

9. If your operating system is UNIX, then make sure that your ORACLE_SID is set correctly and that the following variables point to the new release directories: ORACLE_HOME,PATH,ORA_NLS10,LD_LIBRARY_PATH

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

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

12. Start Up the New Database
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup upgrade

Using the startup upgrade command tells Oracle to automatically modify certain parameters, including initialization parameters that cause errors otherwise

13. If you are upgrading from a release other than 10.1, create the SYSAUX Tablespace. The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section.
CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE

14. If you upgrading to release 1, run the Upgrade Script. Run the Upgrade Script corresponding to the Oracle version you would like to upgrade:
o 8.0.6: u0800060.sql
o 8.1.7: u0801070.sql
o 9.0.1: u0900010.sql
o 9.2: u0902000.sql

15. If you upgrading to Oracle Database 10g Release 2, only one common SQL script has to be invoked when performing a database upgrade. Oracle automatically determines what version is being upgraded and runs the appropriate upgrade scripts for that database and all of its included components:
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

16. Depending of the release you are upgrading to, run utlu10*s.sql (Post-Upgrade Status Tool) to display the results of the upgrade:
SQL> @utlu101s.sql TEXT
SQL> @utlu102s.sql
SQL> SPOOL OFF
Note that the utlu101s.sql script is followed by the word TEXT, to enable the printing of the script output.
The tool simply queries the DBA_SERVER_REGISTRY table to determine the upgrade status of each individual component.

17. Check the spool file and verify that the packages and procedures compiled successfully. Rerun the catupgrd.sql script, if necessary.

18. Restart the instance
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

19. If Oracle Label Security is in your database:
SQL> @olstrig.sql

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

21. 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';

22. Exit SQL*Plus