IBM Support

Hands-on example for online tablespace restore and rollforward

Technical Blog Post


Abstract

Hands-on example for online tablespace restore and rollforward

Body

In some customer's cases, I faced the situation that some customers wanted to restore and rollforward a specific tablespace from backup image due to the file system corruption on container files of that tablespace. Plus, they ask for the access to tables in other tablespaces without any down time.
 
Thus, for your understanding, I made a hands-on for 'online tablespace  restore and rollforward'. In this example, you will see
-------------------------------------------
1. the database is up and running
2. tables in non-corrupted tablespaces are always accessible
3. only tables in corrupted tablespaces will be recovered
-------------------------------------------
 
c.f. backup, restore and rollforward command will close the connection in the current session. That's why I used B session for those commands. The database connection in A session is not terminated by the commands in B session.
 
-------------------------------------------
[Preparation]
v111_22a@woongc1:/home/v111_22a> db2 create db tstv111
DB20000I  The CREATE DATABASE command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 "update db cfg for tstv111 using LOGARCHMETH1 disk:/tmp/logs"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 backup db tstv111 to /dev/null
 
Backup successful. The timestamp for this backup image is : 20180411074402
 
 
[A session]
v111_22a@woongc1:/home/v111_22a> db2 connect to tstv111
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 11.1.2.2
SQL authorization ID   = V111_22A
Local database alias   = TSTV111
 
v111_22a@woongc1:/home/v111_22a> db2 create tablespace tsttbsp
DB20000I  The SQL command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 "create table tsttbl (c1 int) in tsttbsp"
DB20000I  The SQL command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 "create table tsttbl_US1 (c1 int) in USERSPACE1"
DB20000I  The SQL command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 "insert into tsttbl values 1"
DB20000I  The SQL command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 "insert into tsttbl_US1 values 9"
DB20000I  The SQL command completed successfully.
v111_22a@woongc1:/home/v111_22a> db2 "insert into tsttbl values 2"
DB20000I  The SQL command completed successfully.
>>>>> tsttbl resides in tsttbsp tablespace while tsttbl_US1 resides in USERSPACE1
 
 
[B session]
v111_22a@woongc1:/home/v111_22a> db2 backup db tstv111 online
 
Backup successful. The timestamp for this backup image is : 20180411074424
>>>>> backup is done
 
 
>>>>> tsttbsp goes bad!
 
 
[B session]
v111_22a@woongc1:/home/v111_22a> db2 "restore db tstv111 tablespace (TSTTBSP) online taken at 20180411074424"
DB20000I  The RESTORE DATABASE command completed successfully.
 
 
[A session]
v111_22a@woongc1:/home/v111_22a> db2 "select * from tsttbl"
 
C1         
-----------
SQL0290N  Table space access is not allowed.  SQLSTATE=55039
>>>>> This is because tsttbsp is under RF pending status
 
v111_22a@woongc1:/home/v111_22a> db2 "select * from tsttbl_US1"
 
C1         
-----------
          9
 
  1 record(s) selected.
>>>>> But tables in other tablespaces are accessible
 
v111_22a@woongc1:/home/v111_22a> 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                                 = TSTTBSP
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0080
   Detailed explanation:
     Roll forward pending
 
 
[B session]
v111_22a@woongc1:/home/v111_22a> db2 "rollforward db tstv111 to end of logs and stop tablespace(TSTTBSP) online"
 
                                 Rollforward Status
 
Input database alias                   = tstv111
Number of members have returned status = 1
 
Member ID                              = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    =  -
Last committed transaction             = 2018-04-10-21.44.39.000000 UTC
 
DB20000I  The ROLLFORWARD command completed successfully.
 
 
[A session]
v111_22a@woongc1:/home/v111_22a> db2 "select * from tsttbl"
 
C1         
-----------
          1
          2
 
  2 record(s) selected.
>>>>> now the table in tsttbsp is accessible
-------------------------------------------
 
For more command detail, please check following links.
RESTORE DATABASE command
 
ROLLFORWARD DATABASE command

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140196