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. The example above 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 x 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) SMS user data table space (relative container)
- USERSP2 (2) DMS user data table space (absolute container /usersp2)
- USERSP3 (3) DMS user data table space (absolute container /usersp3)
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 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 '/newusersp2' 10000)
-
db2 set tablespace containers for 4 using (file '/newusersp3' 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 BKxy 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) SMS user data table space
- USERSP2 (2) DMS user data table space
- USERSP3 (3) 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 and USERSP3
are 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 and USERSP3 using
the DROP TABLESPACE command, otherwise, the backup will fail.
To
restore USERSP2 and USERSP3 at a later time, you need to reissue a
database restore from BK1.