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.
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.