ORACLE : Aplicar Patch upgradation from 10.2.0.1 to 10.2.0.5 Upgrade Oracle 10g Release 2 from 10.2.0.1 to 10.2.0.5

This post demonstrate a step by step guide to apply oracle patchset 10.2.0.5 (patch number 8202632) on 10.2.0.1 database. My current environment is Oracle 10gR2 (10.2.0.1) installed on Redhat Enterprise Linux 5 update 4 32-bit.

Preparing for the upgrade to 10.2.0.5

Stop all oracle components running like LISTENER, EM , ISQLPLUS and DB itself etc.

/* Stop the isqlplus if running */
$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

/* Stop the EM dbconsole */

$ emctl stop dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0  
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://ora10.home.com:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ... 
 ...  Stopped. 

/* Stop the listener */

$ lsnrctl stop

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

/* Shutdown the database itself */

$ sqlplus / as sysdba

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

Take a backup of Oracle Home and Database

Once the oracle database is shutdown, take a cold backup of your database and a backup of your ORACLE_HOME.

$cd /u01/apps/oracle/oradata/

/*
 All my data files , control files and log files are in a directory ora10g
 at the location /u01/apps/oracle/oradata/.
 I am going to make a tar archive of ora10g directory. If these files
 are at separate locations then add all those locations into the tar archive.
 And since this is just a test database and is very small in size so tar archive
 works much better then every thing else. But if it would be a production db and 
 is big in size then I would consider other faster ways to take a cold backup of
 my data files.
*/

$ tar czf /home/oracle/ora10g.tar.gz ora10g

$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1

/*
 This is my ORACLE_HOME so I would make a tar archive of "db_1" directory.
*/

$ cd /u01/apps/oracle/product/10.2.0/
$ tar czf /home/oracle/oraHomeBackup.tar.gz db_1

Manage your data with TimeZone before upgrade

(Only perform this step if you have data or Scheduler jobs with TZ info)

From 9i onwards Oracle has 2 datatypes that may have data stored affected by a update of the RDBMS DST (Daylight Saving Time) definitions, those are TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ). If you have TZ data stored in your database you need to go through the following steps to ensure the integrity of your data while the database upgrade.

Check which TIMEZONE version file you are currently using.

SQL> select version from v$timezone_file;

   VERSION
----------
         2

If this gives 4 then you may simply proceed with the upgrade even if you have TZ data.

If this gives higher then 4, look at the meta link note: Note 553812.1

If this gives lower then 4, perform the following steps:

Download utltzpv4.sql and run it.

SQL> @utltzpv4.sql    
DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS
               *
ERROR at line 1:
ORA-00942: table or view does not exist


Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Your current timezone version is 2!
.
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
data is affected by version 4 transition rules.
.
Any table with YES in the nested_tab column (last column) needs
a manual check as these are nested tables.

PL/SQL procedure successfully completed.


Commit complete.

SQL> 

/* Once the script finishes successfully execute the following query */

column table_owner format a4
column column_name format a18
select * from sys_tzuv2_temptab;

TABL TABLE_NAME                     COLUMN_NAME          ROWCOUNT NES
---- ------------------------------ ------------------ ---------- ---
SYS  SCHEDULER$_JOB                 LAST_ENABLED_TIME           3
SYS  SCHEDULER$_JOB                 LAST_END_DATE               1
SYS  SCHEDULER$_JOB                 LAST_START_DATE             1
SYS  SCHEDULER$_JOB                 NEXT_RUN_DATE               1
SYS  SCHEDULER$_JOB                 START_DATE                  1
SYS  SCHEDULER$_JOB_RUN_DETAILS     REQ_START_DATE              1
SYS  SCHEDULER$_JOB_RUN_DETAILS     START_DATE                  1
SYS  SCHEDULER$_WINDOW              LAST_START_DATE             2
SYS  SCHEDULER$_WINDOW              NEXT_START_DATE             2

9 rows selected.

