Active DataGuard does NOT show ORA-04068 when package in primary changes

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Wed, 15 Mar 2017 11:35:28 +0100
Message-ID: <CALH8A93Ea_e8gNe+N721OszJ+K1kN31vA_Bh6qeP2N=X3ZviGw_at_mail.gmail.com>

Hi List,

I found an interesting situation where I do NOT get an error stack like this on an Active DataGaurd, whereas get it on the primary: 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

My question is: Is this expected / documented behavior and I'm only blind searching in the docs?

Version: 11.2.0.4.161018 plus a bunch of one-offs (it's an EBS R12 env).

Testcase:
(results are only shown when of any interest) PRI ... Primary DB
ADG ... Active DataGuard

it's important the sessions are not closed within the test!

prepare user:
*SYS_at_PRI: *
*======= *
-- create the user
create user SR_TEST1 identified by "Sr_hidd3nPw;" default tablespace users temporary tablespace temp;
grant connect, create session, create procedure, create table to SR_TEST1; alter user sR_TEST1 quota unlimited on users;

*SESSION1 _at_ PRI: *
*=========== *
-- connect & create objects
connect SR_TEST1/"Sr_hidd3nPw;"
set serveroutput on

create table tab1 (id number, ver varchar2(30)); insert into tab1 values (1, '_');
commit;

create or replace PACKAGE PACK AS
first_load date := to_date('3333-01-01 01:01:01', 'YYYY-MM-DD HH24:MI:SS'); PROCEDURE proc;
END PACK;
/

CREATE OR REPLACE
PACKAGE BODY PACK AS PROCEDURE proc AS
BEGIN
if first_load > sysdate then
first_load := sysdate;
end if;
DBMS_OUTPUT.PUT_LINE('1: loaded first on ' || to_char(first_load,'YYYY-MM-DD HH24:MI:SS') ); NULL;
END proc;

END PACK;
/
update tab1 set ver='1' where id=1;
commit;

create or replace procedure proc1
is
begin
SR_TEST1.pack.proc;
end;
/

• test & initiate set serveroutput on exec SR_TEST1.proc1 select * from tab1;

> 1: loaded first on 2017-03-15 09:52:00
>
> PL/SQL procedure successfully completed.
>

select * from tab1;
>
> ID VER
>---------- ------------------------------
> 1 1

*SESSION2 _at_ PRI: *
*=========== *
-- connect & create objects
connect SR_TEST1/"Sr_hidd3nPw;"
set serveroutput on

• test & initiate set serveroutput on exec SR_TEST1.proc1 select * from tab1;

> 1: loaded first on 2017-03-15 09:53:00
>
> PL/SQL procedure successfully completed.
>

select * from tab1;
>
> ID VER
>---------- ------------------------------
> 1 1

*SESSION3 _at_ ADG *
*=========== *
-- connect & create objects
connect SR_TEST1/"Sr_hidd3nPw;"
set serveroutput on

• test & initiate set serveroutput on exec SR_TEST1.proc1

> 1: loaded first on 2017-03-15 09:54:00
>
> PL/SQL procedure successfully completed.
>
> select * from tab1;
>
> ID VER
> ---------- ------------------------------
> 1 1

*SESSION 1 _at_ PRI: *
*============ *
*-- change package body*

CREATE OR REPLACE
PACKAGE BODY PACK AS PROCEDURE proc AS
BEGIN
if first_load > sysdate then
first_load := sysdate;
end if;
DBMS_OUTPUT.PUT_LINE('*2*: loaded first on ' || to_char(first_load,'YYYY-MM-DD HH24:MI:SS') ); NULL;
END proc;

END PACK;
/
update tab1 set ver='*2*' where id=1;
commit;

• test set serveroutput on exec SR_TEST1.proc1 select * from tab1;

> *2*: loaded first on 2017-03-15 09:55:51
>
> PL/SQL procedure successfully completed.
>
>
> ID VER
> ---------- ------------------------------
> 1 *2 *

*SESSION 2 _at_ PRI: *
*=========== *

• test set serveroutput on exec SR_TEST1.proc1 SR_TEST1_at_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 _at_ ADG *
*=========== *

• test & initiate set serveroutput on exec SR_TEST1.proc1

*1*: loaded first on 2017-03-15 09:54:00

PL/SQL procedure successfully completed.

select * from tab1;

ID VER

---------- ------------------------------

1 *2*

• HERE you see the update on the table is applied on ADG already, but the procedure output is still prefixed with 1: - not 2: as it should be.
• to check: the package is there! set pages 99 select text from all_source where name = 'PACK' and type = 'PACKAGE BODY' 3 order by line;

TEXT

---

PACKAGE BODY PACK AS PROCEDURE proc AS
BEGIN
if first_load > sysdate then
first_load := sysdate;
end if;
DBMS_OUTPUT.PUT_LINE('*2*: loaded first on ' || to_char(first_load,'YYYY-MM-DD HH24:MI:SS') );

NULL;
END proc;

END PACK; 12 rows selected.

any hint is very welcome.

-- 
Martin Berger
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

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

Received on Wed Mar 15 2017 - 11:35:28 CET