I have a problem: I have two production schemas on one database each serving two different appplicaitons.

I need to migrate (move) one schema to an entirely new database that will be dedicated for that application with zero downtime. SO EXPORT/IMPORT can not be used.

I have license to even active data guard. But if I setup data guard from current database DB to new DB, should I switch over OR failover or what else so that the current primary will continue to be primary in its role and only the new DB will change its role from standby to fully operational primary. Then we simply direct the application to new and start the new db. Thanks for your help.

  • When you say "zero downtime", do you actually mean zero or "very short/minimal"?
    – Mat
    Jun 14, 2013 at 4:40
  • it'll be a failover since you won't be touching the primary, only to disable the archive transfer. but 0 downtime + consistency won't be possible that way. You need to be looking at streams or even GoldenGate. Best would be if some downtime was possible, maybe at night? Active dataguard is only useful for read operations.
    – Munchi
    Jun 14, 2013 at 12:48
  • You can achieve minimal downtime by using oracle goldengate software.
    – user39238
    May 29, 2014 at 23:45

1 Answer 1


This is by no means the only way or necessarily the best way. You should consider streams and GoldenGate as well, but if at least one application is designed to use packages and access the data only through those packages, consider the following:

  1. Create a new database and setup views of the original database's tables for one schema using a database link.
  2. Copy the package code for the application using that schema to the new database.
  3. Switch that application to use the new database. It should work exactly the same, except that it will be slower due to the link.
  4. For each table or group of related tables, add package code that for each database call that maintains data in both the local and remote tables. There are many ways you could do this. One way would be to have a switch table with a row for each application table. You could make a replication routine for the table and when the tables are in sync it could update the switch table to let the code know that it doesn't need to maintain both tables.

Yes, this is complex, and yes it would take a lot of time and testing, but given a requirement of zero down time, it should be expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.