How to do migration of a database using Shareplex and Oracle Export/Import with a minimum of down (43873)

At times one may want to migrate the database from one operating system to another using Shareplex as a tool with a minimum of downtime on the source database. It is possible to use Shareplex in conjunction with Oracle Export/Import to accomplish this subject to certain assumptions as detailed in the Resolution section. 

One would like to use Shareplex for migrating the database from one OS platform to another.

The steps involving the procedure are outlined as below:

1. Create the database instance on target which does not have to be in sync with source (say it is created from the Export of the source taken at a point in time). It can also be created as a database with empty target tables (when performing Export with rows=n)
2. Create a Shareplex instance on target.
3. Activate a config from source to target. The target will not be in sync with source at this time due to the way the Export/Import was done but nonetheless one would be able to test if the data does replicate across.
4. Now one can do the migration of the database that would effectively involve synchronizing the target database with the help of source using Oracle Export/Import with a minimum of downtime as follows:

If you do not want to incur any downtime on source database when doing target resync, the preferred approach is to use Oracle hotbackup in conjunction with reconcile command of Shareplex. In our situation this is not feasible as source and target have dissimilar OS. So the option that would work for us is Oracle Export/Import. If you are willing to incur a very brief downtime, Oracle export/import can be used conveniently to resync target database while users can keep accessing source database. You will need to provide a very brief downtime lasting a few moments and if that is not at all possible, you will run into out of sync for those tables which had open transactions prior to starting Export with CONSISTENT option. You can run Oracle Export with the CONSISTENT flag set to 'Y' as follows:

1. Briefly restrict access to the source database.

2. Flush from the Source to the target (i.e. on source at sp_ctrl> flush o.sourcesid)

3. Begin an Export of the Source database with CONSISTENT=Y

4. Allow user access to source database.

5. After the Export is finished on the Source, and after Post stops due to flush on the Target, Import the data into the Target server.

6. Compare the Source tables to the Target tables to make sure both are the same structure (no DDL changes have occurred).

7. Disable any triggers or constraints (except PK or U type) on target database.

8. Run cleanup.sql on target database to clean up the Shareplex internal tables that were inherited from source database and can interfere with Post process as well as future activation emanating from secondary to primary.

9. Start post on target.

So the only downtime is up to the step #4 in the above. Another thing that you may have to watch for is the rollback segment issues since you are using the CONSISTENT flag in Export since the two factors that can strain the rollback segments is the size of the database being exported and the quantum of changes taking place while the Export is in progress. Export can be a little slow if run with CONSISTENT=Y.