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.
Recommended Posts
