12c Upgrade stuck at fixed object stats

A while ago I did some database upgrades to version 12.1.0.2. One of those upgrades made me a bit nervous. I used “dbca” to perform the upgrade and it was kind of stuck. So I went to the command prompt and started to investigate this. What I found was this:

12c_fixed_obj_stats

A session was waiting for “ADR block file read” again and again. The SQL that was causing this waits can be seen in the screenshot too. It was gathering statistics for X$DBKFDG, part of the “gather fixed object stats” step.

A quick research in My Oracle Support did not bring up any helpful information. My first idea was to check the ADR but I found it to be nearly empty, at least in the suspicous directories like “trace” etc. So I posted this issue on Twitter and asked for help. @realadrienne pointed to a similar issue related to Recovery Advisor and the list of critical issues. So I checked that using

SQL> select * from v$ir_failure;

which returned loads of records that had a priority of “critical” and a status of “closed”. So I tried to get rid of these records using “adrci”.

adrci> purge -age 1 -type hm

This took quite a while, about 10 minutes. to complete. But in the end the v$ir_failure view had no records anymore and the fixed object stats where gathered quickly.

So now there is an additional pre-upgrade check on my personal list, that says “clean up v$ir_failure”. You should add this to your list too to prevent unnecessary delays during database upgrades.

Thanks to @realadrienne, @FranckPachot and @OracleSK for the quick and very helpful assistance.

Thanks also to @MikeDietrichDE for the feedback on this issue.

 

 

Advertisements