EBR – Part 5: Explicit Actualization of Dependent Objects

This is part 5 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In this part I’ll demonstrate everything I wrote about in the previous post.

Visit the index page for all the parts of the series

Creating a New Edition

We want to change the PEOPLE_DL spec, and as in any online upgrade, we start by creating a new edition. Let’s create edition V2 and grant use on it to DEMO_EBR:

-- connected as system
ORA$BASE> create edition v2;

Edition created.

ORA$BASE> grant use on edition v2 to demo_ebr;

Grant succeeded.

Invalidation of Dependent Objects

Before doing the online upgrade properly (i.e., using the new edition), let’s see what happens in a non-EBR environment.
First let’s see the objects in our schema and the dependencies between them before the upgrade:

SQL> break on object_type on object_name on status skip 1
SQL> select o.object_type,
  2         o.object_name,
  3         o.status,
  4         nullif(
  5           d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  6           ' []'
  7         ) depends_on
  8  from   user_objects      o,
  9         user_dependencies d
 10  where  o.object_type like 'PACKAGE%'
 11  and    d.name(+) = o.object_name
 12  and    d.type(+) = o.object_type
 13  and    d.referenced_owner(+) = user
 14  order by o.object_type,
 15           o.object_name,
 16           d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  DEPENDS_ON
------------ ------------ ------- --------------------
PACKAGE      APP_MGR      VALID

             PEOPLE_DL    VALID   PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   APP_MGR [package]
                                  PEOPLE_DL [package]

             PEOPLE_DL    VALID   PEOPLE [table]
                                  PEOPLE_DL [package]


6 rows selected.

Compiling the PEOPLE_DL spec will invalidate its dependent objects – the PEOPLE_DL body and the APP_MGR body:

SQL> alter package people_dl compile specification;

Package altered.

SQL> select o.object_type,
  2         o.object_name,
  3         o.status,
  4         nullif(
  5           d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  6           ' []'
  7         ) depends_on
  8  from   user_objects      o,
  9         user_dependencies d
 10  where  o.object_type like 'PACKAGE%'
 11  and    d.name(+) = o.object_name
 12  and    d.type(+) = o.object_type
 13  and    d.referenced_owner(+) = user
 14  order by o.object_type,
 15           o.object_name,
 16           d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  DEPENDS_ON
------------ ------------ ------- --------------------
PACKAGE      APP_MGR      VALID

             PEOPLE_DL    VALID   PEOPLE [table]

PACKAGE BODY APP_MGR      INVALID APP_MGR [package]
                                  PEOPLE_DL [package]

             PEOPLE_DL    INVALID PEOPLE [table]
                                  PEOPLE_DL [package]


6 rows selected. 

This is the expected behavior, and, as explained in the previous post, one of the motivations for using EBR for online upgrades.
The dependent objects can be revalidated explicitly (using the ALTER PACKAGE … COMPILE BODY statement) or implicitly (as soon as they are used; for example, by executing app_mgr.do_something).

Delayed Actualization

Now, let’s return to our edition-enabled environment.
In the developer session, let’s change the session edition to V2:


-- the developer session
V1> @set_edition v2

Session altered.

V2>

At this point, right after creating the new edition, no editioned objects are actual in this edition; all of them are inherited.
Let’s see the objects in our schema and the dependencies between them, and also the edition in which each editioned object is actual:


V2> break on object_type on object_name on status on edition_name skip 1
V2>  select o.object_type,
  2         o.object_name,
  3         o.status,
  4         o.edition_name,
  5         nullif(
  6           d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7           ' []'
  8         ) depends_on
  9  from   user_objects      o,
 10         user_dependencies d
 11  where  d.name(+) = o.object_name
 12  and    d.type(+) = o.object_type
 13  and    d.referenced_owner(+) = user
 14  order by nvl2(o.edition_name, 2, 1),
 15           o.object_type,
 16           o.object_name,
 17           d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE       VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   ORA$BASE     PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   ORA$BASE     APP_MGR [package]
                                               PEOPLE_DL [package]

             PEOPLE_DL    VALID   V1           PEOPLE [table]
                                               PEOPLE_DL [package]


8 rows selected.

Now let’s do our task – change the PEOPLE_DL spec. For showing how it works we don’t have to change anything – we can simply recompile the spec:


V2> alter package people_dl compile specification;

Package altered.

Compiling an existing object (using the ALTER statement) explicitly actualizes the object in the current edition (if it’s not actual in this edition yet).
Let’s see it by repeating the query of the objects and their dependencies:


V2> select o.object_type,
  2         o.object_name,
  3         o.status,
  4         o.edition_name,
  5         nullif(
  6           d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7           ' []'
  8         ) depends_on
  9  from   user_objects      o,
 10         user_dependencies d
 11  where  d.name(+) = o.object_name
 12  and    d.type(+) = o.object_type
 13  and    d.referenced_owner(+) = user
 14  order by nvl2(o.edition_name, 2, 1),
 15           o.object_type,
 16           o.object_name,
 17           d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE       VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   V2           PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   ORA$BASE     APP_MGR [package]

             PEOPLE_DL    VALID   V1           PEOPLE [table]


6 rows selected.

And indeed, we can see that the PEOPLE_DL spec is now actual in the current edition, V2.
But we can also observe some weird things regarding the PEOPLE_DL body and the APP_MGR body – the dependent objects of the PEOPLE_DL spec:

  • It seems they do not depend on the PEOPLE_DL spec anymore
  • They seem valid, although (as we’ve seen above in the non-EBR example) they must have been invalidated by the compilation of the PEOPLE_DL spec
  • It seems that we still inherit them from ancestor editions, although (as explained in the previous post) this is impossible under the EBR rules

All of this is the result of the fact that Oracle delays the actualization of the dependent objects until they are used.

Let’s call the app_mgr.do_something procedure, and as a result Oracle will actualize and validate the package bodies.


V2> exec app_mgr.do_something

wow! this is not the base edition anymore

PL/SQL procedure successfully completed.

V2> select o.object_type,
  2         o.object_name,
  3         o.status,
  4         o.edition_name,
  5         nullif(
  6           d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7           ' []'
  8         ) depends_on
  9  from   user_objects      o,
 10         user_dependencies d
 11  where  d.name(+) = o.object_name
 12  and    d.type(+) = o.object_type
 13  and    d.referenced_owner(+) = user
 14  order by nvl2(o.edition_name, 2, 1),
 15           o.object_type,
 16           o.object_name,
 17           d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE       VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   V2           PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   V2           APP_MGR [package]
                                               PEOPLE_DL [package]

             PEOPLE_DL    VALID   V2           PEOPLE [table]
                                               PEOPLE_DL [package]


8 rows selected.

That’s better! Now everything is in order and makes sense.
But remember that we cannot count on the implicit actualization and revalidation mechanism in an online upgrade scenario, due to its procrastination characteristic.

Explicit Actualization

So one of the most important advices I can give you about EBR is:

Before exposing a new edition, always actualize all the dependent objects explicitly

How to do it? Let’s repeat the demo, but this time we’ll explicitly actualize and revalidate the dependent objects.

-- connected as system
ORA$BASE> drop edition v2 cascade;

Edition dropped.

ORA$BASE> create edition v2;

Edition created.

ORA$BASE> grant use on edition v2 to demo_ebr;

Grant succeeded.

-- the developer session (connected as demo_ebr)
V1> @set_edition v2

Session altered.

V2> alter package people_dl compile specification;
Package altered.

V2> select o.object_type,
  2         o.object_name,
  3         o.status,
  4         o.edition_name,
  5         nullif(
  6           d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7           ' []'
  8         ) depends_on
  9  from   user_objects      o,
 10         user_dependencies d
 11  where  d.name(+) = o.object_name
 12  and    d.type(+) = o.object_type
 13  and    d.referenced_owner(+) = user
 14  order by nvl2(o.edition_name, 2, 1),
 15           o.object_type,
 16           o.object_name,
 17           d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE       VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   V2           PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   ORA$BASE     APP_MGR [package]

             PEOPLE_DL    VALID   V1           PEOPLE [table]


6 rows selected.

It’s important to note that it’s not enough to compile the objects that are in status “INVALID”, as you may do in non-EBR environments, because the objects may appear (misleadingly) as “VALID”, like in our example.
You can just go and compile all the objects in the schema, but this will unnecessarily actualize all the objects in the new edition.
You can use dbms_utility.compile_schema (with compile_all=>false) or the UTL_RECOMP procedures, but (at least in 11g) these procedures suffered from some nasty bugs (for example bug 13502183), so beware.

I use the following script:


V2> --
V2> -- Name:    revalidate.sql
V2> -- Author:  Oren Nakdimon
V2> -- Tested:  11.2; 12.1; 12.2
V2> --
V2> -- this script is provided AS IS without warranty of any kind
V2> --
V2> declare
  2      type obj_t is record(
  3          object_name user_objects.object_name%type,
  4          namespace   user_objects.namespace%type);
  5      type obj_tt is table of obj_t;
  6
  7      l_obj_list obj_tt;
  8
  9      l_obj_count binary_integer := 0;
 10
 11  begin
 12      loop
 13          select object_name,namespace
 14          bulk   collect
 15          into   l_obj_list
 16          from   user_objects
 17          where  edition_name != sys_context('userenv', 'session_edition_name')
 18          or     status = 'INVALID';
 19
 20          exit when l_obj_list.count = l_obj_count;
 21
 22          l_obj_count := l_obj_list.count;
 23
 24          for i in 1 .. l_obj_count
 25          loop
 26              dbms_utility.validate(user, l_obj_list(i).object_name, l_obj_list(i).namespace);
 27          end loop;
 28      end loop;
 29  end;
 30  /

