same databases different versions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: same databases different versions

  1. #1
    Join Date
    Feb 2001
    Posts
    20

    Arrow 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
    keesara

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    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.
    or
    - 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:

    http://www.oracle-base.com/Articles/9i/UUID9i.asp

    That won't help you now but it may be useful in future.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    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.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    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
    UNION ALL
    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.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width