Re: Re-organising a database?

tim.kearsley_at_milton-keynes.gov.uk (Tim Kearsley) wrote in message news:<725736ef.0304140119.6623ab37_at_posting.google.com>...
> Hi all,
>
> Environment is:
>
> Oracle 8.1.7 on AIX 4.3.3 on RS6000 SP hardware
>
> I've got a legacy database, about 30 Gbytes in size, which I would
> very much like to re-organise. Primarily I would like to implement
> LMTs and to put the datafiles into a structure which is fairly close
> to OFA.
>
> The obvious solution I suppose is to create the new database and
> export/import from the existing one. My problem is the time this
> appears to be taking. A trial took some two days by the time I had
> imported, re-created indices and solved a few problems with invalid
> packages. The database is pretty heavily used and it is going to be
> hard for me to sell a weekend's downtime to the users.
>
> Are there any other techniques I could use to reduce this downtime? I
> admit that I haven't tried tuning the export/import process. At
> present the export is done to a Unix pipe which compresses the dump
> file on the fly. Likewise the trial import was done by uncompressing
> the dump file on the fly. Is it likely to be substantially faster to
> export to multiple files rather than compressing (I am facing an OS
> filesize limit)?

It is entirely possible the compress/uncompress is your limiting factor, I've seen it on other vendors unix boxes of a similar size. Other tuning, particularly undo contention and redo log buffers may also impact things, not to mention rollbacks and archiving. Those things should all be examined, as I've seen such things make >50% cuts in elapsed time. Certainly I've seen some machines just crawl as the compress takes over, especially if the Oracle part of it would be processor bound anyways (as it is surprisingly often). However, I would look at the incremental moves the others suggested, as they may make more sense, depending on your actual user, sizing, integrity, performance and political issues.

>
> Any thoughts or ideas very welcome.
>
> Thank you.
>
> Tim Kearsley
> Database Manager
> Milton Keynes Council

jg

--
@home.com is bogus.
"This road work is expected to be completed in 2002." - Caltrans sign
next to I-5.