Re: Cursor_sharing=similar.....failed

Daniel,

Not sure what platform or version of Oracle you're on, but I did run into something similar on AIX with Oracle v9.2.0.5. An overview is in MetalinkNote #3639594.8. It's a bug. Check the note to see if your platform.version fits for this issue. Not sure if there's a patch for this - we were in the process of upgrading to 9.2.0.7 and the problem disappered. *

"*

When PMON cleans up a dead process then it may notice an in-progress "ALTER SYSTEM SET PARAMETER" command and assume (incorrectly) that this was from the failed process. It may then try to clean up the in-progress command and so conflict with the running session performing the ALTER SYSTEM command.

This shows up as numerous "PMON failed to acquire latch, see PMON dump" messages."

On 8/7/07, daniel.hubler_at_aurora.org <daniel.hubler_at_aurora.org> wrote:
>
>
> Looking for ideas/comments/suggestions..........
>
> We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at
> 6am.
> Did that via an "ALTER SESSION SET....." command.
> The environment is large (7TB) but not very busy on Saturday morning.
> We had cursor_sharing=similar in multiple test environments, for many
> months without issue.
>
> At 9:15am, we started to generated ora-04031 errors.
> By 9:30am, we could no longer sign-on.
> We ended up shutting down the middle-tier, and deleting all of the
> OS processes on the DB server, that represented connections to the
> instance.
> At that point, we were able to signon, backout the change, and startup the
> middle-tier again.
>
> In the alert log, we saw the ora-04031 errors, and also many (thousands)
> of messages saying
> "PMON failed to acquire latch, see PMON dump".
>
> The PMON trace file show the same entry, repeated thousands of times:
> "PMON unable to acquire latch 2c4cb108 library cache
> possible holder pid = 1000 ospid=42586708"
>
>
> Any ideas on what happened would be appreciated.
> Thanks.
>
>
>
>
> Dan Hubler
> Database Administrator
> Aurora Healthcare
> daniel.hubler_at_aurora.org

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