Fixing ORA-07445 [opixrb()+6944] + ORA-600 [opixrb] Over Gateway

Today I was contacted by one of our customers (one of the biggest Ukraine banks) – that faced next issue after upgrading server-size PL/SQL code to new version on one of their critical systems:

  • they wasn’t able to compile one of packages – package compilation session just ended with the next message
    • ORA-01331 end of file communication channel
  • from the server-side(trace-file) error message was next:
    • ORA-07445: exception encountered: core dump [opixrb()+6944] [SIGSEGV] [ADDR:0x0] [PC:0x103C1D980] [Address not mapped to object] []
  • current SQL Statement from the trace-file:
    • SELECT :”SYS_B_0″ DUMMY FROM “dbo”.”sysusers”@APS WHERE “name” = :”SYS_B_1″
  • customer confirmed that they are using Oracle Gateway (Heterogeneous Services) link to Microsoft SQL Server inside mentioned package
  • there weren’t any changes to mentioned package during application code update
  • there weren’t any patches applied to ORACLE_HOME since everything worked OK
  • it just stopped compiling
  • they must have working system tomorrow morning!
  • they are on Apr2012 PSU over 11.2.0.3 – so almost the most patched latest available patchset for 11.2

So it was like Severity 1 issue, because of time limit and critical system factor, so I just asked DBA to isolate exact failing procedure and score its importance (later he confirmed that it wasn’t critical function inside package and they were ready to comment failing code).

I have checked My Oracle Support Knowledge Base, BUG Database and Google “Knowledge Base” for already known issues, but wasn’t lucky…

Actually, I’m absolutely sure that DBA did similar research before asking for help…;-)

So, I looked into trace-file more carefully – the opixrb function was a little bit familiar – Tanel Poder was talking about opi* functions during his last Advanced Oracle Troubleshooting v2.0 Online Training(May-June of 2012)… but don’t pay too much attention to opi*

The most interesting was the Current SQL Statement with next binds :”SYS_B_0″ and :”SYS_B_1″.

It’s absolutely clear that here we have some kind of automatic literal replacement to bind variables – behavior specific to Oracle RDBMS  when parameter CURSOR_SHARING set to to SIMILAR or FORCE.

I have checked trace-file section Compilation Environment Dump and found: CURSOR_SHARING = FORCE

So, I just asked to compile package after setting CURSOR_SHARING to EXACT and problem has gone!

Explanation:

  • actual catalyst of issue was the value EXACT set to parameter CURSOR_SHARING at instance level sometime before installing new application PL/SQL code
  • mentioned package was invalidated by dependencies from changed objects
  • at the time of compilation new value on CURSOR_SHARED required to transform SQL text to using bind-variables, but
  • as a result of transformation we have got not valid statement, because of there is not actually binding to :”SYS_B_0″
  • so, we have some kind of not yet published/fixed BUG at the interfacing level of bind variable substitution and Oracle Gateway (Heterogeneous Services)

Workaround:

Conclusion:

  • going for help to Oracle Support usually requires quite much time, so You may try to find solution/workaround by yourself
  • actually, I would prefer that bind variable substitution will be disabled all time for statements passing to Oracle Gateway (just like with CURSOR_SHARING = EXACT) and we actually control witch statement will be passed to Gateway.

Oleksandr

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s