Patches and how to apply patches ?

Patching  is one  of  the  most  common  task  performed  by  DBA's  in  day-to-day  life  . Here , we  will discuss  about  the various  types  of  patches  which  are  provided  by  Oracle  . Oracle  issues  product fixes  for  its  software called  patches. When  we  apply  the  patch  to  our  Oracle  software installation, it updates  the  executable  files, libraries,  and  object  files in  the software  home directory . The patch application  can  also  update  configuration  files and  Oracle-supplied  SQL schemas . Patches  are  applied by  using  OPatch, a utility supplied by Oracle , OUI  or  Enterprise Manager Grid Control . 

Oracle Patches are of various kinds . Here , we are broadly categorizing  it  into  two groups .

1.) Patchset  : 
2.) Patchset Updates : 

1.) Patchset  :  A group of  patches  form  a  patch  set. Patchsets  are  applied  by  invoking OUI (Oracle Universal Installer) . Patchsets  are  generally  applied  for  Upgradation  purpose . This  results  in  a version change  for our  Oracle  software, for  example,  from  Oracle Database 11.2.0.1.0  to  Oracle  Database 11.2.0.3.0.  We will cover this issue later .

2.) Patchset Updates : Patch Set Updates  are  proactive cumulative patches containing  recommended bug fixes that are released on a regular and predictable schedule .  Oracle has catergaries as :

i.) Critical Patch Update (CPU)  now refers  to  the  overall  release of security  fixes each  quarter  rather than the cumulative database security  patch  for  the quarter.  Think  of  the  CPU as  the  overarching quarterly release and not as a single patch .

ii.) Patch Set Updates (PSU)  are  the same cumulative patches  that  include  both  the  security  fixes and priority  fixes.  The key with  PSUs  is  they are  minor version  upgrades   (e.g., 11.2.0.1.1  to  11.2.0.1.2). Once a  PSU  is  applied, only  PSUs  can  be  applied  in  future  quarters  until  the database  is  upgraded to a new base version. 

iii.) Security Patch Update (SPU)  terminology  is  introduced  in  the October 2012 Critical Patch Update as  the  term  for the quarterly security patch.  SPU  patches are the same  as  previous  CPU  patches,  just a new  name .  For  the database, SPUs  can  not  be  applied  once  PSUs  have  been  applied  until  the database is  upgraded  to a  new base version.

iv.) Bundle Patches  are the quarterly patches for Windows  and  Exadata  which  include  both the quarterly security patches as well as recommended fixes. 

PSUs(PatchSet Updates) or CPUs(Critical Patch Updates) ,SPU are applied via opatch utility.

How to get Oracle Patches :

We  obtain  patches  and  patch  sets  from  My Oracle Support (MOS) . The  ability  to  download a specific  patch  is  based  on  the contracts associated  to  the support  identifiers  in  our  My Oracle Support  account.  All  MOS  users  are  able  to  search  for  and  view all  patches,  but  we  will  be prevented  from  downloading  certain types  of  patches  based  on  our  contracts.

While   applying   Patchset or  patchset upgrades , basically there are two entities in the Oracle Database environment 

i. )  Oracle Database Software
ii.)  Oracle Database

Most of the database patching activities involve, in the following sequence

•  Update  "Oracle Database Software"  using   './runInstaller'  or   'opatch apply'   known  as "Installation"  Tasks.
• Update  "Oracle Database"  (catupgrd.sql  or  catbundle.sql ...etc)  to  make  it  compatible  for  newly  patched  "Oracle database Software"   known  as  "Post Installation"  tasks. 

Patchset  OR  CPU/PSU  (or one-off)  patch  contains  Post  Installation  tasks  to  be  executed  on  all Oracle  Database  instances  after  completing  the Installation  tasks. If  we  are  planning  to  apply  a patchset  along  with  required  one-off-patches (either CPU or PSU or any other one-off patch),  then  we can  complete  the  Installation  tasks  of  the   Patchset+CPU/PSU/one-off  patches at once and then execute  Post  Installation  tasks of  the  Patchset+CPU/PSU/one-off  patches  in  the  same sequence as they were  installed . 

This  approach  minimizes  the  requirement  of  database  shutdown  across  each  patching  activity and simplifies  the  patching  mechanism as two tasks: 

