Aw: Active DataGuard does NOT show ORA-04068 when package in primary changes
From: <rogel_at_web.de>
Date: Wed, 15 Mar 2017 12:49:43 +0100
Message-ID: <trinity-e279fa89-5279-4088-a928-7e218e6b25a6-1489578583634_at_3capp-webde-bs22>
, as expected)
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 15 2017 - 12:49:43 CET
Date: Wed, 15 Mar 2017 12:49:43 +0100
Message-ID: <trinity-e279fa89-5279-4088-a928-7e218e6b25a6-1489578583634_at_3capp-webde-bs22>
Hi Martin,
this does not reproduce for me on 12.1.0.2 (where I get
SESSION3 _at_ ADG exec SR_TEST1.proc1
BEGIN SR_TEST1.proc1; END;
BEGIN SR_TEST1.proc1; END;
*
FEHLER in Zeile 1:
ORA-04068: Der bestehende Status der Packages wurde aufgehoben
ORA-04061: Bestehender Status von package body "SR_TEST1.PACK" wurde annulliert
ORA-04065: Ausführung nicht erfolgreich, package body "SR_TEST1.PACK" wurde geändert oder gelöscht
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "SR_TEST1.PACK" konnte nicht gefunden werden
ORA-06512: in "SR_TEST1.PROC1", Zeile 4
ORA-06512: in Zeile 1
FEHLER in Zeile 1:
ORA-04068: Der bestehende Status der Packages wurde aufgehoben
ORA-04061: Bestehender Status von package body "SR_TEST1.PACK" wurde annulliert
ORA-04065: Ausführung nicht erfolgreich, package body "SR_TEST1.PACK" wurde geändert oder gelöscht
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "SR_TEST1.PACK" konnte nicht gefunden werden
ORA-06512: in "SR_TEST1.PROC1", Zeile 4
ORA-06512: in Zeile 1
, as expected)
Matthias
Gesendet: Mittwoch, 15. März 2017 um 11:35 Uhr
Von: "Martin Berger" <martin.a.berger_at_gmail.com>
An: "Oracle-L oracle-l" <oracle-l_at_freelists.org>
Betreff: Active DataGuard does NOT show ORA-04068 when package in primary changes
Von: "Martin Berger" <martin.a.berger_at_gmail.com>
An: "Oracle-L oracle-l" <oracle-l_at_freelists.org>
Betreff: Active DataGuard does NOT show ORA-04068 when package in primary changes
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