This topic provides a number of examples of rebuild operations.
Scenario 1
In the following examples, there
is a recoverable database called MYDB with the following table spaces
in it:
- SYSCATSPACE (system catalogs)
- USERSP1 (user data table space)
- USERSP2 (user data table space)
- USERSP3 (user data table space)
The following backups have been taken:
- BK1 is a backup of SYSCATSPACE and USERSP1
- BK2 is a backup of USERSP2 and USERSP3
- BK3 is a backup of USERSP3
- Example 1
The following rebuilds the entire database to the most recent
point in time:
- Issue a RESTORE DATABASE command with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK3 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and all table
spaces are in NORMAL state.
- Example 2
The following rebuilds just SYSCATSPACE and USERSP2 to a point
in time (where end of BK3 is less recent than the point in time,
which is less recent than end of logs):
- Issue a RESTORE DATABASE command with the REBUILD option
and specify the table spaces you want to include.
db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP2)
taken at BK2 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
PIT option (this assumes all logs have been saved and are
accessible):
db2 rollforward db mydb to PIT
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and only SYSCATSPACE
and USERSP2 are in NORMAL state. USERSP1 and USERSP3 are in RESTORE_PENDING
state.
To restore USERSP1 and USERSP3 at a later time, using
normal table space restores (without the
REBUILD option):
- Issue the RESTORE DATABASE command without the REBUILD option
and specify the table space you want to restore. First restore USERSPI:
db2 restore db mydb tablespace (USERSP1) taken at BK1 without prompting
- Then restore USERSP3:
db2 restore db mydb tablespace taken at BK3 without prompting
- Issue a ROLLFORWARD DATABASE command with the END
OF LOGS option and specify the table spaces to be restored
(this assumes all logs have been saved and are accessible):
db2 rollforward db mydb to end of logs tablespace (USERSP1, USERSP3)
The
rollforward will replay all logs up to the PIT and then stop for these
two table spaces since no work has been done on them since the first
rollforward.
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
- Example 3
The following rebuilds just SYSCATSPACE and USERSP1 to end
of logs:
- Issue a RESTORE DATABASE command with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in image
taken at BK1 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and only SYSCATSPACE
and USERSP1 are in NORMAL state. USERSP2 and USERSP3 are in RESTORE_PENDING
state.
- Example 4
In the following example, the backups BK1 and BK2 are no longer
in the same location as stated in the history file, but this is not
known when the rebuild is issued.
- Issue a RESTORE DATABASE command with the REBUILD option
, specifying that you want to rebuild the entire database to the
most recent point in time:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK3 without prompting
At this point, the
target image is restored successfully, but an error is returned from
the restore utility stating it could not find a required image.
- You must now complete the rebuild manually. Since the database
is in the rebuild phase this can be done as follows:
- Issue a RESTORE DATABASE command and specify
the location of the BK1 backup image:
db2 restore db mydb tablespace taken at BK1 from location
without prompting
- Issue a RESTORE DATABASE command and specify
the location of the BK2 backup image:
db2 restore db mydb tablespace (USERSP2) taken at BK2 from
location without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and all table
spaces are in NORMAL state.
- Example 5
In this example, table space USERSP3 contains independent data
that is needed for generating a specific report, but you do not want
the report generation to interfere with the original database. In
order to gain access to the data but not affect the original database,
you can use REBUILD to generate a new database
with just this table space and SYSCATSPACE. SYSCATSPACE is also required
so that the database will be connectable after the restore and roll
forward operations.
To build a new database with the most recent
data in SYSCATSPACE and USERSP3:
- Issue a RESTORE DATABASE command with the REBUILD option,
and specify that table spaces SYSCATSPACE and USERSP3 are to be restored
to a new database, NEWDB:
db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP3)
taken at BK3 into newdb without prompting
- Issue a ROLLFORWARD DATABASE command on NEWDB
with the TO END OF LOGS option (this assumes
all logs have been saved and are accessible):
db2 rollforward db newdb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db newdb stop
At this point the new database is connectable and only
SYSCATSPACE and USERSP3 are in NORMAL state. USERSP1 and USERSP2
are in RESTORE_PENDING state.
Note: If container paths are an issue
between the current database and the new database (for example, if
the containers for the original database need to be altered because
the file system does not exist or if the containers are already in
use by the original database) then you will need to perform a redirected
restore. This example assumes the default autostorage database paths
are used for the table spaces.
Scenario 2
In the following example, there
is a recoverable database called MYDB that has SYSCATSPACE and one
thousand user table spaces named Txxxx, where xxxx stands
for the table space number (for example, T0001). There is one full
database backup image (BK1)
- Example 6
The following restores all table spaces except T0999 and T1000:
- Issue a RESTORE DATABASE command with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in image except
tablespace (T0999, T1000) taken at BK1 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database will be connectable and all
table spaces except T0999 and T1000 will be in NORMAL state. T0999
and T1000 will be in RESTORE_PENDING state.
Scenario 3
The examples in this scenario
demonstrate how to rebuild a recoverable database using incremental
backups. In the following examples, there is a database called MYDB
with the following table spaces in it:
- SYSCATSPACE (system catalogs)
- USERSP1 (data table space)
- USERSP2 (user data table space)
- USERSP3 (user data table space)
The following backups have been taken:
- FULL1 is a full backup of SYSCATSPACE, USERSP1, USERSP2 and USERSP3
- DELTA1 is a delta backup of SYSCATSPACE and USERSP1
- INCR1 is an incremental backup of USERSP2 and USERSP3
- DELTA2 is a delta backup of SYSCATSPACE, USERSP1, USERSP2 and
USERSP3
- DELTA3 is a delta backup of USERSP2
- FULL2 is a full backup of USERSP1
- Example 7
The following rebuilds just SYSCATSPACE and USERSP2 to the
most recent point in time using incremental automatic restore.
- Issue a RESTORE DATABASE command with the REBUILD option.
The INCREMENTAL AUTO option is optional. The
restore utility will detect what the granularity of the image is and
use automatic incremental restore if it is required.
db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP2)
incremental auto taken at DELTA3 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and only SYSCATSPACE
and USERSP2 are in NORMAL state. USERSP1 and USERSP3 are in RESTORE_PENDING
state.
- Example 8
The following rebuilds the entire database to the most recent
point in time using incremental automatic restore.
- Issue a RESTORE DATABASE command with the REBUILD option.
The INCREMENTAL AUTO option is optional. The
restore utility will detect what the granularity of the image is and
use automatic incremental restore if it is required.
db2 restore db mydb rebuild with all tablespaces in database
incremental auto taken at DELTA3 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and all table
spaces are in NORMAL state.
- Example 9
The following rebuilds the entire database, except for USERSP3,
to the most recent point in time.
- Issue a RESTORE DATABASE command with the REBUILD option.
Although the target image is a non-incremental image, the restore
utility will detect that the required rebuild chain includes incremental
images and it will automatically restore those images incrementally.
db2 restore db mydb rebuild with all tablespaces in database except
tablespace (USERSP3) taken at FULL2 without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
Scenario 4
The examples in this scenario
demonstrate how to rebuild a recoverable database using backup images
that contain log files. In the following examples, there is a database
called MYDB with the following table spaces in it:
- SYSCATSPACE (system catalogs)
- USERSP1 (user data table space)
- USERSP2 (user data table space)
- Example 10
The following rebuilds the database with just SYSCATSPACE and
USERSP2 to the most recent point in time. There is a full online database
backup image (BK1), which includes log files.
- Issue a RESTORE DATABASE command with the REBUILD option:
db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP2)
taken at BK1 logtarget /logs without prompting
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs after the end
of BK1 have been saved and are accessible):
db2 rollforward db mydb to end of logs overflow log path (/logs)
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and only SYSCATSPACE
and USERSP2 are in NORMAL state. USERSP1 is in RESTORE_PENDING state.
- Example 11
The following rebuilds the database to the most recent point
in time. There are two full online table space backup images that
include log files:
- BK1 is a backup of SYSCATSPACE, using log files 10-45
- BK2 is a backup of USERSP1 and USERSP2, using log files 64-80
- Issue a RESTORE DATABASE command with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK2 logtarget /logs without prompting
The
rollforward operation will start at log file 10, which it will always
find in the overflow log path if not in the primary log file path.
The log range 46-63, since they are not contained in any backup image,
will need to be made available for roll forward.
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option, using the overflow log path for log
files 64-80:
db2 rollforward db mydb to end of logs overflow log path (/logs)
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and all table spaces
are in NORMAL state.
Scenario 5
In the following examples, there
is a recoverable database called MYDB with the following table spaces
in it:
- SYSCATSPACE (0), SMS system catalog (relative container)
- USERSP1 (1) DMS user data table space (absolute container /usersp2)
- USERSP2 (2) DMS user data table space (absolute container /usersp3)
The following backups have been taken:
- BK1 is a backup of SYSCATSPACE
- BK2 is a backup of USERSP1 and USERSP2
- BK3 is a backup of USERSP2
- Example 12
The
following rebuilds the entire database to the most recent point in
time using redirected restore.
- Issue a RESTORE DATABASE command with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK3 redirect without prompting
- Issue a SET TABLESPACE CONTAINERS command for
each table space whose containers you want to redefine. For example:
db2 set tablespace containers for 3 using (file '/newusersp1' 10000)
-
db2 set tablespace containers for 4 using (file '/newusersp2' 15000)
- Issue a RESTORE DATABASE command with the CONTINUE option:
db2 restore db mydb continue
- Issue a ROLLFORWARD DATABASE command with the TO
END OF LOGS option (this assumes all logs have been saved
and are accessible):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable and all table
spaces are in NORMAL state.
Scenario 6
In the following examples, there
is a database called MYDB with three database partitions:
- Database partition 1 contains table spaces SYSCATSPACE, USERSP1
and USERSP2, and is the catalog partition
- Database partition 2 contains table spaces USERSP1 and USERSP3
- Database partition 3 contains table spaces USERSP1, USERSP2 and
USERSP3
The following backups have been taken, where BK
xy represents
backup number
x on partition
y:
- BK11 is a backup of SYSCATSPACE, USERSP1 and USERSP2
- BK12 is a backup of USERSP2 and USERSP3
- BK13 is a backup of USERSP1, USERSP2 and USERSP3
- BK21 is a backup of USERSP1
- BK22 is a backup of USERSP1
- BK23 is a backup of USERSP1
- BK31 is a backup of USERSP2
- BK33 is a backup of USERSP2
- BK42 is a backup of USERSP3
- BK43 is a backup of USERSP3
- Example 13
The following rebuilds the entire database to the end of logs.
- On database partition 1, issue a RESTORE DATABASE command
with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK31 without prompting
- On database partition 2, issue a RESTORE DATABASE command
with the REBUILD option:
db2 restore db mydb rebuild with tablespaces in database taken at
BK42 without prompting
- On database partition 3, issue a RESTORE DATABASE command
with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK43 without prompting
- On the catalog partition, issue a ROLLFORWARD DATABASE command
with the TO END OF LOGS option (assumes all logs
have been saved and are accessible on all database partitions):
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
At this point the database is connectable on all database
partitions and all table spaces are in NORMAL state.
- Example 14
The following rebuilds SYSCATSPACE, USERSP1 and USERSP2 to
the most recent point in time.
- On database partition 1, issue a RESTORE DATABASE command
with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in database
taken at BK31 without prompting
- On database partition 2, issue a RESTORE DATABASE command
with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in image taken at
BK22 without prompting
- On database partition 3, issue a RESTORE DATABASE command
with the REBUILD option:
db2 restore db mydb rebuild with all tablespaces in image taken at
BK33 without prompting
Note: this command omitted
USERSP1, which is needed to complete the rebuild operation.
- On the catalog partition, issue a ROLLFORWARD DATABASE command
with the TO END OF LOGS option:
db2 rollforward db mydb to end of logs
- Issue a ROLLFORWARD DATABASE command with the STOP option:
db2 rollforward db mydb stop
The rollforward
succeeds and the database is connectable on all database partitions.
All table spaces are in NORMAL state, except USERSP3, which is in
RESTORE PENDING state on all database partitions on which it exists,
and USERSP1, which is in RESTORE PENDING state on database partition
3. When an attempt is made to access data in USERSP1 on database
partition 3, a data access error will occur. To fix this, USERSP1
will need to be recovered:
- On database partitions 3, issue a RESTORE DATABASE command,
specifying a backup image that contains USERSP1:
db2 restore db mydb tablespace taken at BK23 without prompting
- On the catalog partition, issue a ROLLFORWARD DATABASE command
with the TO END OF LOGS option and the AND
STOP option:
db2 rollforward db mydb to end of logs on dbpartitionnum (3) and stop
At this point USERSP1 on database partition 3 can have
its data accessed since it is in NORMAL state.
Scenario 7
In the following examples, there
is a
nonrecoverable database called MYDB with the following
table spaces:
- SYSCATSPACE (0), SMS system catalog
- USERSP1 (1) DMS user data table space
- USERSP2 (2) DMS user data table space
There is just one backup of the database, BK1:
- Example 15
The following demonstrates using rebuild on a nonrecoverable
database.
Rebuild the database using only SYSCATSPACE and USERSP1:
db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP1)
taken at BK1 without prompting
Following
the restore, the database is connectable. If you issue the LIST
TABLESPACES command or the MON_GET_TABLESPACE table function,
you see that the SYSCATSPACE and USERSP1 are in NORMAL state, while
USERSP2 is in DELETE_PENDING/OFFLINE state. You can now work with
the two table spaces that are in NORMAL state.
If
you want to do a database backup, you will first need to drop USERSP2
using the DROP TABLESPACE statement, otherwise, the backup will fail.
To restore USERSP2 at a later time, you need to reissue
a database restore from BK1.