Oracle Database manual upgrade from 10.2.0.2 to 10.2.0.3

I am providing step by step Oracle database manual upgrade process from 10.2.0.2 to 10.2.0.3

NOTE: I hope you have already created oracle 10.2.0.3 home in your machine.
On my machine I have oracle 10.2.0.2 binaries in "/opt/mis/oracle/product/10.2.0.2" path and oracle 10.2.0.3 binaries in "/opt/mis/oracle/product/10.2.0.3" path.

This is for solaris/linux servers.

Step 1: Pre checks
a) Check SYSTEM table space has atleast 150 MB free.
b) Check shared_pool_size parameter is set to 400MB.
Show parameter shared_pool_size;
If it is not set do "ALTER SYSTEM SET SHARED_POOL_SIZE='400M' SCOPE=both;"
c) Check java_pool_size parameter is set to 150MB.
Show parameter java_pool_size
If it is not set, do ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;"

Step 2: Disable all cron jobs related to the ORACLE_SID
This step is to ensure that no cron(unix) jobs are running for this database. It is not needed if you do not have any cron jobs running for this ORACLE_SID.
To check cron jobs for a particular ORACLE_SID do " crontab -l grep ORACLE_SID "

Step 3: Create spfile if it doesn't exist in 10.2.0.2_HOME/dbs directory.
Check whether your current database is mounted using spfile or not. For this use the follwing query.
SQL> SELECT NVL(value,'NO')FROM v$parameter WHERE NAME='spfile';
If it is not mounted using spfile, create an spfile using...
UNIX PROMPT> cd $ORACLE_HOME/dbs
UNIX PROMPT> sqlplus "/as sysdba"
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup;

Step 4: Change the $ORACLE_HOME enviroment variable
Now we have to set the $ORACLE_HOME environment variable to 10.2.0.3
UNIX PROMPT> env | grep ORACLE_HOME
ORACLE_HOME=/opt/mis/oracle/product/10.2.0.2 (This is before change.)

You have to change this in the enviroment file. In my solaris system these environment variables are stored in .cshrc file in the home directory.
Any way the normal environment variable file entries will look as follows.
Change the ORACLE_HOME variable. See below.
unsetenv LD_LIBRARY_PATH_64
unsetenv LD_LIBRARY_PATH
setenv ORACLE_SID BMC4INT
setenv ORACLE_BASE /opt/mis/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/10.2.0.3
setenv LD_LIBRARY_PATH_64 $ORACLE_HOME/lib:$ORACLE_HOME/javavm/lib
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/javavm/lib
unsetenv TWO_TASK
unsetenv ORACLE_SERVER
setenv DBA $ORACLE_BASE/admin # OFA
setenv NLS_LANG AMERICAN_AMERICA.UTF8
setenv ORA_NLS10 $ORACLE_HOME/nls/data # new in 10.2

Step 5: Copy the spfile.ora from 10.2.0.2_oracleHome/dbs to 10.2.0.3_oraclehome/dbs directory.
UNIX PROMPT> cd /opt/mis/oracle/product/10.2.0.2/dbs/
UNIX PROMPT> cp spfileORALCE_SID.ora /opt/mis/oracle/product/10.2.0.3/dbs/
NOTE:Do not change the COMPATIBLE parameter. It should set
to 10.2.0.2 only.

Step 6: Double check env variables for 10.2.0.3
UNIX PROMPT> env | grep ORACLE_HOME
Now it should show 10.2.0.3

Step 7: Start the upgrade
UNIX PROMPT> mkdir /tmp/upgrade/ -> create this directory to store the log files.
UNIX PROMPT> sqlplus "/as sysdba"
SQL> startup upgrade
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
Now the upgrade process will starts. It will take atleast 45 mins to 1:15 mins.
At last it will show the components list and their status.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.3.0 00:28:27
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:04:42
Oracle XDK VALID 10.2.0.3.0 00:01:02
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:36
Oracle Expression Filter INVALID 10.2.0.3.0 00:00:22
Oracle Enterprise Manager VALID 10.2.0.3.0 00:03:51

SQL>spool off;

Step 8: Set the COMPATIBLE parameter to 10.2.0.3.
SQL> alter system set compatible='10.2.0.3' scope=both;
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter compatible; -> It should show 10.2.0.3

Step 9: Check the status of all the components of DBA_REGISTRY:
SQL> select comp_name,comp_id,version,status from dba_registry;
SQL> exit;

Step 10: Create a script called "recomp.sql" in "/tmp/upgrade" directory.
This script is used to execute utlrp.sql file along with some other manual compile statemetns.
UNIX PROMPT> cd /tmp/upgrade/
UNIX PROMPT> vi recomp.sql
Add the follwing entries to the file.

col object_type for a20
col object_name for a32
col owner for a12
spool recomp.log
select owner,object_type,count(1) from dba_objects where status<>'VALID'
group by owner,object_type;
@?/rdbms/admin/utlrp
set echo on feed on
select owner,object_type,object_name from dba_objects where status<>'VALID' and
owner in ('SYS','SYSTEM');
alter session set nls_length_semantics=byte;
alter view KU$_XMLSCHEMA_VIEW compile;
select owner,object_type,object_name from dba_objects where status<>'VALID' and
owner in ('SYS','SYSTEM');
spool off;
exit;

UNIX PROMPT> nohup sqlplus "/ as sysdba" @recomp.sql &
It will execute the file in nohup mode.
Step 11: Checking
Select comp_name,comp_id,version,status from dba_registry;
Now all the objects should show "VALID" state.

Step 12: Change the Listener
Change the listener.ora file in $ORACLE_HOME/network/admin/ folder and make home
point to new 10.2.0.3 home.

Step 13: Restore the cron jobs for this ORACLE SID