Move data between two oracle database servers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Move data between two oracle database servers

  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Move data between two oracle database servers

    Hi All. I am a newbie DBA. i need help/guidlines in achieving a particular task in my place of work.

    I have two Oracle 10g Database servers. the production server( server A) runs on AIX 5.3 while the other (server B)runs on windows server 2003.
    Server A is online realtime transactional database which i want the data in a particular Table be moved to a table with similar structure on Server B(not online realtime transactional) leaving just a month old data on server A. that is, a check is made on server A every month to move data older than one month to server B and leaving data not older than a month in the table. i guess this involves data movement and deletion.

    The idea that came to my mind is to use STREAMING(replication) but to my knowlgde the server B will be updated each time there is a change in the Table in Server A. and also the deletion part is also a problem for me. will replication solve this task?

    i will appreciate any help and tips i can get in completing this task.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    how automatic do you want this to be?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    It looks more like a monthly archive and purge process to me.

    1- Archive part: Move OLD data from server A to server B
    2- Purge part: Purge OLD data in server A

    Would you consider partitioning affected table in server A?

    Partitioning will allow you to just switch OLD partition to a table in server A then transfer that table to server B and do there whatever you have to do.

    Please note that switching partition will take care in a single step of both archiving and purging. No delete, no reorg needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by davey23uk View Post
    how automatic do you want this to be?
    This should run automatically like a job at the end of evry month. Data older than a month will move to server B(archive) and data less than a month remains on the production server.

  5. #5
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by PAVB View Post
    It looks more like a monthly archive and purge process to me.

    1- Archive part: Move OLD data from server A to server B
    2- Purge part: Purge OLD data in server A

    Would you consider partitioning affected table in server A?

    Partitioning will allow you to just switch OLD partition to a table in server A then transfer that table to server B and do there whatever you have to do.

    Please note that switching partition will take care in a single step of both archiving and purging. No delete, no reorg needed.
    Thanks PAVB. You are correct with the first part of your reply on the archiving. thats exactly my objective.

    meanwhile on the second part about partition table, the table involve here is not a partioned table. My understanding of your comment here is that if i decide to partition the table,it will serve the purpose of archiving data older than a month into server B and then purge them from server A. my question now is will there be no negative impact on the table if i decide to partition it for this purpose? this is because the table is realtime online to card payment transactions. please pardon my limited knowledge on this issue.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by sesal View Post
    Thanks PAVB. You are correct with the first part of your reply on the archiving. thats exactly my objective.

    meanwhile on the second part about partition table, the table involve here is not a partioned table. My understanding of your comment here is that if i decide to partition the table,it will serve the purpose of archiving data older than a month into server B and then purge them from server A. my question now is will there be no negative impact on the table if i decide to partition it for this purpose? this is because the table is realtime online to card payment transactions. please pardon my limited knowledge on this issue.
    No worries.

    As always happens... you have to test it.

    A normal table is nothing but an Oracle segment.

    A partitioned table is nothing but a collection of Oracle segments - one per partition - that show up as a single logical unit.

    For a second lets imagine table is partitioned by range for some date - the same date you are planning to use for archive/purge purposes - in the YYYYMM format.

    New rows will go to the newest partition while old rows will be in previous month partition.

    Now imagine today is Aug 1st and you want to archive/purge June leaving July alive.

    The process will be to switch June partition against a "staging" empty table of the same structure... kaboom! in a single and instantaneously operation you have June data transfered to the "staging" table while the June partition is now empty.

    Now you can do whatever you want with the "staging" table, exp/imp into server B, create a dblink in server B pointing to server A and reading accross it, whatever you want.

    In normal conditions you shouldn't see any performance degradation, moreover since you have to run no deletes on server A table the whole process is less taxing and it wouldn't require any table reorganization to take care of fragmentation.

    Just my two cents.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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