Wednesday, September 17, 2014

A simple monitor for long running sql’s in an Oracle database.

A simple monitor for long running sql’s in an Oracle database.

If you are here, you are probably searching for something that I searched for before putting this together. This is a simple job that checks for any sessions that is taking long time to execute in the database. Since a single, bad, long running query could cause performance issue at the database level, it is good to be aware of what is happening in our database, especially in oltp environment where response times are critical.

We can also use this to be aware of the problem sqls that are candidates for tuning.

  • We can schedule the job according to our needs. When it runs and how often it is run.
  • We can exclude user accounts that should not be monitored (SYS, SYSTEM, DBSNMP, etc)
  • We determine the long running alert threshold. Anything running beyond this number of seconds is a candidate session/sql for the job to alert us.
  • We log into a temporary table. We also store the history for 30 days in a history table.
  • We capture important details that we’d like to know (user, machine, sql_id, sql_text, etc)
  • The job sends an email with the full sql text as well.
  • Alerts are sent only 1 time within the hour for the same user and sql combination, even if it continues to run long throughout the hour. Another email is sent if the same combination runs long in the next hour.
  • For multiple parallel sessions, we get 1 email so we don’t get bombarded with several emails.



Below is a screenshot of how we get notified. This is a very basic setup but it works for me and is RAC aware.





How to setup the job

Create our work table(dba_long_sqlstat) and history table(dba_hist_long_sqlstat).


  CREATE TABLE "SYS"."DBA_LONG_SQLSTAT"
   (     "INST_ID" NUMBER,
         "USERNAME" VARCHAR2(30 BYTE),
         "OSUSER" VARCHAR2(30 BYTE),
         "MACHINE" VARCHAR2(64 BYTE),
         "PROGRAM" VARCHAR2(48 BYTE),
         "SQL_ID" VARCHAR2(13 BYTE),
         "LAST_CALL_ET" NUMBER,
         "SQL_TEXT" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("SQL_TEXT") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;



  CREATE TABLE "SYS"."DBA_HIST_LONG_SQLSTAT"
   (     "CAPTURE_TIMESTAMP" DATE DEFAULT SYSDATE,
         "INST_ID" NUMBER,
         "USERNAME" VARCHAR2(30 BYTE),
         "OSUSER" VARCHAR2(30 BYTE),
         "MACHINE" VARCHAR2(64 BYTE),
         "PROGRAM" VARCHAR2(48 BYTE),
         "SQL_ID" VARCHAR2(13 BYTE),
         "LAST_CALL_ET" NUMBER,
         "SQL_TEXT" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("SQL_TEXT") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

Create the procedure

Customize the procedure for your needs and create the procedure. The procedure should work as is. I have highlighted in red the areas which you should likely customize.

1) The user account we don’t want to monitor.
2) last_call_et => in my case any sql running above 180 seconds will be alerted.
3) notification email address


create or replace
procedure     long_running_sql
 is

v_db_name v$parameter.value%type;
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );

begin

dbms_utility.exec_ddl_statement('truncate table dba_long_sqlstat');

insert into dba_long_sqlstat
select
s.inst_id, s.username, s.osuser, s.machine, s.program, s.sql_id,l_et,st.sql_fulltext
from
(
select distinct inst_id, username, osuser, machine, program, sql_id, max(last_call_et) as l_et
from gv$session
where
username is not null and
username not in ('SYS','SYSTEM','DBSNMP') and
status='ACTIVE'
and last_call_et > 180
group by
inst_id, username, osuser, machine, program, sql_id
) s
left outer join gv$sql st
on s.sql_id = st.sql_id;
commit;

select value into v_db_name from v$parameter where name = 'db_name';


for rec in
(
select * FROM dba_long_sqlstat WHERE ROWID IN
         (SELECT MAX(ROWID) FROM
                 (SELECT inst_id, username, osuser, machine, program, sql_id, last_call_et, sql_text
                  FROM   (
                          SELECT inst_id, username, osuser, machine, program, sql_id, last_call_et, sql_text, rank() over (partition by sql_id order by last_call_et desc) rnk
                          FROM dba_long_sqlstat
                          where sql_id not in (select sql_id from dba_hist_long_sqlstat where capture_timestamp between TRUNC(SYSDATE, 'HH24') and sysdate)
                          )
                 WHERE rnk = 1
                 )
         group by sql_id)
)
loop

