Thursday, August 6, 2020

utlrp hangs after upgrade to 12c ERP database

We were performing database upgrade from oracle 11g to oracle 12c with Oracle Apps 12.1.3. We faced an issue where database upgrade took < 30 mins. However, it made huge number of invalids ~170K. Compiling these invalid objects by utlrp took  several hours.  Finally was able to resolve this issue. Here is what was done-

  • Dropped non-working invalids. Database was compiling many invalids and using dblinks as there were some views/synonyms which were pointing to remote databases. Since, the dblinks were not working, so utlrp was getting struck there.
  • Increased Job queue processes- In oracle 12c,  job_queue_processes was 25 and it was taking 1 hour to compile invalids. Increased  it to 50 and invalids got compiled in half the time- 30 minutes.

No comments: