Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: Using Log files for database Restore

    Hi Everybody,

    I am working in a bank that has independent databases at 80+ branches.
    Every location takes an offline backup every day.

    At last date of every month, the branches dispatch me the months last day backup.

    I restore the databases (Version recovery) from the recieved backups and consolidate a few tables from each locations restored db in a central database using Import/Export for reporting purposes.

    My problem is that I have to unzip backup images of 80 branches and have to change the timestamps in batch files, that is too boring.

    My question is that if I switch to ARCHIVAL LOGGING at all the locations, would it be possible that I just use logs for updating changes to restored database at the consolidation server.

    That is I just restore the databases once on the consolidation server (version recovery) and after that just collect the log files, place them in the restored databases logs directory and restore to End of Logs.


    Pleas help me in this regard as its becoming very difficult for me to restore all the databases and month end.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by faisalb

    My question is that if I switch to ARCHIVAL LOGGING at all the locations, would it be possible that I just use logs for updating changes to restored database at the consolidation server.

    That is I just restore the databases once on the consolidation server (version recovery) and after that just collect the log files, place them in the restored databases logs directory and restore to End of Logs.

    Yes, but you can do it only once. After you have recovered a database by rolling it forward, you cannot do it again until you do a new RESTORE.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2006
    Posts
    471
    or leave the db in rollforward state for next run - but db is not accessible when in rollforward state
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  4. #4
    Join Date
    Mar 2008
    Posts
    7
    Quote Originally Posted by n_i
    Yes, but you can do it only once. After you have recovered a database by rolling it forward, you cannot do it again until you do a new RESTORE.

    That means that I have to do a version recovery every month end by using the recieved timestamps,
    and the advantage of archival logging is just point in time recovery.

    The issue is that every month, I have to restore a lot of db2 databases on my head office consolidation server. I use the month end backup timestamps for that by unzipping the timestamps and making changes to batch files.

    Isn't there any better solution instead of the method I am using.




    Thnaks

  5. #5
    Join Date
    Mar 2008
    Posts
    7
    Quote Originally Posted by guyprzytula
    or leave the db in rollforward state for next run - but db is not accessible when in rollforward state

    I have to use the databases for month end consolidation (there is a program that exports latest data from some tables and then loads them in to a central database that I use for reporting).

    How can I leave it in rollforward state

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you can identify the "latest data" in the tables, you can use a simple EXPORT at the sources and then LOAD at the server.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Mar 2008
    Posts
    7
    Quote Originally Posted by stolze
    If you can identify the "latest data" in the tables, you can use a simple EXPORT at the sources and then LOAD at the server.


    Yes I can identify the transactions date in some tables but in a number of tables same rows are update, like for customer balances table there is only one row for each customer and new balance is updated in the same row.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    For those update scenarios, you could add a "last updated" column to your table. DB2 LUW V9.5 has a clause "FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP" for this.

    Another idea would be to use the Capture part of Replication and just extract the data changes and apply them at your target system. In fact, maybe you can even use a regular replication setup...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Mar 2008
    Posts
    7
    Adding a "last update" columns would be a major change to the databases structure for which I dont want to go and we are using version 8.2
    I will look in to this
    clause "FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP".

    Our future plan is to go for db2 replication as each server will be allotted a Live IP address and we will switch to online replication using capture and apply.

    Meanwhile I will do what I am doing, or I will ask the branches to send the export files of tables and make a program at the consolidation server or a batch file that will use the export files and load data in to central db.

    Thanks a lot stolze for your time and help.

    Any ones else comments are appreciated

Posting Permissions

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