declare
  v_from      varchar2(80) :=  v_db_name;
  v_recipient varchar2(80) := 'xxxx@gmail.com';
  v_subject   varchar2(80) := 'long running sql on '||v_db_name;
  v_mail_host varchar2(30) := 'localhost';
  v_mail_conn utl_smtp.connection;
  crlf        varchar2(2)  := chr(13)||chr(10);
begin
 v_mail_conn := utl_smtp.open_connection(v_mail_host,25);
 utl_smtp.helo(v_mail_conn, v_mail_host);
 utl_smtp.mail(v_mail_conn, v_from);
 utl_smtp.rcpt(v_mail_conn, v_recipient);
 utl_smtp.data(v_mail_conn, 'Content-Type: text/html;' || crlf ||
                          'To: '     || v_recipient || crlf ||
                          'Subject: '|| v_subject || crlf ||
                          'The following sql is running for '||  rec.last_call_et || ' seconds in database '|| v_db_name ||'<br><br></html>' ||
                          'DB User: '|| '<html><font color="darkblue">'|| rec.username || '</font><br></html>' ||
                          'On Instance: '|| '<html><font color="darkblue">'|| rec.inst_id || '</font><br></html>' ||
                          'OS User: '|| '<html><font color="darkblue">'|| rec.osuser || '</font><br></html>' ||
                          'Source Machine: '|| '<html><font color="darkblue">'|| rec.machine || '</font><br></html>' ||
                          'Program: '|| '<html><font color="darkblue">'|| rec.program || '</font><br><br></html>' ||
                          'SQL ID: '|| '<html><font color="red">'|| rec.sql_id || '</font><br><br></html>' ||
                          '<html><body><font face="courier" size="1" color="maroon">'|| rec.sql_text ||'</font></body></html>');
  utl_smtp.quit(v_mail_conn);
  end;

end loop;

insert into dba_hist_long_sqlstat(
 inst_id,
 username,
 osuser,
 machine,
 program,
 sql_id,
 last_call_et,
 sql_text)
select * from dba_long_sqlstat;

delete from dba_hist_long_sqlstat where capture_timestamp < (sysdate - 30);
commit;

end long_running_sql ;

Schedule the job. Again, customize it according to your needs.

I have set this to run every 2 minutes, during business hours(gmt) and on weekdays.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'long_running_sql_check_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN long_running_sql; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;BYHOUR=12,13,14,15,16,17,18,19,20,21,22;BYDAY=MON,TUE,WED,THU,FRI',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Runs weekdays during business hours ');
END;
/

Test the procedure using the below block.
You should login as a user other than the users you don’t monitor (SYS, SYSTEM, DBSNMP)


BEGIN
  DBMS_LOCK.sleep(seconds => 500);
END;
/

We use Grid control and several other tools that Oracle and other vendors provide for monitoring databases and sqls. The above job is a simple setup if we want alerted real-time and notified via email about any sql that is running long and which could potentially cause an issue.

As always, test before using it. It works for me. You take all responsibility if you want to try it out J
Drop a note on any enhancements that you do which improves this from basic to a better setup.


Applying PSU 11.2.0.3.11 to RDBMS home

Applying PSU 11.2.0.3.11 to RDBMS home


Environment:

Single instance running on Red Hat Enterprise Linux Server release 5.8 (Tikanga)
Oracle rdbms 11.2.0.3

I will be using ‘11.2.0.3.11 (Jul 2014) Grid Infrastructure Patch Set Update (GI PSU) - Patch: 18706488’ to patch a Single Instance Database Server/home.

PSU - What/ When ??

Patch Set Updates (PSUs) are proactive cumulative patches containing recommended bug fixes that are released every quarter on the Tuesday closest to the 17th of January, April, July, and October.

Identifying the right PSU for your environment

-- I will be patching 11.2.0.3 patch set binaries.

-- Find the current recommended PSU for your version of software ?

Use support note, Oracle Recommended Patches -- Oracle Database [ID 756671.1]

As of Sep 2014, the recommended patch for 11.2.0.3 is patch 11.2.0.3.11 (Jul 2014)

Database
Document
Description
Rolling RAC
Patch Download
11.2.0.3.11 (Jul 2014) Database Patch Set Update (PSU)
Yes


Grid Infrastructure
Document
Description
Rolling RAC
Patch Download
11.2.0.3.11 (Jul 2014) Grid Infrastructure Patch Set Update (GI PSU)
Yes

We can use any of the above downloads to patch an 11.2.0.3 RDBMS/DB single instance home.  Grid Infrastructure PSU includes DB PSU as well.


Patch Installation Prerequisites

To take a backup of the binaries, do the following tar of the binaries home.  Oracle also automatically takes a backup during opatch apply and stores it in “.patch_storage” under the $ORACLE_HOME where you apply the patch. This will be used during rollback, if the apply fails

As Oracle software owner

. oraenv
DB11203

env |grep -i oracle_

cd $ORACLE_HOME

tar -cvpf /patch_stage_dir/patchset/july2014/oh_bkp/oh_bkp.tar ./


where
c - compress
v - verbose
p - preserve permissions
f - tar file
/patch_stage_dir/patchset/july2014/oh_bkp/oh_bkp.tar is the backup tar file


Note: Always use the readme.html from the patch source. It contains complete details required for applying the patch successfully.

Latest OPatch
               
OPatch is the utility used to apply the patch. Download the latest OPatch utility from My Oracle Support patch 6880880.


Check the current OPatch version

> . oraenv
ORACLE_SID = [oracle] ? DB11203

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH


> which opatch
/u01/app/oracle/product/11.2.0.3/OPatch/opatch


> opatch version
OPatch Version: 11.2.0.3.5

OPatch succeeded.


As software home owner, unzip the downloaded OPatch to the $ORACLE_HOME

cd /patch_stage_dir/patchset/july2014/opatch        (Location where my downloaded patch is staged)

unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME


Check the new OPatch version

> opatch version
OPatch Version: 11.2.0.3.6

OPatch succeeded.


Validation of Oracle Inventory

Before patch application, let check the consistency of the oracle inventory. If the below command succeeds, we are good.

$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME


Download and Unzip the Patch

This directory must be empty and not be /tmp

cd /patch_stage_dir/patchset/july2014/18706488    (Location where my downloaded patch is staged)

> unzip p18706488_112030_Linux-x86-64.zip

> ls -ltr
total 595076
-rw-r--r--  1 oracle dba        21 Jan  9  2014 README.txt
-rw-r--r--  1 oracle dba         0 Jan  9  2014 atp_lfp
drwxr-xr-x  5 oracle dba      4096 Jan  9  2014 17592127
-rw-r--r--  1 oracle dba       450 May 29 08:26 bundle.xml
drwxrwxr-x 13 oracle dba      4096 Jun 11 12:51 18522512              à DB PSU
-rw-rw-r--  1 oracle dba     59977 Jul 14 17:10 README.html
-rw-rw-r--  1 oracle dba      2367 Jul 15 12:02 PatchSearch.xml
-rw-r--r--  1 oracle dba 606878754 Sep 15 20:02 p18706488_112030_Linux-x86-64.zip


Note: Use below support note to find the correct DB PSU for your version.

Readme - Patch Installation and Deinstallation For 11.2.0.3.x GI PSU (Doc ID 1494646.1)
In this example, the <GI_PSU_number> is 18706488; the <GI_Components_number> is 17592127; and the <DB_PSU_number> is 18522512.


One-off Patch Conflict Detection and Resolution

Check for conflicting patches by doing the following for the RDBMS home you are planning to patch.

Use My Oracle Support Note 1061295.1 Patch Set Updates - One-off Patch Conflict Resolution to determine, for each conflicting patch, whether a conflict resolution patch is already available, and if you need to request a new conflict resolution patch or if the conflict may be ignored.

> opatch prereq CheckConflictAgainstOHWithDetail -oh $ORACLE_HOME -phBaseDir /patch_stage_dir/patchset/july2014/18706488/18522512
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.3
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-30-19PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


Patch Installation

Note:

-- Stop EM agent if it is running on your server before you apply the patch. Our grid control agent is 12.2

> emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping agent ..... stopped.


-- Make sure there is enough free space available in your RDBMS home volume
-- If the available free space is less, try to use ‘opatch util cleanup’ to clean old backup files from previous patching. It is usually a hidden directory in ORACLE_HOME called .patch_storage


Apply OPatch auto as Oracle user on 11.2.0.3 rdbms home

Shutdown all database and listener services running from the Oracle home that we are patching.


