Kevin Kempf's Blog

June 5, 2017

The trouble with editions

Filed under: Online Patching, R12.2 — kkempf @ 10:27 am
socrates
(fake quote to grab your attention)

EBS 12.2 and Edition Based Redefinition

Unless you’ve been living under a rock for the past 5 years, you’re probably aware that EBS 12.2 online patching leverages Edition Based Redefinition (EBR).  In a nutshell, when you begin an online patch cycle, the adop phase=prepare command creates a new edition of the database which “contains” all current objects, but will also absorb database changes as you apply patches.  Simplified, it becomes your “on deck” database, ready to take effect (or step up to the plate, if you prefer the analogy) after you complete the adop phase=cutover.

Recognizing you have a problem

Left unattended, this system will continue to work for a long time without issue.  In my case, over 2.5 years, before I realized I had become an edition hoarder and needed help.  I realized that I had old editions hanging around, back to the day of our 11i to 12.2 go live.  Oracle very conveniently names the editions after the date/time they were created.  So you can see our first 12.2 edition was V_20141127_2225 (11/27/2014 at 10:25pm).

select edition_name from dba_editions order by edition_name;
ORA$BASE
V_20141127_2225
V_20141128_1314
V_20141128_1504
V_20141128_2136
V_20141129_0027
V_20141129_0218
V_20141129_0709
V_20141129_0803
V_20141129_0852
V_20141129_1244
V_20141130_0623
V_20141130_0719
V_20150108_0953
V_20150127_1002
V_20150208_1037
V_20150208_1222
V_20150209_1058
V_20151006_1147
V_20151105_1218
V_20160107_1508
V_20160129_1412
V_20160318_0805
V_20160408_0959
V_20160420_1007
V_20160614_1519
V_20160616_1401
V_20160928_1047
V_20161111_0914
V_20170316_1417
V_20170320_1356
V_20170322_1318
V_20170329_1355
V_20170330_1819
V_20170512_1050

35 rows selected.

Get Help

The fix is pretty simple, but I’ll add a real-world “gotcha” to Oracle’s documentation.  You can look  at EBS 12.2 Maintenance guide(https://docs.oracle.com/cd/E26401_01/doc.122/e22954.pdf) Pg 3-27 to see the official verbiage, but here’s the gist of how to drop the old editions:

  • adop phase=prepare
  • adop phase=apply patches=(whatever patches you want to do, or none)
  • adop phase=actualize_all
  • adop phase=finalize finalize_mode=full
  • adop phase=cutover
  • adop phase=cleanup cleanup_mode=full

I’ll point out that Oracle’s document does not include the apply phase, however you can indeed apply patches during an edition cleanup.  Not only have we done it, but Oracle said it was fine to do in an SR.

The other thing the document doesn’t tell you is that this is not necessarily an impact-free operation.  We had a long maintenance window on a Sunday afternoon and decided to defer the phase=cleanup cleanup_mode=full until Monday morning when users were in the system.  Within 10 minutes, we had calls to the help desk because of massive object contention and locks, to the point where nobody could log into EBS.  I look at Enterprise Manager and it’s bright red, with hundreds of blocking locks in the system.  I don’t know if this is a result of the fact that we were dropping 30+ editions, or if it will happen just doing 1 edition.  Regardless, we ended up stopping the front end during the next maintenance cycle, then running the cleanup, and it was done in 45 mins or less.

SQL> select edition_name from dba_editions order by edition_name;

EDITION_NAME
------------
V_20170512_1050

Blog at WordPress.com.