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.
