Re: Migrating 9i to 10g performance issues

From: Sandra Becker <sbecker6925_at_gmail.com>
Date: Tue, 25 Mar 2008 07:38:00 -0600
Message-ID: <3c5f7820803250638s2c16d270t91a8c61a83e3ba00@mail.gmail.com>


Thanks, I hadn't thought of a logical standby.

On Tue, Mar 25, 2008 at 7:20 AM, Yechiel Adar <adar666_at_inter.net.il> wrote:

> I think you should look into materialized views.
> Another option to check is a logical standby database.
>
> I did not worked with either so I do not know if they suitable but I am
> sure others on the list will kill these options if they do not fit your
> environment.
>
> Adar Yechiel
> Rechovot, Israel
>
>
>
> Sandra Becker wrote:
>
> Source DB: Dell server, RHEL4, 12G RAM, Oracle 64 bit 9.2.0.8 SE1, little
> endian
> Target DB: IBM VM on series z9, SLES10, 4G RAM, Oracle 10.2.0.3 EE, big
> endian
> Database is just shy of 1 terabyte in size--70 percent of data is in a
> single table; total of 212 tables.
> 12 very large tables, including the monster--most have no column, like a
> static date, that can be used to logically break the data into "partitions".
>
> I'm working on procedures to move our production database from the Dell to
> the IBM. My tests so far indicate that I'm going to need more than 24
> hours. Management, in their infinite wisdom, is insisting that it be done
> in less than 8. It will take as long as it takes, but I'm wondering what I
> can do to speed things up. So far I've done the following:
>
> 1) exp/imp - too slow overall
> 2) plsql that commits at regular intervals, depending on the size of the
> table - works very well for all tables under 1M; can load 111 tables in
> under 2 hours using 2 concurrent sessions. Works for larger tables, but
> obviously takes much longer. I had 2 sessions doing tables under 1M and 2
> doing tables between 1M and 100M concurrently. Didn't try for the 12 tables
> over 100M.
> 3) Direct-path insert - used on the table holding 70 percent of the
> data. Four months ago I insisted this table have a static date column
> added. I can logically break the data loads down by date--they want the
> most current data loaded first, the remainder can be done over a period of
> days. This is working reasonably well, but having done this same thing once
> before on this table, I know it will take about a month to get all the data
> moved based on the constraints I'm working under--can't be done during core
> business hours, etc.
> 4) I put the target database in noarchivelog mode for my testing. Is
> this a wise move for migrating production during the go live?
>
> Manage has suggested that I leave off old data and load it later. Doesn't
> work with 95 pecent of the tables because of their structure and foreign key
> constraints. They also suggested I use both the primary and the standby
> databases to read from. No way to test this until I go live--constraints
> again--although this actually was part of my plan from the beginning. Will
> too many concurrent sessions loading data slow things down too much? What
> would I look at to determine this? 10g is new to me so I'm not familiar
> with all the features yet and may be missing something significant.
>
> Any suggestions are appreciated, other than telling management what they
> can do with their time constraint. (Already did that.)
>
> Thanks.
>
> Sandy
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 25 2008 - 08:38:00 CDT

Original text of this message