Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: db2 backup and restore not working

    I'm using DB2 v9.1 LUW. I created a simple database and table and inserted some rows. I'm testing the backups and restores. Then did a db2 backup (both online and offline). Then I deleted the rows from the table. Then did a restore and rollforward. Well the table shows 0 rows so it didn't restore the table data. I don't understand why. Could anyone help me with what I'm doing wrong? I thought a backup would restore the data in the database.

    Here is the online backup of what I did:

    dbtest$ db2 "select * from db2inst1.test"

    ID TIMESTAMP
    ----------- --------------------------
    1 2011-02-02-16.52.08.582552
    2 2011-02-02-16.53.27.752275

    2 record(s) selected.


    dbtest$ db2 backup db test online to /db2_backup/dbtest/db
    Backup successful. The timestamp for this backup image is : 20110202165610

    dbtest$ db2 "delete from db2inst1.test"
    DB20000I The SQL command completed successfully.

    dbtest$ db2 "select * from db2inst1.test"

    ID TIMESTAMP
    ----------- --------------------------

    0 record(s) selected.


    dbtest$ db2 restore db test from . taken at 20110202165610 replace existing

    SQL2539W Warning! Restoring to an existing database that is the same as the
    backup image database. The database files will be deleted.
    DB20000I The RESTORE DATABASE command completed successfully.
    dbtest$ db2 rollforward db test to end of logs and complete

    Rollforward Status

    Input database alias = test
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = not pending
    Next log file to be read =
    Log files processed = S0000003.LOG - S0000004.LOG
    Last committed transaction = 2011-02-02-22.57.19.000000 UTC

    DB20000I The ROLLFORWARD command completed successfully.
    dbtest$ db2 connect to test

    Database Connection Information

    Database server = DB2/AIX64 9.1.4
    SQL authorization ID = DB2INST1
    Local database alias = TEST

    dbtest$ db2 "select * from db2inst1.test"

    ID TIMESTAMP
    ----------- --------------------------

    0 record(s) selected.



    Then I thought I had to restore the logs and that did not work, I got an SQL2581N error.



    dbtest$ db2 restore db test logs from . taken at 20110202165610 logtarget /db2_data/test/logs/NODE0000
    SQL2581N Restore is unable to extract log files from the backup image to the
    specified path. Reason code "1".
    dbtest$ db2 rollforward db test to end of logs and complete

    Rollforward Status

    Input database alias = test
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = not pending
    Next log file to be read =
    Log files processed = S0000003.LOG - S0000004.LOG
    Last committed transaction = 2011-02-02-22.57.19.000000 UTC

    DB20000I The ROLLFORWARD command completed successfully.
    dbtest$ db2 connect to test

    Database Connection Information

    Database server = DB2/AIX64 9.1.4
    SQL authorization ID = DB2INST1
    Local database alias = TEST

    dbtest$ db2 "select * from db2inst1.test"

    ID TIMESTAMP
    ----------- --------------------------

    0 record(s) selected.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Guess what the ROLLFORWARD .... TO END OF LOGS command does. Consider rolling forward to a point in time, keeping in mind that ROLLFORWARD expects the timestamp value in UTC.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you rollforward to end of logs, that will include any activity captured in the logs that happened after the backup finished.

    You can rollforward to a specific point in time to avoid this, or if you restore an offline backup you can restore without rolling forward.

    In DB2 9.7, you can restore to end of backup (not sure if this feature is available in DB2 9.1).

    To obtain the minimum rollforward time (for restoring online backups), you can rollforward to a time several years prior, and DB2 will give you an error message telling you the minimum rollforward time (you can resubmit the rollforward again with the corrected time). Check the Command Reference manual for the exact syntax and date format on rollforward.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Another option is to specify the INCLUDE LOGS option when performing an online backup and then extract them from the image and use during rollforward (specify OVERFLOW LOG PATH and NORETRIEVE options)

  5. #5
    Join Date
    Feb 2007
    Posts
    84
    Okay I got the rollforward with the timestamp value to work, so thank you for that information.

    I'm trying to get the option with rollfoward (specify OVERFLOW LOG PATH and NORETRIEVE options) to work. I'm trying different things as tests and documenting.
    I did this:

    dbtest$ db2 backup db test online to /db2_backup/dbtest/db include logs

    Backup successful. The timestamp for this backup image is : 20110203120755

    dbtest$ db2 restore db test from . taken at 20110203120755 replace existing
    SQL2539W Warning! Restoring to an existing database that is the same as the
    backup image database. The database files will be deleted.
    DB20000I The RESTORE DATABASE command completed successfully.


    Then I removed the logs:
    dbtest$ cd /db2_data/test/logs/NODE0000
    dbtest$ ls
    S0000000.LOG S0000002.LOG S0000004.LOG S0000006.LOG S0000008.LOG S0000010.LOG S0000012.LOG S0000014.LOG
    S0000001.LOG S0000003.LOG S0000005.LOG S0000007.LOG S0000009.LOG S0000011.LOG S0000013.LOG SQLLPATH.TAG
    dbtest$ rm *.LOG


    Then I restored the logs from the backup:
    db2 restore db test logs from . taken at 20110203120755 logtarget /db2_data/test/logs/NODE
    DB20000I The RESTORE DATABASE command completed successfully.


    Now I'm trying to do a RF as you suggested but I might be doing it wrong too. I'm getting an error. Can you explain this and let me know what I'm going wrong?


    db2 rollforward db test to end of logs and stop overflow logs path /db2_data/test/logs/NODE0000 noretrieve

    Here is the error:
    SQL0104N An unexpected token "logs" was found following "OVERFLOW". Expected
    tokens may include: "LOG". SQLSTATE=42601


    I want different options on how to restore from the backup or at a specific point in time...

    Thank you so much for your help and please be patient with me, I'm not experienced with this...yet

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What I usually do is this:

    1) backup online, include logs
    2) restore db and logs at same time (just add the logtarget clause to the main restore)
    3) do rollforward and complete (it will usually fail because it needs to rollforward to a minimum time).
    4) rollforward to the minimum time given is step 3

    As long as the logs can be read from logtarget during the rollforward, it should work.

    Andy

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    rockycj, going by what is entered:

    db2 rollforward db test to end of logs and stop overflow logs path /db2_data/test/logs/NODE0000 noretrieve

    The Error message tells you exactly what it doesn't like.

    SQL0104N An unexpected token "logs" was found following "OVERFLOW". Expected
    tokens may include: "LOG". SQLSTATE=42601

    You have LOGS (plural) after OVERFLOW. The syntax requires LOG (singular).

    Follow ARWinner's suggestion and you shouldn't have any problem.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One more thing. When you restore log files to logtarget from the backup (if you don't already have the logs needed in the active or archive log paths), then make sure the logtarget path is empty before you start (which means that you should use a separate logtarget path other than active log or archive log path).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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