Upgrading from 10.2.0.1 to 10.2.0.4 (Win)

Oracle 10g Release 2 (10.2.0.4) Patch Set 3 for Microsoft Windows (32-Bit) 

Identify the Oracle Database Installation
This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home. To identify Oracle home directories, view the/etc/oratab file.Use Oracle Universal Installer to identify Oracle home directories. From the Welcome screen, click Installed Products, then click the Environment tab. You should then cross-check this by executing

opatch lsinventory -all

D:\oracle\product\10.2.0\db_1\OPatch>set ORACLE_HOME=D:\oracle\product\10.2.0\db_1

D:\oracle\product\10.2.0\db_1\OPatch>opatch lsinventory -all
Invoking OPatch 10.2.0.1.0

Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : D:\oracle\product\10.2.0\db_1
Central Inventory : n/a
   from           : C:\Program Files\Oracle\Inventory
OPatch version    : 10.2.0.1.0
OUI version       : 10.2.0.1.0
OUI location      : D:\oracle\product\10.2.0\db_1\oui
Log file location : D:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\opatch-00_Jan_24_10-15-56-GMT+03_Mon.log
Lsinventory Output file location : D:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\lsinv\lsinventory-00_Jan_24_10-15-56-GMT+03_Mo
n.txt
--------------------------------------------------------------------------------
List of Oracle Homes:
  Name          Location
   OraDb10g_home1         D:\oracle\product\10.2.0\db_1
Installed Top-level Products (1):
Oracle Database 10g                                                  10.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
D:\oracle\product\10.2.0\db_1\OPatch>

Download and Extract the Installation Software
Download and extract the p6810189_10204_Win32.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

Stopping All Services for a Single Instance Installation
Shut down the following Oracle Database 10g services in the order specified before installing the patch set.
You can also stop these services using the Control Panel. After stopping the all services, check the other Oracle process running using task manager and kill them if required eg; hsodbc.exe.

Back Up the System

Oracle recommends that you create a backup of the Oracle Inventory, Oracle 10g home and Oracle 10g Database before you install the patch set. If you are planning to apply this patch to Oracle RAC, ensure that you create a backup of these components on all nodes before applying the patch.

Installation Tasks

1. Log on as a member of the Administrators group to the computer on which you are going to install Oracle components.
2. Start Oracle Universal Installer located in the unzipped area of the patch set. For example, Oracle_patch\setup.exe.
3. On the Welcome screen, click Next.
4. In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next
5. On the Summary screen, click Install.When the installation is complete, the End of Installation screen appears.On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

Postinstallation Tasks
Log in with administrator privileges.
Set the values for the environment variables ORACLE_HOME, ORACLE_SID and PATH.
D:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=FRADB

D:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 26 17:59:15 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile=D:\FRATEST\FRADB\pfile\initFRADB.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1300856 bytes
Variable Size             264242824 bytes
Database Buffers          801112064 bytes
Redo Buffers                7086080 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
The above error occurs because we have upgraded the software but database has not been upgraded yet.
So shutdown your instance and start with upgrade option.

SQL> startup upgrade pfile=D:\FRATEST\FRADB\pfile\initFRADB.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1300856 bytes
Variable Size             264242824 bytes
Database Buffers          801112064 bytes
Redo Buffers                7086080 bytes
Database mounted.
Database opened.
SQL>

Analyze DB by running the Pre-Upgrade Information Tool.

The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 10.2. 

SQL> spool d:\temp\fradb_upgrade_info.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102i.sql

SQL> spool off

For me the log showed the following

**********************************************************************         
Tablespaces: [make adjustments in the current environment]                     
**********************************************************************         
--> SYSTEM tablespace is adequate for the upgrade.                             
.... minimum required size: 233 MB                                             
.... AUTOEXTEND additional space required: 3 MB                                
WARNING: --> UNDOTBS01 tablespace is not large enough for the upgrade.         
.... currently allocated size: 50 MB                                           
.... minimum required size: 397 MB                                             
.... increase current size by: 347 MB                                          
.... tablespace is NOT AUTOEXTEND ENABLED.                                     
--> SYSAUX tablespace is adequate for the upgrade.                             
.... minimum required size: 159 MB                                             
WARNING: --> TEMP tablespace is not large enough for the upgrade.              
.... currently allocated size: 30 MB                                           
.... minimum required size: 58 MB                                              
.... increase current size by: 28 MB                                           
.... tablespace is NOT AUTOEXTEND ENABLED.                                     
.                                                                              
******************************************************************

**********************************************************************         
Miscellaneous Warnings                                                         
**********************************************************************         
WARNING: --> Database contains INVALID objects prior to upgrade.               
.... USER PUBLIC has 19 INVALID objects.         

Remove all the problems before upgrading the database like below.

SQL> ALTER DATABASE DATAFILE 'D:\FRATEST\FRADB\UNDOTBS01.DBF' autoextend on
  2  /
Database altered.
SQL> ALTER DATABASE TEMPFILE 'D:\FRATEST\FRADB\TEMP.DBF' autoextend on
  2  /
Database altered.
SQL> exec dbms_utility.compile_schema('PUBLIC');
PL/SQL procedure successfully completed.

Upgrade Database Now.

Run the upgrade script.

SQL> spool d:\temp\fradb_patch.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql

SQL> spool off

Verify the status of upgrade as below

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ -----------
Oracle Database Catalog Views
10.2.0.4.0                     VALID
Oracle Database Packages and Types
10.2.0.4.0                     VALID
2 rows selected.

Shutdown and startup the instance 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup pfile=D:\FRATEST\FRADB\pfile\initFRADB.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1300856 bytes
Variable Size             264242824 bytes
Database Buffers          801112064 bytes
Redo Buffers                7086080 bytes
Database mounted.
Database opened.
SQL>

Verify from Database

SQL> select substr(ACTION_TIME,1,40) action_time,substr(ACTION,1,8) action,substr(NAMESPACE,1,7) namespace,
  2  substr(VERSION,1,11) version,ID,substr(COMMENTS,1,40) comments,BUNDLE_SERIES
  3  from registry$history
  4  /

ACTION_TIME                     ACTION   NAMESPA VERSION             ID COMMENTS                         BUNDLE_SERIES
------------------------------- -------- ------- ----------- ---------- ---------------------------------------- ------
-------------
26-JAN-11 06.29.22.602000 PM    UPGRADE  SERVER  10.2.0.4.0             Upgraded from 10.2.0.1.0