Upgrade database from 9i to 10g – Windows

Followings steps when performed, will upgrade the database from 9i to 10g. 

Please note that you need to install ORACLE_HOME for 10g before going ahead.

Step 1) Run the script utlu102i.sql from 10g OH in 9i database

D:\oracle\ora92\bin>sqlplus “/as sysdba”

SQL*Plus: Release 9.2.0.6.0 – Production on Tue Oct 31 17:26:25 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production

SQL> spool preupgrade.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    10-31-2006 17:26:55
.
**********************************************************************
Database:
**********************************************************************
–> name:       orcl
–> version:    9.2.0.6.0
–> compatible: 9.2.0
–> blocksize:  8192
.
**********************************************************************
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: 246 MB
…. AUTOEXTEND additional space required: 76 MB
WARNING: –> TEMP tablespace is not large enough for the upgrade.
…. currently allocated size: 10 MB
…. minimum required size: 58 MB
…. increase current size by: 48 MB
…. tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: –> “shared_pool_size” needs to be increased to at least 173667533
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: –> “large_pool_size” needs to be increased to at least 8388608
WARNING: –> “session_max_open_files” needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************
— No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views         [upgrade]  VALID
–> Oracle Packages and Types    [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
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
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
–> New “SYSAUX” tablespace
…. minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

Once the above step is executed, follow the below steps to complete the upgrade.

Step 2) Make the recomended changes and bounce the database. Run the script again
        and check if any recomendations.

Step 3) Make the following changes
       
        Copy the init.ora file from old 9i ORACLE_HOME/dbs to new 10g ORACLE_HOME/dbs
        Copy the password file from old 9i ORACLE_HOME/dbs to new 10g ORACLE_HOME/dbs
        Change the compatible parameter to 9.2.0
       
Step 4) Delete the oracleservice in windows
        ORADIM -DELETE -SID <db_name>
       
Step 5) Create a new oracleservice
        ORADIM -NEW -SID <db_name> -INTPWD PASSWORD -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

Advertisements

2 thoughts on “Upgrade database from 9i to 10g – Windows

  1. I think instead of “Change the compatible parameter to 9.2.0
    ” it should be “Change the compatible parameter to 10.2.0”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s