opatch

How to apply Database Patches

Database patches are of various kinds like,But in broad there are II types:

I)Patchset:

To upgrade to higher version we use Database patchset.i.e For Upgrading from 10.2.0.1.0 to 10.2.0.4.0 we have to use Patchset.

II)Patchset Updates:
Patchset Updates are mainly divided into two types:

1)CPU(Critical Patch Update):
Critical Patch Update, quarterly delivered by Oracle to fix security issues.

2)PSU(Patch Set Update):
PatchSet Update, also quarterly delivered by Oracle,it includes CPU and a bunch of other one-off patches.

PSUs have been introduced for the first time on Oct-2009,We can use them, and stop to apply CPUs. PSUs, as CPUs, are cumulatives, no need to apply all of them, just apply the last one should be enough. One-off patch is single fix, to fix a particular issue.

We need to have metalink(My Oracle Support)Account to download this patch and applying instructions will be there in readme file of Patch.

1)How to apply Patchsets:
-------------------------------------------------

Patchsets are applied via OUI(Oracle Universal Installer).Patchsets are usually applied to upgrade oracle version
Eg: When we want to upgrade from Base release 10.2.0.1.0 to 10.2.0.4.0 for more useful feature and to avoid bugs,We have to use Patchset 10.2.0.4.0.So Patchsets change the version number.


2)How to apply Patch set updates or Critical Patch Updates:
--------------------------------------------------------------------------

PSUs(PatchSet Updates) or CPUs(Critical Patch Updates)are applied via opatch utility.
opatch is an interim utility for applying Database interim patches which will fix the bug in the release. For making use of the opatch utility you have to download the opatch recent version and apply as follow:


Downloading and installing the latesh Opatch version:
------------------------------------------------------
Below are the steps for downloading and installing the latest opatch version.opatch is very much useful for applying the database patches to fix various bugs and it is very much important to have the latest version.

1) Please download the latest OPatch version from My Oracle Support (MOS)

a) Click on the "Patches & Updates" tab

b) In the "Patch Name or Number" field type 6880880

c) In the "Platform" field select the relevant platform

d) Click the Search button.

e) Select the patch that corresponds to the Oracle release installed:

6880880 Universal Installer: Patch OPatch 11.2

f) Click the Download button
Once the above task is done copy the patch to $ORACLE_HOME directory and move the previous OPatch directory to separate directory in OS.We can use winscp or ftp for copying this patch from MOS to windows and
than windows to linux Box.

Now,unzip this patch zip file.The files will be extracting as shown below:

Eg:
-----
$ cd $ORACLE_HOME
(If an OPatch directory already exist then move it)
$ mv Opatch /TEST/OPatch_100057
$cd $ORACLE_HOME
$ unzip p6880880_101000_LINUX64.zip
Archive: p6880880_101000_LINUX64.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/README.txt
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/bt1.txt
inflating: OPatch/docs/bt2.txt
inflating: OPatch/docs/tmp
inflating: OPatch/emdpatch.pl
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl

[oracle@kor300949lx1 OPatch]$ opatch version
Invoking OPatch 11.2.0.1.5

OPatch Version: 11.2.0.1.5

OPatch succeeded.

[oracle@kor300949lx1 OPatch]$ opatch apply /u01/app/8264365
Invoking OPatch 11.2.0.1.5

Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-05-16_17-18-01PM.log

Patch history file: /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Here is your opatch latest version ready,Let us what all it consists of:

Go to the below mentioned path for all opatch commands description.

$ cd $ORACLE_HOME/OPatch/docs/
$ vi Users_Guide.txt

In general for applying any patch,We have to use the below command:
$cd $ORACLE_HOME/OPatch
$opatch apply /u01/Patch_dir/

Where :/u01/Patch_dir: The path where you have kept your Patch in the OS.

Hope the above post helps in understanding Database patches completely.

********************************************************************************************
*********************************************************************************************

How to apply oracle database patch (OPatch) without downtime.

====================================================

To apply Opatch
coditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches.
in single instance its not possible.
but for RAC instance its possible.
as in RAC there will be two seperate oracle home and two seperate instances running once instance on each oracle_home

use this command:

opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME


when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.


steps before applying patch:
----------------------------

1) check the database status.
wch_db.sql
-----------
select name,
open_mode,
database_name,
created,
log_mode,
platform_name
from v$database;



2) Check the object's invalid.

user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

3) Take backup of invalid's

create table bk_inv_ as select * from dba_objects
where status='INVALID';

4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and
download the latest version and uncompress
in $ORACLE_HOME.

5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc

inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba

if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME

inventory must point to the current $ORACLE_HOME which is getting patched.

6) check free space on $ORACLE_HOME
df -h $ORACLE_HOME

7) chek the utilities like
which ld
which ar
which make
etc as per readme file.

8) unzip the patch
unzip -d /loc_2_unzip p.zip

