Backup and Recovery Procedures for Archive Logging [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
Backup and Recovery Procedures for Archive Logging
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
NOTE Online backup procedures in archive mode requires the presence of a
product known as TurboSTORE (TSTORE.PUB.SYS). If your system does
not have this product, you must use static backup procedures for
archive mode backups.
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 ISQL Reference Manual for
ALLBASE/SQL and IMAGE/SQL 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 group and account 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.
[REV BEG]
You cannot use STOREONLINE or STOREONLINE PARTIAL if there are any
pseudo-mapped files configured in your system. Before doing
STOREONLINE or STOREONLINE PARTIAL, use the SQLUtil command
SHOWACCESS to see if any DBEFiles are pseudo-mapped. Convert the
pseudo-mapped files back to mapped files using the SQLUtil
MOVEFILE command.[REV END]
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 group and account 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. Respond Y to the prompt "Use Static Store
(y/n)?" Refer to the section "Managing Log Files," below.
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
group and account 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 current
group and account,
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 fully qualified
filename (including group and account) of the DBEFiles you are
going to restore (you must use the fully qualified 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 group and account 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 group and
account 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 group that does not contain any DBEConFile or SYSTEM
DBEFileSet.
8. From that new group 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 current
group and account, 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 group and account 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.
MPE/iX 5.5 Documentation