Migrating and Backing up Schemas (complex database structures)

Question

Hey guys, I need to figure out a way to back up and also migrate our Oracle database from our production schema to the dev schema and the other way around.

We have bunch of config tables that drive how systems on our platform run, and when setting up new systems or doing maintenance, we need to update our config tables. We want to be able to work on the dev schemas and after setting up a system/feature, we want to be able to migrate all those configs to the dev schemas.

I thought of running a procedure where we give the ID of the system (from the main table) and i would go through all the tables and select nvl(..) and if it doesn't exist, i would insert into, and if it does exist then i just run an update on that row.

This code will get very messy and complicated especially since the whole config schema is very complex and it might be hard to handle all the keys properly.

Another option i was looking at was triggers, so when setting up a new system, there would be a log of all the statements we ran while setting up/editing a system, then we would run it on our production schema.

I'm on a coop term, and have only been working with databases for 6 months, so i don't know that much and any information/advice would be greatly appericiated.

(We use pl/sql)

Answer 1

What about using export / import (or datapump) to bring over the config tables?

Answer 2

Check out data comparison tools like this Think TOAD has one built in. I'm sure there are others out there too.

Answer 3

It is common to have tables in a schema that are what we call "static data", i.e. the users don't change it because it controls how the application works.

Each change to config data should not be run ad-hoc in the target environment. Instead, you design and code your DML carefully in one or more scripts, which get tested in a dev environment, checked into change control, and can be re-run in any environment when required.