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

  • From: "Jeremiah Wilton" <jeremiah@xxxxxxxxxxx>
  • To: <adar666@xxxxxxxxxxxx>
  • Date: Tue, 5 Aug 2008 10:04:21 -0700

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


Other related posts: