HPlogo ALLBASE/SQL Database Administration Guide: HP 9000 Computer Systems > Chapter 6 Backup and Recovery

Backup and Recovery Procedures for Archive Logging
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

This section describes the steps you should follow if you will be using archive logging in full production after loading your tables. The following are described separately:

  • Online backup procedures in archive mode

  • Static backup procedures in archive mode

  • Adding files to the archive log

  • Archive recovery procedures

Online Backup Procedures in Archive Mode

To use archive logging, follow these steps:

  1. Load all your tables using the ISQL LOAD command before making the first backup. Preferably, the loading should be done in nonarchive mode. Refer to the description of the LOAD command in the "ISQL Commands" chapter of the ALLBASE/ISQL Reference Manual for suggestions on how to obtain best performance with the LOAD command.

  2. Add an appropriate number of log files to the DBEnvironment using the ADDLOG command. A minimum of two log files is necessary.

  3. For your initial backup, from the directory that contains the DBEConFile and the SYSTEM DBEFileSET, use the SQLUtil STOREONLINE command to store a copy of the DBEnvironment. This command stores the DBEnvironment and initiates archive logging. If archive logging is already on, it remains on. It is recommended that your initial backup be a full backup but if you have decided that only a subset of the DBEnvironment is essential, you can do a STOREONLINE PARTIAL for your first backup. Backups subsequent to your initial backup may use STOREONLINE or STOREONLINE PARTIAL as appropriate.

  4. As soon as possible, use the STORELOG command to store a copy of the log files that were written to by active transactions while the STOREONLINE or STOREONLINE PARTIAL was being done.

    These log files are necessary for later recovery, since they contain log entries for transactions that were in process at the time the STOREONLINE command was issued. The STOREONLINE or STOREONLINE PARTIAL command will indicate the log sequence numbers for the log files which need to be stored.

  5. When a log file becomes full, back it up using the STORELOG command. Be sure to label each log file backup with the log sequence number and the date of the backup. To check the size of a log file, you can use the SHOWLOG command. Once you back up the log file, ALLBASE/SQL can use it again. Refer to the section "Managing Log Files," below.

    NOTE: The online backup is not usable for rollforward recovery until you have backed up all log files that contain transactions that were active during the time the STOREONLINE command was in progress.

Once you have turned archive logging on, the only way to turn it off is to define a new log with the START DBE NEWLOG statement.

Static Full or Partial Backup Procedures in Archive Mode

It is also possible to carry out static backups in archive mode. A static backup is one that is made with the SQLUtil STORE or STORE PARTIAL command, and it requires you to STOP the DBEnvironment. The procedure is as follows:

  1. Load all your tables using the ISQL LOAD command before making the first backup.

  2. Stop the DBEnvironment.

  3. Add an appropriate number of log files to the DBEnvironment using the SQLUtil ADDLOG command. A minimum of two log files is necessary.

  4. Start the DBEnvironment again, and immediately issue a BEGIN ARCHIVE and a COMMIT ARCHIVE statement. Stop the DBEnvironment again.

  5. From the directory containing the DBEConFile and SYSTEM DBEFileSet use the SQLUtil STORE or STORE PARTIAL command to create a static backup of the DBEnvironment, including the DBECon file. Reply Y to the prompt "Do you wish to proceed (y/n)?" It is recommended that your initial backup be a full backup but if you have decided that only a subset of the DBEnvironment is essential, you can do a STORE PARTIAL for your first backup. Backups subsequent to your initial backup may use either the SQLUtil STORE or STORE PARTIAL command, as appropriate.

  6. When a log file becomes full, back up the log file using the STORELOG command.

Once you have turned archive logging on, the only way to turn it off is to define a new log with the START DBE NEWLOG statement, omitting the ARCHIVE option.

Adding Files to the Archive Log

In archive logging, your log will contain records for all the transactions your system handles between backups. The frequency of backup depends on the total volume of data being logged. A formula for calculating the total size is given in the "Physical Design" chapter.

If you develop a need for additional log files, you can use the SQLUtil ADDLOG command to provide another file. Then, when a transaction fills up the first file, ALLBASE/SQL will switch to the second one automatically.

In archive logging, the log grows continually, so you must continue providing additional log file space until you do another full backup. You can make old log files available for reuse by issuing the SQLUtil STORELOG command. Once a file has been stored this way, it can be reused with a different sequence number as soon as all the active transactions in it are complete.

More detailed information about managing archive log files is found in a separate section below.

Archive Recovery Procedures

Soft crashes (program aborts or system failures) rarely damage DBEnvironment files and can usually be remedied with rollback recovery, which is carried out automatically the next time the DBEnvironment is started. This does not require any special action on your part. You simply restart any transactions that were active at the time of the crash.

Media failures, on the other hand, can be very serious and often require you to reconstruct your DBEnvironment from a backup. This requires rollforward recovery using the log files previously stored.

Rollback Recovery