> cd /patch_stage_dir/patchset/july2014/18706488/18522512

> ls -ltr
total 48
drwxrwxr-x 5 oracle dba 4096 Jun 11 12:50 17540582
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 13696216
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 16619892
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 14275605
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 18522512
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 16902043
drwxrwxr-x 5 oracle dba 4096 Jun 11 12:50 14727310
drwxr-xr-x 4 oracle dba 4096 Jun 11 12:50 13343438
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 13923374
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 18031683
drwxrwxr-x 4 oracle dba 4096 Jun 11 12:50 16056266
-rw-r--r-- 1 oracle dba   21 Jun 11 12:50 README.txt
-rw-r--r-- 1 oracle dba   29 Jun 11 12:50 README.html
-rw-r--r-- 1 oracle dba 2872 Jun 11 12:51 patchmd.xml


> opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.3
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-32-22PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following executables are active :
/u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-32-22PM_1.log

OPatch failed with error code 73


logs at /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-32-22PM_1.log

[Sep 16, 2014 9:33:16 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/oracle at Tue Sep 16 21:33:16 GMT-00:00 2014
[Sep 16, 2014 9:33:17 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/oracle at Tue Sep 16 21:33:17 GMT-00:00 2014
[Sep 16, 2014 9:33:17 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/renamedg at Tue Sep 16 21:33:17 GMT-00:00 2014
[Sep 16, 2014 9:33:17 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/renamedg at Tue Sep 16 21:33:17 GMT-00:00 2014
[Sep 16, 2014 9:33:17 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1 at Tue Sep 16 21:33:17 GMT-00:00 2014
[Sep 16, 2014 9:33:17 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1 at Tue Sep 16 21:33:17 GMT-00:00 2014
[Sep 16, 2014 9:33:17 PM]    Following executables are active :
                             /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1
[Sep 16, 2014 9:33:17 PM]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                             The details are:


                             Following executables are active :
                             /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1
[Sep 16, 2014 9:33:17 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/proc at Tue Sep 16 21:33:17 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/proc at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/rman at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/rman at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/wrap at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/wrap at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/plshprof at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/plshprof at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:18 PM]    Start fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/nid at Tue Sep 16 21:33:18 GMT-00:00 2014
[Sep 16, 2014 9:33:19 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.3/bin/nid at Tue Sep 16 21:33:19 GMT-00:00 2014
[Sep 16, 2014 9:33:19 PM]    OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Sep 16, 2014 9:33:19 PM]    Finishing UtilSession at Tue Sep 16 21:33:19 GMT-00:00 2014
[Sep 16, 2014 9:33:19 PM]    Log file location: /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-32-22PM_1.log
[Sep 16, 2014 9:33:19 PM]    Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Sep 16, 2014 9:33:19 PM]    StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:6420)
[Sep 16, 2014 9:33:19 PM]    StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:1614)
[Sep 16, 2014 9:33:19 PM]    StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1136)
[Sep 16, 2014 9:33:19 PM]    StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[Sep 16, 2014 9:33:19 PM]    StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[Sep 16, 2014 9:33:19 PM]    StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[Sep 16, 2014 9:33:19 PM]    StackTrace: java.lang.reflect.Method.invoke(Method.java:592)
[Sep 16, 2014 9:33:19 PM]    StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:322)
[Sep 16, 2014 9:33:19 PM]    StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:2343)
[Sep 16, 2014 9:33:19 PM]    StackTrace: oracle.opatch.OPatch.main(OPatch.java:614)


Some process is active on file /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1

Find the process


> /sbin/fuser /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1
/u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1:  5848m

> ps -ef|grep -i 5848
oracle    5848  5738  0 Jul23 pts/4    00:00:00 sqlplus
oracle   22898 18209  0 21:37 pts/1    00:00:00 grep -i 5848

It is some sqlplus session.

> kill -9 5848

> /sbin/fuser /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1
> 


Looks ok now. Proceeding to patch again

>  opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0.3
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-38-48PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   16902043  17540582  18031683  18522512

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: xxxxxx

..
..
..

