RE: PSU 12827731

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Mon, 16 Jan 2012 09:32:38 +0000
Message-ID: <EDA437CAA8612C418E013CDA4B4A7551633A6057_at_CWYIGMBCRP01.Corp.Acxiom.net>

Thanks. I came up with this 4 hours ago and Oracle support just agreed with me. Hope they're right.

Reference http://blogs.oracle.com/UPGRADE/entry/upgrade_and_an_interesting_surprise

And you might believe that having the fix included in the PSU does enable the code of this fix?? At least I would believe that. But as Roy would call it "the old 'hidden bug fix' trick..." the code is there but you'll have to enable it. MOS Note 10187168.8 describes what you'll have to do.

Actually you'll have to set this hidden parameter: _cursor_features_enabled26
and besides that this nice event in your init.ora/spfile: events= "106001 trace name context forever, level 1024" Then bounce the instance ... an voila ... the patch is now enabled.

Bug 11836382: ORA-600 [15206]

ORA-600 [15206] Generated Creating Very Large Trace Files [ID 1321149.1]

Modified 22-JUL-2011 Type PROBLEM Status MODERATED

In this Document
Symptoms<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id21149.1&addClickInfo=%3cdata%20search_text=%22ORA-600%2015206%22%20search_result_size=%226%22%20search_result_count=%226%22%20powerview_id=%22Support%20Identifier:%203217057%20(Acxiom%20Corporation),%2014401669%20(Acxiom%20Corporation),%203064890%20(BAXTER%20HEALTH%20CARE),%203321094%20(Baxter%20Healthcare%20Corporation),%203294644%20(Baxter%20Healthcare%20Corporation),%202661521%20(MOORE%20CORPORATION%20LTD),%20671072%20(Pechiney%20Plastic%20Packaging,%20Inc.),%203073240%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%201039789%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%203403199%20(RR%20DONNELLEY%20&%20SONS%20COMPANY)%22%20on_off=%22on%22%20item_position_in_list=%221%22/%3e#SYMPTOM> Changes<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id21149.1&addClickInfo=%3cdata%20search_text=%22ORA-600%2015206%22%20search_result_size=%226%22%20search_result_count=%226%22%20powerview_id=%22Support%20Identifier:%203217057%20(Acxiom%20Corporation),%2014401669%20(Acxiom%20Corporation),%203064890%20(BAXTER%20HEALTH%20CARE),%203321094%20(Baxter%20Healthcare%20Corporation),%203294644%20(Baxter%20Healthcare%20Corporation),%202661521%20(MOORE%20CORPORATION%20LTD),%20671072%20(Pechiney%20Plastic%20Packaging,%20Inc.),%203073240%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%201039789%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%203403199%20(RR%20DONNELLEY%20&%20SONS%20COMPANY)%22%20on_off=%22on%22%20item_position_in_list=%221%22/%3e#CHANGE> Cause<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id21149.1&addClickInfo=%3cdata%20search_text=%22ORA-600%2015206%22%20search_result_size=%226%22%20search_result_count=%226%22%20powerview_id=%22Support%20Identifier:%203217057%20(Acxiom%20Corporation),%2014401669%20(Acxiom%20Corporation),%203064890%20(BAXTER%20HEALTH%20CARE),%203321094%20(Baxter%20Healthcare%20Corporation),%203294644%20(Baxter%20Healthcare%20Corporation),%202661521%20(MOORE%20CORPORATION%20LTD),%20671072%20(Pechiney%20Plastic%20Packaging,%20Inc.),%203073240%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%201039789%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%203403199%20(RR%20DONNELLEY%20&%20SONS%20COMPANY)%22%20on_off=%22on%22%20item_position_in_list=%221%22/%3e#CAUSE> Solution<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id21149.1&addClickInfo=%3cdata%20search_text=%22ORA-600%2015206%22%20search_result_size=%226%22%20search_result_count=%226%22%20powerview_id=%22Support%20Identifier:%203217057%20(Acxiom%20Corporation),%2014401669%20(Acxiom%20Corporation),%203064890%20(BAXTER%20HEALTH%20CARE),%203321094%20(Baxter%20Healthcare%20Corporation),%203294644%20(Baxter%20Healthcare%20Corporation),%202661521%20(MOORE%20CORPORATION%20LTD),%20671072%20(Pechiney%20Plastic%20Packaging,%20Inc.),%203073240%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%201039789%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%203403199%20(RR%20DONNELLEY%20&%20SONS%20COMPANY)%22%20on_off=%22on%22%20item_position_in_list=%221%22/%3e#FIX> References<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id21149.1&addClickInfo=%3cdata%20search_text=%22ORA-600%2015206%22%20search_result_size=%226%22%20search_result_count=%226%22%20powerview_id=%22Support%20Identifier:%203217057%20(Acxiom%20Corporation),%2014401669%20(Acxiom%20Corporation),%203064890%20(BAXTER%20HEALTH%20CARE),%203321094%20(Baxter%20Healthcare%20Corporation),%203294644%20(Baxter%20Healthcare%20Corporation),%202661521%20(MOORE%20CORPORATION%20LTD),%20671072%20(Pechiney%20Plastic%20Packaging,%20Inc.),%203073240%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%201039789%20(RR%20DONNELLEY%20&%20SONS%20COMPANY),%203403199%20(RR%20DONNELLEY%20&%20SONS%20COMPANY)%22%20on_off=%22on%22%20item_position_in_list=%221%22/%3e#REF>

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2.0 and later [Release: 11.2 and later ] Information in this document applies to any platform. Symptoms
You are running your application that inserts or updates tables when you experience an ORA-600 [15206] and now large trace files are being generated.

