Managing Log Files

The DBA must manage the size, number, and location of ALLBASE/SQL log files. This is done by performing the following tasks using a special set of SQLUtil log management commands:

Monitoring the log with SHOWLOG
Adding log files with ADDLOG
Storing log files with STORELOG
Rescuing log files with RESCUELOG
Purging log files with PURGELOG
Restoring log files with RESTORELOG
Moving log files with MOVELOG

Monitoring the Log with SHOWLOG

Use the SHOWLOG command to display the names of the individual log files (single or dual) associated with a DBEnvironment, the archive mode of the log, and the available file space remaining.

Displaying Files in the Log

You can use the SHOWLOG command to display the log file directory either dynamically or statically. The dynamic SHOWLOG is done if you respond Y to the prompt



   Connect? (y/n) (opt):

The default option is a dynamic SHOWLOG.

Normally, you should do a dynamic SHOWLOG so as to display the most current information about available log space and about the backup status of log files. You use the static SHOWLOG only when it is not possible to connect to the DBEnvironment, as after a media failure.

Here is an example of a dynamic SHOWLOG:



   >> showlog

   DBEnvironment Name: ../sampledb/PartsDBE

   Maintenance Word:  Return 

   Connect? (y/n) (opt): y



   Archive Mode:  ON

   Log Sequence Number Containing Most Recent Archive Checkpoint: 1

   Current Log Sequence Number: 1

   First Log Sequence Number Needed for Recovery: 1

   Log Mode is: Single

   Number of Free Blocks: 340



   First Log Name: DBELog1

   First Log File Status: Useable

   Log File Size:  250

   Log Identifier Is: 1

   Log Sequence Number: 1

   Log Backup Status: Not Ready for Backup



   First Log Name: DBELog2

   First Log File Status: Useable

   Log File Size:  250

   Log Identifier Is: 2

   Log Sequence Number: 0

   Log Backup Status: Not Ready for Backup

In this example, SHOWLOG displays the following information:

Archive logging is on, and the log mode is single, meaning that only one set of files is maintained.
The archive checkpoint--the point at which the last STOREONLINE command began storing the DBEnvironment--is in the file with log sequence number 1.
The current log file is DBELOG1, which has a log sequence number of 1.
If rollforward recovery should become necessary, it would have to start at log sequence number 1. After subsequent STOREONLINE commands, this sequence number will become larger.
DBELOG1 and DBELOG2 combined still have a total of 340 free log blocks (pages).
DBELOG1 has not yet been backed up. It is not yet ready for backup, since it is the current log file, that is, it is still being written to.
A second log file, DBELOG2, has been added to the DBEnvironment with the ADDLOG command. This file has not been used, so it still has log sequence number 0. It has also not been backed up, since it does not contain any log records, and thus has never needed to be backed up.
Note that since the DBEnvironment is in single logging mode, both files have only a First Log Name.

The following is an example of a dynamic SHOWLOG display for a DBEnvironment running in nonarchive mode with dual logging:



   >> showlog

   DBEnvironment Name: ../sampledb/PartsDBE

   Maintenance Word:  Return 

   Connect? (y/n) (opt): y



   Archive Mode:  OFF

   Log Sequence Number Containing Most Recent Archive Checkpoint: 0

   Current Log Sequence Number: 1

   First Log Sequence Number Needed for Recovery: 0

   Log Mode is: Dual

   Number of Free Block(s): 259



   First Log Name: DBELog1

   First Log File Status: Useable

 

   Second Log Name: DBELog2

 

   Second Log File Status: Useable



   Log File Size: 300

   Log Identifier Is: 1

   Log Sequence Number: 1

   Log Backup Status: Backup Is Not Required

In this example, the Log Sequence Number Containing Most Recent Archive Checkpoint and the First Log Sequence Number Needed for Recovery are both 0, since archive logging is off.

Log File Status Types

For a dynamic SHOWLOG, the types of backup status are as follows:

Not Ready for Backup: This is the current file, which is not full yet.
Ready for Backup: The file is full, and the DBE is now using a different file, so this one is ready to be stored with STORELOG.
Backup is Done: The file has already been backed up.
Backup is Not Required: The log file does not need to be backed up if the DBEnvironment is in nonarchive mode, or the DBEnvironment is in archive mode and this log file has never been used.

For a static SHOWLOG, the types of backup status are as follows:

Backup is Required: The file has not been backed up yet.
Backup is Done: The file has already been backed up.
Backup is Not Required: The DBEnvironment is in nonarchive mode, so the file does not need to be backed up.

Displaying Available File Space

You can also use the dynamic SHOWLOG command to display the number of free log blocks (pages) available for logging. In archive mode, SHOWLOG does not tell how many pages are available in previously used log files that have not already been backed up. Until the files are backed up, these log pages are not free.

Using the CHECKPOINT command

For nonarchive log files, a CHECKPOINT command tells you how many free log file pages there are, and it frees log file pages held by completed transactions. If long transactions tend to fill up the log file, you should increase the log file size.

For archive log files, a CHECKPOINT command also tells you how many free log file pages there are, and it frees log file pages in files that have been stored. CHECKPOINT does not tell how many pages are available in previously used log files that have not already been backed up. Moreover, CHECKPOINT uses more system resources than SHOWLOG, which is the preferred method for obtaining the number of free pages.

To free log file pages in archive mode, you must back up some of the log files using STORELOG. After backup, these files become available for reuse as soon as there are no active transactions in them. As the number of free log pages approaches zero, you should add log files, or use STORELOG to make a backup of any non-current log file, which then becomes available for reuse.

Adding Log Files with ADDLOG

Using multiple log files gives you the greatest flexibility with logging. In nonarchive mode, you can add a file when it is needed for unusually large transactions, and you can recover easily from a LOG FULL condition without having to shut the DBEnvironment down. In archive mode, you can achieve continuous DBEnvironment availability by adding a group of log files, then developing a schedule of DBEnvironment and log backup.

