Oracle 11g upgrade and SYS.AUD$ obstacles

During one of the Oracle 10gR2 databases manual upgrade to Oracle 11gR2 couple of days ago, to our own surprise, the upgrade procedure took nearly 5.34 hrs to finish. Although we had done earlier 100's of database manual upgrades comfortably within 2 hrs time window for individual database, the delay indeed surprised us this time. Unfortunately, there is no log that could help us understanding what is really going on. We started investigation the delay after reaching the anticipated upgrade duration.  The only new addition in this upgrade was a standby database in place this time. But, this shouldn't be the genuine reason of the delay, right?
When we queried the v$session , we found the following statement was executing:

update SYS.AUD$ set dbid = 223249361 where dbid is null and rownum <= 10000

It was understood that during the course of Oracle Server component upgrade in 11gR2, the upgrade procedure performs the following actions on the SYS.AUD$ table:

  • Add 11 new columns
  • Updates ntimestamp column
  • Update DBID column with the appropriate value for every <10000 records in a loop
Fortunately the SYS.AUD$ table in the database has over 6.4 million records. And the update statement on the table was for every 10000 records at a time which took 4.30 hrs time to complete. Subsequently the remaining components upgrade procedure took 1 hr time to complete.

When we did our upgrades earlier, auditing option was not enabled on the databases, hence, we never encountered such delayed scenario before. In order to speed up the upgrade length, when SYS.AUD$ contains huge amount records, it is best recommend the following practice:

This would certainly help us when we plan Oracle 12c upgrade in the future.

No comments: