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":"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
ibm11140196