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

Chuck:

# 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.

Where are you spending your time in this 36 hour time frame when the
import is running?

I can import 60 gig in under an hour on a relatively modest generic
IBM x server with EMC clariion storage.

Sounds like something is drastically wrong somewhere in your setup but
start with the wait interface and see what is going on during your
horribly long running import.

You should consider standby or GoldenGate.

Standby doesn't require spending any money. You copy your prod DB to
the new site, open it as a standby, copy archived redo from prod and
apply it. When the standby has caught up with prod and you're ready to
cut over, you shut down prod and open the standby. The outage for the
cutover can be extremely short; how short depends on how fast you can
copy archived redo and apply it. You'll need to spend some time with
the manuals to work out how to do it, though there are step-by-step
tutorials on the web that will get you started.

GoldenGate does much the same thing, and I believe that it requires
far less effort to get working. The downside is that (a) you need to
pay for it, and (b) it doesn't copy certain things, which means that
your copy DB might not be exactly the same as your prod. For that
reason, you'll need to figure out whether you can use it. Again, time
must be spent with the manuals.


-- Phil

On Jun 1, 5:39*pm, Chuck <chuckh1958_nos... (AT) gmail (DOT) com> wrote:
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.

On 2 jun, 00:52, "phil_herr... (AT) yahoo (DOT) com.au"
<phil_herr... (AT) yahoo (DOT) com.au> wrote:
I don't think standby is an option here; keep in mind that the OP is
not only upgrading but also changing platform.
And, like you said, GoldenGate doesn't come free.
I agree with John that OP should try to figure out why exp/imp takes
that long.

--
Jeroen

On Jun 1, 2:39*pm, Chuck <chuckh1958_nos... (AT) gmail (DOT) com> wrote:
Like the others said, that sounds too slow. Have you tried exporting
into a pipe and compressing, then going over the network? Also,
creating tables as select can sometimes do surprisingly good, but it
also is very sensitive to pipe size.

It could very well be lobs too, you might consider doing those
separately from everything else. I take it they are not inline?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...-entertainmen/

On Jun 1, 5:39*pm, Chuck <chuckh1958_nos... (AT) gmail (DOT) com> wrote:
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 --

On 6/1/2011 8:40 PM, joel garry wrote:
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.

Chuck:

# 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.

That still sounds like way too long for importing 60 gig ... what
shows up in wait events during the time frame the 7.5 hour import is
running?

# I'm thinking maybe to set indexes=n as well and build the indexes
afterwards in parallel.

If you are using the old import you have to do the indexfile thing
etc ...

Some other possible things are to check LOG_BUFFER and DB_CACHE_SIZE
and think about turning off archivelog mode during the import
process. Have you set shared pool and cache things to a reasonable
large minimum size if you are letting oracle do any of the automatic
memory messing up?

On Jun 2, 7:42*am, Chuck <chuckh1958_nos... (AT) gmail (DOT) com> wrote:
When I was on older hardware that was the kind of performance I'd
see. Are you using a large imp buffer (such as buffer=20000000)?
statistics=recalculate? Sometimes it makes sense to do the import
noarchivelog, switch to archivelog and make your first "real" backup,
since most of your disk writing is probably redo/archiving and you can
backup online. Statistics may need to be redone anyways, many of us
have seen that oracle gets it mostly right by default, except for the
histogram default, and there always seems to be a few sql that just
work different across versions. Depending on how much time and effort
you are putting into user acceptance, you may want to correct the
statistics as a separate project and import and lock those.

Sounds like most of your gain was the commit after every row that
commit=y does with lobs. Using a large buffer would help on the
tables without lobs.

Another thought is the initial setting of each table. I've found it
helps to precreate tables with proper sizing before imp (at least the
biggest/most volatile ones if you have thousands), to avoid over or
under allocating space. You can overallocate with compress=y, and
spend a lot of time dealing with extent handling with a small initial,
though less with LMT than DMT. lobs may have their own
idiosyncracies.

direct=y recordlength=65535 exports can make a difference on that end,
if you don't hit bugs or misfeatures.

jg
--
@home.com is bogus.
http://www.itpro.co.uk/633907/oracle...es-open-office