Password (optional):                     
                                                                                          
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.                                                                                         
(Oracle Home = '/u01/app/oracle/product/11.2.0.3')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
Is the local system ready for patching? [y|n]                                                                                                                                 
y                                                                                                                                                                              
User Responded with: Y                                                                                                                                                         
Backing up files...                                                                                                                                                           
Applying sub-patch '16902043' to OH '/u01/app/oracle/product/11.2.0.3'                                                                                                        
ApplySession: Optional component(s) [ oracle.idm.oid, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.                                                                                                                                                                                  
Patching component oracle.ldap.rsf, 11.2.0.3.0...                                                                                                                         
Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...                                                                                                                      
Patching component oracle.owb.rsf, 11.2.0.3.0...                                                                                                                                                                                                        
Patching component oracle.rdbms, 11.2.0.3.0...                                                                                                                            
Patching component oracle.rdbms.rsf, 11.2.0.3.0...                                                                                                                                                                                                                                
Patching component oracle.sysman.console.db, 11.2.0.3.0...                                                                                                                                                                                                                                                                                                    
Verifying the update...                                                                                                                                                        
Applying sub-patch '17540582' to OH '/u01/app/oracle/product/11.2.0.3'                                                                                                        
ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.                                                                                                                                                                                                                         
Patching component oracle.rdbms, 11.2.0.3.0...                                                                                                                                                                                                                     
Patching component oracle.rdbms.rsf, 11.2.0.3.0...                                                                                                                                                                                                                 
Patching component oracle.sdo, 11.2.0.3.0...                                                                                                                                                                                                
Patching component oracle.ldap.rsf, 11.2.0.3.0...                                                                                                                                                                                                                  
Patching component oracle.precomp.common, 11.2.0.3.0...                                                                                                                                                                                                                                                                                                       
Patching component oracle.ordim.client, 11.2.0.3.0...                                                                                                                                                                                                                                                                                                        
Patching component oracle.rdbms.util, 11.2.0.3.0...                                                                                                                                                                                                                                                                                                           
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...                                                                                                                                                                                                                                                                                                      
Patching component oracle.sdo.locator, 11.2.0.3.0...                                                                                                                          
Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.ordim.jai, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '18031683' to OH '/u01/app/oracle/product/11.2.0.3'
ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '18522512' to OH '/u01/app/oracle/product/11.2.0.3'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Verifying the update...

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'                                                    
/u01/app/oracle/product/11.2.0.3/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'


Composite patch 18522512 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2014-09-16_21-38-48PM_1.log

OPatch completed with warnings.



Refer note Opatch warning: overriding commands for target xxxx (Doc ID 1448337.1)

If you check the $OH/install/make.log you will see that these warnings existed before patching i.e. after the original installation.  This is not an issue which the patch you are applying has introduced, simply a warning which opatch has correctly captured and is reporting back to the user.

> cat /u01/app/oracle/product/11.2.0.3/install/make.log |grep -i pcscfg.cfg
/usr/bin/make -f ins_precomp.mk links proc gen_pcscfg ORACLE_HOME=/u01/app/oracle/product/11.2.0.3//u01/app/oracle/product/11.2.0.3/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'

So, it is ignorable


Check Applied Patch

> $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'DATABASE P'
18522512   18522512  Tue Sep 16 21:46:52 GMT-00:00 2014DATABASE PATCH SET UPDATE 11.2.0.3.11 (INCLUDES CP
18031683   18031683  Tue Sep 16 21:46:23 GMT-00:00 2014DATABASE PATCH SET UPDATE 11.2.0.3.10 (INCLUDES CP
17540582   17540582  Tue Sep 16 21:45:40 GMT-00:00 2014DATABASE PATCH SET UPDATE 11.2.0.3.9 (INCLUDES CPU
16902043   16902043  Tue Sep 16 21:44:27 GMT-00:00 2014DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPU
16619892   16619892  Fri Oct 25 00:44:33 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPU
16056266   16056266  Fri Oct 25 00:43:44 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPU
14727310   14727310  Fri Oct 25 00:43:21 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPU
14275605   14275605  Fri Oct 25 00:42:25 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPU
13923374   13923374  Fri Oct 25 00:42:09 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES
13696216   13696216  Fri Oct 25 00:41:32 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES
13343438   13343438  Fri Oct 25 00:40:14 GMT-00:00 2013DATABASE PATCH SET UPDATE 11.2.0.3.1


Patch Post Installation Steps


Start databases, listeners and Enterprise Manager Agent.

For each 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

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Check the log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors


Upgrade Oracle Recovery Manager Catalog

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:

$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;