Backing-up and Restoring Data

This chapter discusses backup and restore of Mimer SQL databanks. Two types of backup procedures are described:

•System Backup, i.e. backing up the databank files from the host operating system. When using host operating system tools for doing databank file backup, the database server must be stopped in order to keep the database consistent.

•Online Backup, i.e. using the SQL system management statements. The main advantage of online backup is that the database server can continue to operate (backup operations are performed in the background).

Some of the discussion in this chapter refers to shadowing databases, see Mimer SQL Shadowing, which is an optional Mimer SQL module that allows one or more copies of a databank to exist on different disks. Shadowing provides a high level of protection from disk failure because the system will automatically use a databank shadow if the master databank is lost, thus allowing normal database activity to continue without interruption. Databank shadows also allow a copy of a databank to be temporarily set offline (e.g. to be backed-up) without interrupting normal system use.

Several references to transaction handling are made in this chapter. If you are not familiar with transaction handling in Mimer SQL see the Mimer SQL Programmer's Manual, Transaction Handling and Database Security.

Background Information

A Mimer SQL database consists of a collection of databanks (each in a separate operating system file) containing tables with data used by the applications. The SYSDB system databank contains a data dictionary describing the different objects in the database.

Note:Backup protection for SYSDB is particularly important for protecting the database, since SYSDB contains all information describing the database structure. If SYSDB is lost, the system must be rebuilt from scratch.

Database Consistency

Database consistency is handled on two levels: physical and logical.

Physical consistency means that the tables are readable by Mimer SQL. This is ensured as long as the databank file is not physically damaged.

Logical consistency means that the tables contain valid data. This is ensured by Mimer SQL’s transaction handling. All transactions are saved in the TRANSDB databank during build-up and are applied to the databanks when they are committed. To use transaction handling, the databank must be created with the TRANSACTION or LOG option.

Transaction handling makes it possible to ensure that a user cannot commit a read write transaction which has read data that is being concurrently updated by another user. If a transaction is successfully committed then all operations in the transaction are performed. If the transaction is aborted due to a conflict, none of the operations in the transaction are performed.

The tables may be logically inconsistent if Mimer SQL is stopped before all operations in a committed transaction have been performed. At some time after the system is restarted, all uncompleted transactions will be read from TRANSDB for automatic completion. This happens in the background on a per-databank basis, after a databank is first accessed following the restart. Transactions that were not committed before the stop are aborted.

The DBOPEN facility, see DBOPEN - Databank Open, can be used to open all databanks in one operation and thus achieve transaction consistency quickly.

LOGDB and TRANSDB Importance

Important: The information stored in TRANSDB is vital to keep the database consistent in all circumstances, not only in case of failure.

The LOGDB information will contain data on all the changes made to the databank from the time the backup copy of the databank was taken until the time of the disk crash. This information is used if a backup copy of a databank file is to be restored.

Note:Data changes that are not logged cannot be restored by this process, therefore it is important to consider the issue of transaction logging carefully.

If a databank becomes unavailable (because the Mimer SQL system is stopped deliberately or by a system failure) during the commitment of a transaction, information is retained in the TRANSDB system databank and used to complete the transaction when the databank becomes available again.

This is only true for databanks with the TRANSACTION or LOG option. Once information has been successfully written to both LOGDB and the databank file, it is removed from TRANSDB.

It is recommended practice to back up all the databanks of the database at the same time, and to ensure that LOGDB is always backed up whenever other databanks are backed up, because the LOGDB information provides the transaction data which links the previous backup copy of a databank with the databank as it exists at the current point in time.

Thus, when restoring a databank it should be brought to a state consistent with the latest backup. This is done either by using the latest backup copy of the databank or by using backed up LOGDB information with an older backup copy of the databank.

The current LOGDB system databank is then used to restore the final changes made between the time of the latest backup and the time the databank was lost.

Example

 

The graphic above describes a scenario which ends up in a system crash.

To recover from this situation the common operation is to start from the most recent backup (T₂) and then use the current LOGDB to recover data up to the state at T₃.

