DB2 DPF Tablespace Recovery Scenarios — Part 3 — Recover Tablespace(s) on ALL database partitions

In this blog post, I will share how to recover tablespaces from ALL database partitions in a DPF database and is 3rd out of 3 parts.The topic is “DB2 DPF Tablespace Recovery Scenarios”.

Part 1 covered “Recovering tablespaces on the Catalog Partition“. Part 2 detailed recovering tablespaces on “Data” partitions (partitions other than catalog partition).

If you read parts 1 and 2, you would see there is a bit of repetition in this blog post.. but, I realize covering the entire restore activity would be helpful for someone who did not read parts 1 and 2.

From high level, steps that we follow to recover tablespaces on any set of partitions would be similar:

1) Find out which tablespaces (both Data and Index) need to be recovered.

2) Find out which partition group do tablespaces belong to.

3) Run restore with options depending on which partition group/partitions do tablespaces belong to.

Parts 1 and 2 show how to identify the tablespaces that need to be recovered in two different scenarios.

Using similar approach, let us assume:

1) We have 13 logical partitions in total (12 data partitions + 1 catalog partition (#0))

2) ‘TABLE5’ and ‘TABLE6’ are tables that need to be recovered

Step 1) The following SQL gives the list of tablespaces (both Data and Index) that need to be recovered.

db2 “select char(TBSPACE,20) from syscat.tables where tabname in (‘TABLE5′,’TABLE6′)
union
select char(INDEX_TBSPACE,20) from syscat.tables where tabname in (‘TABLE5′,’TABLE6′)
with ur”;

1
——————–
TBSP5
TBSP6
TBSP5_IX
TBSP6_IX

4 record(s) selected

Assuming we did not know which partitions do these tablespaces really belong to, the following SQL gives the list of database partitions which the above tablespaces belong to:

Step 2) To determine which database partitions do these tables belong to, use the following SQL:

db2 “select char(tbsp_name, 20) tablespace, char(DBPGNAME, 20) nodegroup, DBPARTITIONNUM from sysibmadm.tbsp_utilization where tbsp_name in (‘TBSP5′,’TBSP5_IX′,’TBSP6′,’TBSP6_IX′) order by tbsp_name, DBPARTITIONNUM with ur”

In this case, this should result 13 partitions times 4 tablespaces = 52 rows.

Step 3) Next step is to kick off the restore

There will be minor difference in the “restore” command syntax that we use when we recover tablespaces on ALL partitions. Instead of asking DB2 to “exclude” catalog node, we ask DB2 to execute restore on ALL partitions.

Here is the command that was used when restore was done on all data partitions:

db2_all “||<<-0< db2 RESTORE DATABASE TESTDB TABLESPACE \(TBSP3, TBSP3_IX, TBSP4, TBSP4_IX\) ONLINE USE TSM TAKEN AT 20140227143006 REPLACE EXISTING WITHOUT PROMPTING”

The command that needs to be used this time would be: (You could run this command from any node)

db2_all “|| db2 RESTORE DATABASE TESTDB TABLESPACE \(TBSP5, TBSP5_IX, TBSP6, TBSP6_IX\) ONLINE USE TSM TAKEN AT 20140227143006 REPLACE EXISTING WITHOUT PROMPTING”

Notice that we took out “-0” after db2_all. This is to tell DB2 to run the command on ALL partitions.

Output of the above command would be a message from ALL (13) partitions.

Checking the status of tablespaces would indicate they are in rollforward pending mode. Here ALLPG is the partition group (or node group) that has in it ALL database partitions.

db2 “select distinct(char(tbsp_name,20)), char(tbsp_state , 40) from SYSIBMADM.TBSP_UTILIZATION where TBSP_STATE <> ‘NORMAL’ AND DBPGNAME = ‘ALLPG’”

1 2
——————– —————————————-
TBSP5 ROLLFORWARD_PENDING
TBSP6 ROLLFORWARD_PENDING
TBSP5_IX ROLLFORWARD_PENDING
TBSP6_IX ROLLFORWARD_PENDING

4 record(s) selected.

Next step is to rollforward logs.

In this situation (as in parts1 and 2), it was determined that the best point to rollforward is an immediate point-in-time after database backup.

Here is my rollforward command: (You can only run rollforward command on the catalog partition).

db2 “ROLLFORWARD DATABASE TESTDB TO END OF BACKUP TABLESPACE (TBSP5, TBSP5_IX, TBSP6, TBSP6_IX) ONLINE”

I want this tablespace to be rolled forward on all the partitions it is on and so I did not use the “except” clause. Also, with “end of backup” clause, the default is for rollforward to run on all database partitions as evident from the syntax from IBM’s Information Center.

Here is the output of the above rollforward command:

Rollforward Status