• Software update and then
• Database update.

Here , we  will  cover  the  Opatch Utility in details along with example. 

OPatch  is  the  recommended (Oracle-supplied)  tool  that  customers  are  supposed  to  use  in  order to apply  or  rollback  patches. OPatch  is  PLATFORM  specific . Release is based on Oracle Universal Installer version .   OPatch  resides in  $ORACLE_HOME/OPatch .  OPatch  supports  the  following :

• Applying  an  interim  patch.
• Rolling  back  the  application  of  an  interim  patch.
• Detecting  conflict  when  applying  an  interim  patch  after  previous  interim  patches  have  been applied.  It  also  suggests  the  best  options  to  resolve a conflict .
• Reporting on installed products and interim patch.

The  patch  metadata  exist in  the  inventory.xml  and  action.xml  files  exists under<stage_area>/<patch_id>/etc/config/   

Inventory  .xml  file  have  the  following  information :

• Bug number 
• Unique Patch ID
• Date of  patch year  
• Required and Optional components 
• OS platforms ID 
• Instance shutdown is required or not 
• Patch can be applied online or not 

Actions   .xml  file  have  the  following  information .

• File name and it location to which it need to be copied 
• Components need to be re-linked 
• Information about the optional and required components

Here are steps for applying  patches on linux Platform : 

1.) Download the required Patches from  My Oracle Support (MOS) : 

• Login to metalink.
• Click "Patches & Updates" link on top menu.
• On the patch search section enter patch number and select the platform of your database.
• Click search.
• On the search results page, download the zip file.

2.)  Opatch version  :
Oracle  recommends  that  we  use  the  latest  released  OPatch , which  is  available  for  download  from My  Oracle  Support .  OPatch  is compatible  only  with  the version  of  Oracle  Universal  Installer  that  is installed  in  the  Oracle  home. We  can  get  all  Opatch  command  by  using  Opatch  help  command .

3.) Stop all the Oracle services  :
Before applying Optach , make sure all the Oracle services are down . If they are not down then stop/down the oracle related Services . Let's crosscheck it 

$ ps -ef |grep pmon
oracle   15871 15484  0 11:20 pts/2    00:00:00 grep pmon

$ ps -ef |grep tns
oracle   15874 15484  0 11:20 pts/2    00:00:00 grep tns

4.) Take Cold Backup :  

It  is  highly  recommended  to backup  the software  directory  which  we  are  patching  before  performing any  patch  operation . This  applies  to  Oracle  Database  or Oracle  Grid  Infrastructure  software installation  directories.  Take the backup of following 

• Take the Oracle software directory backup 

$ tar -zcvf   /u01/app/oracle/product/11.2.0/ohsw-bkp-b4-ptch.tar.gz   /u01/app/oracle/product/11.2.0   

• Take  backup of oracle database . 

$ tar  -zcvf    /u01/app/oracle/oradata/dbfl-b4-ptch.tar.gz     /u01/app/oracle/oradata     
Here all the database files are in oradata  directory .

• Take backup of OraInventary

$ tar  -zcvf   /u01/app/oraInventary/orinv-b4-ptch.tar.gz     /u01/app/oraInventary  


5.)  Apply  OPatches 
Set  our current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:

$ export  PATH=$ORACLE_HOME/OPatch:$PATH:
$ opatch apply .

6.)  Post Installation : 

Once , the  Opatch  installation  completed  successfully . Perform  the  post  Installation steps . Startup  the oracle  database  with  new  patched  software and  run catbundle.sql  scripts  which  is  found  in          $ORACLE_HOME/rdbms/admin   directory .

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.


7.) Finally check the status of patch status : 
We can check the final status of applied patched new Oracle Home by using the below command .
SQL > select  *   from  dba_registry_history   order  by  action_time  desc ;


Notes :
i.) If  we are using  a  Data Guard Physical Standby database, we  must  install  this  patch  on  both  the primary  database and  the physical  standby database .

ii.)  While applying patching take care of  mount point status .There should be sufficient Space .

For More About Opatch : Click Here

Blocking Locks in Oracle

All About Blocking Locks in Oracle

