same databases different versions
Hiii, we have 30 databases have same data struture but different versions and different locations.
Now we are accessing via network,now my company would like synchronize all databases and access from one location.
How we do this synchronize all databases?
Is there any tools?
plss help me
This may not be simple depending on how you are using them. The major problem with synchronization is when you have conflicting keys. For example, if you are using a sequence for a primary key and all systems started at 1 and incremented, you would have lots of conflicts. The solutions are:
- Add a site column and add this to any primary or unique keys.
- Trawl through the data correcting any conflicts and cascading those changes through all dependancies.
The first one will result in redevelopment of your apps. The sencond will involve some serious data conversion.
In a similar vein, people at different site may enter the same codes as primary keys...
I've had to do data migrations like this a few times and in all cases it took alot of work and pain. I don't know of any tools that can automate it for you.
If you planned this during the development stage and already have a site indicator, or you used banding zones in your sequences it should be as easy as exporting each database and importing into the central one. Obviously reference data will give primary key violations but the "real" data will be fine.
Because of this sort of thing I now try to use UUIDs instead of sequences where possible:
That won't help you now but it may be useful in future.
I don't know but the simplest thing that comes to my mind is have multiple schemas i.e each site have in their own schema and just exp/imp data to respective schemas. For reporting purpose you can create one user and have synonyms based on site_tablename or something like that. This way you have one Instance accessed from multiple location if that can work out in your case.
You will still get the problems of conflicting primary keys when doing joins through these views unless you include the schema indicator in each view like:
CREATE OR REPLACE VIEW tab1 AS
SELECT 'SCHEMA1' AS schema, a.* FROM schema1.tab1 a
SELECT 'SCHEMA2' AS schema, b.* FROM schema2.tab1 b
Looks alot like a federated database (loved by Microsoft & IBM, hated by Oracle)
Of course, this will still affect your app.
Click Here to Expand Forum to Full Width