Roll forward session - CLP example
The ROLLFORWARD DATABASE command allows multiple operations to be specified at a time, separated by keywords AND. For example, to roll forward to the end of the log and finish, you can use the following separate commands:
db2 rollforward db sample to end of logs db2 rollforward db sample complete
The combination is:
db2 rollforward db sample to end of logs and complete
Although these two methods are equivalent, it is recommended that you complete such operations in two steps. It is important to verify that the rollforward operation is progressing as expected before stopping to avoid losing any logs.
If the rollforward command encounters an error, the rollforward operation cannot be completed. In this case, the error is returned so that you can correct the error and reissue the above command. However, if the error cannot be corrected, the rollforward can be forced by issuing the following command:
db2 rollforward db sample complete
This command makes the database online and restores to the log point before the failure occurs.
Example 2
Roll the database forward to the end of the log (two table spaces have been restored):
db2 rollforward db sample to end of logs db2 rollforward db sample to end of logs and stop
These two statements are equivalent. You can roll forward to the end of the log without AND STOP or AND COMPLETE table spaces. No table space name is required. If not specified, all table spaces requiring rollforward recovery will be included. If only a subset of these table spaces will be rolled forward, their names must be specified.
Example 3
After restoring three table spaces, roll one forward to the end of the log and the other two forward to a certain point in time. All operations are performed online:
db2 rollforward db sample to end of logs tablespace(TBS1) online db2 rollforward db sample to 1998-04-03-14.21.56 and stop tablespace(TBS2, TBS3) online
It should be noted that the two rollforward operations cannot run concurrently. The second command can only be invoked after the first rollforward operation has been successfully completed.
Example 4
After restoring the database, roll forward to a certain point in time and use OVERFLOW LOG PATH to specify the user exit directory to save the archived logs:
db2 rollforward db sample to 1998-04-03-14.21.56 and stop overflow log path (/logs)
Example 5
In the following example, there is a database called sample. Back up the database and include the recovery log in the backup image; restore the database; and then roll the database forward to the end of the backup timestamp.
Back up the database and include recovery logs in the backup image:
db2 backup db sample online include logs
Restore the database using a backup image:
db2 restore db sample
Roll the database forward to the end of the backup timestamp:
db2 rollforward db sample to end of backup
Example 6 (Partitioned database environment)
There are three database partitions: 0, 1, and 2. Define table space TBS1 on all database partitions and table space TBS2 on database partitions 0 and 2. After restoring the database on database partition 1 and TBS1 on database partition 0 and 2, roll forward the database on database partition 1:
db2 rollforward db sample to end of logs and stop
This returns a warning SQL1271 (the database has been restored, but one or more table spaces on database partitions 0 and 2 are offline).
db2 rollforward db sample to end of logs
This command rolls forward TBS1 on database partitions 0 and 2. In this case, the clause TABLESPACE(TBS1) is optional.
Example 7 (Partitioned database environment)
In the following example, there is a partitioned database called sample. Backup all database partitions using a single system view; restore the database on all database partitions; roll the database forward to the end of the backup timestamp.
Perform single system view (SSV) backup:
db2 backup db sample on all nodes online include logs
Restore the database on all database partitions:
db2_all "db2 restore db sample taken at 1998-04-03-14.21.56"
Roll the database forward to the end of the backup timestamp:
db2 rollforward db sample to end of backup on all nodes
Example 8 (Partitioned database environment)
In the following example, there is a partitioned database called sample. Using db2_all, backup all database partitions by one command; restore the database on all database partitions; and roll the database forward to the end of the backup timestamp.
Using db2_all, backup all database partitions by one command:
db2_all "db2 backup db sample include logs to /shared/dir/"
Restore the database on all database partitions:
db2_all "db2 restore db sample from /shared/dir/"
Roll the database forward to the end of the backup timestamp:
db2 rollforward db sample to end of backup on all nodes
Example 9 (Partitioned database environment)
After restoring table space TBS1 only on database partitions 0 and 2, roll forward TBS1 on database partitions 0 and 2:
db2 rollforward db sample to end of logs
Ignore database partition 1.
db2 rollforward db sample to end of logs tablespace(TBS1)
This command failed because TBS1 was not ready for rollforward recovery on database partition 1. Report SQL4906N.
db2 rollforward db sample to end of logs on dbpartitionnums (0, 2) tablespace(TBS1)
Successful completion.
db2 rollforward db sample to 1998-04-03-14.21.56 and stop tablespace(TBS1)
This command failed because TBS1 was not ready to roll forward recovery on database partition 1; all segments must roll forward together.
Note: The dbpartitionnum clause will not be accepted after the table space is rolled forward to a certain point in time. Roll-forward operations must be performed on all database partitions where the table space resides.
After restoring TBS1 on database partition 1:
db2 rollforward db sample to 1998-04-03-14.21.56 and stop tablespace(TBS1)
Successful completion.
Example 10 (Partitioned database environment)
Restore table space on all database partitions and roll forward to PIT2 without specifying AND STOP. The rollforward operation is still in progress. Cancel and roll forward to PIT1:
db2 rollforward db sample to pit2 tablespace(TBS1) db2 rollforward db sample cancel tablespace(TBS1) ** restore TBS1 on all dbpartitionnums ** db2 rollforward db sample to pit1 tablespace(TBS1) db2 rollforward db sample stop tablespace(TBS1)
Example 11 (Partitioned database environment)
Roll forward to restore table spaces on eight database partitions (3 to 10) listed in the db2nodes.cfg file:
db2 rollforward database dwtest to end of logs tablespace (tssprodt)
The rollforward recovery to the end of the log (not the point of time) was successfully completed. It is not necessary to specify the database partition where the table space is located. The utility defaults to the db2nodes.cfg file.
Example 12 (Partitioned database environment)
Roll forward to restore six small table spaces on a single database partition database partition group (on database partition 6):
db2 rollforward database dwtest to end of logs on dbpartitionnum (6) tablespace(tsstore, tssbuyer, tsstime, tsswhse, tsslscat, tssvendor)
The rollforward recovery to the end of the log (not the point of time) was successfully completed.
Example 13 (partition table - roll forward to the end of the log on all data partitions)
Partitioned tables are created using table spaces tbsp1, tbsp2 and tbsp3 and indexed in tbsp0. Later, users added data partitions to tables in tbsp4 and connected data partitions from tables in tbsp5. All table spaces can be rolled forward to the end of the log.
db2 rollforward db PBARDB to END OF LOGS and stop tablespace(tbsp0, tbsp1, tbsp2, tbsp3, tbsp4, tbsp5)
Successful completion.
Example 14 (Partitioned tables - roll forward to the end of the log on a table space)
Initially, partitioned tables were created using table spaces tbsp1, tbsp2 and tbsp3 and indexed in tbsp0. Later, users added data partitions to tables in tbsp4 and connected data partitions from tables in tbsp5. Table space tbsp4 is damaged and requires recovery and rollforward to the end of the log.
db2 rollforward db PBARDB to END OF LOGS and stop tablespace(tbsp4)
Successful completion.
Example 15 (Partition Table - Roll forward to a point in time on all data partitions, including those that have been added, connected, disassembled, or indexed)
Partitioned tables are created using table spaces tbsp1, tbsp2 and tbsp3 and indexed in tbsp0. Later, users added data partitions to tables in tbsp4, connected data partitions from tables in tbsp5, and separated data partitions from tbsp1. Users roll forward to PIT operations on all table spaces used by partitioned tables, including those specified in the INDEX IN clause.
db2 rollforward db PBARDB to 2005-08-05-05.58.53 and stop tablespace(tbsp0, tbsp1, tbsp2, tbsp3, tbsp4, tbsp5)
Successful completion.
Example 16 (Partitioned tables - roll forward to PIT on a small portion of table space)
Partitioned tables are created using three table spaces (tbsp1, tbsp2 and tbsp3). Later, the user detached all data partitions from tbsp3. Only roll forward to PIT on tbsp1 and tbsp2 is allowed.
db2 rollforward db PBARDB to 2005-08-05-06.02.42 and stop tablespace( tbsp1, tbsp2)