Re: Cloning 21 GB Table - Use of Snapshot ? - Review Approach please

We had to migrate a 24/7 database to another platform and downtime had to be at an absolute minimum. Included in the database was a 7 G table. The whole database was about 80G.

We created the tables on the new platform and then created snapshots using the existing tables. We did full refreshes. Small tables could run in parallel but the largest tables had to be done serially. Once a table had been fully refreshed it was put on a 15 minute update cycle.

After the last table had been fully refreshed we stopped the production listener and, using the maintenance listener, did a fast refresh of all tables. We then dropped all the snapshots (leaving the underlying tables), repointed ONAMES and were in business with 15 minutes of downtime.

There was some impact from running the snapshots but it was minimal. The full refresh of the largest table took about 2 hours but your milage will vary depending on platform and network connections. It really doesn't matter because your only downtime is for the last, fast refresh.

I really like this method because you can do most of the work with no time constraints. You don't have to try to do it all over the weekend between 10pm Suturday and 2am Monday.

--
http://www.freelists.org/webpage/oracle-l