Upgrade Oracle 9.2.0.8 to Oracle 11.2.0.3

Hello aspiring and fellow DBA's, posting brief overview about a recent upgrade in my project. And sadly, this is a windows environment!!!


==================================== EPDM upgrade from 9.2.0.8 to 11.2.0.3


1. run pre-requisite ( utlu112i.sql in 9i database)

Take 9i pfile backup and copy to 11g home location.

2. update pfile with required parameters from pre-requisite.
gather DBMS_stats for DBSNMP.

3. shut down the 9i database

4 . create ORADIM service in windows box

oradim -new -sid EPSTG -syspwd system123 -pfile H:\oracle\product\11.2.0\dbhome_1\database\INITepstg.ora

refresh services in services.msc

5.  set ORACLE_HOME=11g home
set ORACLE_SID=EPDM

startup upgrade

6. create sysaux tablespace with segment management auto

create tablespace SYSAUX datafile 'H:\EP_DM\oradata\sysaux01.dbf' size 500M reuse
    extent management local
    segment space management auto
    online;

7. now run the catupgrd.sql

8. shutdown & startup the database and run utlrp.sql for any invalid objects

9 . check status of packages in database_registry

select comp_id,version,status from dba_registry;


POST UPGRADE SCRIPTS :

--------------------------------
SQL> @?/rdbms/admin/utlu112s.sql

Recompile any remaining stored PL/SQL:
-------------------------------------
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql


LISTENER/TNS NAMES.ORA

--> right click on MY COMPUTER --> properties --> advanced --> enivronment settings --> update TNS_ADMIN & ORACLE_HOME
with new Oracle HOME

then set new ORACLE_HOME in cmd prompt
then start netca
configure listner.ora and update tnsnames.ora