ORA-29382 or ORA-06512 during database upgrade from 11.1 to 12.1

  • A+
Categories:Oracle

Last year, when I was migrating/upgrading a huge Oracle database from 11.1 to 12.1, I encountered one really tough issue and I found the reason after several months.

The error messages were like this during the upgrade with DBUA:

In fact they were caused by the same Oracle resource plan issue, so if the first issue was fixed, then the later two issues would disappear also.

At the first time I just ignored such error and finished the upgrade, then I checked the database system objects while did not find any issue. The upgraded database worked really well as a non-cdb database.

I had to face this issue when I did the same migration one more time and imported it to a 12c CDB as a PDB, and it became a nightmare to me.

I found sometimes the instance would be terminated abnormally, and got errors from the database alert logs:

While I did find the plan named INTERNAL_PLAN, and I thought this issue was caused by the resource plan issue during the upgrade.

I searched lots of notes on the Oracle support portal and the whole internet, while none of them worked and I got really confused and tired.

Several months passed and I checked this issue time to time.

One day I compared the resource plans on the production database and a new created 11.1 database and one big assumption appeared:

The customer modified some database default resource plans while some values would cause error in 12.1 database so I got this issue and in fact if I ignored it the updated database would have wrong resource plans or miss some of them.

In the production database, I got below settings:

These subplans worked well on the production servers so I did not try to change them for a long time.

And I changed them as below before the upgrade:

Guess what happened? OMG! All these errors gone during the upgrade including the ORA-7452 error!

This issue was hard because I did not think the workable subplans would become invalid during the upgrade, and I also could not do the upgrade tests as needed.

It was lucky for me to find the root cause before the final cutover. :)

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: