Re: Migrating large database between platforms: using snapshots

Vsevolod Afanassiev 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

How about this instead:

1. Create a database by using RMAN to clone your existing database (no downtime).
2. Use DBMS_RECTIFIER_DIFF to identify and rectify any records changed during the cloning.

If you can afford downtime though ... I'd suggest transportable tablespaces as preferable to export/import.

-- 
Daniel Morgan
Puget Sound Oracle Users Group