9) Go the patch directory
cd /loc_2_unzip/patch_number

10) Bring down the listner.
cd $ORACLE_HOME/bin
lsnrctl stop

11) Bring down the database
Shutdown immediate.

12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin

13) Start the patch
opatch napply -skip_subset -skip_duplicate

for RAC database then database can be up
as it may be having more then one instance
so you can bring down one instance and listener and apply the patch and open it
and then do the same on another node.
like this db will be up and no user will face issue in outage also.

to apply opatch in RAC instance
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and
-oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.

--------------------------------------------------------
. All-Node Patch

. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3

. (no downtime)

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3
-------------------------------------------------------------

14) Once patch installation is completed need to do post patching steps.

a) starup the instance
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log

c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup

If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
alter database set cluster database=true scope=spfile;

restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows



user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

if any new invalids seen then again take backup of invalid objects and compile it.

create table bk_inv_ as select * from dba_objects
where status='INVALID';

@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.


16) Confirm that patch has been applied successfully or not at db level also.


post_patch.sql
--------------
col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;

================================================================================
HOW TO APPLY PATCH on a DATABASE
================================================================================
1) Make a note of the output of below command:
ps -ef|grep pmon
ps -ef|grep tns

2) Login to database and take the snaps of all the invalid objects as mention below:
spool invalid_Object_pre.log
set linesize 256
set pagesize 256
col object_name for a30
select name from v$database;
select owner, object_name, object_type from dba_objects where status <>'VALID' order by owner, object_name;
spool off

3) Set OEM Agent blackouts on the server as follows :
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin
b) ./emctl start blackout servername_date_time_maint-nodelevel

4) Stop OEM Agent on the database server as follows:
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin/
b) ./emctl stop agent

5) Shutdown all the databases under this ORACLE_HOME with below command;

SHUTDOWN IMMEDIATE

6) stop all the LISTENER under this ORACLE_HOME

7) Cross-Check the database and Listener are down with below command:
ps -ef|grep pmon
ps -ef|grep tns

8)Take the Backup of global inventory (oraInventory) and local inventory as menetion below:
To find global inventory
cat /etc/oraInst.loc
or
cat /var/opt/oracle/oraInst.loc
go to that path and cd ..
Ex:
tar -cvf oraInventory_today'sdate oraInventory
Each ORACLE_HOME has its own inventory, its called local inventory
Ex:
cd /opt/oracle/product/10.2.0.4
tar -cvf inventory_today'sdate inventory

9)Go to below Location:
uname -a (Find the server details)
Download the patch from Oracle site to ORACLE_HOME

To know the bit (whether 64 or 32) use the following command
$ getconf KERNEL_BITS or getconf -a | grep KERNEL

unzip p9352224_92080_AIX64-5L.zip

10)Apply the Patch as mention below:
fuser -cu $ORACLE_HOME
(A) export PATH=$PATH:$ORACLE_HOME/OPatch
export TMP=$ORACLE_HOME/tmp
export TMPDIR=$TMP
export JDK_HOME=$ORACLE_HOME/jdk -- for upto 9i
export PATH=$PATH:$JDK_HOME/bin:. -- -- for upto 9i
(B) Check the which opatch version and opatch is pointing to, as mention below:
opatch version (It should 1.0.0.0.57 or higher)
which opatch (It Should be /prod01/app/oracle/product/9.2/OPatch/opatch)
(C) Apply the patch with below command:
opatch apply or opatch napply -skip_subset -skip_duplicate

11) Crosscheck the Patch is applied or not with below command:
opatch lsinventory

12) startup the Database
STARTUP
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
CONNECT / AS SYSDBA
STARTUP
@catbundle.sql psu apply
shutdown immediate
startup
++++ REPEAT the same steps for all databases. ++++++
IMP : Check the status of compoents after patching :
select COMP_NAME,STATUS from dba_registry;

13) Crosscheck the Patch is register in the database with below command:
SELECT * from REGISTRY$HISTORY;

14) Cross check the invalid objects as mention below:
spool invalid_Object_Post.log
set linesize 256
set pagesize 256
col object_name for a30
select name from v$database;
select owner, object_name, object_type from dba_objects where status <>'VALID' order by owner, object_name;
spool off

15) If the Output of invalid_Object_Post.log >(greater) than the output of invalid_Object_Pre.log then execute the below cmd:
@$ORACLE_HOME/rdbms/admin/utlrp.sql;
select owner, object_name,object_type from dba_objects where status <>'VALID' order by owner, object_name;
16) start the all listeners

17) Start OEM Agent on the database server as follows:
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin/
b) ./emctl start agent

18) Remove OEM Agent blackouts on the server as follows:
a) cd /prod01/app/oracle/product/AGENT/agent10g/bin/
b) ./emctl stop blackout servername_date_time_maint-nodelevel.

19) Logout from the Server.

**********************************END*************************************