Re: Migrating large database between platforms: using snapshots

"Vsevolod Afanassiev" <vafanassiev_at_yahoo.com> wrote
> Hi,
> I need to migrate 500 GB Oracle 9.2.database from one platform (Sun
> SPARC Solaris) to another (Sun x86 Solaris) with minimal outage. The plan
> is:
> 1. Create empty database on the new server.
> 2. Shutdown application.
> 3. Run Export on the old server
> 4. Run Import on the new server
> 5. Start application and connect to the new database.
> It works fine, just takes too long, even with direct export, importing
> multiple schemas in parallel, etc.
> Would it be possible to use fast refresheable snapshots (Materialized
> Views) to pre-create and
> pre-populate several largest tables? From DBA_TABLES and DBA_SEGMENTS a
> materialized view is just another table. So I'll create materialized
> view logs in the old database to enable fast refresh, then create
> materialized views in the new database, populate them and make sure that
> Fast refresh is working. All this before shutting down the application.
> Then shut down the
> application and do last fast refresh.
> Is there any drawback in using a table which is MV?
> Another option would be to create tables in the new database by doing
> SELECT * from MV.
> Sev

hello Vsevolod,

One more option: a very fast copy method is CTAS (create table as Select) , and you can do that over
a db_link to the old database. Easy and fool-proof. Then create the indexes using NOLOGGING option and perhaps parallel.

cheers, Roelof Schierbeek Received on Sun Oct 01 2006 - 03:23:56 CDT