RE: Database Migration and Upgrade across Servers ( from Solaris to HP)

From: Jeremiah Wilton <>
Date: Tue, 5 Aug 2008 10:04:21 -0700
Message-ID: <023a01c8f71d$4e57ac60$eb070520$@net>

Yechiel Adar wrote:
> I second Allen suggestion and just want to add the whenever possible I
> prefer to create a new database over upgrade in place.
> This way you have time to test your copy procedure and the application
> people can play with the new database without interfering with
> production.

I don't see the benefit. If you have a second set of equipment, you can just as easily test an upgrade in place on a copy of your database. The decision as to how to approach this is highly dependent upon database size, among other factors. If you are working with databases in the hundreds of G, you may discover that exporting the whole database and re-importing it takes far longer than is acceptable, given that the database can be just as safely upgraded in place in far less time. I continue to be puzzled by the seemingly widespread notion that it is generally a good idea to completely unload and reload your data just to achieve a software version upgrade. If you have other goals in mind, like reorganization or "defragmentation", tackle them independently.

In a previous thread on this topic, Brandon provided some good examples of when exp/imp might actually be a sound business decision:

  • The testing/validation cycle for any change event at your organization is prohibitively onerous, so ironically you opt to be *less* cautious and bundle several changes (upgrade and reorg or migration) into a single event.
  • The database is so tiny that it actually will take less time to complete a full export and import than to run catupgrd.sql.
  • The database has never been reloaded since 7.3 or 8.0 and you are now on 10g or 11g and encountering bugs related to legacy block format or something like that.

Other than these examples, "upgrade by exp/imp when possible" is another one of those insidious Oracle rules of thumb that should be stamped out. Only do things for a reason!

Jeremiah Wilton
ORA-600 Consulting

Received on Tue Aug 05 2008 - 12:04:21 CDT

Original text of this message