1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Selective incremental replication between two instances?

Discussion in 'Server Administration and Options' started by kmicic, Aug 4, 2009.

  1. kmicic

    kmicic Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have one 10g database in other country.
    I want part of their db (selected tables or tablespaces) and import that data to my 10g DB and i want keep to date this data.

    I know two ways
    1. Data Pump Imp/Emp via FTP
    , but i can't send only data that have changed (incremental), i must pumping whole selected part of database (i want only new data from their DB, but consistanse with my DB)

    2. RMAN etc. or other archivisation tool, i can do incremental achivisation, but can i send files to another instances (my db) and load only that data? Can i do that with SQL*Loader?

    Sorry for my english.
    Cheers, thanks for response!
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Hmmm what do you have in mind? Are you willing to do a multi-master replication or your concern is only with a few select tables?

    If it's the latter, then see if you can suffice your requirement with Materialized views.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    What you want, I think, is Streams, which can replicate schemas or selected tables between two databases.
     
  4. kmicic

    kmicic Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Yes, i think about it but i don't know that it is good solution for my problem.
    I try say simple.

    Use Case:

    One 10g DB in location A (i name it hear DB_A), second it's new 10g DB in location B (DB_B).

    I want:

    1. Choose several tables from DB_A. (i.e. Salary, Names)
    2. I don't want whole DB_A (this is big DB)
    3. I want this information (i.e. tables: Salary, Names) AT LEAST ones a week.

    And hear is my issue.
    I can use "Data Pump Export technology" and export a dump file from DB_A (location A) via FTP to server (location B) with DB_B and use IMPDP (Data Pump Import Technology).
    In this way i can send only for example tables Salary and Names. I can choose any table what i want from DB_A (or tablespace, schema, etc..)

    BUT...

    I don't want transfer for example hole table DB_A.Names to DB_B.Names!!!
    If DB_B.Names already have this same names that in table DB_A.Names,
    i ONLY want transfer this new names from DB_A.Names, which are not yet in DB_B.Names!

    Of course first time at the beginnig i must transfer all records from choosen tables from DB_A to my DB_B, because it (DB_B) would be empty initially.

    I don't know which Oracle technology can do this for me.
    Maybe a must use several technology.
    I thinking about SQL*Loader, Data Intagrator, Data Pump Exp/Imp, External Tables, etc.

    But i search dedicated solutions.
    Maybe someone do this before and can help.

    Cheers i now read about Streams, thanks Zargon for suggestions.
    PS
    Sameer in Multi-Master Replication i think a can't choose only part (i.e. few tables) of DB to replicate.
     
  5. apunhiran

    apunhiran Active Member

    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    65
    Hello,
    There are several ways to transfer data between databases:
    1.) Using Oracle materialized views.
    You can create materialized view on the destination server and refresh data as and when you want using dbms scheduler jobs. If you want to be able to update data in both the database and keep them in sync you can use updatable mviews for that purpose.
    2.) Using oracle stream replication, its a little tough to implement, but its a good option when you want to update data in both the database and keep them in sync.
    3.) Multimaster replication easy to setup and will keep the data up to date all the time.
    In case there is no direct connectivity between the databases, then you can look at the option of loading the data as external table and then joining the tables to update insert or use merge to update the changed data. Let me know if you have any more questions.
    Regards
    Apun Hiran
     
  6. kmicic

    kmicic Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Important apsects are:

    1. Only one-directional transfer of business data (only from main DB_A to DB_B)
    (DB_A is a source of information for DB_B database, not vice versa)
    2. Pick up only part of tables from main DB_A (not all tables are necessary in DB_B)
    3. In the event of data changes in the main db (DB_A), data in the DB_B should be updated, but they do not have to be taken immediately, can be done once a week.
    If its possible we dont want have continuous connection with DB_A server.


    Apun Hiran, thanks for suggestions.

    I read extensively about the technology suggested to me and slowly outlines the vision of good solution according to my opinion. But i want just to be sure, perhaps someone already solves a similar problem and has some simple and effective solution.
     
  7. apunhiran

    apunhiran Active Member

    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    65
    Hello,
    I am actually working on a similar project right now.
    I am using MVIEWS 1 way to transfer data from DB_A to DB_B. WIth MVIEW not all tables need to be refreshed or transferred. You can create MVIEW for only the tables you want to sync between the 2 database.
    You can define schedule for data sync, once a week, once a day, depending on your requirement. I think stream and multi-master replication will not work for you.

    How do you plan to get changed data from DB_A every week. If you can get data as flat files (CSV type), you can open them as external table in DB_B and then merge data with the tables. That way you dont need direct connection between the 2 database servers. You can just ftp the file to the server, open them as external table and use MERGE command to sync the data.
    Hope this helps!!
    Regards
    Apun