@chuckh1958_nospam: "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"
    @chuckh1958_nospam: "<quote>
The import was running with commit=y. I removed that and the import went
from 36 hours to 7.5 hours which may be within my client's tolerance for
down time. I'm thinking maybe to set indexes=n as well and build the
indexes afterwards in parallel."
    @chuckh1958_nospam: "<quote>
I'm using all LMT's on the destination database. I haven't used DMT's
since LMTs became available in v8 I think. I was also using buffer=4m
which is perhaps too small. I'll try 20m instead. This was my import line...
imp / fromuser=x touser=x recordlength=65535 buffer=4000000
log=import_adbase.log"
    @chuckh1958_nospam: "One more minor factor I forgot to mention :) ....
I am also bunzipping the export file on the fly when importing it...
bunzip2 < expdat.dmp.bz2 > mypipe &
imp file=mypipe ...
Don't ask. That's the way it's being delivered."
    @chuckh1958_nospam: "<quote>
LOL. No. It's just a practice I've used for years but I never had to
deal with LOBs before.  I never saw much improvement with buffers over
4m. Maybe now I finally will. We shall see."
    @chuckh1958_nospam: "<quote>
Actually I didn't know that impdp would work with an export file. Thanks
for the tip.
Even though I've been working with 10g and up for 5 years I almost never
use datapump because it doesn't allow you to compress on the fly. The
11g version has a compression=all option but it requires another license
to use it and my company has not paid for that license, so I continue to
use exp/imp until they either stop distributing it or I encounter a DB
with a feature that exp doesn't support.
I'll give impdp a try with the export file."
    @chuckh1958_nospam: "<quote>
Every time I try to load the 9i dump file with impdp I get
ORA-39143: dump file "/oramisc/export/TPMACPRD/test.dmp" may be an
original export dump file
and it fails"
{'source': 'USENET', 'other_information': '{"reply_count": 6, "forum_name": "comp.databases.oracle.server.20140604.mbox"}', 'file': 'data/output/usenet-comp.databases.parquet'}