3

I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined.

When I set off the following command db2 list tablespace I get the following output (e.g.):

Tablespace ID                        = 6
Name                                 = USERTEMP4
Type                                 = System managed space
Contents                             = User Temporary data
State                                = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Can anybody help me to escape from this situation? I didn't start any restore recently so I'm wondering how or why the tablespaces have changed to this state.

mustaccio
  • 20,465
  • 17
  • 49
  • 60
Thomas Graf
  • 31
  • 1
  • 2
  • Unless something or someone purged the db history, you can run the query below to see the RESTORE actions, you may need additional filters to reduce the result-set size: `SELECT start_time, end_time, operation, operationtype, substr(tbspnames,1,132) as tbsp FROM sysibmadm.db_history where operation='R'` – mao Jul 13 '18 at 12:59
  • Unfortunately, the db history was purged and so the information on the restore action is lost (that was also the response of the abort command). Any other tips regarding this problem? – Thomas G Jul 19 '18 at 07:50
  • If you are the asker of the question please visit the help center to see how you can [merge your accounts](https://dba.stackexchange.com/help/merging-accounts). That way you could edit additional information into your question. – Tom V Jul 19 '18 at 07:53
  • Are access-controls adequate? If you are in a regulated industry there are clear risks. Forensics on shell-history (for the restore, rollforward,purge history), keystroke-recording, login-audits, tape-subsystem (if restoring from tape), etc, all may help to identify the actions, if you have the relevant tools. Presumably you already recovered database access, but seek to identify and prevent recurrences. – mao Jul 24 '18 at 11:59

1 Answers1

1

May be you didn't do a restore, but someone surely did, as the indicated tablespace state is only possible when a redirected restore has been initiated -- this is well described in the manual.

Normally to complete the redirected restore you will need to issue SET TABLESPACE CONTAINERS FOR 6 USING (PATH '/whatever'), where "6" is the tablespace ID, and similarly for all other tablespaces in this state, then run RESTORE DB yourdb CONTINUE to complete the operation (details here).

However, since you apparently don't recognize this restore operation, you'll probably want to abort it instead: RESTORE DB yourdb ABORT.

mustaccio
  • 20,465
  • 17
  • 49
  • 60
  • @Thomas Graf, also have a look at the "generate script" option. You can ask Db2 what it takes to restore a backup file. See https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0021521.html for an example – Lennart Dec 21 '18 at 20:53