Input database alias = TESTDB
Number of nodes have returned status = 13

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 TBS working S0285597.LOG – 2014-02-02-18.01.07.000000 UTC
1 TBS working S0084232.LOG – 2014-01-30-08.15.05.000000 UTC
2 TBS working S0084951.LOG – 2014-02-02-18.01.14.000000 UTC
3 TBS working S0085896.LOG – 2014-01-30-08.12.04.000000 UTC
4 TBS working S0084391.LOG – 2014-01-30-08.12.04.000000 UTC
5 TBS working S0084162.LOG – 2014-02-02-18.01.12.000000 UTC
6 TBS working S0084403.LOG – 2014-01-22-19.00.14.000000 UTC
7 TBS working S0084272.LOG – 2014-02-02-18.01.15.000000 UTC
8 TBS working S0083390.LOG – 2014-02-02-18.01.14.000000 UTC
9 TBS working S0083145.LOG – 2014-02-02-18.01.16.000000 UTC
10 TBS working S0083770.LOG – 2014-02-02-18.01.14.000000 UTC
11 TBS working S0084257.LOG – 2014-02-02-18.01.10.000000 UTC
12 TBS working S0090640.LOG – 2014-02-02-18.01.11.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

Here is the status of these tablespaces after the above rollforward:

db2 “select distinct(char(tbsp_name,20)), char(tbsp_state , 40) from SYSIBMADM.TBSP_UTILIZATION where TBSP_STATE <> ‘NORMAL’ AND DBPGNAME = ‘ALLPG’”

1 2
——————– —————————————-
TBSP5 ROLLFORWARD_IN_PROGRESS
TBSP6 ROLLFORWARD_IN_PROGRESS
TBSP5_IX ROLLFORWARD_IN_PROGRESS
TBSP6_IX ROLLFORWARD_IN_PROGRESS

4 record(s) selected.

Once I found that the timestamp shown as the “Last committed transaction” in the output of rollforward command (above) is satisfactory, I stopped the rollforward activity. Here is how you do it:

db2 “ROLLFORWARD DATABASE TESTDB STOP TABLESPACE (TBSP5, TBSP5_IX, TBSP6, TBSP6_IX) ONLINE”

Rollforward Status

Input database alias = TESTDB
Number of nodes have returned status = 13

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 not pending – 2014-02-02-18.01.07.000000 UTC
1 not pending – 2014-01-30-08.15.05.000000 UTC
2 not pending – 2014-02-02-18.01.14.000000 UTC
3 not pending – 2014-01-30-08.12.04.000000 UTC
4 not pending – 2014-01-30-08.12.04.000000 UTC
5 not pending – 2014-02-02-18.01.12.000000 UTC
6 not pending – 2014-01-22-19.00.14.000000 UTC
7 not pending – 2014-02-02-18.01.15.000000 UTC
8 not pending – 2014-02-02-18.01.14.000000 UTC
9 not pending – 2014-02-02-18.01.16.000000 UTC
10 not pending – 2014-02-02-18.01.14.000000 UTC
11 not pending – 2014-02-02-18.01.10.000000 UTC
12 not pending – 2014-02-02-18.01.11.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

db2 “select distinct(char(tbsp_name,20)), char(tbsp_state , 40) from SYSIBMADM.TBSP_UTILIZATION where TBSP_STATE <> ‘NORMAL’ AND DBPGNAME = ‘ALLPG’”

1 2
——————– —————————————-
TBSP5 BACKUP_PENDING
TBSP5 BACKUP_PENDING
TBSP6_IX BACKUP_PENDING
TBSP6_IX BACKUP_PENDING

4 record(s) selected.

We will quickly backup tablespaces to /dev/null to get them out of backup pending state. But please take a full backup as soon as you can.

db2 “BACKUP DATABASE TESTDB ON ALL DBPARTITIONNUMS TABLESPACE (TBSP5, TBSP6, TBSP5_IX, TBSP6_IX) ONLINE to /dev/null”

Part Result
—- ————————————————————————
0000 DB20000I The BACKUP DATABASE command completed successfully.
0001 DB20000I The BACKUP DATABASE command completed successfully.
0002 DB20000I The BACKUP DATABASE command completed successfully.
0003 DB20000I The BACKUP DATABASE command completed successfully.
0004 DB20000I The BACKUP DATABASE command completed successfully.
0005 DB20000I The BACKUP DATABASE command completed successfully.
0006 DB20000I The BACKUP DATABASE command completed successfully.
0007 DB20000I The BACKUP DATABASE command completed successfully.
0008 DB20000I The BACKUP DATABASE command completed successfully.
0009 DB20000I The BACKUP DATABASE command completed successfully.
0010 DB20000I The BACKUP DATABASE command completed successfully.
0011 DB20000I The BACKUP DATABASE command completed successfully.
0012 DB20000I The BACKUP DATABASE command completed successfully.

Backup successful. The timestamp for this backup image is : 20140301131655

db2 “select distinct(char(tbsp_name,20)), char(tbsp_state , 40) from SYSIBMADM.TBSP_UTILIZATION where TBSP_STATE <> ‘NORMAL’ AND DBPGNAME = ‘ALLPG’”

1 2
——————– —————————————-

0 record(s) selected.

db2 “select distinct(char(tbsp_name,20)), char(tbsp_state , 40) from SYSIBMADM.TBSP_UTILIZATION where TBSP_STATE <> ‘NORMAL’”

1 2
——————– —————————————-

0 record(s) selected.

I ran simple selects to test access to tables that were recovered and asked the end user to validate the data. I found that the user likes what he saw in tables which is what I really needed.

Hope you found this blog post useful.  Questions and comments are welcome and highly appreciated. Thanks for reading.

Pavan Kristipati.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s