https://www.ibm.com/docs/en/db2/11.1?topic=commands-rollforward-database
Last Updated: 2022-06-17
The ROLLFORWARD DATABASE command recovers a database by applying transactions recorded in the database log files. The ROLLFORWARD DATABASE command can be run after a database or a table space backup image was restored, or if any table spaces were taken offline by the database due to a media error.
The database must be recoverable (that is, the logarchmeth1 or logarchmeth2 database configuration parameters must be set to a value other than OFF) before the database can be recovered with rollforward recovery.
In a partitioned database environment, this command can be invoked only from the catalog partition but each partition participates in the rollforward operation. A database or table space rollforward operation to a specified point in time affects all database partitions that are listed in the db2nodes.cfg file. A database or table space rollforward operation to the end of logs affects the database partitions that are specified. If no database partitions are specified, it affects all database partitions that are listed in the db2nodes.cfg file; if rollforward recovery is not needed on a particular partition, that partition is ignored.
In a Db2® pureScale® environment, this command can be issued from any member, and online table space-level rollforward operation can be performed while other members are online. Unlike in partitioned database environments in which users can choose to rollforward through a subset of the database partitions, in a Db2 pureScale environment the logs from all members are automatically applied.
If a rollforward operation is in progress on a member when it fails, the ROLLFORWARD command can be reissued from any member. The rollforward resumes from where it was left off when the original member failed.
For partitioned tables, you are also required to roll forward related table spaces to the same point in time. This requirement applies to table spaces that contain data partitions of a table. If a single table space contains a portion of a partitioned table, rolling forward to the end of the logs is still allowed.
It is not possible to roll forward through log files created on a differentDb2 release version. This restriction is an important consideration when upgrading to a new Db2 database release version.
One of the following authorities:
None. This command establishes an exclusive database connection.
ROLLFORWARDDATABASEDBdatabase-aliasUSERusernameUSINGpasswordTOisotimeON ALL DBPARTITIONNUMSUSING UTC TIMEUSING LOCAL TIMEEND OF BACKUPON ALL DBPARTITIONNUMSEND OF LOGSOn Database Partition clauseAND COMPLETEAND STOPCOMPLETESTOPCANCELQUERY STATUSUSING UTC TIMEUSING LOCAL TIMEOn Database Partition clauseTABLESPACEONLINE(,tablespace-name)ONLINEOVERFLOW LOG PATH(log-directory,Log Overflow clause)NORETRIEVERECOVER DROPPED TABLEdrop-table-idTOexport-directory
On Database Partition clause
ONALL DBPARTITIONNUMSEXCEPTDatabase Partition List clauseDatabase Partition List clause
Database Partition List clause
DBPARTITIONNUMDBPARTITIONNUMS(,db-partition-number1TOdb-partition-number2)
Log Overflow clause
,log-directoryON DBPARTITIONNUMdb-partition-number1
This value is specified as a time stamp, a 7-part character string that identifies a combined date and time. The format is yyyy-mm-dd-hh.mm.ss
(year, month, day, hour, minutes, seconds), expressed in Coordinated Universal Time (UTC, formerly known as GMT). UTC helps to avoid having the same time stamp associated with different logs (because of a change in time associated with daylight saving time, for example). The time stamp in a backup image is based on the local time at which the backup operation started. The CURRENT TIMEZONE special register specifies the difference between UTC and local time at the application server. The difference is represented by a time duration (a decimal number in which the first two digits represent the number of hours, the next two digits represent the number of minutes, and the last two digits represent the number of seconds). Subtracting CURRENT TIMEZONE from a local time converts that local time to UTC.
Note:
If an END OF LOGS rollforward is attempted, you cannot switch to a point-in-time (PIT) rollforward. To rollforward to a PIT when a previous END OF LOGS rollforward is in progress, you must redo the restore and then run the rollforward command.
db2 rollforward db sample to end of logs and complete
. When rolling table spaces forward to a point in time, the table spaces are placed in backup pending state.This option cannot be used to cancel a rollforward operation that is actually running. It can only be used to cancel a rollforward operation that is in progress but not actually running at the time. A rollforward operation can be in progress but not running if:
Use this option with caution, and only if the rollforward operation that is in progress cannot be completed because some of the table spaces have been put in rollforward pending state or in restore pending state. When in doubt, use the LIST TABLESPACES command to identify the table spaces that are in rollforward in progress state, or in rollforward pending state.
Note: There might be a gap between Log files processed and Next log file to be read. This reflects the existence of inflight transactions, so the log files starting from the oldest inflight transaction are still required to be processed in the next round of rollforward, and these log files should not be removed from the log path.
yyyy-mm-dd-hh.mm.ss
) suffixed by either "UTC" or "Local" (see USING LOCAL TIME). This time stamp marks the last transaction committed after the completion of rollforward recovery. The time stamp applies to the database. For table space rollforward recovery, it is the time stamp of the last transaction committed to the database.QUERY STATUS is the default value if the TO, STOP, COMPLETE, or CANCEL clauses are omitted. If TO, STOP, or COMPLETE was specified, status information is displayed if the command has completed successfully. If individual table spaces are specified, they are ignored; the status request does not apply only to specified table spaces.
For partitioned tables, point in time roll forward of a table space containing any piece of a partitioned table must also roll forward all of the other table spaces in which that table resides to the same point in time. The table spaces containing the index partitions are included in the list of pieces of a partitioned table. Roll forward to the end of the logs for a single table space containing a piece of a partitioned table is still allowed.
If a partitioned table has any attached or detached data partitions, then PIT rollforward must include all table spaces for these data partitions as well. To determine if a partitioned table has any attached, detached, or dropped data partitions, query the Status field of the SYSDATAPARTITIONS catalog table.
Because a partitioned table can reside in multiple table spaces, it will generally be necessary to roll forward multiple table spaces. Data that is recovered via dropped table recovery is written to the export directory specified in the ROLLFORWARD DATABASE command. It is possible to roll forward all table spaces in one command, or do repeated roll forward operations for subsets of the table spaces involved. If the ROLLFORWARD DATABASE command is done for one or a few table spaces, then all data from the table that resided in those table spaces will be recovered. A warning will be written to the notify log if the ROLLFORWARD DATABASE command did not specify the full set of the table spaces necessary to recover all the data for the table. Allowing rollforward of a subset of the table spaces makes it easier to deal with cases where there is more data to be recovered than can fit into a single export directory.
db2 rollforward db sample to end of logs
db2 rollforward db sample complete
Alternatively, you can use the AND parameter to combine the two operations, as follows:
db2 rollforward db sample to end of logs and complete
However, you should perform the operations in two steps. Before you stop the rollfoward operation, it is important to verify that it progressed as you expected and that no logs are missing. This is especially important if a bad log is found during rollforward recovery, and the bad log is interpreted to mean the “end of logs”
. In such cases, an undamaged backup copy of that log might be used to continue the rollforward operation through more logs. However, if the rollforward AND STOP option is used, and the rollforward encounters an error, the error is returned to you. In this case, the only way to force the rollforward to stop and come online despite the error (that is, to come online at that point in the logs before the error) is to issue the ROLLFORWARD STOP command.
db2 rollforward db sample to end of logs
db2 rollforward db sample to end of logs and stop
These two statements are equivalent. Neither AND STOP or AND COMPLETE is needed for table space rollforward recovery to the end of the logs. Table space names are not required. If not specified, all table spaces requiring rollforward recovery will be included. If only a subset of these table spaces is to be rolled forward, their names must be specified.
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
Two rollforward operations cannot be run concurrently. The second command can only be invoked after the first rollforward operation completes successfully.
db2 rollforward db sample to 1998-04-03-14.21.56 and stop
overflow log path (/logs)
db2 rollforward db sample to end of logs and stop
This returns warning SQL1271 (“Database is recovered but one or more table spaces are offline on database partition(s) 0 and 2.”
).
db2 rollforward db sample to end of logs
This rolls TBS1 forward on database partitions 0 and 2. The clause TABLESPACE(TBS1) is optional in this case.
db2 rollforward db sample to end of logs
Database partition 1 is ignored.
db2 rollforward db sample to end of logs tablespace(TBS1)
The following command fails because TBS1 is not ready for rollforward recovery on database partition 1. SQL4906N is issued.
db2 rollforward db sample to end of logs on dbpartitionnums (0, 2)
tablespace(TBS1)
The following command runs successfully:
db2 rollforward db sample to 1998-04-03-14.21.56 and stop
tablespace(TBS1)
The following command fails because TBS1 is not ready for rollforward recovery on database partition 1; all pieces must be rolled forward together. With table space rollforward recovery to a point in time, the database partition parameter is not accepted. The rollforward operation must take place on all the database partitions on which the table space is located.
db2 rollforward db sample to 1998-04-03-14.21.56 and stop
tablespace(TBS1)
This completes successfully.
db2 rollforward db sample to pit2 tablespace(TBS1)
db2 rollforward db sample cancel tablespace(TBS1)
** restore TBS1 on all database partitions **
db2 rollforward db sample to pit1 tablespace(TBS1)
db2 rollforward db sample stop tablespace(TBS1)
db2 rollforward database dwtest to end of logs tablespace (tssprodt)
This operation to the end of logs (not point in time) completes successfully. The database partitions on which the table space resides do not have to be specified. The utility defaults to the db2nodes.cfg file.
db2 rollforward database dwtest to end of logs on dbpartitionnum (6)
tablespace(tsstore, tssbuyer, tsstime, tsswhse, tsslscat, tssvendor)
This operation to the end of logs (not point in time) completes successfully.
db2 rollforward db sample to end of backup and complete
db2 restore database sample... logtarget /backup_logs
In all environments, use the following command to roll forward the database to the end of the backup image using the logs restored from the online backup image:
db2 rollforward database sample to end of backup and stop overflow log path (/backup_logs)
The archive log path (specified by LOGARCHMETH1 or LOGARCHMETH2) is configured to use DISK method, such as DISK:/some_logarch_path/.
In all environments, use the following command to roll forward the database using logs from the archive log path directly
db2 rollforward database sample to end of logs and stop overflow log path (/some_logarch_path/
This prevents Db2 from retrieving log files and placing them in the local retrieve location. This improves performance by avoiding the cost of additional I/O copy operations.
An alternative set of logs can be supplied to the ROLLFORWARD command using the OVERFLOW LOG PATH parameter. To provide log files, you must create the necessary set of paths based on the database environment and populate these paths with the log files required for the ROLLFORWARD command.
Environment-specific commands are used to roll forward a database:
mkdir -p /overflow
or
mkdir -p /overflow/NODE0000/LOGSTREAM0000
With the path created and populated, use the following command to roll forward the database using the logs in the overflow log path:
db2 rollforward database sample to end of logs overflow log path (/overflow)
After verifying that the ROLLFORWARD command was sufficiently completed, use the following command to complete the rollforward operation:
db2 rollforward database sample to end of logs and stop overflow log path (/overflow)
To provide an alternate set of logs and use the default overflow log path for multiple partitions, you can create paths under the default overflow log path for each partition. You also need to append a corresponding database partition number and log stream ID to each path.
Assuming that there are four partitions numbered 0, 1, 2, and 3, create the following paths:
mkdir -p /overflow/NODE0000/LOGSTREAM0000
mkdir -p /overflow/NODE0001/LOGSTREAM0001
mkdir -p /overflow/NODE0002/LOGSTREAM0002
mkdir -p /overflow/NODE0003/LOGSTREAM0003
With the paths created and populated, use the following command to roll forward the database using the logs in the default overflow log path:
db2 rollforward database sample to end of logs overflow log path (/overflow)
After verifying that the ROLLFORWARD command was sufficiently completed, use the following command to complete the rollforward operation:
db2 rollforward database sample to end of logs and stop overflow log path (/overflow)
You might not able to use the default overflow log path for any database partition. You can provide an alternate set of logs and override the default overflow log path.
To override the default overflow log path, each partition is treated as a single partition environment. Create an override path, with or without a database partition number and log stream ID appended.
Assume there are four partitions numbered 0, 1, 2, and 3. To override the default overflow log path for partition 1 with /node1, and to override the default overflow log path for partition 3 with /node3, create the following paths:
mkdir -p /overflow/NODE0000/LOGSTREAM0000
mkdir -p /node1 OR mkdir -p /node1/NODE0001/LOGSTREAM0001
mkdir -p /overflow/NODE0002/LOGSTREAM0002
mkdir -p /node3 OR mkdir -p /node3/NODE0003/LOGSTREAM0003
With the paths created and populated, use the following command to roll forward the database using the logs in the default overflow log path and override log paths:
db2 rollforward database sample to end of logs on all dbpartitionnums
overflow log path (/overflow, /node1 on dbpartitionnum 1, /node3 on dbpartitionnum 3)
After verifying that the ROLLFORWARD command was sufficiently completed, use the following command to complete the rollforward operation:
db2 rollforward database sample to end of logs on all dbpartitionnums and stop
overflow log path
(/overflow, /node1 on dbpartitionnum 1, /node3 on dbpartitionnum 3)
To provide an alternate set of logs, create paths with a corresponding database partition number and log stream ID appended.
Assuming that there are three members (log streams) numbered 0, 1, and 2, create the following paths:
mkdir -p /overflow/NODE0000/LOGSTREAM0000
mkdir -p /overflow/NODE0000/LOGSTREAM0001
mkdir -p /overflow/NODE0000/LOGSTREAM0002
With the paths created and populated, use the following command to roll forward the database using the logs in the default overflow log path:
db2 rollforward database sample to end of logs overflow log path (/overflow)
After verifying that the ROLLFORWARD command was sufficiently completed, use the following command to complete the rollforward:
db2 rollforward database sample to end of logs and stop overflow log path (/overflow)
Note: When a log file is present in both the overflow log path and the active log path, the log file in the active log path is used.
If restoring from an image that was created during an online backup operation, the specified point in time for the rollforward operation must be later than the time at which the online backup operation completed. If the rollforward operation is stopped before it passes this point, the database is left in rollforward pending state. If a table space is in the process of being rolled forward, it is left in rollforward in progress state.
If one or more table spaces are being rolled forward to a point in time, the rollforward operation must continue at least to the minimum recovery time, which is the last update to the system catalogs for this table space or its tables. The minimum recovery time (in Coordinated Universal Time, or UTC) for a table space can be retrieved using the LIST TABLESPACES SHOW DETAIL command. In a Db2 pureScale environment, the LIST TABLESPACES command is deprecated; use the following monitoring UDF: SELECT * FROM TABLE( SYSPROC.MON_GET_TABLESPACE('TBSPACE_1,0) )
In a Db2 pureScale environment, ensure that there is adequate free disk space in the retrieval path before starting a rollforward operation. This allows the operation to retrieve the larger number of files from the archive, as required in a Db2 pureScale environment, without affecting performance. Use the following formula to calculate how much space you need to retrieve the value of the active log space for all members: (logprimary + logsecond) * number of members
.
If you want to roll forward a table space that contains a system-period temporal table or bitemporal table to a point in time, the ROLLFORWARD DATABASE command must include the name of the table space that contains the associated history table. You can roll forward the table space for the temporal table or the table space for the history table individually, but only to the end of logs.
Rolling databases forward might require a load recovery by using tape devices. If prompted for another tape, you can respond with one of the following inputs:
If the ROLLFORWARD DATABASE command cannot find the next log that it needs, the log name is returned in the SQLCA, and rollforward recovery stops. If no more logs are available, use the STOP parameter to terminate rollforward recovery. Incomplete transactions are rolled back to ensure that the database or table space is left in a consistent state.
If a database rollforward detects a log record for a table space schema transport, the corresponding transported table space is taken offline and moved into drop pending state. This occurs because of the absence of the complete logs of transported table spaces to rebuild transported table spaces and their contents. You can take a full backup of the target database after the transport is complete so that a subsequent rollforward operation does not pass the point of schema transport in the log stream.
Note: Rolling forward through a redistribute operation cannot restore the database content since log records are not recorded for data redistribution. See the “REDISTRIBUTE DATABASE PARTITION GROUP command”
.
If you extracted log files from a database backup, and you plan to run the ROLLFORWARD command with the TO END OF BACKUP clause for that database, use the NORETRIEVE option. The NORETRIEVE option is needed because all the required log files are available and no files need to be retrieved from the archive location. Without using the NORETRIEVE option, some archived log files can be retrieved which are not needed for this rollforward. If the archive locations contain log files from other log chains, the ROLLFORWARD results in SQLCODE -1265.
自然语言处理爱好者,欢迎交流。QQ: 7214218