When the system is restarted, the current TRANSDB is used to automatically recover up to the moment of the crash.

If the most recent backup cannot be used, an older backup has to be brought in (T₁). This backup is restored up to the consistent state at T₂ by using the LOGDB stored in the backup at T₂.

Then the current LOGDB and TRANSDB are used to restore the transactions committed after the backup at T₂.

Note:Wherever possible, LOGDB should be stored on a different disk unit, with a separate disk controller, from the other databanks in order to minimize the risk that a disk crash or damaged disk controller destroys both the log and the other databanks.
LOGDB and TRANSDB should always be located on different physical disks which are ideally served by separate disk controllers and no other databank files should be located on either disk, since data may be lost if both TRANSDB and LOGDB are destroyed.
Refer to Organizing Databank Files, for more details on data security and databank files.

Updates Recorded in LOGDB

The LOGDB system databank contains logged update information for each databank with LOG option.

It is recommended that all databanks, including LOGDB, are backed up at the same time and that LOGDB is cleared after backup by resetting the log. Thus, the backed up LOGDB will contain the information required to make databanks from the preceding backup consistent with the current backup.

This will provide double backup protection by allowing a lost databank to be recovered in one of the two ways listed below:

•restore the databank from the most recent backup and apply the updates currently held in LOGDB, or

•restore the databank from an earlier backup, then sequentially use the LOGDB files from each subsequent backup to make the databank consistent with the most recent backup, and finally apply the updates currently held in LOGDB.

The records in LOGDB should be cleared after a complete backup, in order to maintain consistency between the backup and LOGDB. This ensures that LOGDB only contains information about changes made to a databank since the last backup of it was taken. (It is possible to backup databanks without clearing LOGDB records, although care must be taken as this leaves the backup and LOGDB in an inconsistent state).

The ability to restore databank updates from a backup copy of LOGDB replaces the databank incremental backups which were supported in previous versions of Mimer SQL. These are still supported for backward compatibility but it is now recommended that LOGDB backups always be taken to offer the same double protection.

TRANSDB Considerations

TRANSDB requires backup protection since it nearly always contains unfinished transactions. If TRANSDB is lost before the Mimer SQL system is restarted, the database will be left in a logically inconsistent state.

Possible effects of losing TRANSDB before the database server is restarted are described in the following scenarios:

•If TRANSDB is lost, some of the databank updates that apply to the most recently committed transactions may have been made while others remain unfinished.
The only safe operation to do to avoid a logically inconsistent database is to bring up the most recent backup copy and restore from LOGDB. In this case, the only loss is those transactions that were not completely written to LOGDB.

•If both TRANSDB and LOGDB are lost, the restoration, as described in the previous bullet, cannot be accomplished.

In the case where a restore is not possible, the best solution is to repair the inconsistency immediately after restarting the database server. This is done by using a tool such as BSQL for manual verification and update of data. This is usually possible if the user who initiated the interrupted transaction can be identified and contacted. (Many applications maintain a parallel audit log file for tracking purposes which can be used as a basis for repair work).

An alternative solution if both LOGDB and TRANSDB are lost, is to start over from the most recent backup of your databanks and reprocess all transactions since that time. This may be a costly operation.

Keeping TRANSDB and LOGDB on separate disks under separate disk controllers will minimize the risk that both databanks are lost at the same time.

A TRANSDB shadow is another possible security enhancement, see Mimer SQL Shadowing.

Note:The TRANSDB system databank must never be deliberately deleted, because uncompleted transactions nearly always remain saved in the databank even if the database server is currently stopped.
If a TRANSDB file containing uncompleted transactions is deleted, inconsistency will occur because the information required to complete those transactions when the database server is re-started will have been lost.

SQLDB Considerations

The contents of SQLDB is transient, so this databank does not need backup protection.

However, it may be convenient to have SQLDB included in the backup so that a complete system can be restored easily, without any additional operations to recreate an empty SQLDB.

