Re: Upgrade 9.2 to 11.2 with minimal downtime

From: onedbguru <onedbguru_at_yahoo.com>
Date: Wed, 1 Jun 2011 16:21:33 -0700 (PDT)
Message-ID: <13eb43e7-94ee-48d7-a3fb-9c8900f0cd9b_at_gh5g2000vbb.googlegroups.com>



On Jun 1, 5:39 pm, Chuck <chuckh1958_nos..._at_gmail.com> 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.
>
> TIA
are you exporting/importing FULL or just the necessary schemas? 36hrs is WAY too long. I have used RHEL5 with 11gR2 and imported 100G+ in an < hour. You have a problem that you should address before going any further. One thing to consider when using streams/DataGuard is the network latency and bandwidth.

You could always try using impdp using a network link. I have not tried it against a 9i database, but certainly cannot hurt to try:

On New 11gR2 database: (to avoid plagiarism allegations, this was copied (and modified) from http://www.orafaq.com/wiki/Data_Pump -- I really hate reinventing the wheel - and all of that other typing.)

Use DBCA to create an empty database, then:

$sqlplus / as sysdba

SQL> grant DATAPUMP_EXP_FULL_DATABASE to system; SQL> grant DATAPUMP_IMP_FULL_DATABASE to system;

SQL> create directory EXPORT_DIR as '/some/real/path';

SQL> create database link old_scott connect to scott identified by tiger using 'old9idb';

$ impdp system/password DIRECTORY=EXPORT_DIR NETWORK_LINK=old_scott schemas=scott;

All work is performed on the target system. The only reference to the source system is via the database link.

And!! No need to create the scott username before starting this. Received on Wed Jun 01 2011 - 18:21:33 CDT

Original text of this message