To expand the capacity of a nonarchive or archive log, use the ADDLOG command, as in the following example:



   >> addlog

   DBEnvironment Name: PartsDBE

   Maintenance Word: 

   Enter Log File Name(s) Separated by a Blank? DBELOG2

   New Log File Size? 250

   Add Log File (y/n)? y



   Log file `DBELOG2' was Added.

   Log Identifier Is: 2

If you are using dual logging, enter two log file names separated by a blank. No more than two names are accepted. The minimum size you can specify for an added log file is 250 pages.

After you add the file to the DBEnvironment, it will be used when the current log file runs out of space.

Storing Log Files with STORELOG

If you are using archive logging, you must use the STORELOG command to create backups of each log file as it fills up. When a file's status (shown in the dynamic SHOWLOG display) is Ready for Backup, you should use the STORELOG command to store a copy of the log. One method of indicating which file to store is by entering its Log Identifier. Here is an example:



   >> storelog

   DBEnvironment Name:  PartsDBE

   Maintenance Word:   Return 

   Log Identifier (opt): 2

   To File Name: /extra/backup2

   Store Log File (y/n)? y



   Log file 'lgn1' with Sequence Number          2  was stored.

The second method of initiating a log file backup is to issue a STORELOG command and press Return when you see the prompt for a log identifier. In this case, STORELOG will back up the file with the lowest possible sequence number that has Ready for Backup status in the dynamic SHOWLOG display. If the STORELOG operation succeeds, the file will be given the new backup status Backup is Done.

A third method of initiating a log file backup is to issue a STORELOG command and enter 0 for the log identifier. In this case, STORELOG will back up all the files which are ready for backup, in the proper sequence by log sequence number, prompting you for filenames as each file is stored. Using this method, all log files can be stored on one tape.




	IMPORTANT: For each successful STORELOG, label the backup tape with the log file name, sequence number, and the date and time of the backup. The sequence number will let you restore and recover log files in the correct order in the event that rollforward recovery is needed.

Rescuing Log Files with RESCUELOG

At certain times, it is necessary to store a copy of a log file that is not yet ready for backup with the STORELOG command. One such time is immediately following a media failure. In this event, STORELOG does not work, since it is impossible to CONNECT to the DBEnvironment. Use the RESCUELOG command to store a copy of any log files not yet backed up at the time of the media failure, as in the following:



   >> rescuelog

   Log File Name: DBELOG6

   Size Of The Log File: 250

   To File Name? save6

   Rescue Log File (y/n)? y



   Log File 'DBELOG6' with Sequence Number        12 Was Rescued.

If the DBECon file is intact, you can use the static SHOWLOG command after a media failure to display the sequence numbers of files with a status of Backup Required. Then you can store these files with RESCUELOG.

Restoring Log Files with RESTORELOG

After a media failure, you prepare for rollforward recovery by restoring the DBEnvironment and the log files. After using RESTORE to restore the DBECon file and all DBEFiles, use the RESTORELOG command to restore each individual log file. The Rename prompt lets you enter a new file name for the log. The input for this prompt is optional, and the default is the original file name. Press Return to use the original file name.

The easiest way to restore and apply log files is to use the RESTORELOG and the RECOVERLOG commands one after another for each file, as in the following example:



   >> restorelog

   DBEnvironment Name:  ../sampledb/PartsDBE

   Maintenance Word:   Return 



   Input Device: /dev/tape

   Local (y/n) (opt): y

   Rename 'lgn1' Log File To: log0001

   Restore the Log File (y/n)? y

   Log File 'log0001' was Restored.



   >> recoverlog

   DBEnvironment Name:  ../sampledb/PartsDBE

   Maintenance Word:   Return 

   Next Log File To Recover: log0001

   Recover Log File (y/n)? y



   Log File Recovered.

   Next Possible Log Sequence Number is     2.

When a log file has been applied to the DBEnvironment as a part of rollforward recovery, it will be purged by ALLBASE/SQL when it no longer contains active transactions. For that reason, if doing partial recovery in which your current log files are undamaged, make sure that the names given restored log files while using the RESTORELOG command do not conflict with the names of existing log files. Otherwise your existing log files could be purged as RECOVERLOG completes its work.

(In fact, partial rollforward recovery is best conducted in a directory which is separated from the one containing your DBEConFile, SYSTEM DBEFileSet, and log files to avoid undesired interaction between files with duplicate names.) Do not attempt to purge logs yourself during the rollforward recovery process.

Purging Log Files with PURGELOG

The only way to purge an individual log file that is no longer needed is with the PURGELOG command. Example:



   >> purgelog

   DBEnvironment Name: ../sampledb/PartsDBE

   Maintenance Word:

   Log Identifier: 2

   Purge Log File (y/n)? y



   Log file(s) Purged.

In the case of dual logging, this command purges both filenames associated with the log identifier.

You can purge a log file that has been backed up, provided it contains no active transactions. You can also purge a log file that has never been used. You cannot use PURGELOG during the rollforward recovery process. If you wish to purge the DBEnvironment and the entire log, use the PURGEALL command. To purge the DBE alone, use the PURGEDBE command.




	NOTE: You should `not` remove log files with the HP-UX `rm` command, since this action is not recorded in the DBECon file. For the same reasons, do `not` use the PURGEFILE command to remove a log file under any circumstances.

Moving Log Files with MOVELOG

You can move a log file from one location to another with the SQLUtil MOVELOG command. Example:



   >> movelog

   DBEnvironment Name: PartsDBE

   Current Log File Name: partslogA3

   New Log File Name: newdrive/partslogA3



   File moved.

MOVELOG lets you place log files on a device that is separate from the data and index files of the DBEnvironment. Note that when you move a log file, you specify its file name; this means that you can place the different members of a dual log sequence on separate devices if you choose.