ADOP edition cleanup

Oracle e-Business Suite administrators have access to pretty extensive documentation regarding Patching procedures. In Oracle EBS 12.2 Oracle introduced ADOP patching method along with Oracle Database EBR (edition-based redefinition) feature that allows minimizing downtimes for patching activities. As with everything in this world – it also comes with a few caveats. So let’s talk about the ADOP cleanup part a bit.

DualFS

In the application tier, it is rather simple – there are two filesystems to take care of. So every time the patch cycle is open, in patchFS actual patching is happening. Once the downtime comes cutover flips the filesystems and now the code is running a patched version. As a DBA I would suggest keeping PatchFS always up to sync. Finish patching cycles with FSClone and avoiding deploying the code directly in RunFS. There are a few options available with FSClone and prepare phases and even some custom synchronization options for very specific use cases. But at the end of the day that is pretty much it when it comes to taking care of the application filesystem.

Database Editions

Different story with the database. Every time a new patch cycle is started (adop phase=prepare) – the database creates a new edition. Thankfully, Oracle has tooling that handles all edition-related issues pretty well, like cross-edition triggers and AD_ZD package. In normal circumstances, APPS DBA should not be looking for any edition-related issues. However, if Oracle security patches are being applied and other maintenances are happening over time, the edition count in the database keeps growing. In some discussions with Oracle EBS folks, I recall the suggestion not to go over 20 editions, documentation states 25 or more. I would suggest not going over 10 or keep even lower.

EBR is quite complicated, but for sake of simplicity, let’s imagine that the edition is like a filesystem based on snapshots. The root filesystem (A) has all the objects (or files). In the new snapshot filesystem (B) if an object or file is created it exists only on that particular one (B), however, all the files that existed in the root filesystem (A) will have only references or links to the root filesystem. So in snapshot filesystem (B) if the object is accessed that existed in root filesystem (A) – basically it has only a link and actual data is in A. That is the whole point not to duplicate data in the database, create new versions online, and switch to it through cutover. Oracle EBS Vision database has over 400K objects, real-life EBS systems will have something like 600k and we have seen heavily customized ones with over 1M objects. So the approach as with the DualFS on application filesystem would not work in the database due to the time required to create those objects and significant space utilization. EBR solves these problems.

Cleanup

In regard to edition cleanup, I have heard several wrong assumptions. One of the common ones is that those editions are being cleaned in actualize phase. Documentation says:

$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

However, phase cleanup_mode=full is the one responsible for moving objects to the latest edition and dropping old editions.

The First edition in the database is usually called ORA$BASE. If there’s still this edition in the database – it’s pretty obvious that edition cleanup is not performed. Oracle EBS provides a few good SQL scripts like ADZDSHOWOBJS.sql showing edition states, ADZDSHOWED.sql will show editions or can obtain data directly from dba_editions. Here’s a typical example.

[apps ~]$ sqlplus @$AD_TOP/sql/ADZDSHOWOBJS.sql

=========================================================================
=                      Editioned Objects Per Edition
=========================================================================

EDITION_NAME       A_VALID  A_INVALID    A_TOTAL    S_VALID  S_INVALID    S_TOTAL      TOTAL
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ORA$BASE		    600046	     85	 600131	      0	      0	      0     600131
V_20210219_0121      12183          0      12183     564922         76     564998     577181
V_20210320_0836      15890          0      15890     577104         76     577180     593070
V_20210618_1013       3120          0       3120     592994         76     593070     596190
V_20220917_2123       1187          0       1187     596114         76     596190     597377
V_20221110_1255         90          0         90     597301         76     597377     597467
V_20221117_1541       2408          3       2411     597392         75     597467     599878

Note :
(A_) - Actual Objects
(S_) - Stub Objects

One of the main problems in my mind with a lot of editions is the fact that those editions are growing internal metadata tables. If you query dba_objects it’ll always return the number of objects in edition however sys.obj$ will multiply that by edition count. And once internal storage has been stretched it can be resized only by export/import. I have seen several performance issues against all_tab_cols or other all_ performance views due to the optimizer acting weird when there are a lot of editions.

Execution

Executing edition cleanup is pretty much like every other ADOP maintenance activity. It can be merged with the ADOP apply phase as well. Below, I’ll share a few recommendations to make things smoother and avoid a few issues learned.

Database connections

By default, ADOP cutover disconnects all APPS sessions and that pretty much guarantees that EBS after cutover will connect to the current or latest database edition. However, it’s not true for any custom schema or application connected to the edition-enabled user in the database.

SQL>  select distinct SESSION_EDITION_ID, count(1) from gv$session group by SESSION_EDITION_ID;

SESSION_EDITION_ID   COUNT(1)
------------------ ----------
                 0        423
          59398809         12
          61212763        620

One option is to restart the database before the ADOP cleanup_full phase. Another is to disconnect all sessions that are connected to previous editions. In the example above there are still 12 sessions connected to edition_id: 59398809 and cleanup will fail with an error, most likely with something like this:

ORA-00600: internal error code, arguments: [kghstack_underflow_internal_3],

Object creation in the process

Avoid creating any objects between actualize and cleanup phase (think about automated object creation, concurrent programs, and schedules) or it may end up in a situation as below. Edition cleanup completes successfully, however, there’s an edition with one or few objects.

EDITION_NAME       A_VALID  A_INVALID    A_TOTAL    S_VALID  S_INVALID    S_TOTAL      TOTAL
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
V_20211210_1255          1          0          1          0          0          0          1
V_20211217_1541       2408          3       2411     597392         75     597467     599878

To find the object of interest run something like:

 select * from dba_objects where EDITION_NAME = 'V_20211217_1541';

Oracle’s existing process to clear this problem would be to start the edition cleanup procedure over again from the prepare, actualize, and cutover phase. However, in some simpler cases, it could be as easy as connecting to the current edition, and recreating those objects with something like CREATE OR REPLACE FORCE EDITIONABLE VIEW [PACKAGE]. Once there are no objects in the OLD edition – rerun (adop phase=cleanup cleanup_mode=full) that will take care of the dangling edition.

Database links or TYPE objects

A lot of headaches may deliver obsolete or nonworking database links. If there’s an object (like a package) in an old edition that references a database link that either is not working or is not present in the current edition – most probably the edition cleanup procedure will fail.
The solution to this is to create dummy DB links (probably even users and dummy objects) to themselves, so those references work, perform edition cleanup and clear those objects out once edition cleanup has been completed. This is especially relevant for environments that have been upgraded from previous versions or migrated and referenced objects are not always database links. Similar issues have been observed with object_type TYPE.

Safety

Always a smart idea before such maintenance is to create a guaranteed restore point. In case of any issues with the data dictionary or edition cleanup. It’s super easy to get back before the process has started and fix the issues in advance before they actually happen. However, to avoid unnecessary issues, make sure there’s enough space in db_recovery_file_dest_size.

To conclude

Oracle database edition cleanup is an important life cycle management task in EBS release 12.2. This operation is not executed so often sometimes we see organizations even forget about that at all. This is not a complicated task as currently, the tooling has grown. It may be a bit challenging task executing cleanup for the first time or executing it against an upgraded environment as we have seen issues in the past. However, if managed to get through the first cycle and issues with the data dictionary are resolved – this should be a walk in the park.