Technical Blog Post
Abstract
Hands-on example for rebuilding(restoring) selected table spaces
Body
This document explains how to restore only a subset of table spaces with commands and their outputs. We already have a link below for this but it does not have actual output in the command line and explain complicated scenarios with incremental backups. So we illustrate the detail in this document.
Why do we need to rebuild selected table spaces:
* In a test and development environment in which you want to work on only a subset of table spaces.
* In a recovery situation in which you need to bring table spaces that are more critical online faster than others, you can first restore a subset of table spaces then restore other table spaces at a later time.
* In a recovery situation, you may want to restore only critical tablespaces in the test environment and export the data there and load it to production environment.
Commands and outputs for rebuilding(restoring) selected table spaces
- USERSPACE1 is only restored from the backup images.
- SYSCATSPACE should always be restored together with any selected table spaces.
1. full backup case
$ db2 "restore db TST111 rebuild with tablespace (SYSCATSPACE, USERSPACE1) taken at 20190705231339"
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 "rollforward db TST111 to end of logs and stop"
SQL1271W Database "TST111" is recovered but one or more table spaces are
offline on members or nodes "0".
$ db2 connect to TST111
Database Connection Information
Database server = DB2/LINUXX8664 11.1.3.3
SQL authorization ID = V111_331
Local database alias = TST111
$ db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 3
Name = TSTTBS1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 4
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 5
Name = USERSPACE2
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 6
Name = TBS_INDEX
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 7
Name = TBS_LOB
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 8
Name = TBS_TABLE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
>>>>> SYSCATSPACE, TEMPSPACE1 and USERSPACE1 are "Normal" state and other tablespaces are "Restore pending" state.
2. full + incremental backup case
$ db2ckrst -d tst111 -t 20190705233840
Suggested restore order of images using timestamp 20190705233840 for
database tst111.
====================================================================
restore db tst111 incremental taken at 20190705233840
restore db tst111 incremental taken at 20190705233755
restore db tst111 incremental taken at 20190705233840
====================================================================
$ db2 "restore db tst111 rebuild with tablespace (SYSCATSPACE, USERSPACE1) incremental taken at 20190705233840"
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 "restore db tst111 incremental taken at 20190705233755"
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 "restore db tst111 incremental taken at 20190705233840"
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 "rollforward db TST111 to end of logs and stop"
SQL1271W Database "TST111" is recovered but one or more table spaces are
offline on members or nodes "0".
>>>>> The results of db2 connect and db2 list tablespaces are identical like above.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13285795