If it returns no rows, there is nothing that needs to be done. Just proceed with the upgrade.

If it retunrs the detail of columns that contain TZ data which may be affected by the upgrade, see metalink note: Note 553812.1

The Note 553812.1 states that if you see SYS owned SCHEDULER objects then it is safe to ignore them and proceed with the upgrade. But if you see user data or user created jobs here then you need to take a backup of data before upgrade and restore it back after the upgrade. Remove any user created jobs and re-create them after the upgrade.

Install the patchset 10.2.0.5

Now come back to the oracle user console and move to the directory where you downloaded the patch and unzip the file.

$ cd /home/oracle
$ unzip p8202632_10205_Linux-x86.zip
$ cd Disk1/
$ ./runInstaller

The first screen is welcome screen.

Provide the Oracle home details here (The oracle 10.2.0.1 home).

The installer will perform prerequisite checks on this screen. Make sure you see the message "The overall result of this check is passed" in the output.

Oracle configuration Manager allows you to associate your configuration with your metalink support account. You may skip this.

Installation Summary.

Installation progress.

.

.

.

Once progress shows 100%, you will be asked to perform some root specific actions.

Login as root

# which dbhome
/usr/local/bin/dbhome
/* 
   this shows the location of dbhome, oraenv and coraenv files
   rename them for 10.2.0.1 as the root.sh create new ones for 
   10.2.0.5
*/
# cd /usr/local/bin/
# mv dbhome dbhome_10201
# mv oraenv oraenv_10201
# mv coraenv coraenv_10201

/* Now execute the script suggested by the installer. */

# /u01/apps/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/apps/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

When root.sh finishes successfully come back to installer and press ok. Then you should see the End of Installation page as below.

Press exit and your ORACLE_HOME is patched with 10.2.0.5 patchset. All your db's working under this ORACLE_HOME will become unusable unless you upgrade your database to 10.2.05 as well.

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268896 bytes
Variable Size             171967328 bytes
Database Buffers          427819008 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit

As you can see the database is unable to open and throwing the error ORA-01092. Lets look at the alert log file to know what actually happened.

$ tail -f /u01/apps/oracle/admin/ora10g/bdump/alert_ora10g.log 
SMON: enabling cache recovery
Fri Jul  2 15:30:15 2010
Errors in file /u01/apps/oracle/admin/ora10g/udump/ora10g_ora_12856.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Fri Jul  2 15:30:15 2010
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12856
ORA-1092 signalled during: ALTER DATABASE OPEN...
$

The alert log states that the database has to be upgraded first using UPGRADE option to be able to OPEN normally.

Upgrade the database from 10.2.0.1 to 10.2.0.5

Now startup the database with upgrade option and run the pre-upgrade information tool to see if the database is okay for the upgrade and if there is some thing to be changed before starting the upgrade.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

SQL> startup upgrade

SQL> spool pre_upgrade.log

SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    07-02-2010 17:39:25
.
**********************************************************************
Database:
**********************************************************************
--> name:       ORA10G
--> version:    10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize:  8192
.

.

.

.

SQL> spool off

The output from utlu102i.sql shows that every thing is fine, no changes are required and the database is ready for upgrade.

Lets start the upgrade process.

SQL> spool upgrade.log
SQL> @catupgrd.sql
.
.
.
SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

When the upgrade script catupgrd.sql finishes shutdown the database and open the spool of the upgrade process and try to find out if any thing failed. If you see something failed try to fix it and re-run the upgrade process.

The upgrade process may leave many objects invalid in the database. Perform a normal startup and run the utlrp.sql script to recompile any invalid objects.

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

SQL> spool recompile.log

SQL> @utlrp.sql

.

.

.

.

SQL> spool off

/*
  When the script utlrp.sql completes go ahead and verify if all the components are 
  upgraded to 10.2.0.5
*/

SQL> select * from v$version

BANNER
----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

The above query shows that the database components are at 10.2.0.5 version now.