There are two different types of recovery: repair and reset. The database administrator performs both with the utility statement RECOVER.
Depending on the possible backup units, the database administrator can choose between the following units for repair and reset:
The entire SESAM/SQL database, including the catalog space and all user spaces
The database's catalog space
Individual user space, space list or space set
A unit of several user spaces with a common time stamp can be designated as a space set. Spaces with the same time stamp are created by a backup with a COPY CATALOG statement or when a number of spaces are backed up by a joint COPY statement. The recovery of a space set as a unit is only possible if logging was not interrupted for any of the user spaces in this space set.
A space list designates a number of user spaces from the backup of an entire database or a space set which are grouped together for a joint RECOVER statement. The spaces in a space list must all have the same time stamp. The recovery of the spaces in a space list is only possible if logging was not interrupted for any of the user spaces in this space list.
To reduce the recovery times, you can also restrict the number of spaces included in RECOVER:
If you specify the SCOPE PENDING clause in RECOVER then the user spaces are repaired if they are identified as defective when opened.
SCOPE PENDING is not permitted for foreign copies or for RECOVER SPACESET ... TO or RECOVER SPACE space list TO.In the case of RECOVER CATALOG, the catalog space is always repaired and the user spaces are only repaired if they are identified as defective when opened.
In the case of RECOVER CATALOG ... TO, the catalog space is always reset. The user spaces are only reset if they are identified as defective when opened or if an inconsistency between catalog space and user space is detected from the point of view of the catalog. This type of inconsistency exists if the user space has been modified since the time of the backup to which the space is being reset.
Administer indexes in index spaces, i.e. in spaces which contain only indexes and are not included in logical data saving. You use two clauses to work with these index spaces.
GENERATE INDEX ON NO LOG INDEX SPACE
You can specify this clause in RECOVER CATALOG. When you specify this, the spaces that contain only indexes and are not involved in the logical data backup are reset and the indexes are reconstructed.NO INDEX
You can specify this clause when performing the reset of a user space, a space list or a space set. It is used to identify indexes as defective instead of reconstructing them when they have been made invalid through the reset of a space. This case occurs if the base table or the partitioned table and an associated index are located on different user spaces and not all the user spaces affected are reset simultaneously.
Repair
Repair is carried out using previously created SESAM backup copies of the database, the catalog space or a user space, a space list or a space set, together with the associated log files. The CAT-LOG files are the log files for the catalog space; the DA-LOG files are the log files for the user spaces.
The database administrator can use the INF mask of the utility monitor to output the related backup files in job variables. The database administrator can obtain information on existing SESAM backup copies of the catalog space from the CAT-REC file with the aid of the utility monitor. To find out what SESAM backup copies of user spaces are available, the database administrator checks the RECOVERY_UNITS catalog table, again with the aid of the utility monitor (see the “Utility Monitor” manual).
The repair returns the database, the catalog space or user space, a space list or space set to the state it was in prior to the occurrence of the error situation.
User spaces, the catalog space or the entire database can also be repaired by means of foreign copies (see section “Repair and reset using foreign copies”).
The repair of user spaces, the catalog space or of the entire database is also possible using a replication (see section “Using a replication to repair an original database”).
Repair of catalog space
When repairing the catalog space, SESAM/SQL uses the information from the CAT-REC file concerning the SESAM backup copy of the catalog space specified by the database administrator in RECOVER, and the information on the associated CAT-LOG files. SESAM/SQL then reads in this SESAM backup copy and applies the modifications logged in the CAT-LOG files.
The figure 18 outlines the repair of the catalog space.
Figure 18: Repair of the catalog space
Repair of a user space
When repairing a user space, a space list or a space set, SESAM/SQL retrieves from the RECOVERY_UNITS catalog table the information on the SESAM backup copy of each user space specified by the database administrator in RECOVER. In the DA-LOGS catalog table, SESAM/SQL finds the DA-LOG files in which changes made to the user space since the copy was made are logged. SESAM/SQL then reads in this SESAM backup copy and applies the modifications logged in the DA-LOG files.
The figure 19 outlines the repair of a user space.
Figure 19: Repair of a user space
Repair of the entire SESAM/SQL database
During a repair of the entire of the database, SESAM/SQL begins by repairing the catalog space on the basis of the SESAM backup copy specified in RECOVER. Using the metadata from the recovered catalog, SESAM/SQL then repairs all the database's user spaces from the latest SESAM backup copy of each user space. This means that the consistency of the database is always guaranteed after an orderly repair operation. If, however, SESAM/SQL aborts the repair of a user space (say, because a DA-LOG file is missing or corrupt) and “freezes” the user space in its current state, the database administrator can opt to proceed with repair or to render the database consistent in its frozen state.
If SESAM/SQL has aborted a repair operation due a missing or corrupt DA-LOG file, the database administrator can complete the recovery with RECOVER RESTART if he/she provides a suitable intact DA-LOG file. To do this, the database administrator must execute the utility statement RECOVER RESTART for each user space.
They can restore the consistency of the database in its frozen state by executing the utility statement RECOVER ADJUST for each user space. When RECOVER ADJUST is executed, SESAM/SQL restores consistency by means of the same adaptation mechanisms as when resetting individual user spaces (see “Database consistency after a reset”).
Reset
Reset as a means of recovering individual user spaces, a space list, a space set, the catalog space or the entire database is always an attractive option whenever logging was deactivated either entirely or temporarily for the backup unit in question and consequently repair is not possible
Equally, reset to the status of the last SESAM backup copies of the spaces in question is the only way of eliminating a database error caused by a defective user program. This also applies even if logging was active. The selective applying of the modifications to the DA-LOG files to mask data modifications caused by the defective user program is not possible.
User spaces, the catalog space or the entire database can also be reset by means of foreign copies (see section “Repair and reset using foreign copies”).
It is also possible to reset user spaces, the catalog space or the entire database on the basis of a replication (see section “Using a replication to repair an original database”).
Reset of user spaces
The database administrator uses the RECOVER TO utility statement to reset a user space, a space list or a space set.
The reset is performed on the basis of a previously created SESAM backup copy of a user space, a space list or a space set. If you choose a space list or a space set, all the spaces must have the same backup time stamp. The reset process then returns the user space or the user spaces of the space list or space set to the status which applied immediately before the SESAM backup copies were created.
CAUTION!
You cannot undo a RECOVER TO. A more recent state can no longer be established.
The database administrator determines what SESAM backup copies of user spaces are available by accessing the RECOVERY_UNITS catalog table with the utility monitor. The required SESAM backup copy can then be selected via the version number of the backup, the time stamp or the file name of the SESAM backup copy.
Resetting user spaces to a mark
A mark is a special entry in the catalog table RECOVERY_UNITS of the information schema. It has a time stamp and as a RECOVER_TYPE is given the string “MARK”. It represents a specified state of the database which can be restored on the basis of a previous backup by applying the changes logged in the logging files.
A mark is written when performing the utility functions LOAD OFFLINE, MIGRATE, IMPORT, RECOVER INDEX or REORG ... NEW ROW_IDS or for the SSL statement ALTER SPACE ... NO LOG. A database administrator can reset a user space to a mark with the utility statement RECOVER SPACE space USING rec_unit TO TIMESTAMP or in the utility monitor via the mask COP using this data. rec_unit can be a SESAM backup copy, a foreign copy or a replication here.
Example
You want to undo a LOAD OFFLINE for the space space.
The following steps are required:
Make the backup available.
In the catalog table RECOVERY_UNITS of the information schema, identify which entry contains the RECOVER_TYPE “MARK” and the RECOVERY_TIMESTAMP before_load with the time stamp of the LOAD.
Execute a RECOVER statement:
RECOVER SPACEspaceUSINGrec_unitTO TIMESTAMPbefore_load
If the functions LOAD OFFLINE, MIGRATE, IMPORT, RECOVER INDEX or REORG ... NEW ROW_IDS are aborted as a result of errors, this usually means that the mark has not yet been written. The space can then be reset to the state prior to the function call using RECOVER SPACE space USING. In the case of LOAD OFFLINE it is important to note that indexes can already be marked as defective at the time of abortion. These indexes are not be rebuilt by RECOVER SPACE space USING which means that RECOVER INDEX may still be required.
Reset of the entire SESAM/SQL database
The database administrator resets the entire database using the RECOVER CATALOG utility statement.
Two options are available here:
Resetting to a SESAM backup copy which was created earlier using RECOVER CATALOG TO:
This reset restores the database to the status which existed at the time the SESAM backup copy was created.
In this case SESAM/SQL first resets the catalog space on the basis of the SESAM backup copy specified in RECOVER CATALOG TO. The metadata of the reset catalog space is then used to repair all the database's user spaces on the basis of the last created SESAM backup copy for the individual user spaces. As a result, the consistency of the database is always guaranteed following a correctly performed repair.
Resetting to a (freely) selectable time using RECOVER CATALOG [USING ...] TO ANY timestamp:
This reset restores the database to the status which existed at the specified time. When ANY is not specified, timestamp must identify the time of a SESAM backup copy of the catalog space. The SESAM backup copy must be entered in the CAT-REC file.
SESAM/SQL reads in the specified SESAM backup copy of the catalog space (USING specified) or the most recent SESAM backup copy of the catalog space before the specified time. SESAM/SQL then applies the changes to the CAT-LOG files which were created subsequent to this SESAM backup copy up to the specified time.
The metadata of the reset catalog space is used to ascertain and read in all the user spaces of the database on the basis of the most recently created SESAM backup copy of the various user spaces. SESAM/SQL then applies the changes to the DA-LOG files which were created subsequent to this SESAM backup copy up to the specified time.
CAUTION!
A reset to the state of a previously created SESAM backup copy performed using RECOVER TO cannot be undone.
Reset of the catalog space
The database administrator can performs the reset of the catalog space using the RECOVER CATALOG_SPACE TO utility statement.
Resets are performed on the basis of a previously created SESAM backup copy for the catalog space. The reset sets the catalog space to the state obtaining at the time of the creation of this SESAM backup copy.
CAUTION!
If the catalog space is reset separately then it is not usually possible to access the individual user spaces since their modification time stamps no longer match those recorded in the catalog.
RECOVER CATALOG_SPACE TO leaves the user spaces unchanged and these have to be repaired separately.
The database administrator can use the utility monitor to identify which SESAM backup copies of the catalog space are available on the basis of the CAT-REC file.The required SESAM backup copy can then be selected via the version number of the backup, the time stamp or the file name of the SESAM backup copy.
CAUTION!
A reset to the state of a previously created SESAM backup copy performed using RECOVER TO cannot be undone. All records following the chosen backup copy are deleted in the CAT-REC file. A more recent state can no longer be established. However, you can back up the CAT-REC file and the CAT-LOG files outside of SESAM/SQL before reset.
Database consistency after a reset
The consistency of the whole of the database is not necessarily guaranteed after the reset of individual user spaces, a space list or a space set.
Consistency during reset involves the following:
The metadata in the catalog space (table definitions and index definitions) must match the structure of the user data in the reset user space.
The user data in a table must match the associated indexes.
The integrity constraints defined for the table must be fulfilled.
If the catalog space is reset separately then the user spaces remain unchanged and must be repaired individually. It is not usually possible to access the user spaces since their modification time stamps no longer match those recorded in the catalog.
If individual user spaces, a space list or a space set are reset, SESAM/SQL forces consistency between the metadata in the catalog space and the user data immediately after the reset by employing the adaptation mechanisms described below.
CAUTION!
Table data can be lost when adapted to the catalog metadata. If the database administrator wishes to ensure that no data is lost during reset as a result of these adaptation mechanisms, he/she should always backup and reset the complete database
Consistency of the catalog space and user spaces
The metadata that describes a user space (the table definitions and index definitions) in the catalog space must be consistent with the structure of the user data in that user space. This consistency requirement is usually not fulfilled if the user space is reset to a SESAM backup copy, and SQL statements used to administer the memory structure or define and administer schemas (statements that have an effect on the user space) have been executed since the SESAM backup copy was created.
For example, a base table or an index may have been created in a user space since the SESAM backup copy was created, and these are then no longer in the user space after a reset. However, the associated metadata still exist in the catalog space. It is also possible that a base table or an index might have been deleted in the user space, in which case the relevant metadata will also have been deleted from the catalog space. Once the user space has been reset, it will contain a base table or index for which no metadata exists in the catalog space.
To monitor the consistency between the catalog space and the user spaces, SESAM/SQL maintains a internal table in each user space; this table contains a description of the user space that corresponds to the relevant metadata in the catalog space. SESAM/SQL uses this table to detect discrepancies between a user space and the associated description in the catalog space, and adapts the user space to the description in the catalog space as follows:
If the catalog space contains metadata for tables and indexes that do not exist in the user space, SESAM/SQL re-creates these tables and indexes in the user space. The tables do not contain data; the indexes have the correct structure and are likewise empty.
If tables and indexes exist in the user space but there is no corresponding metadata in the catalog space, SESAM/SQL deletes the tables and indexes in question from the user space.
If tables' definitions and, thus, their descriptions in the catalog space have been modified, SESAM/SQL deletes the tables in question and re-creates them in the user space in line with the modified definitions. These new tables are empty.
If data is lost when SESAM/SQL restores consistency between the catalog space and the user spaces, the database administrator can restore the original data to the newly created tables, which are initially empty:
He/she begins by unloading the data from a SESAM backup copy with the utility statement UNLOAD OFFLINE ... FROM COPY_FILE (see the “SQL Reference Manual Part 2: Utilities”).
He/she then loads this data into the newly created tables with the utility statement LOAD.
Consistency between base tables and the related indexes
During reset, SESAM/SQL guarantees consistency between a base table and the indexes defined for it by rebuilding the indexes, provided the base table or the partitioned table and a related index are located in different user spaces and the database administrator has not reset all the user spaces affected.
If the table and the related indexes are in the same space list or space set and the database administrator has reset the space list or space set, consistency is guaranteed. SESAM/SQL does not rebuild the indexes in this case.
Consistency between base tables and integrity constraints
During the reset of a user space, SESAM/SQL checks whether the user space contains any base tables for which integrity constraints have been defined. If this is the case, SESAM/SQL checks whether the integrity constraints currently defined in the catalog space for the base tables are still fulfilled.
If an integrity constraint is violated, SESAM/SQL puts the user space in the state “check pending” (see section “Space state after the execution of utility statements”).
It is up to the database administrator to correct the violation of the integrity constraints and to free the user space from the “check pending” state. He/she does this with the utility statement CHECK CONSTRAINTS and SQL statements that query and update data, which he/she issues with the Pragma CHECK OFF ).
Recovering databases in various situations
Suitable measures for recovery allow a defective SESAM/SQL database to be recovered in a variety of error situations. Generally, the various RECOVER statements will recover a database without difficulty. Other problematic situations occur which require special treatment before the database can be recovered. Sometimes, data will be lost in these cases. SESAM/SQL always, however, ensures consistency between the catalog space and the user spaces.
Recovering user spaces in various situations
The table 48 describes not only the normal situation for RECOVER SPACE, but also a number of problematic situations and the corresponding measures which allow a user space to be repaired or reset.
Situation | Measures | Result/ | ||||
Logical data backup | Correct repair of the space: | Space is repaired | ||||
DA-LOG file not | If DA-LOG can be made available, continue with | Space is repaired | ||||
If DA-LOG file cannot be made available, | Data lost as a result of | |||||
SESAM backup copy not | If another SESAM backup copy is available with | Space is repaired | ||||
If the entry for the SESAM backup copy has | Data lost as a result of | |||||
Space cannot be | Use administration statements to place the
Repeat RECOVER | Space is repaired | ||||
Space faulty, no SESAM | Recover the empty space with RECOVER | At first complete loss | ||||
Delete space with DROP SPACE FORCED. | Complete loss of all |
Table 48: Possible results for RECOVER SPACE
Recovering the database or the catalog space in various situations
The table 49 describes not only the normal situation for RECOVER CATALOG or RECOVER CATALOG_SPACE, but also a number of problematic situations and the corresponding measures which allow repair or reset of the database or catalog space.
Situation | Measures | Result/ | |||||||
Logical data backup | Execute RECOVER CATALOG_SPACE or SCOPE PENDING clause: | Catalog space or | |||||||
Logical data backup | Execute RECOVER CATALOG ... GENERATE | Database is repaired | |||||||
Problems on repairing | First execute RECOVER CATALOG_SPACE, then | see table 48 | |||||||
CAT-LOG file no longer | If CAT-LOG file can be made available: Repeat | Catalog space or | |||||||
If the CAT-LOG file cannot be made available:
|
| ||||||||
SESAM backup copy not | If another SESAM backup copy and the | Catalog space or | |||||||
If an entry for a suitable SESAM backup copy has | Complete data loss | ||||||||
Catalog Space cannot be |
Save the faulty catalog space for diagnostic | Catalog space is |
Table 49: Possible results for RECOVER CATALOG and RECOVER CATALOG_SPACE
Recovering the database if the CAT-REC file is faulty
Wherever possible, the CAT-REC file should be mirrored (see “Backing up the CAT-REC file” ). If the CAT-REC should nevertheless become damaged, you must contact your software customer service department.