Upgrade Oracle 10g from 10.2.0.1 to 10.2.0.4 (Single Instance)

Before database upgrade it is recommanded to backup the PRODUCTION database.

1. Stop all services of oracle

[oracle@ittestdb ~]$ echo $ORACLE_BASE

/u01/app/oracle
[oracle@ittestdb ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
[oracle@ittestdb ~]$ echo $ORACLE_SID
orcl
[oracle@ittestdb ~]$
[oracle@ittestdb ~]$ emctl stop dbconsole
TZ set to Asia/Baghdad
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://ittestdb.amardhaka.com:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control …
… Stopped.
[oracle@ittestdb ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Stopping iSQL*Plus …
iSQL*Plus stopped.
[oracle@ittestdb ~]$

[oracle@ittestdb ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 08-FEB-2010 13:17:18

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@ittestdb ~]$ sqlplus “/as sysdba”

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@ittestdb ~]$ ps -ef|grep oracle
root     13754 32094 0 13:13 pts/1    00:00:00 su – oracle
oracle 13755 13754 0 13:13 pts/1    00:00:00 -bash
oracle 14525 13755 0 13:18 pts/1    00:00:00 ps -ef
oracle 14526 13755 0 13:18 pts/1    00:00:00 grep oracle
[oracle@ittestdb ~]$

Step 2: Install the Database Patch Set

[oracle@ittestdb ~]$ export DISPLAY=10.13.5.95:0.0
[oracle@ittestdb ~]$ /u01/stage/patch/Disk1/runInstaller
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3
Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-02-08_01-22-19PM. Please wait …[oracle@ittestdb ~]$ Oracle Universal Installer, Version 10.2.0.4.0 Production

[oracle@ittestdb ~]$

Step 3 : Upgrade Database

[oracle@ittestdb ~]$ ps -ef|grep oracle
root     13754 32094 0 13:13 pts/1    00:00:00 su – oracle
oracle 13755 13754 0 13:13 pts/1    00:00:00 -bash
oracle 18304 13755 0 13:28 pts/1    00:00:00 ps -ef
oracle 18305 13755 0 13:28 pts/1    00:00:00 grep oracle
[oracle@ittestdb ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Feb 8 13:28:53 2010

Connected to an idle instance.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  1267188 bytes
Variable Size             318769676 bytes
Database Buffers          889192448 bytes
Redo Buffers               15507456 bytes
Database mounted.
Database opened.
SQL> SPOOL /u01/stage/patch/Disk1/upgrade_info.log
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    02-08-2010 13:30:50
.
**********************************************************************
Database:
**********************************************************************
–> name:       ORCL
–> version:    10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 488 MB
…. AUTOEXTEND additional space required: 8 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 400 MB
…. AUTOEXTEND additional space required: 370 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 246 MB
…. AUTOEXTEND additional space required: 16 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 58 MB
…. AUTOEXTEND additional space required: 38 MB
–> EXAMPLE tablespace is adequate for the upgrade.
…. minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
– No update parameter changes are required.
.
**********************************************************************
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
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java          [upgrade] VALID
–> Oracle Java Packages         [upgrade] VALID
–> Oracle Text                  [upgrade] VALID
–> Oracle XML Database          [upgrade] VALID
–> Oracle Workspace Manager     [upgrade] VALID
–> Oracle Data Mining           [upgrade] VALID
–> OLAP Analytic Workspace      [upgrade] VALID
–> OLAP Catalog                 [upgrade] VALID
–> Oracle OLAP API              [upgrade] VALID
–> Oracle interMedia            [upgrade] VALID
–> Spatial                      [upgrade] VALID
–> Expression Filter            [upgrade] VALID
–> EM Repository                [upgrade] VALID
–> Rule Manager                 [upgrade] VALID
.

PL/SQL procedure successfully completed.

SQL> SPOOL OFF;

SQL> SPOOL /u01/stage/patch/Disk1/patch.log

SQL> @?/rdbms/admin/catupgrd.sql

177 PROCEDURE selectTablespace( tsname IN varchar2 );

178
179    — This procedure informs this package that the caller intends to do
180    — point-in-time recovery on the specified tablespace. This procedure must
181    — be called once for each tablespace in the recovery set.
182    — It alter selected tablespace read only, also checks datafiles in the
183    — selected tablespace.
184    –
185    — Input parameters:
186    –   tsname
187    –     The tablespace name.
188    –
189    — Exceptions:
190    –   WRONG_ORDER (ORA-29301)
191    –     wrong dbms_pitr package functions/procedure order.
192    –   WRONG_TSNAME (ORA-29304)
193    –     select tablespace does not exist
194    –   NOT_READ_ONLY (ORA-29305)
195    –     cannot alter the tablespace read only
196    –   FILE_OFFLINE (ORA-29306)
197    –     datafile is not online

SQL>

SQL>
SQL>
SQL> SPOOL OFF

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  1267188 bytes
Variable Size             335546892 bytes
Database Buffers          872415232 bytes
Redo Buffers               15507456 bytes
Database mounted.
Database opened.
SQL>

To compile invalid objects

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_END 2010-02-08 15:02:23

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

SQL>

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

Oracle Workspace Manager 10.2.0.4.3 VALID

JServer JAVA Virtual Machine 10.2.0.4.0 VALID

Oracle XDK 10.2.0.4.0 VALID

Oracle Database Java Packages 10.2.0.4.0 VALID

Oracle Expression Filter 10.2.0.4.0 VALID

Oracle Data Mining 10.2.0.4.0 VALID

Oracle Text 10.2.0.4.0 VALID

Oracle XML Database 10.2.0.4.0 VALID

Oracle Rule Manager 10.2.0.4.0 VALID

Oracle interMedia 10.2.0.4.0 VALID

OLAP Analytic Workspace 10.2.0.4.0 VALID

Oracle OLAP API 10.2.0.4.0 VALID

OLAP Catalog 10.2.0.4.0 VALID

Spatial 10.2.0.4.0 VALID

Oracle Enterprise Manager 10.2.0.4.0 VALID

SQL>exit

Now Start other services (listener, EM, iSQLPlus…)
from
http://faruqueahmed.wordpress.com/2010/02/08/upgrade-oracle-10g-from-10-2-0-1-to-10-2-0-4-single-instance/
http://kenchendz.itpub.net/post/33766/479143