Some data retrieval requests in Mimer SQL may require large work areas or transaction handling areas for intermediate processing of the data (for instance, requests to sort or group large result sets will require large work tables in SQLDB). This is particularly relevant when ad-hoc queries may be submitted with little thought for the processing requirements or performance of the query. In systems where files expand automatically, the file for SQLDB can become very large as the result of one badly-planned query.

The databank attributes GOALSIZE and MAXSIZE are to manage databank file sizes. See Mimer SQL Reference Manual, CREATE DATABANK.

Databank Backups

A databank backup is a copy of the databank file.

A databank backup is the starting point for any restore operation, and should be stored in a safe place separate from the working databank files (copied to a different disk or preferably written to backup media and removed from the machine).

The backup can be taken either by using the Mimer SQL system administration statements for online backup, see Online Backups Using the SQL Statements, or by using the host file system utilities in a system backup, see System Backups Using the Host File System.

After a backup is taken, the updates logged for the databank in question should be cleared from LOGDB. This will be done automatically when the SQL system management statements for online backup are used.

The DBC program, see Databank Check Functionality, should be executed for each databank in the backup operation in order to validate the physical consistency of the databank.

For a system backup, the backup copies of the databanks should always be taken when the system is closed and the databanks are in a logically consistent state. That is, no uncompleted transactions should exist and all databanks should be backed up at the same time to safeguard database consistency.

System vs. Online Backups

The main advantage of online backup is that all databanks, including the system databanks, can be backed up while the system can remain operational. The backup is initiated and executed by use of SQL statements only. The disadvantage can be that there must be enough disk space available to copy the complete database.

If the disk space is limited, a system backup can be preferable. For a system backup, the database server must be stopped. A system backup needs certain SQL statements (such as set online/offline) to be used together with operating system commands for file copying, etc.

SQL Statements for Backing-up Databanks

Refer to the Mimer SQL Reference Manual, SQL Statements for a detailed description and syntax definition of the SQL system management functions. A brief description of the purpose of each function appears here.

Online Backup Commands

The SQL system management statements that can be used to take backups are:

To use these statements to take a databank backup, the user must either be the creator of the databank, or have BACKUP privilege.

When the SQL statements are used to take a backup of a databank, the entire process of taking a databank backup is handled automatically.

The use of a backup transaction ensures that the backups taken within the transaction are consistent with one another, as each backup is effectively taken at the same point in time. Log records are cleared for successfully backed up databanks when the backup transaction is committed. If LOGDB is included in the backup transaction all log records are cleared.

Online/Offline Commands

The SQL system management statements (typically used when taking databank backups using the host file system) that can be used to set a databank, shadow or the whole database online or offline are:

A user setting the database online/offline, must have BACKUP privilege and must be the only user accessing the database.

A user setting a databank or a shadow online/offline, must either be the creator of the databank or have BACKUP privilege.

Restore Command

The SQL system management statement used to recover a databank in the event of it being damaged or destroyed is:

A user using this function to restore a databank must be the creator of the databank or have BACKUP privilege.

Backing-up Databanks

This section describes procedures for taking databank backups and for restoring a databank in the event of it being damaged or destroyed.

Online Backups Using the SQL Statements

The procedure for taking databank backups using the SQL system management statements is detailed below.

A CREATE BACKUP statement is executed for each databank to be backed up and databank consistency is ensured by starting a backup transaction using the START BACKUP statement.

The backup transaction is committed by using the COMMIT BACKUP statement, which will perform the backup and clear the relevant LOGDB records. The ROLLBACK BACKUP statement can be executed to abort the backup transaction, which will preserve LOGDB.

Note:The databank check functionality (the DBC program) should be run before archiving the backup copies of the databank files (e.g. copying them to CD/RW) to verify the physical integrity of the databank files.

To backup databanks online, do the following:

1Perform SQL statements for initiating and executing the backup.

SQL> START BACKUP;

SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK databank-name;

.

.   (repeat for each databank to be backed up)

.

SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK logdb;

SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK sysdb;

SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK transdb;

SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK sqldb;

SQL> COMMIT BACKUP;

SQL> EXIT;

 

2Verify the backup copies from the operating system command line using the DBC program.

