Dienstag, 11. April 2017

Oracle Active Dataguard is a great software for offloading read only activities from the primary nodes to nodes which is not utilized anyhow (at least during non-disaster times).
A very good example for doing so is regular extraction of data for processing in a data warehouse. That's also the case which led to this post.

The setting is just slightly more complex: both, primary and standby site are RACs - of course when you plan to switchover/failover, they should be somehow similar. (And in case of a disaster, it's planned to failover an disable all not-so-important activities; warehouse extract would be one of those).

Also the offloaded activities are slightly more complex than a simple query. In this case PL/SQL code is included. According to the documentation, that's not an issue at all - it's still read only. But in this DB (11.2.0.4), it was an issue: Sometimes the result was not as expected.

Now I first need to define the expectation: in this special case, the ERROR is the expectation, and no error the wrong result. Whenever a package (with global variables) is changed in a session, all other sessions which initialized the package before the change, but called it afterwards, must get

04068, 00000, "existing state of packages%s%s%s has been discarded"
// *Cause: One of errors 4060 - 4067 when attempt to execute a stored
// procedure.
// *Action: Try again after proper re-initialization of any application's
// state.

Then the application can catch this error, re-initiate the package and continue.
that's how it should be.

But we sometimes had strange results in the test environment. After some investigation, we found it and I simplified it to this Testcase:

-- test
set serveroutput on
exec SR_TEST1.proc1
SR_TEST1@EBSSID051 > BEGIN SR_TEST1.proc1; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SR_TEST1.PACK" has been invalidated
ORA-04065: not executed, altered or dropped package body "SR_TEST1.PACK"
ORA-06508: PL/SQL: could not find program unit being called: "SR_TEST1.PACK"
ORA-06512: at "SR_TEST1.PROC1", line 4
ORA-06512: at line 1
> select * from tab1;
ID VER
---------- ------------------------------
1 2
-- THIS Is the expected result

SESSION3@INST1_ADG:==================

-- test & initiate
set serveroutput on
exec SR_TEST1.proc1
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SR_TEST1.PACK" has been invalidated
ORA-04065: not executed, altered or dropped package body "SR_TEST1.PACK"
ORA-06508: PL/SQL: could not find program unit being called: "SR_TEST1.PACK"
ORA-06512: at "SR_TEST1.PROC1", line 4
ORA-06512: at line 1
select * from tab1;
ID VER
---------- ------------------------------
1 2

Even the testcase is clear and simple, it was not that easy to identify the root cause in a more complex life environment. Special thanks to Andy Sayer who helped me to sort and refine my ideas over twitter.

With this testcase I was able to open a SR at Oracle, and after some some Support-ping-pong I got a useful information:
It is known as Bug: 18357555: ADD SUPPORT FOR KGLIV_BRNULPALL TO KGLCLUSTERMESSAGE() / KQLMBIVG()

I'm only aware of one Patch:25808607 (for PSU:11.2.0.4.160419 - together with some other patches) which is available right now. But you can ask for your own, if you have an Active Dataguard and RAC, and Packages with global variables.