Redirected PIT Tablespace Restore

Kathryn Ericsson

27 February 2017

 

Procedure to Perform a Redirected Restore to a PIT to Restore Only One Tablespace

 

This document addresses how to restore a DB2 LUW database into another database, to a specific point in time (PIT), and restore only one tablespace out of several from a database-level backup. 

DB2 10.5, Linux operating system.  TSM in place to handle log archiving and database backups.

 

DB Name               Desc

DB_PROD              Production database

DB_COPY               The soon-to-be copy of the production database, with only selected tablespaces, restored to a point in time

 

 

DB_COPY will reside in the same DB2 instance as DB_PROD.

They will both share the same DB2 instance owner (db2iown).

 

DB_COPY database objects (tablespaces, logs, etc) will reside in different directories from DB_PROD.

 

DB_COPY objects will be restored to Jan 31, 2017 at 9am local time.

 

The application tablespaces to be recovered are TSPTAB and TSPIDX.

 

Summary of Steps:

1.     Prep Work

2.     Analyze Existing Backups and Logs

3.     Create the New Copy of the Database

4.     Run the Redirected Restore (RR) Command to Create the Script

5.     Edit the RR script

6.     Execute the Edited RR Script

7.     Post-Restore, Manage Logs

8.     Rollforward the Database

9.     Connect to the Database, and Confirm the Data

 

 

1.    Prep Work:

Ensure space is available for the new objects on a different path.  In this case, the path will be in a filesystem starting with Ôfilesystem,Õ which is completely different from DB_PROD:

Prepare the path for the database data and logs.  Make directory paths similar to this:

/filesystem/db2iown/DB_COPY/data

/filesystem/db2iown/DB_COPY/logs

 

Obtain config information about the existing DB_PROD.

db2 "get database config for DB_PROD" > DB_DB_PROD_dbcfg.txt

 

Obtain database DDL information about the existing DB_PROD.

db2look -d DB_PROD -createdb  -i db2iown  -w  'p  w here' -o  DB_PROD_ddl.txt

 

 

2.    Analyze Existing Backups and Logs: 

Run this command to obtain TSM info.  See below for non-TSM shops:

db2adutl query full db DB_PROD > bkup_list.save

Database had a full backup on Sunday January 29, 2017 at 23:39

And an incremental on Jan 31, 2017 at around 3am local server time with log S0006674:

 

db2adutl output:

Full:

20170129233958  Oldest log: S0006668.LOG  DB Partition Number: 0

 

Incremental:

    Time: 20170131025950  Oldest log: S0006674.LOG  DB Partition Number: 0    Sessions: 2

 

For shops without TSM, this command can obtain information about backups:

db2 list history backup all for DB_PROD

( Op = ÔBÕackup, Obj = ÔDÕatabase, Type ÔNÕ (Online) (vs ÔOÕ=Incremental online), Status ÔAÕctive (vs ÔEÕxpired):)

 

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID

 -- --- ------------------ ---- --- ------------ ------------ --------------

  B  D  20170129233958002   N    A  S0006668.LOG S0006669.LOG

 ----------------------------------------------------------------------------

ÉÉÉÉ

 

Start Time: 20170129233958

   End Time: 20170130002714

     Status: A

 ----------------------------------------------------------------------------

 

 

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID

 -- --- ------------------ ---- --- ------------ ------------ --------------

  B  D  20170131025950002   O    A  S0006674.LOG S0006674.LOG

 ----------------------------------------------------------------------------

ÉÉÉÉ

 

Start Time: 20170131025950

   End Time: 20170131030458

     Status: A

 ----------------------------------------------------------------------------

 

SHOWTIME!

 

3.    Create the New Copy of the Database:

CREATE DATABASE DB_COPY ON '/filesystem/db2iown/DB_COPY/data'  DBPATH ON  '/home/db2iown/'

 

4.    Run the Redirected Restore Command to Create the Script:

This example will create a script called, ÒRESTORE_REDIRECT.clpÓ