dbc  backup-file-name  report-filename  sysdb-file-name

.

. (repeat for each backup file created above)

.

3Archive the verified backup copies (e.g. copy to DVD/CD).

The START BACKUP statement will start a backup transaction which will ensure that all the backups taken are consistent with one another (they are effectively backed up at the same point in time).

The CREATE BACKUP statement will only create an empty backup copy file. The entire contents of the specified databank file is copied to the specified file by COMMIT BACKUP. (If any of the backups fail, the ROLLBACK BACKUP statement can be executed to ensure that the log records are preserved.)

The COMMIT BACKUP statement will clear all the LOGDB records that apply to the databanks backed up in the backup transaction.

Databank backup file names are subject to the same restrictions that apply to the SQL statement CREATE DATABANK - see the Mimer SQL Reference Manual.

System Backups Using the Host File System

The procedure for taking databank backups using the host file system is detailed below.

We recommend that you always take a backup of all databanks, including SYSDB, LOGDB, SQLDB and TRANSDB.

Note:The database server must be stopped in order to close the SYSDB databank file for a host system backup. This unlocks SYSDB and ensures that no operations are performed between taking copies of the databanks and dropping the log.
However, if using shadowing, databank shadows allow a copy of a databank to be temporarily set offline, e.g. to be backed up, without interrupting normal system use.

To backup databanks using the system backup method:

1Set the database offline using the following command:

SQL> SET DATABASE OFFLINE;

 

2Stop the database server so that the system databanks are closed and can therefore be backed up

mimcontrol -t database

 

3Run the DBC program on each databank to verify the physical integrity of the databank files

dbc  backup-filename  report-filename  sysdb-filename

 

4Perform the backup, e.g. copy all databank files to tape (including the system databanks SYSDB, LOGDB, TRANSDB and SQLDB).

5Start the database server

mimcontrol -s database

 

6Set the database online again using the following command to clear all log records:

SQL> SET DATABASE ONLINE RESET LOG;

 

The RESET LOG option removes all records written to LOGDB since the last backup.
This is essential to maintain consistency between the log and the backup. If the backup fails, the PRESERVE LOG option should be used when setting the databank online to leave LOGDB unaltered.

It is essential that all databanks are backed up at the same time to ensure logical consistency between them.

It is also important that transactions are in a consistent state which is ensured by using the SET DATABASE OFFLINE statement. The statement will not return until the database has been brought into a consistent state prior to going offline. In particular, setting the database offline will ensure all background processing done by the database server has completed.

Restoring a Databank

Restoring a databank after it has been damaged or destroyed will typically involve both the host file system and SQL statements.

Note:Data need not be restored in the event of a power failure or system shut-down that does not damage the databank files, since any transactions that were committed but not completed at the time of the failure are automatically completed when the databank involved is next accessed.

Any databank restore operation must start with a backup copy of the databank file that is not damaged or corrupt. This is generally the copy taken during the last backup, either taken by the host operating system or by using the SQL system management statements for online backup.

Usually, the host file system is used to copy the backup file from the backup media to disk. The file is generally placed in the normal location for the databank file (as recorded in the data dictionary, SYSDB). However, in certain circumstances it may be necessary to place it in an alternative location, e.g. if the disk is unavailable.

The procedure for restoring a databank is as follows:

Note:Step 2 and 3 are only required during certain circumstances:

1Bring a valid backup copy of the databank from the backup media to disk.

2If the file has been placed in a location that is different to the location of the original databank file, alter the databank to reference the new file location using the following command:

SQL> ALTER DATABANK databank-name INTO 'new-file-name'

 

3If restoring from an older backup, i.e. not the latest one, information should be restored from the LOGDB included in the following backup (that was taken after the time the backup restored in step 1 was taken).

For each LOGDB backup file, the information recorded in it should be applied to the databank using the following command:

SQL> ALTER DATABANK databank-name RESTORE USING 'logdb-backup-file-name'

 

4Finally, apply the updates made since the most recent backup(s) restored in the preceding steps were taken. These updates are currently recorded in LOGDB and they are restored using the following command:

