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

    Unanswered: Issues with restore the database

    Hi all,

    In our setup db2 V9.7 with fixpack 4

    Facing issues with restoring the database
    ===============================

    I took the offline backup for the database XXXX

    I tried to restore the database with the command restore went successfully:
    ================================================== =======

    db2 "restore database XXX from tisdev/temp taken at 20121222001827 into XXX"

    Do you want to continue ? (y/n) y
    DB20000I The RESTORE DATABASE command completed successfully.


    When i tried to connect the database :
    =============================
    [db2inst1@XXXX ~]$ db2 connect to XXXX
    SQL1117N A connection to or activation of database "TIS" cannot be made
    because of ROLL-FORWARD PENDING. SQLSTATE=57019

    Applied the rollforward command also
    =============================

    db2 rollforward db XXXX to end of logs and complete

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

    I checked the applications also but none of applications is connecting the database.

    Rollforward query status
    ==================

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

    It's also restore pending state?

    Could you please give me the suggestions how to connect the database?

    Thanks,
    laxman..

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You specified INTO on the restore command - are you restoring into a new database? If so, you may need to redefine tablespace containers, check db2diag.log for error logged during restore.

    If you don't want to apply the logs following the restore, specify WITHOUT ROLLING FORWARD on the restore command or issue ROLLFORWARD DB <db name> STOP/COMPLETE (don't specify TO END OF LOGS).

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

    Thanks for your valuable reply.

    I am trying to restore to the new database.

    I am new for restoring the database,

    From the source database this the tablespace container structure.

    b2 => LIST TABLESPACE CONTAINERS FOR 6 SHOW DETAIL

    Tablespace Containers for Tablespace 6

    Container ID = 0
    Name = /tisdev/data/db2inst1/NODE0000/TIS_STGD/T0000006/C0000000.LRG
    Type = File
    Total pages = 1024
    Useable pages = 992
    Accessible = Yes

    Could you please tell me how to restore to the destination database from the backup image.

    Thanks,
    laxman..

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Are source and target databases on the same server?

    list tablespace containers is not enough. Please provide the following for the source db: db2pd -d <db name> -tab

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

    Yes you are correct, i am trying to restore the database on the same server.

    I have the mentioned the details what you asked:
    =====================================

    Database Partition 0 -- Database TIS_DWHD -- Active -- Up 6 days 17:13:24 -- Date 12/28/2012 18:09:30

    Tablespace Configuration:
    Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
    0x00002AB0F15EB9A0 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE
    0x00002AB0F15ED100 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE1
    0x00002AB0F15F0820 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1
    0x00002AB0F15F1F80 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 SYSTOOLSPACE
    0x00002AB0F15F36E0 4 DMS Regular 32768 32 No 32 2 2 Off 1 0 31 TSN_REG_DWH1
    0x00002AB0F15F4E40 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
    0x00002AB0F15EB9A0 0 40960 40956 38704 0 2252 38704 38704 0x00000000 0 0 No
    0x00002AB0F15ED100 1 1 1 1 0 0 0 0 0x00000000 0 0 No
    0x00002AB0F15F0820 2 24576 24544 16704 0 7840 17088 17088 0x00000000 1355714741 0 No
    0x00002AB0F15F1F80 3 8192 8188 240 0 7948 240 240 0x00000000 1356594502 0 No
    0x00002AB0F15F36E0 4 163840 163808 34208 64 129536 34368 34368 0x00000000 1356658750 0 No
    0x00002AB0F15F4E40 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
    0x00002AB0F15EB9A0 0 Yes Yes 33554432 -1 No None None No
    0x00002AB0F15ED100 1 Yes No 0 0 No 0 None No
    0x00002AB0F15F0820 2 Yes Yes 33554432 -1 No None None No
    0x00002AB0F15F1F80 3 Yes Yes 33554432 -1 No None None No
    0x00002AB0F15F36E0 4 No No 0 0 No 0 None No
    0x00002AB0F15F4E40 5 Yes No 0 0 No 0 None No

    Containers:
    Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
    0x00002AB0F15ECEC0 0 0 File 40960 40956 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000000/C0000000.CAT
    0x00002AB0F15EE5C0 1 0 Path 1 1 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000001/C0000000.TMP
    0x00002AB0F15F1D40 2 0 File 24576 24544 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000002/C0000000.LRG
    0x00002AB0F15F34A0 3 0 File 8192 8188 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000003/C0000000.LRG
    0x00002AB0F15F4C00 4 0 File 163840 163808 - 0 /tisdev/data/db2inst1/NODE0000/SQL00002/tisdev/data
    0x00002AB0F15F6300 5 0 Path 1 1 0 0 /tisdev/data/db2inst1/NODE0000/TIS_DWHD/T0000005/C0000000.UTM


    Please give me the suggestions over this..

    Thanks,
    laxman..

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    db2pd is for db TIS_DWHD and it looks like 'list tablespace containers' you posted earlier is for TIS_STGD. TIS_DWHD doesn't have tablespace id 6. If you're trying to restore the backup image of TIS_DWHD, then try the following:

    db2 restore db TIS_DWHD from ... taken at ... into <new db name> redirect without rolling forward
    db2 "set tablespace containers for 4 using (file '/tisdev/data/db2inst1/NODE0000/TSN_REG_DWH1/data' 163840)"
    db2 restore db TIS_DWHD continue

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

    Thanks for your kind reply..

    When i am doing restore, people who are using the tablespace they are able to access?

    Please advice i am new for restoration...

    Thanks,
    laxman..

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what a great invention by IBM : the online doc
    If you are new, why not first reading some doc and try to understand what you are doing..
    I hope you have access to this or download the pdf files an READ some chapters:
    Performing a redirected restore operation
    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

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

    Thanks for your kind reply.

    In our setup we have partitioned databases, and 3 linux boxes.

    As per your information, Restoration done successfully for the newly created databases.

    From the 1st box i can able to connect the restored database.

    db2inst1@prddw1 ~]$ db2 connect to XXX

    Database Connection Information

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

    From the 2nd and 3rd box i can't able to connect the database.

    [db2inst1@prddw2 ~]$ db2 connect to XXX
    SQL0276N Connection to database "XXX" cannot be made because it is in the
    restore pending state. SQLSTATE=08004


    Could you please give the suggestions? Where i went wrong?

    Thanks,
    laxman..

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as the message indicates : restore pending state
    it means : when restoring the db, some containers could not be created/restored because of invalid/non-existing/used path
    you have to use redirected restore and supply the path for each container...
    look in diaglog that will indicate also the ts/containers that could not be restored..
    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

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

    Thanks for your kind reply.

    Need to restore the entire tablespaces? Like:

    db2 set tablespace containers for 0 using (PATH '/db2prod/data/db2inst1/TIS_STGP//SYSCATSPACE' );

    db2 set tablespace containers for 1 using (PATH '/db2prod/data/db2inst1/TIS_STGP//TEMPSPACE1' );

    db2 set tablespace containers for 2 using (PATH '/db2prod/data/db2inst1/TIS_STGP//USERSPACE1' );

    db2 set tablespace containers for 3 using (PATH '/db2prod/data/db2inst1/TIS_STGP//SYSTOOLSPACE' );


    Thanks,
    laxman....

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated before
    Performing a redirected restore operation
    you can generate a script with these statements using "generate script" as option for restore... see syntax diagram for details
    RESTORE DATABASE
    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

Posting Permissions

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