A  very  nice  post  has  been  written  by  Oracle  Ace Director  Arup Nanda  about the  Blocking  Locks . It  is  so simple  and  brilliantly explained . Check  the  below  link  and  enjoy  reading  about  basic  concepts behind locks . Before going to this link , read my previous post on ITL ( Interested Transaction List)  for better understading of this post .

http://arup.blogspot.in/2011/01/how-oracle-locking-works.html

Another  Brilliant  demo  on  Blocking  lock is  explained  by  "Natalka Roshak" .  She  has so wonderfully  explained .  Enjoy reading this link too. 

http://www.orafaq.com/node/854

Here,  i  have  added  few  more query on  locks along  with  it's output . For  demo purpose , i  have created  a  table "blck_tab"  and  locked this  table manually  and accessing this locked table from other sessions .Let's have a look .

SQL> create table blck_tab (id number , name varchar(22));
Table created.

SQL> insert into blck_tab values(1,'abc');
1 row created.

SQL> insert into blck_tab values(2,'xyz');
1 row created.

SQL> insert into blck_tab values(3,'pqr');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from blck_tab  for update ;             ----- Table Locked
        ID               NAME
----------    ----------------------
         1                   abc
         2                   xyz
         3                   pqr

Session 1 : 

SQL>conn test1/test1
SQL> update test.blck_tab set name='qwert' where id=3;
--->>  Waiter   <---- 

Session 2 : 

SQL> conn hr/hr
SQL> delete test.blck_tab where id=1;
--->>  Waiter   <---- 


Query to check locks :
(For Non-RAC)
SQL> SELECT  SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
  2  DECODE(REQUEST, 0, 'NO','YES' ) WAITER
  3  FROM   V$LOCK
  4  WHERE  REQUEST > 0 OR BLOCK > 0
  5  ORDER BY block DESC;
   SID     BLOCKER      WAITER
------     ------------       ----------
    37            YES                 NO
    34             NO                 YES
    31             NO                  YES
or 

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from gv$lock l1, gv$lock l2
  3  where l1.block >0 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2;
       SID    'ISBLOCKING'         SID
----------  ------------------      --------
        37      IS BLOCKING          31
        37      IS BLOCKING          34

For RAC 
SQL>select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK>0 and l2.request > 0and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

BLOCKING_STATUS

------------------------------------------------------------------------------------------------------------
TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 )  is blocking TEST1@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=34 )

TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 )  is blocking HR@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=31 )


Expanded Lock  Query
===============
SQL> SELECT vs.username,  vs.osuser,   vh.sid locking_sid,  vs.status status,
 vs.module module,   vs.program program_holding,   jrh.job_name,   vsw.username,
 vsw.osuser,   vw.sid waiter_sid,   vsw.program program_waiting,  jrw.job_name,
 'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command"
FROM  v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,
 dba_scheduler_running_jobs jrh,
 dba_scheduler_running_jobs jrw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid
 AND vh.sid = jrh.session_id(+)
 AND vw.sid = jrw.session_id(+);




Blocking locks with Sid and SQl 

SQL> set lines 200
column pu format a8 heading 'O/S|login|ID' justify left
column su format a15 heading 'Oracle/User ID' justify left
column prog format a15 heading 'Program' justify left
column machine format a15 heading 'machine' justify left
column stat format a8 heading 'Session|Status' justify left
column sser format 999999 heading 'Oracle|Serial|No' justify right
column txt format a28 word heading 'SQL TEXT' 
column RUNT format a15 word heading 'Run Time' 
set pagesize 1000
select
 s.username su,
 s.program prog,
 s.sid sid,
 lpad(p.spid,7) pid,
 substr(sa.sql_text,1,2000) txt,
 ltrim(to_char(floor(s.last_call_et/3600),'00009')) ||':'
 || ltrim(to_char(floor(mod(s.last_call_et,3600)/60),'09')) ||':'
 || ltrim(to_char(mod(s.last_call_et,60),'09')) RUNT
from v$process p,
 v$session s,
 v$sqlarea sa
where p.addr = s.paddr
 and s.username is not null
 and s.sql_address=sa.address(+)
 and s.sql_hash_value=sa.hash_value(+)
 and s.sid in (SELECT   SID FROM     V$LOCK WHERE    REQUEST > 0 OR BLOCK > 0)
order by 1,2
/

