Upgrade Database from 9i to 10g Rrelease 2 On MS Windows

If you want to upgrade your Oracle database from 9i to 10g R2 you must apply patchset 7 R2 (9.2.0.8) to upgrade directly.

We can use three methods to upgrade our oracle database.
- Export / Import
- Database Upgrade Assistant
- Manually by using Scripts

Upgrade Steps:

Step 1:Pre-Upgrade Tasks
1. Install the oracle 10g database software in new oracle home.
2. Connect to the oracle database form 9i and run pre-upgrade scripts (utlu102i), which is store in Oracle 10 g Home/rdbms/admin.
3. Follow the steps suggested from output of above steps.

Step 2:
(As per instruction of above output, you must change database and initSID file.)
1. Shutdown the 9i instance
SQL> CONN / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
2. Stop the OracleServiceSID Oracle service of the oracle 9i database
C:\> NET STOP OracleService
3. Delete the OracleServiceSID at command line of 9i Home
C:\>ORADIM –DELETE –SID

Step 3:
1.Create the new oracle database 10g service at command prompt using the following command.
C:\>ORADIM –NEW –SID –INTPWD –STARTMODE A
2. Put your init file in database folder at new oracle 10g home from 9i.

Step 4:
1. Connect to the new oracle 10g instance as a user sysdba privilege and issue following command:
SQL> CONN / AS SYSDBA
SQL> STARTUP UPGRADE
You don’t need to use the PFILE option to specify the location of your initialization parameter file in our case because we are using INIT file in default location (which is reside in Oracle10gHome/database). We have just put init file at new oracle 10g home from 9i.
The error may occur, when you attempting to start the new oracle Database 10g release. If you receive, issue the SHUTDOWN ABORT commands to shut down the database and correct the problem.

Step 5:Create SYSAUX Tablespace
The SYSAUX tablespace is used to consolidate data from a number of tablespaces that where separated in previous releases.

SQL> CREATE TABLESPACE SYSAUX DATAFILE 'SYSAUX01.DBF'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

Step 6:Upgrade Process
1. Set the system to spool results to a log file for later verification of success:
SQL> SPOOL upgrade.log
2. Run the upgrade scripts.
SQL> @catupgrd.sql
The catupgrd.sql script determines which scripts needed to be run and then runs each necessary script.
3. Run the result of the upgrade display report.
SQL> @utlu102s.sql
The Post-upgrade status Tool display the status of the database components in the upgrade database and time required to complete each component upgrade.
4. Turn off the spooling of script result to the log file
SQL> spool off;
5. Shutdown the instance and restart.
SQL> STHTDOWN IMMEDIATE
SQL> STARTUP

Step 7:Post-Upgrade Tasks
1. Remove the obsolete initialization parameter from parameter file.
2. Check Invalid objects in database by using following Query:
SQL> SELECT COUNT ( * )
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

3. Run utlrp.sql scripts to recompile any remaining stored Pl/SQL and java codes.
SQL > @utlrp.sql