Under normal circumstances, rollback recovery is automatic. When the DBEnvironment is stopped, either implicitly when the last DBE session terminates or explicitly when the STOP DBE statement is executed, ALLBASE/SQL writes a checkpoint to the log file. When the DBEnvironment is started again, ALLBASE/SQL will perform rollback recovery if any transactions were still uncommitted at the time the DBEnvironment was stopped.

Rollback recovery is all you need to recover from the most common types of system failures, such as a soft crash. To recover from a crash that does not damage the DBEnvironment files or the log files, you merely issue a START DBE or CONNECT statement to start the DBEnvironment. This will automatically use the log file to roll back any transactions that were not committed at the time of failure.

Starting the DBEnvironment ensures the following:

  • All incomplete transactions at the time of failure are rolled back.

  • All committed transactions are permanently recorded in DBEFiles.

  • A new checkpoint is taken.

Full Rollforward Recovery

Full rollforward recovery lets you recreate an entire DBEnvironment following a media failure or logical corruption of DBEnvironment files. The process depends on the availability of backed up copies of the DBECon file, as well as all log files and DBEFiles.

Rollforward recovery begins with a restored DBEnvironment. Use the following steps in SQLUtil:

  1. Use the SQLUtil SHOWLOG command (with a connect option of "no") to determine which log files were not yet stored at the time of the crash and which show the status "backup required."

  2. Use the RESCUELOG command to store a copy of any log files that have not been stored at the time of the crash, including all log files that contained active transactions (those files with a status of "backup required").

  3. Use the RESTORE command to restore the DBEnvironment into the same directory from which it was originally backed up. If any old DBEnvironment files are present, remove them first. RESTORE restores the DBECon file and all DBEFiles.

  4. Use the SHOWLOG command (with a connect option of "no") to display the log's status as it was when the DBEnvironment was backed up. Make a note of the First Log Sequence Number Needed for Recovery.

  5. Use the SETUPRECOVERY command to initiate a recovery process. If you wish, you can specify a time to recover to. SETUPRECOVERY also lets you specify the name and characteristics of the new log file for the restored DBEnvironment. The restored DBEnvironment is automatically in archive mode, since it had previously been stored that way with a STOREONLINE command.

  6. Use RESTORELOG to restore a copy of each log file to the working directory, starting with the file that corresponds to the First Log Sequence Number Needed for Recovery from Step 4. For each file you restore, you must specify a new file name. It is easiest to maintain an orderly process if the new file name contains the log sequence number of the log file being stored.

  7. Use RECOVERLOG to apply each restored log file to the DBEnvironment. Enter the new name of the file as chosen during Step 6. ALLBASE/SQL will check the sequence number of the file as you apply it. If the file is out of sequence, you will see an error message.

    Once a log file is no longer needed for recovery, it is purged automatically.

  8. Repeat steps 6 and 7 until you have restored and recovered all the log files desired.

    NOTE: If you have enough space on your system, you can use several RESTORELOG commands one after the other to restore all the log files, then several RECOVERLOG commands one after the other to apply all the log files to the DBEnvironment. When restoring and recovering groups of log files in this way, it is a good idea to rename each restored file to a distinct file name that indicates the order in which the file will be applied to the DBEnvironment. Example: 
    

   Log00001

   Log00002

   Log00003

   Log00004

  9. After you have recovered all log files, issue the ENDRECOVERY command to complete the recovery process. (This step is not needed if you specified a recovery time in the SETUPRECOVERY command.)

  10. Use the SQLUtil PURGEFILE command to purge any old log files that remain. Be careful not to purge the log file you specified when using the SETUPRECOVERY command.

  11. Use the SQLUtil ADDLOG command to create additional log files as needed. You must have at least two files if you use a dual log and you want ALLBASE/SQL to automatically switch to a new log file when the current log file is full.

  12. Exit from SQLUtil, then start the DBEnvironment as you would normally.

Each RESTORELOG and RECOVERLOG step in this process could be carried out with a different invocation of SQLUtil. Also, rollforward recovery is possible from an earlier archive backup of the DBEnvironment, ignoring an intervening backup, as long as the logs are available. Simply apply all the logs in sequence up to the desired recovery time after restoring the DBEnvironment from the earlier backup. (SHOWLOG will display the First Log Sequence Number Needed for Recovery.) Rollforward recovery across new logs is also supported, provided the logs are archive logs.

Partial Rollforward Recovery

Partial rollforward recovery lets you recreate a subset of the DBEnvironment following a media failure or logical corruption of DBEnvironment files. The process depends on the availability of backed up copies of the DBECon file, as well as all log files and DBEFiles for the subset of the DBEnvironment damaged by the failure.

