Oracle DBA, a practical approach !!: 2014

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

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 ||' </html>' ||

'DB User: '|| '<html>'|| rec.username || ' </html>' ||

'On Instance: '|| '<html>'|| rec.inst_id || ' </html>' ||

'OS User: '|| '<html>'|| rec.osuser || ' </html>' ||

'Source Machine: '|| '<html>'|| rec.machine || ' </html>' ||

'Program: '|| '<html>'|| rec.program || ' </html>' ||

'SQL ID: '|| '<html>'|| rec.sql_id || ' </html>' ||

'<html><body>'|| rec.sql_text ||'</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

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
Note:18522512.8	11.2.0.3.11 (Jul 2014) Database Patch Set Update (PSU)	Yes	Patch:18522512

Grid Infrastructure

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

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

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

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

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

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]

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]

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;

Wednesday, September 17, 2014

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

Applying PSU 11.2.0.3.11 to RDBMS home