1) ASSUMPTIONS
==============
> Oracle 10gR2 (10.2.0.1) already installed
> OS version : OEL 5.6
2)STOP ALL ORACLE COMPONENT
===========================
A) Stop the isqlplus if running
$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
iSQL*Plus instance on port 5560 is not running ...
B) Stop the EM dbconsole
$ emctl stop dbconsole TZ set to Asia/Calcutta 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.
C) 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
D) Shutdown the database (CLEAN SHUTDOWN)
$ sqlplus / as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
3) Take a backup of Oracle Database and Oracle_Home
===================================================
A) BACKUP ORADATA FILES
---------------------------------------------
As we have done a clean shutdown so no archive backup will be necessary as there won't be any recovery required later on.
Our database size is very small so we will backup our oradata file by simple operating system commands.
ORADATA location ( contains datafile, controlfile, logfile) : /u01/app/oracle/oradata/brijesh/BRIJESH
$ pwd
/u01/app/oracle/oradata/brijesh
tar czf /u01/staging/brijesh.tar.gz BRIJESH
$ ls -ltr /u01/staging/brijesh.tar.gz -rw-r--r-- 1 oracle oinstall 129499851 Nov 22 16:57 /u01/staging/brijesh.tar.gz
B) BACKUP ORACLE_HOME
-------------------------------------------
$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
$ cd /u01/app/oracle/product/10.2.0
$ tar czf /u01/staging/brijeshorahome.tar.gz db_1
$ ls -ltr /u01/staging/brijeshorahome.tar.gz
-rw-r--r-- 1 oracle oinstall 781601082 Nov 22 17:03 /u01/staging/brijeshorahome.tar.gz
4) Download & Install Oracle Patchset 10.2.0.4 (6810189)
========================================================
you need to download patch as per your OS type from MyOracleSupport site .
For present case it is "10.2.0.4 for Linux x86-64"
Now from Oracle user go to the directory where the patch is put and unzip the patch
$ cd /u01/staging/10204_patch $ unzip p6810189_10204_Linux-x86.zip
Start the installation
$ cd Disk1/ $ ./runInstaller
1) First screen is welcome screen.
2) Second screen Provide the Oracle home details here (The oracle 10.2.0.1 home).
3) Third screen : 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.
4) Fourth screen : Oracle configuration Manager allows you to associate your configuration with your metalink support account. You may skip this.
5) Fifth screen : Installation Summary.
6) Once progress shows 100%, you will be asked to perform some root specific actions.
7) Login as root in new window
# 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.4 */ # 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.
8) Press exit and your ORACLE_HOME is patched with 10.2.0.4 patchset.
NOTE : All your db's working under this ORACLE_HOME will become unusable unless you upgrade your database to 10.2.04 as well.
5) Upgrade the database from 10.2.0.1 to 10.2.0.4
=================================================
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
What if you start the database now ??
$ 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.
cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 23 00:41:57 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 331350016 bytes Fixed Size 2083720 bytes Variable Size 209716344 bytes Database Buffers 113246208 bytes Redo Buffers 6303744 bytes Database mounted. Database opened. SQL> spool pre_upgrade.log SQL> SQL> @utlu102i.sql Oracle Database 10.2 Upgrade Information Utility 11-23-2011 00:43:32 . ********************************************************************** Database: ********************************************************************** --> name: BRIJESH --> 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: 496 MB .... AUTOEXTEND additional space required: 6 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 402 MB .... AUTOEXTEND additional space required: 372 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 327 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 58 MB .... AUTOEXTEND additional space required: 38 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 --> Real Application Clusters [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 . ********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING: --> Database contains INVALID objects prior to upgrade. .... USER PUBLIC has 21 INVALID objects. . 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] . .
TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UPGRD_END 2011-11-23 02:24:46 . Oracle Database 10.2 Upgrade Status Utility 11-23-2011 02:24:46 . Component Status Version HH:MM:SS Oracle Database Server VALID 10.2.0.4.0 00:06:36 JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:01:36 Oracle XDK VALID 10.2.0.4.0 00:00:20 Oracle Database Java Packages VALID 10.2.0.4.0 00:00:20 Oracle Text VALID 10.2.0.4.0 00:00:13 Oracle XML Database VALID 10.2.0.4.0 00:01:40 Oracle Real Application Clusters INVALID 10.2.0.4.0 00:00:01 Oracle Workspace Manager INVALID 10.2.0.4.3 00:00:33 Oracle Data Mining VALID 10.2.0.4.0 00:00:15 OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:29 OLAP Catalog VALID 10.2.0.4.0 00:01:25 Oracle OLAP API VALID 10.2.0.4.0 00:00:45 Oracle interMedia VALID 10.2.0.4.0 00:03:17 Spatial VALID 10.2.0.4.0 00:02:01 Oracle Expression Filter VALID 10.2.0.4.0 00:00:22 Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:34 Oracle Rule Manager VALID 10.2.0.4.0 00:00:13 . Total Upgrade Time: 00:21:47 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.
6) RECOMPILE THE INVALIDS
=========================
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.
===============================================================================
SQL> select count(*) from dba_objects where STATUS='INVALID';
COUNT(*)
----------
521
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-11-23 02:35:27
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-11-23 02:36:24
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
-------------------
38
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.
====================================================================================================
7) VERIFICATION
===============
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 Enterprise Manager 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 INVALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 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
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
Oracle Real Application Clusters 10.2.0.4.0 INVALID
8) ************************* ERRORS RECIEVED ************************************
===================================================================================
1) While running catupgrd.sql, it failed with
ERROR at line 1:
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared
pool","DECLARE
display_mode VAR...","sga heap(1,0)","kglsim heap")
This happened because SGA_TARGET value was very low >>> sga_target=329715200
After increasing the value of sga_target to 629715200 upgrade completed successfully
2) As can be seen above Oracle Workspace Manager (OWM) is seen "INVALID" after upgrade.
Oracle Workspace Manager, is a new feature of Oracle9i that transparently and securely versions relational content in place with no changes to application SQL (DML), while permitting simultaneous read and write access to the same production data. Workspace Manager also supports Oracle8i, and Oracle Spatial, and is supported by Oracle9i Enterprise Manager.
Use Workspace Manager to...
Improve concurrency for database updates that complete over days, weeks or months by managing them in workspaces
Track the history of changes to data and view the database as it existed at any point in time
Create multiple data scenarios in separate workspaces for what-if analysis
Support Oracle Spatial long transactions
DEINSTALL /INSTALL of this component-OWM solved the issue
DEINSTALL
Note: De-installing Oracle Workspace Manager will remove any existing workspaces and the associated metadata.
1.
Disable versioning on all version-enabled tables in the database before de-installing Oracle Workspace Manager. To verify if you have any currently version-enabled tables in your database:
From SQL*Plus connected to the database as a DBA user, enter:
select * from all_wm_versioned_tables;
Once you have confirmed you have no versioned tables, or the count read 0, then you can proceed with the deinstall.
2.
To uninstall Workspace Manager, run the following script, as SYS:
$ORACLE_HOME/rdbms/admin/owmuinst.plb
INSTALL
If you wish to reinstall Workspace Manager at a later time, you can run, as SYS:
$ORACLE_HOME/rdbms/admin/owminst.plb
The Install / Upgrade / De-install process is also discussed in the readme included in the kit. The readme is supplied with Workspace Manager patch kits.
These scripts can be run at any time without having to bounce the database, as long as OWM is not being used.
VERIFICATION
=============
SQL> select comp_name, status, version
from dba_registry
where comp_name='Oracle Workspace Manager';
COMP_NAME STATUS VERSION
---------------------------------------- -------- ------------
Oracle Workspace Manager VALID 10.2.0.4.3
---------------------------------------<<<<<>>>>>-------------------------------------------------