Monday, February 13, 2012

In-place upgrade from 11.2.0.2 to 11.2.0.3

When using 11.2 the preferred method for upgrade is out of place upgrade. But it is possible to do an in-place upgrade if the situation offer no other option (eg. hard disk space constraints). It is possible to do in-place upgrade to single instance and RAC Oracle homes. Oracle upgrade guide provide more information on this. Before proceeding it must be stated that "Oracle strongly recommends that you do not upgrade Oracle Database to release 11.2.0.2 or 11.2.0.3 using the existing Oracle home." (from upgrade guide)

This blog uses a single instance standard edition oracle home to upgrade from 11.2.0.2 to 11.2.0.3.

1. Make a backup of following files and folders in the existing Oracle home (11.2.0.2 in this case)
ORACLE_HOME/dbs
ORACLE_HOME/network/admin
ORACLE_HOME/owb/bin/admin
ORACLE_HOME/hostname_dbname
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname
Following shell command can speed things up
cd $ORACLE_HOME
tar -cvzf 11.2.tgz ./dbs ./network/admin ./owb/bin/admin ./`hostname`_$ORACLE_SID ./oc4j/j2ee/OC4J_DBConsole_`hostname`_$ORACLE_SID
2. Find out the Oracle Home name from the inventory.xml
<HOME_LIST>
<HOME NAME="enterprise_11g1" LOC="/opt/app/oracle/product/11.1.0/ent" TYPE="O" IDX="4"/>
<HOME NAME="standard_11g1" LOC="/opt/app/oracle/product/11.1.0/std" TYPE="O" IDX="5"/>
<HOME NAME="enterprise_10g2" LOC="/opt/app/oracle/product/10.2.0/ent" TYPE="O" IDX="6"/>
<HOME NAME="standard_10g2" LOC="/opt/app/oracle/product/10.2.0/std" TYPE="O" IDX="7"/>
<HOME NAME="OraDb11g_home1" LOC="/opt/app/oracle/product/11.2.0/std3" TYPE="O" IDX="1"/>
<HOME NAME="OraDb11g_home2" LOC="/opt/app/oracle/product/11.2.0/ent11.2.0.3" TYPE="O" IDX="10"/>
<HOME NAME="agent12g1" LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/core/12.1.0.1.0" TYPE="O" IDX="12">
   <REFHOMELIST>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.db.agent.plugin_12.1.0.1.0"/>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.db.discovery.plugin_12.1.0.1.0"/>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.oh.agent.plugin_12.1.0.1.0"/>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.oh.discovery.plugin_12.1.0.1.0"/>
   </REFHOMELIST>
</HOME>
3. Detach the Oracle home from the inventory
$ORACLE_HOME/oui/bin/runInstaller -detachHome ORACLE_HOME="/opt/app/oracle/product/11.2.0/std3"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 12001 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'DetachHome' was successful.
This would remove the Oracle home entry from the inventory.xml
<HOME_LIST>
<HOME NAME="enterprise_11g1" LOC="/opt/app/oracle/product/11.1.0/ent" TYPE="O" IDX="4"/>
<HOME NAME="standard_11g1" LOC="/opt/app/oracle/product/11.1.0/std" TYPE="O" IDX="5"/>
<HOME NAME="enterprise_10g2" LOC="/opt/app/oracle/product/10.2.0/ent" TYPE="O" IDX="6"/>
<HOME NAME="standard_10g2" LOC="/opt/app/oracle/product/10.2.0/std" TYPE="O" IDX="7"/>
<HOME NAME="OraDb11g_home2" LOC="/opt/app/oracle/product/11.2.0/ent11.2.0.3" TYPE="O" IDX="10"/>
<HOME NAME="agent12g1" LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/core/12.1.0.1.0" TYPE="O" IDX="12">
   <REFHOMELIST>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.db.agent.plugin_12.1.0.1.0"/>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.db.discovery.plugin_12.1.0.1.0"/>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.oh.agent.plugin_12.1.0.1.0"/>
      <REFHOME LOC="/opt/app/oracle/grid_agent/agent11g/agent12g/plugins/oracle.sysman.oh.discovery.plugin_12.1.0.1.0"/>
   </REFHOMELIST>
</HOME>
4. Remove or move the existing Oracle home to new location
mv std3 std3.bak
5. Install the new version from the patch set using the same location
6.Untar (unzip) the earlier backed up folders into new oracle home.
7. Start listener on the new oracle home.

8. Run DBUA from new oracle home and upgrade the database. Below screenshot shows Oracle home value for current and target database is same.
Upgrade summary
Useful metalink notes
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]