Oracle/User ID  Program           SID     PID          SQL TEXT                                  Run Time

--------------- ---------------          ------- -------   ----------------------------                ---------------

HR              sqlplus.exe             31        2812    delete test.blck_tab where         00001:32:11

                                                                                 id=1

TEST            sqlplus.exe             37    1928                                                          00000:06:35

TEST1           sqlplus.exe             34    1880       update test.blck_tab set            00002:11:34

                                                                            name='qwert' where id=3

Blocker/Waiter object details

SQL> col object_name for a28
SQL> col owner for a15
SQL> select do.owner,do.object_name , row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
  2  from
  3  v$session s,
  4  dba_objects do
  5  where sid in (select l2.sid blocking from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2) and
  6  s.ROW_WAIT_OBJ# = do.OBJECT_ID;

OWNER           OBJECT_NAME    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- -------------- ------------- -------------- --------------- ------------- ------------------
TEST            BLCK_TAB        74697              4             524             2          AAASPJAAEAAAAIMAAC

TEST            BLCK_TAB        74697              4             524             0          AAASPJAAEAAAAIMAAA


Note for RAC  use  gv$ instead of v$ .

Steps To Configure WLS Plug-in in IPlanet Web Server

Steps To Configure WLS Plug-in in IPlanet Web Server

• Install SunOne Web-Server.
• Install WLS with the Plugin Option enabled.This option installs WLS with all the plugins files required for supported web-servers.
• Refer BEA_HOME\weblogic92\server\plugin for plugin files.

 

• Navigate to appropriate OS and copy the IPlanet plug-in files.
• Plug-in file for (IPlanet)Sun Server is proxy61.dll. 
•  Paste the proxy61.dll file in Sun Server’s plugin\lib folder(SUNHOME\WebServer6.1\plugins\lib). 

• The next is to edit the magnus.conf and obj.conf files of Sun Server and make entry for WLS plug-in(SUNHOME \WebServer6.1\https-beatest\config).

• The entries that should be made in magnus.conf file are:

(1)A LoadModule entry in Loadmodule specification. 

Init fn="load-modules" funcs="wl_proxy,wl_init" shlib="C:/Sun/WebServer6.1/plugins/lib/proxy61.dll"

Init fn="wl_init"  

(2) Object specification in obj.conf for cluster environment.

Service fn="wl_proxy" WebLogicCluster="127.0.0.1:7003,127.0.0.1:7005" Debug="ALL" WLLogFile="proxy1.log"

127.0.0.1:7003,127.0.0.1:7005 is the cluster addresses to which the sun server redirects the request. The application is deployed on this cluster.

(3) Object specification in module configuration for single server.

Service fn="wl_proxy"

WebLogicHost=127.0.0.1

WebLogicPort=7001

WLLogFile="proxy1.log"

• After completing these specification start the Sun server.
• Start the WLS admin server and all managed servers participating in the cluster.
• Open the browser and type the request URL to the application deployed in the cluster.

For eg: http://localhost/CR

Where CR is the context root of the deployed application.

Thread dump in weblogic

Coming to step by step learning:

--------------------------------

What is Thread dump?

Thread Dump is a textual dump of all active threads and monitors of Java apps running in a Virtual Machine.

When we will take Thread dump? (Scenarios)

1.  Scenario 1: when server is hang Position, i.e. that time server will not respond to coming requests.

2.  Scenario 2: While sever is taking more time to restart

3.  Scenario 3: When we are Getting exception like “java.lang.OutOfMemoryException”

4.  Scenario 4: Process running out of File descriptors. Server cannot accept further requests because sockets cannot be created

5.  Scenario 5: Infinite Looping in the code

How many ways take Thread Dumps?

Many types we have to take a Thread dumps. As per your flexibility you can choose one Procedure. For analyzing take dumps some Intervals (like every 10mins, 10mins etc.).

Generating Dump Talking Procedures

1. Take Thread dump from Console by Using of below command

    $kill -3 PID

   (For Getting PID, Use this Command ps –ef | grep “java”)

Here The Output of the Thread Dump will be generated in the Server STDOUT.

(Note: If a process is not responding to kill -3 then it’s a JVM bug.)

2. Generation Thread Dump via Admin Console

