Tough problem!!!

[VinodN]

Hello Erman, we are facing a very difficult issue in our 12.2.10 EBS instance. DB is 19c. There are some old editions which are preventing successful completion of character set conversion using DMU. But we are unable to drop those editions.

Already tried the following:

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

ADZDSHOWIOBJS.sql shows no inherited objects.

Also tried exec sys.ad_zd_sys.retire_old_editions;

Your insight will be really helpful!

[ErmanArslansOracleBlog]

So, you have executed the following ->
       
1.adop phase=prepare
2.adop phase=actualize_all
3.adop phase=finalize finalize_mode=full
4.adop phase=cutover
5.adop phase=cleanup cleanup_mode=full

So what do you have in adzdshowlog.out just after executing the command sequence above?

[VinodN]

adzdshowlog.out

This is the output.

[VinodN]

Looks like after from 19.18 onwards, the cleanup cycle does not immediately drop editions which are not in use. A background job does it and it takes its own time.
To immediately drop them support asked us to run DBMS_EDITIONS_UTILITIES.CLEAN_UNUSABLE_EDITIONS

[ErmanArslansOracleBlog]

In the log -> "19c Implementation - Drop covered objects deferred to Native Cleanup Background Job"

And yes, support is right.

With Oracle Database 19c Release Update 19.18 or later, cleanup of unused editioned objects in old database editions is handled by a background cleanup process in the database itself. A requested cleanup in adop will signal to the database that an old database edition is no longer in use, and over time the database will clean up unused objects in old editions using a background job. This will allow adop cleanup to complete much more quickly.

@$AD_TOP/sql/ADZDRUNCLEANUNUSE.sql does that job by executing

"sys.dbms_editions_utilities.clean_unusable_editions"  

adop does this only if your db version is 19c or above.

So listen what Oracle Support says.

[VinodN]

Support asked us to run the cleanup procedure. But strangely, editions got created again... To begin with there were 16 editions. Cleanup proc delete 15 and only one remained. But after a while again there were 12 editions. All we did was resumed DMU session.

[Laurel]

Hi! did you commit after cleanup if you run manually that call :) ?

[ErmanArslansOracleBlog]

Yes. Laurel got a point. How did you dropped those editions manually?

[VinodN]

We just ran sys.dbms_editions_utilities.clean_unusable_editions.
Oracle support asked us to run it, as we did not want to wait for the background process/job to drop the editions.

[VinodN]

The procedure drops the editions. Drop is DDL, so autocommit. The document also doesn't say anything about commit.

[ErmanArslansOracleBlog]

Those the editions are dropped after you execute that procedure, right?

you checked it using dba_editions and see that all were dropped.
and then you continue with DMU operations and then you checked the dba_editions again and this time you saw new editions created. Is that right?

[VinodN]

Yes, Erman. That's how it happended.

[ErmanArslansOracleBlog]

Send me the contents of dba_editions (before and after ...)

[VinodN]

Unfortunately, we had to scrap that instance in a hurry and do the whole thing from scratch, as DMU was not moving ahead at all. During its initial scan, the old editions where there, but when it was not able to find those during post-conversion tasks (because we had dropped them, as per support's advise) it could not go ahead. So there was nothing left do to but begin from the fresh instance again.

[ErmanArslansOracleBlog]

So? Do you have any problems with those editions now?

[VinodN]

Now we have just done scan of the new cloned db. Oracle has asked us to drop editions and do scan again. Problem is, last time it took almost 12 hours to drop the last edition..

[VinodN]

Why were those editions re-created? Were they not dropped really in the first place?

[ErmanArslansOracleBlog]

Were they new editions or were they the old ones?

If they were the old ones, then it means the cleanup / removal / deletion process could not remove them.. So in that case , you should concentrate on diagnosing that part.

There is no mechanism that creates database editions without any reasons.. (reasons should be building a patching cycle, and things like that..)

[VinodN]

They must have been old editions as we did not run any new patching cycle, etc.

[ErmanArslansOracleBlog]

So check why the removal of the dropped edition could not succeed. (you executed the removal process manually right? so check your own log and check the log of that standard database procedure)