Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: Rollforward question

    I just restored a tablespace from a backup and tried to roll the tablespace forward. When I entered the rollforward command and specified only the tablespace that was recovered name in the tablespace () part of the comamnd I got the message:

    SQL4906N The list of table space names specified is an incomplete set for the rollforward operation.

    After this I entered the other tablespace that I knew had indexes to tables in the tablespace I had specified and got the same message. How can you tell which tablespace names should be included in the rollforward command ?

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    $ db2 "select tabname,tbspaceid,tbspace,index_tbspace,long_tbspa ce
    from syscat.tables where tbspaceid =<tablespace id>"

    This will shows all the tables that have parts in other tablespaces.
    remember that only DMS has parts in different tablespaces.

    The tablespace id can be found by list tablespaces command.



    regards,

    mujeeb

  3. #3
    Join Date
    Apr 2004
    Posts
    190

    Add all tablespaces

    I added all the tablespaces that were returned from running you sql statement, but I received the same error message.

    SQL4906N The list of table space names specified is an incomplete set for the rollforward operation.

    There must be a bug in db2, because I even added every tablespace name in the database, and got the same error message. I'm using db2 7.1 GA. I'm trying to rollforward to a point in time. Is there a work-around for this 'possible' problem. Has anyone else run into this problem?

    Thanks in advance

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    You can perform PIT recovery until a certain time.. so what time is your cut-off time.

    list the commands you are using.also your restore image timestamp

    Before rolling a table space forward, invoke the LIST TABLESPACES SHOW
    DETAIL command.
    This command returns the minimum recovery time, which is
    the earliest point in time to which the table space can be rolled forward.

    regards,

    mujeeb

  5. #5
    Join Date
    Mar 2004
    Posts
    448
    remember if you have multiple tables then take the max of that time.

    regards,

    mujeeb

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    also put the rollforward query status

    regards,

    mujeeb

  7. #7
    Join Date
    Apr 2004
    Posts
    190

    Tablespace Info

    I using:

    rollforward database records to 2004-07-08-04.00.00.000000 and stop tablespace (OCCSPACE2, OCCIDXSPACE) online


    Here is the tablespace info: I'm sure these are the only tablespaces required. Why is db2 telling me otherwise?

    Tablespace ID = 1
    Name = OCCSPACE2
    Type = Database managed space
    Contents = Any data
    State = 0x0080
    Detailed explanation:
    Roll forward pending
    Total pages = 13172736
    Useable pages = 13169664
    Used pages = 0
    Free pages = 0
    High water mark (pages) = 0
    Page size (bytes) = 4096 Tablespace ID = 13
    Name = OCCIDXSPACE
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 8781824
    Useable pages = 8780800
    Used pages = 4569472
    Free pages = 4211328
    High water mark (pages) = 5336704
    Page size (bytes) = 4096
    Extent size (pages) = 128
    Prefetch size (pages) = 1024
    Number of containers = 8
    Minimum recovery time = 2002-07-23-01.59.08.000000
    Extent size (pages) = 256
    Prefetch size (pages) = 768
    Number of containers = 12
    Minimum recovery time = 2004-06-15-17.20.24.000000

  8. #8
    Join Date
    Apr 2004
    Posts
    190
    Tablespace ID = 1
    Name = OCCSPACE2
    Type = Database managed space
    Contents = Any data
    State = 0x0080
    Detailed explanation:
    Roll forward pending
    Total pages = 13172736
    Useable pages = 13169664
    Used pages = 0
    Free pages = 0
    High water mark (pages) = 0
    Page size (bytes) = 4096
    Extent size (pages) = 256
    Prefetch size (pages) = 768
    Number of containers = 12
    Minimum recovery time = 2004-06-15-17.20.24.000000





    Tablespace ID = 13
    Name = OCCIDXSPACE
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 8781824
    Useable pages = 8780800
    Used pages = 4569472
    Free pages = 4211328
    High water mark (pages) = 5336704
    Page size (bytes) = 4096
    Extent size (pages) = 128
    Prefetch size (pages) = 1024
    Number of containers = 8
    Minimum recovery time = 2002-07-23-01.59.08.000000

  9. #9
    Join Date
    Apr 2004
    Posts
    190
    I got the same error when I used the statement below, not specifying any tablespace names.

    rollforward database records to 2004-07-08-04.00.00.000000 and complete

  10. #10
    Join Date
    Mar 2004
    Posts
    448
    This is what I will see after the restore
    I have a partition db , so take only the node 0 as applicable

    [db2dev@aqeel SAMPLE]$ db2 "rollforward database sample query status"

    Rollforward Status

    Input database alias = sample
    Number of nodes have returned status = 4

    Node number Rollforward Next log Log files processed Last committed transaction
    status to be read
    ----------- -------------------------- ------------------- ------------------------- --------------------------
    0 TBS pending S0000233.LOG-S0000236.LOG 2004-04-06-09.26.42.000000
    1 not pending S0000129.LOG-S0000135.LOG 2004-04-06-09.26.42.000000
    2 not pending S0000106.LOG-S0000108.LOG 2004-04-06-09.26.42.000000
    3 not pending S0000122.LOG-S0000124.LOG 2004-04-06-09.26.42.000000


    if it is working instead of pending then
    1. cancel the roll forward.
    2. restore the tablespaces again.
    3. query using the rollforward command.
    4. rollforward the tablespaces, using stop(I prefer it)
    5. again query it.
    6. rollforward with complete command.

    regards,

    mujeeb

  11. #11
    Join Date
    Mar 2004
    Posts
    448
    The working will appear only after the rollforward command, if it is unsuccessful as in your case.

    regards,

    mujeeb

  12. #12
    Join Date
    Apr 2004
    Posts
    190

    rollforward status

    Here is the info from the query status:

    rollforward database records query status

    Rollforward Status

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

    Node number = 0
    Rollforward status = TBS pending
    Next log file to be read = S0072282.LOG
    Log files processed = -
    Last committed transaction = 2004-07-19-14.28.13.000000

    I added a tablespace to the list that got rid of the SQL4906N error above, but know I'm getting the following:

    db2 => rollforward database records to 2004-07-13-20.01.18.000000 tablespace (OCCSPACE1, OCCSPACE2, OCCIDXSPACE) online
    SQL4908N The table space list specified for roll-forward recovery on database
    "RECORDS" is invalid on node(s) "0".

    2 of the tablespaces are not in rollforward pending state, but apparently I had to add them in order to get a complete list of tablespaces. i.e. tablespaces which either has indexes or tables in them related to the rollforward pending tablespace.

    Thanks

  13. #13
    Join Date
    Mar 2004
    Posts
    448
    1. Cancel the rollforward on the tablespace that is in pending.

    2. restore all the tablespaces from the backup image before the time that you want to rollforward (the min of minimum recovery, as I mentioned before).

    3. rollforward all the tablespaces(only the tablespaces that you restored,no addition or no subtraction).

    regards,

    mujeeb

  14. #14
    Join Date
    Apr 2004
    Posts
    190

    Minumum Recovery Time

    Would it work if I used the End time of the backup to rollforward ? For example the end time of the backup is 20040708003538. So I run:

    rollforward database records to 2004-07-08-00.00.00.000000 and stop tablespace(TBS1, TBS2, TBS3) online

    In this scenario I assume all the tablespace min recovery will be < than the backup end time, correct?

  15. #15
    Join Date
    Mar 2004
    Posts
    448
    you can't give a time less than the minimum recovery time.
    The reason is simple, your performed that steps.

    1. You performed backup of the tablespace. at time t1.
    2. you add a object to that tablespace at time t2.
    3. Now you restored and rollforward the tablespaces at time t1, but the
    catalog tablespace containes information of that object, but this
    object is not in tablespace.

    Big error!!!

    You can do a test

    1. list tablespaces show detail and note down the minimum recovery time.
    2. create an object in that tablespace.
    3. Now check the minimum recovery time.

    regards.

    mujeeb

Posting Permissions

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