a.  login to Admin Console(with Admin Username/Password)

b.  Click on Server, after choose your server

c.  Goto Monitoring TAB

d.  Goto Threads TAB, after click on “Dump Thread Stack” Button

e.  Now you can view the all the Threads in Same page

f.  Copy and paste in a txt file.

3.  We can Collect Thread Dump Using “WebLogic.Admin” which is deprecated, but still available or may be available in near future as well As i think because it is one of the best debugging utility for Admins.

  java WebLogic.Admin -url t3://hostname: port -username Weblogic -password Weblogic THREAD_DUMP

This Thread Dumps will be generated in Servers STDOUT file

4. Getting Thread Dumps by using Jstack Utility

    a.jstack –m (to connect to a live java process)

    b. jstack –m [server_id@]

                (to connect to a remote debug server)

    (-m Means print both java and native frames (mixed mode)) 

5. By Using WLST Script, can contain extension of (.py)

connect(‘weblogic’,'weblogic’,'t3://hostname:port′) cd (”Servers’) ls()cd (‘AdminServer’) ls() threadDump()

 Execute this Script in console. 

What can I Analysis with Thread Dumps?

We need to analyze the thread dumps for analyzing running threads and their states to identifying.

How can I analysis thread dumps?

For analyze thread dumps we have lots of tools to understand easily thread states

1.  samurai tool :

  (Visit: http://yusuke.homeip.net/samurai/en/index.html)

    In this tool you can identify all the Thread states by     identifying colors. We need to take care about Deadlocks and waiting state threads.

   More Details:

    $ java -jar samurai.jar

     After running we will get a Screen like below

      Goto Thread dump tab

    When Samurai detects a thread dump in your log, a tab named "Thread Dump" will appear.

 You can just click "Thread dumps" tab to see the analysis result. Samurai colors idle threads in gray, blocked threds in red and running threds in green. There are three result views and Samurai shows "Table view" by default. In many case you are just interested in the table view and the sequence view. Use the table view to decide which thread needs be inspected, the sequence view to understand the thread's behavior. You should takecare especially threds always in red.

2.  TDA Tool :

 (Visit: https://tda.dev.java.net/)

Actions taken for Issue resolving

1.  Classic Dead Locks : Look for the threads waiting for monitor entry

For Example :

"ExecuteThread: '95' for queue: 'default'" daemon prio=5 tid=0x411cf8 nid=0x6c waiting for monitor entry [0xd0f80000..0xd0f819d8]

    at weblogic.common.internal.ResourceAllocator.release(ResourceAllocator.java:766)

    at weblogic.jdbc.common.internal.ConnectionEnv.destroy(ConnectionEnv.java:590)

Reason: The above thread is waiting to acquire lock on Resource Allocator object. The next step is to identify the thread that is holding the Resource Allocator object

"ExecuteThread: '0' for queue: '__weblogic_admin_rmi_queue'" daemon prio=5 tid=0x41b978 nid=0x77 waiting for monitor entry [0xd0480000..0xd04819d8]

    at weblogic.jdbc.common.internal.ConnectionEnv.getPrepStmtCacheHits(ConnectionEnv.java:174)

    at weblogic.common.internal.ResourceAllocator.getPrepStmtCacheHitCount   (ResourceAllocator.java:1525)

Reason: This thread is holding lock on source Allocator object, but is waiting for Connection Env object. This is a classic deadlock.

     

2.  Threadsin wait() state:

   A sample dump:

"ExecuteThread: '10' for queue: 'SERV_EJB_QUEUE'" daemon prio=5 tid=0x005607f0 nid=0x30 in Object.wait() [83300000..83301998]

  at java.lang.Object.wait(Native Method)

  - waiting on (a weblogic.ejb20.pool.StatelessSessionPool)

at weblogic.ejb20.pool.StatelessSessionPool.waitForBean(StatelessSessionPool.java:222)

Reason: The above thread would come out of wait() under two conditions

 (Depending on application logic)

1) One of the thread available in the execute queue pool would call notify() on this object when an instance is available. (If the wait() is indefinite).

  This can cause the thread to hang for ever if server never does a notify() to this object.

2) If the timeout exceeds, the thread would throw an exception and back to execute queue thread pool.

Heap Dump Concept