Applying CPUOct2011 Patch on 11.1.0.7/Linux(64 bit)

STEPS:-

  1. Database Version
  2. OS version
  3. Download CPUOct2011 patch for 11.1.0.7
  4. Opatch Version
  5. Sessions Status
  6. Invalid objects
  7. Status of Oracle Services
  8. Backup
  9. Apply Opatch
  10. Post Installation –
  11. Loading Modified SQL Files into the Database
  12. Recompiling Views in Database

1) Database Version

SQL> select * from v$version;

BANNER
 --------------------------------------------------------------------------------
 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
 PL/SQL Release 11.1.0.7.0 - Production
 CORE 11.1.0.7.0 Production
 TNS for Linux: Version 11.1.0.7.0 - Production
 NLSRTL Version 11.1.0.7.0 - Production
SQL>

2) OS version

-bash-3.2$ file /bin/ls
/bin/ls: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped

3)Download OCT2011CPU patch for 11.1.0.7

 

 

4)Opatch Version:- To apply OCT2011CPU patch, Opatch version should be 11.1.0.6.2 or later, We can query Opatch version by below query and current Opatch version is 11.1.0.8.3 , If not it can be downloaded from My Oracle Support patch 6880880by selecting the 11.1.0.0.0 release.

bash-3.2$ export PATH=/u00/app/oracle/product/11.1.0/OPatch:$PATH
-bash-3.2$ opatch lsinventory
Invoking OPatch 11.1.0.8.3
Oracle Interim Patch Installer version 11.1.0.8.3
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
Oracle Home       : /u00/app/oracle/product/11.1.0
Central Inventory : /u00/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version : 11.1.0.8.3
OUI version       : 11.1.0.7.0
OUI location      : /u00/app/oracle/product/11.1.0/oui
Log file location : /u00/app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch2012-01-27_13-18-17PM.log
Patch history file: /u00/app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /u00/app/oracle/product/11.1.0/cfgtoollogs/opatch/lsinv/
                                   lsinventory2012-01-27_13-18-17PM.txt

 

5)Sessions Status

Check How Many sesion are ACTIVE, If any found Ask Application team to bring down all Applications/Processes.

6)Invalid objects

7)Status of Oracle Services

Shutdown Database(s) & Listener(s). Check for any running processes.

8 ) Backup

Take Cold Backup of Database & Backup of (ORACLE_HOME & Inventory)

9) Apply Opatch

-bash-3.2$ cd 12828097/
-bash-3.2$ pwd
/u00/app/oracle/product/Patches/12828097
-bash-3.2$ opatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.1.0.8.3
Oracle Interim Patch Installer version 11.1.0.8.3
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
UTIL session
Oracle Home       : /u00/app/oracle/product/11.1.0
Central Inventory : /u00/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.8.3
OUI version       : 11.1.0.7.0
OUI location      : /u00/app/oracle/product/11.1.0/oui
Log file location : /u00/app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch2012-01-27_18-42-18PM.log
Patch history file: /u00/app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Skip patch 10336548 from list of patches to apply: This patch is not needed.
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home...
OPatch continues with these patches:   10009173  10009222  10009229  10009241  10009246  10336525  10336560
10336577  10426994  11807271  11807283  11807293  11807302  11829892  11829897  12560374  12560385  12560386
12560390  12560393  12560398  12560400  12560404  12560405  12560459  12560461  12687462  12828097  12984367
12984369  12984377  13000884  8342506  8563941  8563942  8563943  8563944  8563946  8563947  8563948  8855553
8855559  8855565  8855570  8855577  9118620  9458814  9458819  9702142  9702143
Do you want to proceed? [y|n] y
User Responded with: Y
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u00/app/oracle/product/11.1.0')
Is the local system ready for patching? [y|n] y
Patch  8421211      : applied on Mon Jun 21 06:34:00 EDT 2010
Unique Patch ID:  11176215
   Created on 10 Apr 2009, 03:35:38 hrs PST8PDT
   Bugs fixed:
     8421211
--------------------------------------------------------------------------------
OPatch succeeded.
-bash-3.2$

Opatch is successful, If it is failed it can be rolled back using option (rollback)

10)Post Installation

database instance running on the Oracle home being patched, connect to the database using SQL*Plus.
Connect as SYSDBA and run the catbundle.sql script as follows:
$cd $ORACLE_HOME/rdbms/admin
$sqlplus / as sysdba
SQL> STARTUP
SQL> @catbundle.sql cpu apply

11)Recompiling Views in the Database

if you have recompiled views for this database during the installation of a previous CPU (or)
if the database was created with Release 11.1.0.6 or later, Below step is not needed.

You can also query Whether the views are recompiled or not by below query. 

SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected
SQL>

(Or) 

We can perform Pre-check Script.

SQL> @recompile_precheck_jan2008cpu.sql
Running precheck.sql...
Number of views to be recompiled :20646
-----------------------------------------------------------------------
Number of objects to be recompiled :42574

Please follow the README.txt instructions for running viewrecomp.sql
PL/SQL procedure successfully completed.
SQL> 

[highlightbold]Recompiling views[/highlightbold]

To recompile views, Bring database in UPGRADE mode.
SQL> select status from v$instance;
STATUS
------------
OPEN MIGRATE
SQL>

-bash-3.2$ cd $ORACLE_HOME/cpu/view_recompile
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jan 27 19:15:18 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
No. of Invalid Objects is :805
Please refer to README.html to for instructions on validating these objects
PL/SQL procedure successfully completed.
Logfile for the current viewrecomp.sql session is : vcomp_A91FNFL_27Jan2012_19_16_01.log
SQL>

Status from registry$history

ID              COMMENTS                       ACTION               VERSION
-----------     ------------------------------ -------------------- ------------------------------
11              CPUOct2011                     APPLY                11.1.0.7
6452863         view recompilation             CPU

12)Compile Invalid objects by executing “utlrp.sql”.

Before Patching

SQL> select count(*),object_type from dba_objects  where status <> 'VALID' and OWNER !='PUBLIC' and
     OBJECT_TYPE!='SYNONYM' group by object_type;
 COUNT(*) OBJECT_TYPE
---------- -------------------
      [red]  47 VIEW[/red]
After Re-compile

 SQL> select count(*),object_type from dba_objects  where status <> 'VALID' and OWNER !='PUBLIC' and
     OBJECT_TYPE!='SYNONYM' group by object_type;
[green]no rows selected[/green]
SQL>

Opatch Stauts

-bash-3.2$ opatch lsinventory|grep 12828097
Patch  12828097     : applied on Fri Jan 27 18:45:53 EST 2012
     9655014, 10249534, 9114072, 9458811, 8836375, 9369783, 11724999, 12828097
-bash-3.2$