Specify the time of the full backup after the Òtaken atÓ parm, and the ÒintoÓ parm is the new database:

db2 "restore db DB_PROD rebuild with tablespace (SYSCATSPACE, TSPTAB, TSPIDX) use tsm taken at 20170129233958  into DB_COPY redirect generate script RESTORE_REDIRECT.clp without prompting"

 

5.    Edit the RR Script (see below):

Two hyphens in column 1 specify a comment.

The only parts you want to change are (enhanced below):

User & Using:

Uncomment and type in the owner and password

Make sure Syscatspace is not commented out

Uncomment the tablespaces you want to restore

Keep the other tablespaces commented out that you do not want to restore

Uncomment the ÔONÕ parm and make sure it is correct

Make sure the ÔINTOÕ parm is pointing to your new db (DB_COPY)

Uncomment the ÔLOGTARGETÕ parm and make sure it is correct

Keep everything else the same

 

-- *****************************************************************************

-- ** automatically created redirect restore script

-- *****************************************************************************

UPDATE COMMAND OPTIONS USING S ON Z ON DB_PROD_NODE0000.out V ON;

SET CLIENT ATTACH_MEMBER  0;

SET CLIENT CONNECT_MEMBER 0;

-- *****************************************************************************

-- ** automatically created redirect restore script

-- *****************************************************************************

RESTORE DATABASE DB_PROD

 USER  db2iown

 USING ' p w here'

REBUILD WITH TABLESPACE (

  "SYSCATSPACE"

--, "TEMPSPACE1"

--, "OTHERTS1"

--, "OTHERTS2"

, "TSPTAB"

, "TSPIDX"

--, "SYSTOOLSTMPSPACE"

--, "REORGTS1"

--, "USERTEMPSPACE32K"

)

USE TSM

OPEN 1 SESSIONS

-- OPTIONS '<options-string>'

TAKEN AT 20170129233958

 ON '/filesystem/db2iown/DB_COPY/data'

-- DBPATH ON '<target-directory>'

INTO DB_COPY

 LOGTARGET '/filesystem/db2iown/DB_COPY/logs'

-- NEWLOGPATH '/db2iown/DB_PROD/logs/NODE0000/LOGSTREAM0000/'

-- WITH <num-buff> BUFFERS

-- BUFFER <buffer-size>

-- REPLACE HISTORY FILE

-- REPLACE EXISTING

REDIRECT

-- PARALLELISM <n>

-- COMPRLIB '<lib-name>'

-- COMPROPTS '<options-string>'

-- WITHOUT ROLLING FORWARD

WITHOUT PROMPTING

;

-- *****************************************************************************

-- ** storage group definition

-- **   Default storage group ID                 = 0

-- **   Number of storage groups                 = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** Storage group name                         = IBMSTOGROUP

-- **   Storage group ID                         = 0

-- **   Data tag                                 = None

-- *****************************************************************************

-- SET STOGROUP PATHS FOR IBMSTOGROUP

-- ON '/filesystem/db2iown/DB_PROD/data'

-- ;

-- *****************************************************************************

-- ** table space definition

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = SYSCATSPACE

-- **   Tablespace ID                            = 0

-- **   Tablespace Type                          = Database managed space                     

-- **   Tablespace Content Type                  = All permanent data. Regular table space.   

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 4

-- **   Using automatic storage                  = Yes    

-- **   Storage group ID                         = 0

-- **   Source storage group ID                  = -1

-- **   Data tag                                 = None

-- **   Auto-resize enabled                      = Yes    

-- **   Total number of pages                    = 73728

-- **   Number of usable pages                   = 73724

-- **   High water mark (pages)                  = 67224

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = TEMPSPACE1

-- **   Tablespace ID                            = 1

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = System Temporary data                      

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 128

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = SYSTOOLSTMPSPACE

-- **   Tablespace ID                            = 21

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = User Temporary data                        

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 4

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = REORGTS1

-- **   Tablespace ID                            = 22

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = System Temporary data                      

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 128

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = USERTEMPSPACE32K

