Re: Upgrade 9.2 to 11.2 with minimal downtime

From: Mark D Powell <>
Date: Thu, 2 Jun 2011 06:52:13 -0700 (PDT)
Message-ID: <>

On Jun 1, 5:39 pm, Chuck <> wrote:
> I need to upgrade a v 9.2 database on AIX to v 11.2 on RHEL5 with
> minimal down time and I'm looking for suggestions. The DB is also being
> physically relocated about 1000 miles away.
> The database has two application schemas, both of which use LOBS and the
> total bytes is about 60g.
> I've tried using exp/imp (datapump isn't an option because of v9 at the
> source) but it takes about 36 hours to import which is too long.
> My next thought is to use streams to keep the DBs in sync, and then just
> switch over to the new DB. I've never used streams though and am
> unfamiliar with it. Is this a viable option? How do I instantiate the
> 11.2 db? Can I use exp/imp and some how tell the v9 DB at what point to
> begin sending changes to the new DB? Will the streams queue be able to
> queue all of the messages that might be created during the 36+ hours it
> takes to copy the export file to the v11 db? Is there a way in v9 to
> tell it what SCN or time to begin queuing at?
> Materialized views is another option I've thought about but from what
> I've read, streams seems to be suited to this sort of thing.
I will agree that 36 hours to import 60G is too long. You need to determine what the bottleneck is. If the problem is your network transfer rate then that is going to be a problem no matter the

When migrating both physical servers and upgrading the database version my favorite method is export/import. You can use tables=exports ran in parallel into pipes so you can export, transfer (ftp), and import multiple concurrent streams from the existing database into the new database. A full=y rows=n export can be used to get the public synonyms, bring stored code, and bring any object missed by prior work.

We like to build the indexes and constraints manually via scripts rather than let the import bring them since we can perform these tasks faster than when performed via import.

The rman utility can be used to condition the data files but since you also need to upgrade versions I think exp/imp is cleaner.

HTH -- Mark D Powell -- Received on Thu Jun 02 2011 - 08:52:13 CDT

Original text of this message