By Martin Wendler —

Oracle: Upgrade from 12.1.0.1 using autoupgrade.jar takes ages

Oracle: Upgrade from 12.1.0.1 using autoupgrade.jar takes ages

Last year, when I upgraded many Oracle databases to 19c, I had a few databases which were still on Oracle 12.1.0.1. In case that you forgot what 12.1.0.1 meant for Oracle and all customers:

  • Until 12.1.0.1, we had Enterprise Edition, Standard Edition and Standard Edition One. All customers had to migrate their Standard Edition (One) licenses to Standard Edition Two
  • 12.1.0.1 was the first database to include the Multitenant feature, which added the ability to create pluggable databases. But in fact, this feature wasn't very complete - very many functions were missing
  • Starting with 12.1.0.1, Oracle included Unified Auditing

Upgrade to 19c

As the Upgrade from 12.1.0.1 to 19c is not directly available, I had to make a "double tap upgrade", which means that first, I had to upgrade to a release newer than 12.1.0.1, which then adds the support to upgrade to 19c.

For the first upgrade, two database versions were available:

  • 12.1.0.2
  • 12.2.0.1

As the latest patches for 12.1.0.2 are older than for 12.2.0.1, I used 12.2.0.1 for the first upgrade.

Software installation

First I installed Oracle 12.2.0.1 and 19c. Then I installed the latest RUs. The listener was created using the 19c software.

Oracle recommends installing Oracle Grid Infrastructure for all systems, because it will add the ability to start all the resources on the system.

But on Windows, we do not really need it, although it's also recommended there. But as long as there are no standby databases, it's not needed. Besides the fact that patching GI needs additional time...

Upgrade

After software isntallation, I used autoupgrade.jar to prepare the database:

%ORACLE_HOME_19c%\jdk\bin\java -jar autoupgrade.jar -config_values "sid=%ORACLE_SID%, source_home=E:\oracle\product\12.1.0.1, target_home=D:\oracle\product\12.2.0.1" -mode analyze

Upgrading the database was quite similar:

%ORACLE_HOME_19c%\jdk\bin\java -jar autoupgrade.jar -config_values "sid=%ORACLE_SID%, source_home=E:\oracle\product\11.2.0.1, target_home=D:\oracle\product\12.2.0.1" -mode deploy

Unfortunately, the upgrade stuck at some point.

I found out that while upgrading, Oracle does something with the Unified Audit Trail. I don't know what the upgrade process does, but it takes very long. Maybe it happens only on Standard Edition, but I didn't test that.

As the customer would never use the audit trail, I simply decided to delete all entries to speed up the upgrade:

EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  USE_LAST_ARCH_TIMESTAMP => FALSE);

After that, upgrading to 12.2.0.1 was successful, and I was able to move on and upgrade to 19c (same command as above, just substitute the Oracle home paths).

Conclusion

  • You should always plan each and every upgrade.
  • You should always have a rollback scenario. In my case, as I moved from an older server to a newer one, I was able to simply stop the database on the old one and copy the files to the new one, so if the upgrade wouldn't be successful, I could just restart the databases on the old server. Another fallback scenario could be to create a snapshot of the server.
  • And you should always test your upgrade. Fortunately, most companies have test systems for that. An additional advantage of the upgrade test is, that you have an idea of how long the upgrade will take on the production environment.
  • And always use autoupgrade.jar for upgrades, as this is the only way Oracle will upgrade databases in future.
  • Try to avoid double tap upgrades [Mike explaines why](https://mikedietrichde.com/2024/12/13/should-you-attempt-double-hop-oracle-database-upgrades/)
  • And always try to be on the latest supported major version and apply patches as soon as possible. Patching is not such a big deal (although it surely isn't as easy as installing RPMs or MISs), but not patching can bring much more pain when it comes to bugs or security issues.

Finally, follow Mike's and Daniel's blogs. They know everything about Oracle upgrades.