PL/SQL procedure successfully completed.

The scripts calls dbms_utility.validate for all the current user’s objects that are either:

  • Invalid

Or

  • Inherited by the current edition (i.e., they are actualized in an ancestor edition and not in the current edition)

If the object should be actualized, dbms_utility.validate does the actualization as part of the revalidation.
This process is done repeatedly until it has no more work to do (i.e., all the required actualization was done, and all the objects are either valid or cannot be validated [due to “real” compilation errors]).


V2> select o.object_type,
  2          o.object_name,
  3          o.status,
  4          o.edition_name,
  5          nullif(
  6            d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7            ' []'
  8          ) depends_on
  9   from   user_objects      o,
 10          user_dependencies d
 11   where  d.name(+) = o.object_name
 12   and    d.type(+) = o.object_type
 13   and    d.referenced_owner(+) = user
 14   order by nvl2(o.edition_name, 2, 1),
 15            o.object_type,
 16            o.object_name,
 17            d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE       VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   V2           PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   V2           APP_MGR [package]
                                               PEOPLE_DL [package]

             PEOPLE_DL    VALID   V2           PEOPLE [table]
                                               PEOPLE_DL [package]


8 rows selected.

For other parts of the EBR series, please visit the index page

5 Comments

  1. Iudith Mentzel

    Hello Oren,

    First of all, thanks a lot for this series and the so detailed and clear explanations 🙂

    In light of how your revalidate.sql script works, it looks to me
    that there is a bug in the Oracle packages reference documentation
    for procedure DBMS_UTILITY.VALIDATE, for all the relevant Oracle versions.

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_UTILITY.html#GUID-314EDB9B-7948-4093-8A74-7159186168CE

    First, the procedure does have a 4-th parameter, “edition name”,
    with default SYS_CONTEXT(‘USERENV’,’CURRENT_EDITION’)
    ( there is an error here, it should probably be CURRENT_EDITION_NAME ).
    This parameter is documented as
    “Currently not operable. Reserved for future use”.

    Second, the documentation specifies the following:

    “If the object being validated is not actual in the specified edition,
    the subprogram automatically switches into the edition in which
    the object is actual prior to validation. That is, a call to VALIDATE
    will not actualize the object in the specified edition.”

    As by your demo example, it looks like this last sentence is definitely
    NOT how the procedure works.

    Cheers & Best Regards,
    Iudith Mentzel

    • Oren Nakdimon

      Thanks for your comment Iudith.
      Yes, you are right.
      I guess what they wanted to say is that “a call to VALIDATE will not actualize the object in the specified edition unless it must be actualized” (unlike ALTER…COMPILE which always actualizes the object in the current edition if it’s not actual yet).
      Thanks,
      Oren.

  2. Marcel Hoefs

    Thank you for this new blog post that answers my question on part 4 perfectly.

    Only the documentation link you provided for dbms_utility.validate contains a statement that seems contradictory to the effect you described.

    In the documentation Oracle states:

    “If the object being validated is not actual in the specified edition, the subprogram automatically switches into the edition in which the object is actual prior to validation. That is, a call to VALIDATE will not actualize the object in the specified edition.”

    Is this not contradictory to the behavior you described?

    “- Inherited by the current edition (i.e., they are actualized in an ancestor edition and not in the current edition)

    If the object should be actualized, dbms_utility.validate does the actualization as part of the revalidation.”

    • Oren Nakdimon

      You are right, Marcel. The documentation is wrong.
      As I replied to Iudit, who raised a similar comment:
      I guess what they wanted to say is that “a call to VALIDATE will not actualize the object in the specified edition unless it must be actualized” (unlike ALTER…COMPILE which always actualizes the object in the current edition if it’s not actual yet).
      Thanks,
      Oren.

      • Marcel Hoefs

        So it seems that the logic that dbms_utility.validate uses to determine whether or not an object must be actualized is the same logic Oracle uses when an object gets actualized on first use.

        Or from the dbms_utility package specification:

        “This procedure validates a database object using the same mechanism that is used for automatic re-validation.”

Leave a Reply

Your email address will not be published.