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