A case analysis of DB2 database table space redirection recovery

First, send redirect Restore command

DB2 RESTORE DB olddb from ' C:\OLDDBbak ' taken at 20150717164847 to ' C: ' Into newdb REDIRECT

Where, OLDDB is the old database, the database name of the backup, NEWDB is the new database name, do not have to create in advance, C:\OLDDBbak is the directory where the backup files are placed, 20150717164847 is

The timestamp of the backup file, depending on the name of the backup file Olddb.0.db2.node0000.catn0000.20150717164847.001,to "C:" is the path to the new database, redirect is a redirect recovery.

Second, redirect table Space

2.1 List Table spaces

DB2 LIST tablespaces

You can see the table space status of the current database


Table Space id = 2
Name = Systoolstmpspace
Type = System Admin Space
Content = User temporary data
Status = 0x2000100
Detailed Explanation:
Undo Suspend
Can define the memory

Table Space id = 3
Name = tbs_01
Type = Database Admin space
Content = All persistent data. Large table space.
Status = 0x2001100
Detailed Explanation:
Undo Suspend
Storage must be defined
Can define the memory



You can see that table Space 3 is the "must-define memory" state, which is going to be redirected.

2.2 View the table space details to be redirected

DB2 LIST tablespace containers for 3 show DETAIL

The results are as follows


Table space container for table Space 3

Container id = 0
Name = E:\OLDDB\TBS\CONTAINER_01_01
Type = File
Total Pages = 25600
Available pages = 25568
accessible = No
Container id = 1
Name = E:\OLDDB\TBS\CONTAINER_01_02
Type = File
Total Pages = 25600
Available pages = 25568
accessible = No



As you can see, the path to this table space is inaccessible to the target system and is redirected.

2.3 for redirection

This table space has two containers that can be redirected separately

DB2 SET tablespace containers for 3 USING (FILE "C:\NEWDB\TBS\CONTAINER_01_01" 25600,file "C:\NEWDB\TBS\CONTAINER_01_02" 25600)

You can also merge redirects

DB2 SET tablespace containers for 3 USING (FILE "C:\NEWDB\TBS\CONTAINER_01" 51200)

Page size is greater than or equal to the original size

This is a redirection of the type of database management space, and if the system manages space types, the commands are as follows:

DB2 SET tablespace containers for USING (PATH "C:\NEWDB\TBS\TBS_SYSTEM_04")

There may be a lot of table spaces to redirect, and the last command to execute is the same as the actual environment:


DB2 SET tablespace containers for 3 USING (FILE "C:\NEWDB\TBS\CONTAINER_01_01" 25600,file "C:\NEWDB\TBS\CONTAINER_01_02" 25600)
DB2 SET tablespace containers for 4 USING (FILE "C:\NEWDB\TBS\CONTAINER_02" 25600)
DB2 SET tablespace containers for 5 USING (FILE "C:\NEWDB\TBS\CONTAINER_03" 51200)
DB2 SET tablespace containers for 6 USING (FILE "C:\NEWDB\TBS\CONTAINER_04" 64000)
DB2 SET tablespace containers for 7 USING (FILE "C:\NEWDB\TBS\CONTAINER_05" 153600)
DB2 SET tablespace containers for 8 USING (FILE "C:\NEWDB\TBS\CONTAINER_06" 51200)
DB2 SET tablespace containers for 9 USING (FILE "C:\NEWDB\TBS\CONTAINER_07" 25600)
DB2 SET tablespace containers for USING (PATH "C:\NEWDB\TBS\TBS_SYSTEM_04")
DB2 SET tablespace containers for one USING (PATH "C:\NEWDB\TBS\TBS_SYSTEM_08")
DB2 SET tablespace containers for USING (PATH "C:\NEWDB\TBS\TBS_USERTMP_04")
DB2 SET tablespace containers for USING (PATH "C:\NEWDB\TBS\TBS_USERTMP_08")



Finally, perform a 2.1-step check to see if there is a tablespace with "must-define storage"

Third, complete the redirect recovery

DB2 RESTORE DB olddb CONTINUE



DB2 Database Redirect Table space Recovery instance

DB2 RESTORE Database Sino into JINAN3 redirect without rolling forward

DB2 "Set tablespace containers for 2 using (file '/opt/data/jinan3/ct/userspace1 ' 65536, file '/OPT/DATA/JINAN3/CT/USERSP Ace2 ' 65536, file '/opt/data/jinan3/ct/userspace3 ' 65536, file '/opt/data/jinan3/ct/userspace4 ' 65536, file '/opt/data/ Jinan3/ct/userspace5 ' 65536) '
DB2 "Set tablespace containers for 3 using (path '/opt/data/jinan3/ct_temp_sys_16 ')"
DB2 "Set tablespace containers for 4 using (path '/opt/data/jinan3/ct_temp_user_16 ')"
DB2 Restore DB Sino continue
------------The actual process--------Note that the redirected tablespace location is defined by its actual requirements-----------------------
F:/2008_1_21>DB2 Restore db gzk_1_5 into Gzk_1 redirect without rolling forward

F:/2008_1_21>DB2 "Set tablespace containers for 3 using (file ' E:/db2/gzk_1/data
8/data8-1.dat ' 64000, file ' E:/db2/gzk_1/data8/data8-2.dat ' 64000) '
db20000i SET tablespace Containers command completed successfully.

F:/2008_1_21>DB2 "Set tablespace containers for 4 using (file ' E:/db2/gzk_1/data
16/data16-1.dat ' 32000) '
db20000i SET tablespace Containers command completed successfully.

F:/2008_1_21>DB2 "Set tablespace containers for 5 using (file ' E:/db2/gzk_1/inde
X8/index8-1.dat ' 64000) '
db20000i SET tablespace Containers command completed successfully.

F:/2008_1_21>DB2 "Set tablespace containers for 6 using (file ' E:/db2/gzk_1/long
8/long8-1.dat ' 64000) '
db20000i SET tablespace Containers command completed successfully.

F:/2008_1_21>DB2 "Set tablespace containers for 7 using (path ' e:/db2/gzk_1/temp
8 ') "
db20000i SET tablespace Containers command completed successfully.

F:/2008_1_21>DB2 "Set tablespace containers for 8 using (path ' e:/db2/gzk_1/temp
16 ') "
db20000i SET tablespace Containers command completed successfully.

F:/2008_1_21>DB2 Restore DB gzk_1_5 continue
The db20000i RESTORE Database command completed successfully.