Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
When you say "zero downtime", do you actually mean zero or "very short/minimal"? – Mat Jun 14 '13 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 '13 at 12:48
You can achieve minimal downtime by using oracle goldengate software. – user39238 May 29 '14 at 23:45

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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