The trace file will contain the following: <- kgeade <- kgeriv_int <- kgeriv <- kgesiv <- ksesic0 <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main <- libc_start_main <- start

Changes
Upgrade to 11.2.0.2 or application of Patch:8946311<https://support.oracle.com/CSP/ui/flash.html#tab=PatchHomePage(page=PatchHomePage&id=gj46o799()),(page=PatchSearchResultsHome&id=gj46pr1y(search=%3CSearch%3E%0A%20%20%3CFilter%20name=%22patch_number%22%20op=%22IS%22%20value=%228946311%22%20type=%22patch_number%22/%3E%0A%20%20%3CFilter%20name=%22plat>. Cause
This will introduce unpublished Bug 10187168.

The error ORA-600 [15206] means that we are trying to create the 65536th child cursor. This upper limit was introduced in the fix for Bug:8946311<https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id�46311>.

This patch allows parent cursors to be obsoleted if the version count exceeds a set threshold. It introduces the new hidden parameter "_cursor_obsolete_threshold". ie: event 106001 is used instead of "_cursor_obsolete_threshold"

High version counts can lead to mutex contention and other contention issues, even if the high version count is for a valid reason. Solution
Perform the following steps:
1. Download and apply the 11.2.0.2.2 PSU Patch:11724916<https://support.oracle.com/CSP/ui/flash.html#tab=PatchHomePage(page=PatchHomePage&id=gj46o799()),(page=PatchSearchResultsHome&id=gj46pr1y(search=%3CSearch%3E%0A%20%20%3CFilter%20name=%22patch_number%22%20op=%22IS%22%20value=%2211724916%22%20type=%22patch_number%22/%3E%0A%20%20%3CFilter%20name=%22pla>

2. Enable event 106001 to address Bug 10187168. To enable the fix "_cursor_features_enabled" needs to be set to a value that depends on the patch level. Please note that the value for _cursor_features_enabled is different for each version.

In 11.1.0.7 set it to value 18 ( 2 is the default value )
In 11.2.0.1 set it to value 34 ( 2 is the default value )
In 11.2.0.2 set it to value 1026 ( 2 is the default value )

EXAMPLE:
So to implement this, we would set the following for 11.2.0.2:

_cursor_features_enabled26
event="106001 trace name context forever, level 1024" If the SELECT is issued again we should not see version_count growing beyond 1024 child cursors. The level value can be higher or lower than 1024 depending on what is felt to be a reasonable value for cursor growth, this would be application specific and this article cannot give advice for specific tuning of this event level.

3. Resolve by recompiling the public synonyms that have differing timestamps using the following query: select

do.obj# d_obj,do.name d_name, do.type# d_type,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",

to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME", decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X from sys.obj$ do, sys.dependency$ d, sys.obj$ po where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#

and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/

order by 2,

If they do not compile, drop and recreate a valid one. References
BUG:10140380<https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id140380> - HIGH NUMBER CHILD CURSORS OVER DBLINK, ORA-600 [17059] THEN ORA-600 [15206] BUG:11836382<https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id836382> - ORA-600 [15206] NOTE:10187168.8<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id187168.8> - Bug 10187168 - Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold NOTE:138554.1<https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id8554.1> - ORA-600 [17059]

From: Syed Jaffar Hussain [mailto:sjaffarhussain_at_gmail.com] Sent: Monday, January 16, 2012 2:56 AM
To: Wolfson Larry - lwolfs
Cc: ORACLE-L
Subject: Re: PSU 12827731

Larry,

We have recently applied this patch in 3 of our cluster environments and luckily, neither any issue nor ORA error has been reported yet.

Regards,

Jaffar
On Mon, Jan 16, 2012 at 11:46 AM, Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com<mailto:lawrence.wolfson_at_acxiom.com>> wrote: Anyone in RAC 11.2.0.2 have trouble after applying this patch?

We got several ORA 600 [15206] and one instance hung. Waiting on Oracle Support

Thanks
Larry

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

--

http://www.freelists.org/webpage/oracle-l

--

Best Regards,

Syed Jaffar Hussain
Oracle ACE Director <http://apex.oracle.com/pls/otn/f?p297:4:4640302666204919::NO:4:P4_ID:186> Oracle Certified Master (10g)<http://education.oracle.com/education/otn/shussain.html> Co-author Oracle 11gR1/R2 RAC Essentials<http://link.packtpub.com/yNZicz> Oracle Magazine DBA of year (2011)
OCP 8i,9i,10g & 11g DBA
RAC Certified Expert
ITIL V3 foundation certified
Oracle RAC SIG Representative for Saudi Arabian region<http://www.oracleracsig.org> Advisory & Governance Council for VirtaThon<http://www.brainsurface.com/virtathon/advisorygovernancecouncil/> I blog at : http://jaffardba.blogspot.com/ LinkedIn : http://www.linkedin.com/in/sjaffarhussain Follow me on twitter : http://twitter.com/#!/sjaffarhussain<http://twitter.com/#%21/sjaffarhussain>

"Winners don't do different things. They do things differently."

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jan 16 2012 - 03:32:38 CST

This message: [ Message body ]
Next message: Jeremy Schneider: "Fwd: Order Request."
Previous message: Syed Jaffar Hussain: "Re: PSU 12827731"
In reply to: Syed Jaffar Hussain: "Re: PSU 12827731"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message