-- **   Tablespace ID                            = 35

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = User Temporary data                        

-- **   Tablespace Page size (bytes)             = 32768

-- **   Tablespace Extent size (pages)           = 128

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** start redirected restore

-- *****************************************************************************

RESTORE DATABASE DB_PROD CONTINUE;

-- *****************************************************************************

-- ** end of file

-- *****************************************************************************

 

 

6.     Execute the Edited RR Script:

db2 -tvf RESTORE_REDIRECT.clp -z RESTORE_REDIRECTout.TXT

Below is the output for this case.  The warnings are fine:

 

UPDATE COMMAND OPTIONS USING S ON Z ON FNLNK_DB_NODE0000.out V ON

DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

 

SET CLIENT ATTACH_MEMBER  0

DB20000I  The SET CLIENT command completed successfully.

 

SET CLIENT CONNECT_MEMBER 0

DB20000I  The SET CLIENT command completed successfully.

 

RESTORE DATABASE DB_PROD USER  db2iown USING           REBUILD WITH TABLESPACE ( "SYSCATSPACE" , "TSPTAB" , "TSPIDX" ) USE TSM OPEN 1 SESSIONS TAKEN AT 20170129233958 ON '/filesystem' DBPATH ON '/home/db2iown/' INTO DB_COPY LOGTARGET '/filesystem/db2iown/DB_COPY/logs' REPLACE EXISTING REDIRECT WITHOUT PROMPTING

 

SQL1277W  A redirected restore operation is being performed. During a table

space restore, only table spaces being restored can have their paths

reconfigured. During a database restore, storage group storage paths and DMS

table space containers can be reconfigured.

DB20000I  The RESTORE DATABASE command completed successfully.

 

 

RESTORE DATABASE DB_PROD CONTINUE

DB20000I  The RESTORE DATABASE command completed successfully.

 

 

While this was running, prior to the ÒDB20000I  The RESTORE DATABASE command completed successfullyÓ message, we were monitoring it from another session:

db2 list utilities

 

ID                               = 36700

Type                             = RESTORE

Database Name                    = DB_COPY

Member Number                    = 0

Description                      = automatic db rebuild SYSCATSPACE...

Start Time                       = 01/31/2017 13:20:01.947519

State                            = Executing

Invocation Type                  = User

 

 

7.    Post-Restore, Manage Logs:

 

Restore is done now.  It is important to note that, without spending space and time to explain, by no means should the database be connected to at this point.

 

The log path needs to be set up properly.

Even though we specified it in the RR script:

(remember LOGTARGET '/filesystem/db2iown/DB_COPY/logs'?)

 

The logpath somehow reverted to mirror the DB_PROD:

db2 "get database config for DB_COPY"

Changed path to log files                  (NEWLOGPATH) =

 Path to log files        = /filesystem/DB_PROD/logs/NODE0000/LOGSTREAM0000/

 

So now we have to update the DB config to point to the proper logpath.

 

db2 update  db cfg for DB_COPY using NEWLOGPATH

            /filesystem/db2iown/DB_COPY/logs/

 

Another query to the DB config shows the new DB has the proper log file setup:

db2 "get database config for DB_COPY"

Changed path to log files                  (NEWLOGPATH) =

/filesystem/db2iown/DB_COPY/logs/NODE0000/LOGSTREAM0000/

 Path to log files                                       =

/filesystem/DB_PROD/logs/NODE0000/LOGSTREAM0000/

 

 

When the restore was done, the db was in this state.  Note the ÒNext Log File to be Read,Ó and the ÒLast Committed Transaction:Ó

 

db2 rollforward database DB_COPY query status

 

                                 Rollforward Status

 

 Input database alias                   = DB_COPY

 Number of members have returned status = 1

 

 Member ID                              = 0

 Rollforward status                     = DB  pending

 Next log file to be read               = S0006668.LOG

 Log files processed                    =  -

 Last committed transaction             = 2017-01-30-08.26.42.000000 UTC

 

 