Partial rollforward recovery begins with a DBEnvironment in which the DBEConFile and the SYSTEM DBEFileSet are intact, and the appropriate log files are intact or have been properly stored. Use the following steps in SQLUtil:

  1. Use the SQLUtil SHOWLOG command (with a connect option of "no") to determine which log files were not yet stored at the time of the crash and which show the status "backup required."

  2. Use the RESCUELOG command to store a copy of any log files that have not been stored at the time of the crash, including all log files that contained active transactions.

  3. Use the SQLUtil STOREINFO command to verify the path and filename of the DBEFiles you are going to restore (you must use the full path and filename, just as shown by the STOREINFO command).

  4. Use the SQLUtil DETACHFILE command to detach from the DBEnvironment the files to be operated on by the partial rollforward recovery. This prevents users from attempting to use these files until you have them rolled forward to the appropriate period in time. You most roll all the way forward to the moment of the failure (or to the moment the file was detached, if the file is detached.)

  5. From the directory from which you stored the DBEnvironment (usually the one containing the DBEConFile and the SYSTEM DBEFileSet) use the RESTORE PARTIAL command to restore the appropriate subset of the DBEnvironment into the same directory from which it was originally backed up. The RESTORE PARTIAL may be from a full backup or a partial backup, as appropriate. (As the log files must have been intact as a condition for doing the RESTORE PARTIAL, you should not need to create or add more log files.) If any damaged DBEnvironment files are still present, remove them first. RESTORE PARTIAL restores the specified DBEFiles.

  6. Use the SHOWLOG command (with a connect option of "no") to display the log's status as it was when the DBEnvironment was backed up. Make a note of the First Log Sequence Number Needed for Recovery.

  7. Create a new directory that does not contain any DBEConFile or SYSTEM DBEFileSet.

  8. From that new directory use the SETUPRECOVERY PARTIAL command to initiate a recovery process, specifying the name for a temporary DBEnvironment and the DBEFiles needed for recovery. Rollforward recovery of the damaged files will be done using the temporary DBEnvironment so that the original DBEnvironment can remain in use while the damaged files are being brought up to the desired state. You must roll forward all the way to the time of the failure (or the time the file was detached, if the file is detached.) SETUPRECOVERY PARTIAL also creates a default temporary log file and DBEFile0 which are automatically removed at the end of the partial recovery process. The temporary DBEnvironment is automatically in archive mode, since the original DBEnvironment had previously been stored that way with a STOREONLINE or STOREONLINE PARTIAL command.

  9. Use RESTORELOG to restore a copy of each log file to the working directory, containing the temporary DBEnvironment, starting with the file that corresponds to the First Log Sequence Number Needed for Recovery from Step 6. For each file you restore, you must specify a new file name. It is easier to maintain an orderly process if the new file name contains the log sequence number of the log file being stored.

  10. Use RECOVERLOG to apply each restored log file to the DBEnvironment. Enter the new name of the file as chosen during Step 6. ALLBASE/SQL will check the sequence number of the file as you apply it. If the file is out of sequence, you will see an error message. Once a log file is no longer needed for recovery, it is purged automatically.

  11. Repeat steps 9 and 10 until you have restored and recovered all the log files desired.

    NOTE: If you have enough space on your system, you can use several RESTORELOG commands one after the other to restore all the log files, then several RECOVERLOG commands one after the other to apply all the log files to the DBEnvironment. When restoring and recovering groups of log files in this way, it is a good idea to rename each restored file to a distinct file name that indicates the order in which the file will be applied to the DBEnvironment. Example: 
    

   Log00001

   Log00002

   Log00003

   Log00004

  12. After you have recovered all log files, issue the ENDRECOVERY command to complete the partial recovery process. (This step is not needed if you specified a recovery time in the SETUPRECOVERY PARTIAL command.) The ENDRECOVERY command will remove the temporary DBEnvironment, including the temporary DBEFile0 and log files, that were created for the partial recovery process.

  13. Use the SQLUtil ADDLOG command to create additional log files as needed. You must have at least two files in each leg of the log if you want ALLBASE/SQL to automatically switch to a new log file when the current log file is full.

  14. Exit from SQLUtil, and move to the directory containing the original DBEnvironment DBEConFile and SYSTEM DBEFileSET.

    Each RESTORELOG and RECOVERLOG step in this process could be carried out with a different invocation of SQLUtil. Also, rollforward recovery which skips an intervening backup is possible from an earlier archive backup of the DBEnvironment, as long as all the logs are available. Simply apply all the logs in sequence up to the desired recovery time after restoring the DBEnvironment from the earlier backup. (SHOWLOG will display the First Log Sequence Number Needed for Recovery.) Rollforward recovery across new logs is also supported, provided the logs are archive logs.

  15. Use the SQLUtil ATTACHFILE command to re-attach the fully recovered DBEFile(s) to the DBEnvironment so they can again be accessed by users. No additional log files should be necessary because the original log files had to be intact as a condition for doing partial rollforward recovery.

  16. Continue using the DBEnvironment as you would normally.



Backup and Recovery Procedures for Nonarchive Logging
  		 


Managing Log Files  
Feedback to webmaster