This post demonstrate a step by step guide to apply oracle patchset 10.2.0.3 (patch number 5337014) on
10.2.0.1 database. My current environment is Oracle 10gR2 (10.2.0.1) installed on Redhat Enterprise
Linux 5 32-bit.
Preparing for the upgrade to 10.2.0.3
Stop all oracle components running like LISTENER, EM , ISQLPLUS and DB itself etc.
]$ isqlplusctl stop
]$ emctl stop dbconsole
]$ lsnrctl stop
]$ 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/app/oracle/oradata/
/* All my data files , control files and log files are in a directory shoaib7 at the location /u01/app/oracle/oradata/. I am going to make a tar archive of shoaib7 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/shoaib7.tar.gz shoaib7
$ echo $ORACLE_HOME
/u01/app/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/app/oracle/product/10.2.0/
$ tar czf /home/oracle/myora_HomeBackup.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 2 then you may simply proceed with the upgrade even if you have TZ data.
# xhost +
Install the patchset 10.2.0.3
Now come back to the oracle user console and move to the directory where you
and unzip the file.
$ cd /home/oracle
$ unzip p5337014_10203_linux.zip
$ cd Disk1/
$ ./runInstaller
The first screen is welcome screen.
Provide the Oracle home details here (The oracle 10.2.0.1 home).
downloaded the patch
5337014_
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.
le
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.3
*/
# 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 a
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 ...
are set as:
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.3 patchset
All your db's working under this ORACLE_HOME
ORACLE_HOME will become unusable unless you
upgrade your database to 10.2.0.3 as well.
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.
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.3
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 09-06-2011 17:39:25
**********************************************************************
Database:
**********************************************************************
--> name: ORA10G
--> 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: 245 MB
.... AUTOEXTEND additional space required: 15 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
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
.
.
.
[output trimmed]
.
.
.
Oracle Database 10.2 Upgrade Status Utility 09-06-2011 18:13:40
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.3.0 00:09:32
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:03:34
Oracle XDK VALID 10.2.0.3.0 00:00:29
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:39
Oracle Text VALID 10.2.0.3.0 00:00:20
Oracle XML Database VALID 10.2.0.3.0 00:01:29
Oracle Workspace Manager VALID 10.2.0.1.3 00:00:39
Oracle Data Mining VALID 10.2.0.3.0 00:00:18
OLAP Analytic Workspace VALID 10.2.0.3.0 00:00:19
OLAP Catalog VALID 10.2.0.3.0 00:00:50
Oracle OLAP API VALID 10.2.0.3.0 00:00:45
Oracle interMedia VALID 10.2.0.3.0 00:03:48
Spatial VALID 10.2.0.3.0 00:01:29
Oracle Expression Filter VALID 10.2.0.3.0 00:00:09
Oracle Enterprise Manager VALID 10.2.0.3.0 00:01:16
Oracle Rule Manager VALID 10.2.0.3.0 00:00:07
.
Total Upgrade Time: 00:25:52
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
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
TIMESTAMP
-----------------------------------------------------------------------------
---
COMP_TIMESTAMP UTLRP_BGN 2011-06-09 18:23:16
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
----------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2011-06-09 18:23:50
PL/SQL procedure successfully completed.
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
-------------------
0
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
---------------------------
0
PL/SQL procedure successfully completed.
SQL> spool off
/*
When the script utlrp.sql completes go ahead and verify if all the
components are
upgraded to 10.2.0.3
*/
set lines 10000
set pages 1000
column comp_name format a40
column version format a12
column status format a6
select comp_name, version, status from sys.dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views 10.2.0.3.0 VALID
Oracle Database Packages and Types 10.2.0.3.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
JServer JAVA Virtual Machine 10.2.0.3.0 VALID
Oracle XDK 10.2.0.3.0 VALID
Oracle Database Java Packages 10.2.0.3.0 VALID
Oracle Expression Filter 10.2.0.3.0 VALID
Oracle Data Mining 10.2.0.3.0 VALID
Oracle Text 10.2.0.3.0 VALID
Oracle XML Database 10.2.0.3.0 VALID
Oracle Rule Manager 10.2.0.3.0 VALID
Oracle interMedia 10.2.0.3.0 VALID
OLAP Analytic Workspace 10.2.0.3.0 VALID
Oracle OLAP API 10.2.0.3.0 VALID
OLAP Catalog 10.2.0.3.0 VALID
Spatial 10.2.0.3.0 VALID
Oracle Enterprise Manager 10.2.0.3.0 VALID
17 rows selected.
The above query shows that the database components are at 10.2.0.3 version now.
Restore the database back to 10.2.0.1 if any thing failed
Let's suppse the upgrade process fails, then you can always go back to 10.2.0.1 level be restoring the
backup we took at the beginning of the process and start over the upgrade.
Just shutdown the database and restore the backups as follows:
$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1
$ cd /u01/apps/oracle/product/10.2.0/
$ rm -fr db_1
$ tar xzf /home/oracle/myora_HomeBackup.tar.gz *
/* This will restore the old ORACLE_HOME */
$ cd /u01/apps/oracle/oradata/
$ rm -fr ora10g
$ tar xzf /home/oracle/ora10g.tar.gz *
/* This will restore a consistent copy of datafiles, controlfiles and redo
log files */
Now start the database and see which version is it?
$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup
set lines 10000
set pages 1000
column comp_name format a40
column version format a12
column status format a6
select comp_name, version, status from sys.dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
JServer JAVA Virtual Machine 10.2.0.1.0 VALID
Oracle XDK 10.2.0.1.0 VALID
Oracle Database Java Packages 10.2.0.1.0 VALID
Oracle Expression Filter 10.2.0.1.0 VALID
Oracle Data Mining 10.2.0.1.0 VALID
Oracle Text 10.2.0.1.0 VALID
Oracle XML Database 10.2.0.1.0 VALID
Oracle Rules Manager 10.2.0.1.0 VALID
Oracle interMedia 10.2.0.1.0 VALID
OLAP Analytic Workspace 10.2.0.1.0 VALID
Oracle OLAP API 10.2.0.1.0 VALID
OLAP Catalog 10.2.0.1.0 VALID
Spatial 10.2.0.1.0 VALID
Oracle Enterprise Manager 10.2.0.1.0 VALID
17 rows selected.
We are back again from where we started.
No comments:
Post a Comment