SQL> ALTER DATABANK databank-name RESTORE USING LOG

Restoring SYSDB

If SYSDB is lost, a backup copy of SYSDB must be restored to allow Mimer SQL to start again. No Mimer SQL-based application can be used before this is done.

If SYSDB is lost or corrupted, a backup copy should be copied to the same file location as the original SYSDB. The contents of SYSDB may then be brought completely up to date by restoring LOGDB information. This is done using the backup and restore functionality in the BSQL program.

Start BSQL and login as SYSADM, or another user with BACKUP privilege. A message is displayed saying that you have an old version of SYSDB that must be restored. Answer Y to the question Restore SYSDB to restore the copy of SYSDB.

Since SYSDB always has the LOG option, this will restore SYSDB to the state it had before it was lost.

Example, assuming a backup of SYSDB has been copied to the original location:

MIMER/DB fatal error -16159 in function CONNECT

        Old version of the databank SYSDB cannot be accessed without

        restoring the databank with the backup and restore utility

 

                    -- Restore databank --

 

Restore SYSDB?[Y]: Y

 

Databank SYSDB restored from log

Re-creating TRANSDB, LOGDB and SQLDB

No Mimer SQL applications can be run if LOGDB, TRANSDB or SQLDB is missing. In this event, starting the BSQL program and logging in as SYSADM will give you an opportunity to re-create the missing databanks with the same file names as the lost databanks, or to alter the recorded filenames in the case where the physical files were moved.

The following example shows how to re-create LOGDB for a database where this system databank is missing:

Mimer SQL command line utility, version 11.0.1A

Username: SYSADM

Password:

2017-06-17 23:15:16.94   <Error>

MIMER/DB kernel error -16142 in function DKOPD1

Databank LOGDB, filename logdb.dbf

File not found, OS error message:

'%SYSTEM-W-NOSUCHFILE, no such file'

 

 

 

 

-- Redefinition of system databank --

 

-- Description of databank name and file --

 

DATABANK

FILENAME

========================================

LOGDB

logdb.dbf

 

Redefinition of LOGDB? [Y]: y

CREATE new file or ALTER filename for LOGDB? (C/A): c

Size [1000]                   : 5000

Databank LOGDB redefined

 

These databanks (at least TRANSDB and LOGDB) are vital to the system consistency, so we strongly recommend that these files are kept intact whenever possible. A complete backup of the entire database should be made before any system databanks are recreated.

If a database has been operational for some time, a situation may arise where one or more of the system databanks LOGDB, TRANSDB or SQLDB has grown very large. In those cases the ALTER DATABANK DROP FILESIZE statement can be used to shrink the file sizes.

The following sections describe how to re-create each of the respective system databanks.

Creating a New LOGDB

1Shut down the database server (if not already stopped).

2Run the DBC tool (Databank Check Utility) on SYSDB and all the user databank files to ensure that none are corrupted.

3Take a valid backup of the whole database.

4Archive a copy of the LOGDB databank file and delete the original file from disk.

5Start the database server.

6Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a path name and a size for the new LOGDB databank file.

Creating a New TRANSDB

Note:Do only perform this operation in case of emergency. Important information may be lost and database consistency can not be guaranteed.

1Shut down the database server (if not already stopped).

2Ensure that all pending transactions have been flushed to the user-databank files on disk by successful execution of DBOPEN in single-user mode.

3Archive a copy of the TRANSDB databank file and delete the original file from disk.

4Start the database server.

5Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a path name and size for the new TRANSDB databank file.

Creating a New SQLDB

1Shut down the database server (if not already stopped).

2Delete SQLDB from disk.

3Start the database server.

4Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a size for the new SQLDB databank file.

Audit trail with READLOG

READLOG is a BSQL function which enables you to read the contents of LOGDB so that you can check logged operations performed on the database since the last backup copy backup was taken.

You can use READLOG as an audit trail or, in the event of a system failure, to determine which databanks need to be restored (i.e. which databanks have been altered since the last backup).

See Mimer SQL User's Manual, READLOG.