Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Need urgent help for offline restore db2 database

    Hi all,

    In our setup db2 v9.7 with fixpack 4

    Need help for db2 restore for offline backup..

    From TIS_DWHD offline backup image to TISDD

    [db2inst1@vrdtisdbhcl01 ~]$ db2 restore db TIS_DWHD from /tisdev/temp taken at 20130111234814 into TISDD redirect without rolling forward
    SQL2529W Warning! Restoring to an existing database that is different from
    the backup image database, and the alias name "TISDD" of the existing database
    does not match the alias name "TIS_DWHD" of the backup image, and the database
    name "TISDD" of the existing database does not match the database name
    "TIS_DWHD" of the backup image. The target database will be overwritten by the
    backup version. The Roll-forward recovery logs associated with the target
    database will be deleted.
    Do you want to continue ? (y/n) y
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    [db2inst1@vrdtisdbhcl01 ~]$ db2 "set tablespace containers for 4 using (file '/tisdev/data/db2inst1/NODE0000/TSN_REG_DWH1/data' 163840)"

    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    [db2inst1@vrdtisdbhcl01 ~]$ db2 restore db TIS_DWHD continue
    DB20000I The RESTORE DATABASE command completed successfully.


    Able to connect the database, but can't able to select the count of the tables...

    [db2inst1@vrdtisdbhcl01 ~]$ db2 connect to TISDD

    Database Connection Information

    Database server = DB2/LINUXX8664 9.7.4
    SQL authorization ID = DB2INST1
    Local database alias = TISDD



    [db2inst1@vrdtisdbhcl01 ~]$ db2 'select count(*) from "db2DWH"."TIS_TRVL_TM_ROUTE"'

    1
    -----------
    SQL0276N Connection to database "TISDD" cannot be made because it is in the
    restore pending state. SQLSTATE=08004


    How to resolve this? Please give me the suggestions...



    Thanks,
    laxman....

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    please read you other entry with same problem....
    or give me a call and will come over to resolve this for you..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2012
    Posts
    177
    Hi guy,

    Thanx for you kind reply....

    As per you previous words, i follow the same...
    please give me the suggestions how to resolve this issue?


    Thanks,
    laxman..

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as in other entry :
    please read the doc...
    specify all containers needed
    generate restore script...
    I hope you understand my English as it is not my native language..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Looks like it's a DPF database, you probably need to restore all partitions separately, starting with the catalog partition.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Sep 2012
    Posts
    177
    Hi guy,

    Thanks for your kind reply..

    As per you suggestion, i tried the same using using redirect generate script:

    db2 restore db TIS_DWHD from /tisdev/temp taken at 20130111234814 into TIS redirect generate script TIS.CLP without rolling forward

    [db2inst1@vrdtisdbhcl01 ~]$ db2 restore db TIS_DWHD from /tisdev/temp taken at 20130111234814 into TIS redirect generate script TIS.CLP without rolling forward
    DB20000I The RESTORE DATABASE command completed successfully.

    [db2inst1@vrdtisdbhcl01 ~]$ db2 -tvf TIS.CLP
    UPDATE COMMAND OPTIONS USING S ON Z ON TIS_DWHD_NODE0000.out V ON
    DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

    SET CLIENT ATTACH_DBPARTITIONNUM 0
    DB20000I The SET CLIENT command completed successfully.

    SET CLIENT CONNECT_DBPARTITIONNUM 0
    DB20000I The SET CLIENT command completed successfully.

    RESTORE DATABASE TIS_DWHD FROM '/tisdev/temp' TAKEN AT 20130111234814 INTO TIS REDIRECT WITHOUT ROLLING FORWARD
    SQL2529W Warning! Restoring to an existing database that is different from
    the backup image database, and the alias name "TIS" of the existing database
    does not match the alias name "TIS_DWHD" of the backup image, and the database
    name "TIS" of the existing database does not match the database name
    "TIS_DWHD" of the backup image. The target database will be overwritten by the
    backup version. The Roll-forward recovery logs associated with the target
    database will be deleted.
    Do you want to continue ? (y/n) Y

    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    SET TABLESPACE CONTAINERS FOR 4 USING ( FILE 'tisdev/data' 163840 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    RESTORE DATABASE TIS_DWHD CONTINUE
    DB20000I The RESTORE DATABASE command completed successfully.

    But facing the same issue:

    [db2inst1@vrdtisdbhcl01 ~]$ db2 'select count(*) from "db2DWH"."TIS_TRVL_TM_ROUTE"'

    1
    -----------
    SQL0276N Connection to database "TIS" cannot be made because it is in the
    restore pending state. SQLSTATE=08004

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    See n_i's post. You have 3 partitions but only restoring the first one. Restore each partition. If a tablespace is not using automatic storage, you need to redefine it using set tablespace containers...

  8. #8
    Join Date
    Sep 2012
    Posts
    177
    Hi db2girl,

    Thanks you helping a lot..

    I am struggling a lot for this restore..I am trying to restore every partitions..

    But, the tablespace contains automatic storage no:


    -- ************************************************** ***************************
    -- ** table space definition
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = SYSCATSPACE
    -- ** Tablespace ID = 0
    -- ** Tablespace Type = Database managed space
    -- ** Tablespace Content Type = All permanent data. Regular table space.
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 4
    -- ** Using automatic storage = Yes
    -- ** Auto-resize enabled = Yes
    -- ** Total number of pages = 40960
    -- ** Number of usable pages = 40956
    -- ** High water mark (pages) = 39100
    -- ************************************************** ***************************

    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = TEMPSPACE1
    -- ** Tablespace ID = 1
    -- ** Tablespace Type = System managed space
    -- ** Tablespace Content Type = System Temporary data
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 32
    -- ** Using automatic storage = Yes
    -- ** Total number of pages = 1
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = USERSPACE1
    -- ** Tablespace ID = 2
    -- ** Tablespace Type = Database managed space
    -- ** Tablespace Content Type = All permanent data. Large table space.
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 32
    -- ** Using automatic storage = Yes
    -- ** Auto-resize enabled = Yes
    -- ** Total number of pages = 24576
    -- ** Number of usable pages = 24544
    -- ** High water mark (pages) = 17088
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = SYSTOOLSPACE
    -- ** Tablespace ID = 3
    -- ** Tablespace Type = Database managed space
    -- ** Tablespace Content Type = All permanent data. Large table space.
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 4
    -- ** Using automatic storage = Yes
    -- ** Auto-resize enabled = Yes
    -- ** Total number of pages = 8192
    -- ** Number of usable pages = 8188
    -- ** High water mark (pages) = 240
    -- ************************************************** ***************************
    -- ************************************************** *************************

    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = TSN_REG_DWH1
    -- ** Tablespace ID = 4
    -- ** Tablespace Type = Database managed space
    -- ** Tablespace Content Type = All permanent data. Regular table space.
    -- ** Tablespace Page size (bytes) = 32768
    -- ** Tablespace Extent size (pages) = 32
    -- ** Using automatic storage = No
    -- ** Auto-resize enabled = No
    -- ** Total number of pages = 163840
    -- ** Number of usable pages = 163808
    -- ** High water mark (pages) = 37696
    -- ************************************************** ***************************
    SET TABLESPACE CONTAINERS FOR 4
    -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
    USING (
    FILE 'tisdev/data' 163840
    );

    -- ************************************************** ***************************
    -- ** Tablespace name = SYSTOOLSTMPSPACE
    -- ** Tablespace ID = 5
    -- ** Tablespace Type = System managed space
    -- ** Tablespace Content Type = User Temporary data
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 4
    -- ** Using automatic storage = Yes
    -- ** Total number of pages = 1
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** start redirected restore
    -- ************************************************** ***************************
    RESTORE DATABASE TIS_DWHD CONTINUE;
    -- ************************************************** ***************************
    -- ** end of file

    How do redefine the tablespace which contains automatic storage NO..

    I had gone through the google using this link:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Need to specify the set tablespace containers for each tablespace?

    Thanks,
    laxman...

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Did you try generating this restore script on every partition and then executing it on every partition (the output you posted is from partition 0, you need to get the same from other partitions)? I've never used generate script option myself, but it looks like it's skipping auto-storage tablespaces and generates set tablespaces for those without auto-storage. If you can't get it to work, then provide "db2pd -d <db name> -tab" for db TIS_DWHD from all partitions.

  10. #10
    Join Date
    Sep 2012
    Posts
    177
    Hi db2girl,

    db2pd -d TIS_DWHD -tab for first partion:
    ================================

    [db2inst1@vrdtisdbhcl01 ~]$ db2pd -d TIS_DWHD -tab

    Database Partition 0 -- Database TIS_DWHD -- Active -- Up 0 days 03:38:01 -- Date 01/12/2013 03:30:29

    Tablespace Configuration:
    Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
    0x00002B314BD5B9A0 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE
    0x00002B314BD5D100 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE1
    0x00002B314BD60820 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1
    0x00002B314BD61F80 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 SYSTOOLSPACE
    0x00002B314BD636E0 4 DMS Regular 32768 32 No 32 2 2 Off 1 0 31 TSN_REG_DWH1
    0x00002B314BD64E40 5 SMS UsrTmp 4096 4 Yes 4 1 1 On 1 0 3 SYSTOOLSTMPSPACE

    Tablespace Statistics:
    Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
    0x00002B314BD5B9A0 0 40960 40956 39100 0 1856 39100 39100 0x00000000 0 0 No
    0x00002B314BD5D100 1 1 1 1 0 0 0 0 0x00000000 0 0 No
    0x00002B314BD60820 2 24576 24544 16832 0 7712 17088 17088 0x00000000 1357526800 0 No
    0x00002B314BD61F80 3 8192 8188 240 0 7948 240 240 0x00000000 1356594502 0 No
    0x00002B314BD636E0 4 163840 163808 37696 0 126112 37696 37696 0x00000000 1357780556 0 No
    0x00002B314BD64E40 5 1 1 1 0 0 0 0 0x00000000 0 0 No

    Tablespace Autoresize Statistics:
    Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
    0x00002B314BD5B9A0 0 Yes Yes 33554432 -1 No None None No
    0x00002B314BD5D100 1 Yes No 0 0 No 0 None No
    0x00002B314BD60820 2 Yes Yes 33554432 -1 No None None No
    0x00002B314BD61F80 3 Yes Yes 33554432 -1 No None None No
    0x00002B314BD636E0 4 No No 0 0 No 0 None No
    0x00002B314BD64E40 5 Yes No 0 0 No 0 None No

    Containers:
    Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
    0x00002B314BD5CEC0 0 0 File 40960 40956 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000000/C0000000.CAT
    0x00002B314BD5E5C0 1 0 Path 1 1 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000001/C0000000.TMP
    0x00002B314BD61D40 2 0 File 24576 24544 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000002/C0000000.LRG
    0x00002B314BD634A0 3 0 File 8192 8188 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000003/C0000000.LRG
    0x00002B314BD64C00 4 0 File 163840 163808 - 0 /tisdev/data/db2inst1/NODE0000/SQL00002/tisdev/data
    0x00002B314BD66300 5 0 Path 1 1 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000005/C0000000.UTM


    2nd partiton:
    =========

    [db2inst1@vrdtisdbhcl02 ~]$ db2pd -d TIS_DWHD -tab

    Database Partition 1 -- Database TIS_DWHD -- Active -- Up 0 days 02:49:12 -- Date 01/12/2013 03:32:31

    Tablespace Configuration:
    Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
    0x00002B484A031100 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE1
    0x00002B484A034820 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1
    0x00002B484A035F80 4 DMS Regular 32768 32 No 32 2 2 Off 1 0 31 TSN_REG_DWH1

    Tablespace Statistics:
    Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
    0x00002B484A031100 1 1 1 1 0 0 0 0 0x00000000 0 0 No
    0x00002B484A034820 2 24576 24544 14848 0 9696 15072 15072 0x00000000 1357526800 0 No
    0x00002B484A035F80 4 163840 163808 39744 0 124064 39744 39744 0x00000000 1357780556 0 No

    Tablespace Autoresize Statistics:
    Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
    0x00002B484A031100 1 Yes No 0 0 No 0 None No
    0x00002B484A034820 2 Yes Yes 33554432 -1 No None None No
    0x00002B484A035F80 4 No No 0 0 No 0 None No

    Containers:
    Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
    0x00002B484A0325C0 1 0 Path 1 1 0 0 /tisdev/data/db2inst1/NODE0001/TIS_DWHD/T0000001/C0000000.TMP
    0x00002B484A035D40 2 0 File 24576 24544 0 0 /tisdev/data/db2inst1/NODE0001/TIS_DWHD/T0000002/C0000000.LRG
    0x00002B484A0374A0 4 0 File 163840 163808 - 0 /tisdev/data/db2inst1/NODE0001/SQL00002/tisdev/data

    Thanks for your help db2girl...

    Thanks,
    laxman....

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I thought you have 3 partitions. Based on db2pd output, tablespace TSN_REG_DWH1 / tablespace ID 4 needs to be redefined and that's exactly what generate script generated. Use restore ... generate script on every partition to generate the script and then execute each script. Or take the script you generated on the first partition and update *DBPARTITIONNUM / *NODE0000. You can also change the container path ( 'tisdev/data') to append the node # / tablespace name, etc... if you want.

Posting Permissions

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