Now the logs need to be copied to the new database path so that the transactions can be rolled forward.

 

Based on the config of DB_PROD, the logs are in:

 

Changed path to log files                  (NEWLOGPATH) =

 Path to log files                                       = /db2iown/DB_PROD/logs/NODE0000/LOGSTREAM0000/

 

The new DB_COPY is expecting logs in:

/filesystem/db2iown/DB_COPY/logs/NODE0000/LOGSTREAM0000/

 

Log files have to be manually copied from the DB_PROD log path to the DB_COPY path.

We know we need at least S0006668.LOG through S0006674.LOG files, because the Ònext log file to be readÓ on the query status shows 6668, and the db2adutl output for the incremental at 3am shows 6674.

 

Therefore, we are going to copy log files from S0006668.LOG through S0006674.LOG.

Based on the timestamps in the DB_PROD log directory, we are also going to copy the next few logs up to and including the point in time we want to restore to (S0006675 and S0006676.  Maybe even S0006677.  Active logs are hard to pinpoint.  Apparently, db2logsForRfwd only shows DB2TSCHG.HIS info for archived logs, not active). 

 

If the logs have been archived, then they need to be retrieved from archives, and copied into the DB_COPY path.

 

8.    Rollforward the Database:

 

Note the value in the Òdb2 rollforward database DB_COPY query statusÓ command of ÒLast Committed Transaction:Ó

Last committed transaction             = 2017-01-30-08.26.42.000000 UTC

 

It is showing UTC time, but our server is on PST time.  So DB2 has restored this database to 1/30/2017 8:26am UTC time, which is the same as 1/29/2017 23:00 PST, when the full backup was taken.

 

To determine the difference from UTC to local server time, issue this SQL:

 

Local server time:

select current timestamp from sysibm.sysdummy1

2017-02-07-09.25.58.727803

 

UTC time:

select current timestamp - current timezone from sysibm.sysdummy1

2017-02-07-17.26.12.939567

 

To determine the difference from UTC to local server time, issue this SQL:

select hour((current timestamp - current timezone) - (current timestamp)) from sysibm.sysdummy1

          8

 

Based on the above, UTC is eight hours ahead of current local server time (PST).

 

The rollforward command uses UTC time by default.  We want to roll forward to Jan 31, 2017 9am local time. 

Issue the rollforward command with the ÒUSING LOCAL TIMEÓ specification to avoid confusion:

db2 rollforward database DB_COPY to 2017-01-31-09.00.00.000000 USING LOCAL TIME

 

While the Rollforward is running, you can check on its progress from another session:

db2 list utilities

 

ID                               = 37180

Type                             = ROLLFORWARD RECOVERY

Database Name                    = DB_COPY

Member Number                    = 0

Description                      = Database Rollforward Recovery

Start Time                       = 01/31/2017 14:29:00.724454

State                            = Executing

Invocation Type                  = User

 

 

After the Rollforward has completed, check the progress of the Rollforward.   You can see the ÒLast Committed TransactionÓ that was applied was at 16:59 UTC, which is around 9am local time.  This means we have rolled forward through the logs to the desired point in time:

db2 rollforward database DB_COPY query status

 

 

                                 Rollforward Status

 

 Input database alias                   = DB_COPY

 Number of members have returned status = 1

 

 Member ID                              = 0

 Rollforward status                     = DB  working

 Next log file to be read               = S0006677.LOG

 Log files processed                    = S0006668.LOG - S0006676.LOG

 Last committed transaction             = 2017-01-31-16.59.08.000000 UTC

 

 

Complete the Rollforward process.  The warning message is OK:

db2 rollforward database DB_COPY stop

SQL1271W  Database "DB_COPY" is recovered but one or more table spaces are

offline on members or nodes "0".

 

9.     Connect to the Database, and Confirm the Data.

Congratulations!  You have restored selected tablespaces to a copy of a database to a point in time.

Happy Recovery!

 

HOME | Search