Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Posts
    15

    Question Unanswered: How to refresh a 24x7 production database without inconsistency issues?

    Hi,

    There is a production database running 24x7 and being access by end users. The database needs to be refreshed with latest information 4 times in 24 hours. What is the best way to do this, without

    1. having any performance hit
    2. the end user being aware and affected by the database refresh. There should be no data inconsistency for the end user.

    Can the experts out there please give your valuable advice in how to go about handling this issue. The database being used is Oracle 10g on Windows.

    Thanks in advance for the help
    Regards
    Anuja

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Define data refresh..... Are you replacing all information in the tables or simply inserting new information for the day?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2005
    Posts
    15
    Data refresh is not just adding new information. its complete update. there could be records which got updated. there could be some which got deleted and the rest remains the same

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What kind of volumes of data are you talking about?

    If its not too big you could do it in a single transactionand thus avoid data inconsistency issues.

    Alan

  5. #5
    Join Date
    Jun 2005
    Posts
    15
    Volume is huge. Millions of records.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Millions of rows is all relative. If it is 5 million, no big deal. If it is 999 million, it will take a while. But the bottom line is do all your inserts, updates, and deletes in one transaction. In other words, only one commit. This will insure that everyone sees the old data until the commit is done and then they will see the new data with nothing in between.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2005
    Posts
    15
    The data volume is around 900GB per update. Nearly 100 tables are affected during this update. Also the update happens in a WAN. So there is network overhead also.

    Another issue is that if this is set as a single transaction, it affects the performance of the system
    Last edited by anujajijeesh; 05-15-07 at 01:39.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What type of company is performing 3.6 terabytes of updates a day and doesn't have a large staff of trained dedicated DBA's.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    900GB is a lot, getting 4 refreshes done in 24 hours is going to be quite difficult. And without performance hit is going to be impossible on a single database server. Also getting 4 x 900GB in 24 hours over a WAN is going to be quite difficult in the first place.

    Alan

  10. #10
    Join Date
    Jun 2005
    Posts
    15
    Will it be of any help if the data is put into partitioned database? Can this help improve the refresh time and performace

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Will it be of any help if the data is put into partitioned database?
    How does have partitions change the amount of data that needs to be transfered or the elapsed time to transfer it?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2005
    Posts
    15
    rephrasing the question.
    If the database is partitioned to be on different systems, can the data refresh be more effective? (If the database is on 2 systems with 2 processors doing the job, won't parallellism be built into it, improving the performance)

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Using two servers would probably solve your problem if they are fast enough but then you have the problem of the application switching between databases, doing this seemlessly is very difficult.

    I suspect the best course is for the data provider to send just the changes over rather than everything, this I suspect would drastically reduce data volumes, enable you to use transactions and elimanate the need for two very power servers.

    Alan

Posting Permissions

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