Re: Upgrade 9.2 to 11.2 with minimal downtime
Date: Thu, 2 Jun 2011 11:34:19 -0700 (PDT)
Message-ID: <6f646ed5-9eac-4502-9c4e-31c91ecdc129_at_k15g2000pri.googlegroups.com>
On Jun 2, 7:42 am, Chuck <chuckh1958_nos..._at_gmail.com> wrote:
> On 6/1/2011 8:40 PM, joel garry 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?
>
> 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.
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
-- _at_home.com is bogus. http://www.itpro.co.uk/633907/oracle-ditches-open-officeReceived on Thu Jun 02 2011 - 13:34:19 CDT