Introduction
This article explains what you should do if you need to restore data. It also discusses Mimer SQL system databanks and contains a quick reference guide to help you decide how to proceed in the event of a system failure.
The starting point for any restore operation is a databank backup on a system using LOG option on the databanks. Whenever possible, you should take a complete backup of all the databanks in your database, both system databanks (SYSDB, LOGDB, TRANSDB and SQLDB) and user databanks. Perform backup on a regular basis to provide a logically consistent databank set. Use the Mimer SQL online backup commands or an operating system backup.
For more information on backing-up your database, see the basic article Mimer SQL Online Backup and the more detailed information given in the System Management Handbook found in the Mimer SQL Documentation Set.
This article is rather long, so we’ve included the following links to help you navigate:
- About System and User Databanks
- Failure Example
- Quick Guide to Restoring a Database
- Restoring SYSDB
- Restoring a User Databank
- Re-creating TRANSDB, LOGDB and SQLDB
- Specifying a New Databank Location
Some Friendly Advice
If you need to restore data, you are probably in a stressful situation. It’s a good idea to run through the procedures described in the article and practice dealing with the different scenarios before anything nasty happens to your data. That way, you will be better prepared for the situation.
Description
About System and User Databanks
The system databank SYSDB contains the data dictionary describing the database structure and all changes to the data dictionary are automatically logged in LOGDB. In addition, the LOGDB databank contains logged update information for user tables created in databanks defined with the LOG option.
As a restore operation involves retrieving the databank from a backup and using the information held in LOGDB to roll the databank forward, SYSDB and the user databanks (those defined with the LOG option) are the only databanks you can restore in the event of a failure.
Note! To simplify any restore operation it is important that you clear the logged information from LOGDB after you have taken a complete backup of the database. If using the Online Backup functionality, this is done automatically when LOGDB is included in the backup.
To restore SYSDB, you use the BSQL program. To restore a user databank, you use SQL commands.
The system databanks TRANSDB and LOGDB are part of the restore mechanism itself and if they are lost or corrupted there is no way of restoring them.
SQLDB only contains transient information and does not need to be restored. If lost, a new SQLDB databank can be created using the BSQL program.
If only LOGDB has been lost (i.e. no other system or user databanks are affected) then a new LOGDB databank should be created.
Note! When LOGDB is recreated or emptied, a full backup of the system must be taken immediately to provide a new point from where a restore attempt can be made.
If TRANSDB has been lost it is likely that some pending transactions will have been lost and the database may be inconsistent. If LOGDB is still available, the safe option is to retrieve all the databanks (SYSDB and all user databanks but excluding LOGDB) from the latest backup and restore each databank using the current LOGDB. There may be a small number of transactions that cannot be restored because they were not completely written to LOGDB at the time of the crash.
For data security reasons, in case of a disk failure, it is strongly recommended that LOGDB is located on a disk unit that is physically separate from that on which the other databanks are located. Ideally, TRANSDB and LOGDB should always be located on different physical disks that are served by separate disk controllers and no other databank files should be located on either disk. This will minimize the risk of an unrecoverable failure.
Note! After changing the data dictionary, i.e. after executing DDL (Data Definition Language) statements, it is recommended that a new backup is taken.
Failure Example
The graphic below illustrates a scenario that ends in a system crash.
To recover from this situation, you would start from the most recent backup (T2) and then use the current LOGDB to recover data up to the state at T3.
When you restart the system, the current TRANSDB is used to automatically recover up to the moment of the crash. If you cannot use the most recent backup, you have to bring up an older backup (T1). You restore this backup up to the consistent state at T2 by using the LOGDB stored in the backup at T2. Then you use the current LOGDB and TRANSDB databanks to restore the transactions committed after the backup at T2.
Quick Guide to Restoring a Database
The table below gives an overview of what to do if a databank, or a set of databanks, is lost or corrupted.
For more information on the different steps you must take, see the sections that follow the table.
Note! It can be a good idea to save a corrupt databank in a different location before starting the restore procedure. If the restore procedure fails, Mimer SQL support may be able to help, using the damaged databank file.
The SYSDB databank file is damaged | The TRANSDB databank file is damaged | The LOGDB databank file is damaged | One or several User Databank(s) are damaged | What to do! |
---|---|---|---|---|
Userdb lost | One or more user databank file(s) are damaged! 1) Move the corrupted user databank file(s) to temporary storage. 2) Retrieve the required user databank(s) from the latest backup. 3) Restore required user databank(s) using the ALTER … RESTORE USING LOG statement. |
|||
Sysdb lost | SYSDB is damaged! 1) Move the corrupt SYSDB file to temporary storage. 2) Retrieve SYSDB from the latest backup. 3) Restore SYSDB (see below). |
|||
Transdb lost | TRANSDB is damaged! 1) Move the corrupt TRANSDB file to temporary storage. 2) Recreate TRANSDB (see below). Note! Since TRANSDB was lost, there might be a small number of transactions that cannot be restored. |
|||
Logdb lost | LOGDB is damaged! 1) Recreate LOGDB (see below). 2) Take a full backup of your database immediately. |
|||
Sysdb lost | Userdb lost | SYSDB and one or more user databank(s) are damaged! 1) Move the corrupt SYSDB file and the damaged user databank file(s) to temporary storage. 2) Retrieve SYSDB and the required user databank(s) from the latest backup. 3) Restore SYSDB (see below). 4) Restore the required user databank(s) using the ALTER … RESTORE USING LOG statement. |
||
Sysdb lost | Transdb lost | SYSDB and TRANSDB are damaged! 1) Move the corrupt SYSDB and TRANSDB files to temporary storage. 2) Retrieve SYSDB from the latest backup. 3) Restore SYSDB (see below). 4) Recreate TRANSDB (see below). Note! Since TRANSDB was lost, there might be a small number of transactions that cannot be restored. |
||
Sysdb lost | Logdb lost | SYSDB and LOGDB are damaged! A) If the current data dictionary corresponds to the latest backup: 1) Move the corrupt SYSDB and LOGDB files to temporary storage. 2) Retrieve SYSDB from the latest backup. 3) Recreate LOGDB (see below). 4) Take a full backup of your database immediately. B) If the data dictionary is changed since the latest backup: 1) Move all current databanks to temporary storage. 2) Retrieve all databanks from the latest backup. (If LOGDB and TRANSDB are not in the backup, recreate them as described below.) Note! In this case there is no way to restore to a later stage than the backup timestamp. |
||
Transdb lost | Userdb lost | TRANSDB and user databanks are damaged! 1) Move TRANSDB and the damaged user databank file(s) to temporary storage. 2) Retrieve the selected user databank(s) from the latest backup. 3) Recreate TRANSDB (see below). 4) Restore selected user databank(s) using the ALTER … RESTORE USING LOG statement. Note! Since TRANSDB was lost, there might be a small number of transactions that cannot be restored. |
||
Logdb lost | Userdb lost | LOGDB and user databanks are damaged! 1) Move all current databank files to a temporary storage. 2) Retrieve all databanks from the latest backup. (If LOGDB and TRANSDB are not in the backup, recreate them as described below.) Note! In this case there is no way to restore to a later stage than the backup timestamp. If you know what you are doing! If only some of the user databanks are damaged, there may be a possibility to take only those files from the backup. But be careful here! Since the databanks taken from the backup cannot be restored from any LOGDB there is a significant risk for inconsistencies in the database. 1) Move LOGDB and damaged user databank file(s) to a temporary storage. 2) Retrieve selected user databank(s) from the latest backup. 3) Recreate LOGDB (see below). 4) Take a full backup of your database immediately. Note! In this scenario there is no guarantee for the state of the database. Without LOGDB, damaged user databanks are not restored which can lead to data inconsistency. |
||
Transdb lost | Logdb lost | TRANSDB and LOGDB are damaged! 1) Move the corrupt LOGDB and TRANSDB files to temporary storage. 2) Recreate LOGDB and TRANSDB (see below). 3) Take a full backup of your database immediately. Note! Since TRANSDB was lost, there might be a small number of transactions that cannot be restored. |
||
Sysdb lost | Transdb lost | Logdb lost | SYSDB, TRANSDB and LOGDB are damaged! A) If current data dictionary corresponds to the latest backup: 1) Move the corrupt SYSDB, TRANSDB and LOGDB files to temporary storage. 2) Retrieve SYSDB from the latest backup. 3) Recreate LOGDB and TRANSDB (see below). 4) Take a full backup of your database immediately. Note! Since TRANSDB was lost, there might be a small number of transactions that cannot be restored. B) If the data dictionary is changed since the latest backup: 1) Move all current databank files to temporary storage. 2) Retrieve all databanks from the latest backup. (If LOGDB and TRANSDB are not in the backup, recreate them as described below.) Note! In this case there is no way to restore to a later stage than the backup timestamp. |
|
Transdb lost | Logdb lost | Userdb lost | TRANSDB, LOGDB and user databanks are damaged! 1) Move all current databank files to a temporary storage. 2) Retrieve all databanks from the latest backup. (If LOGDB and TRANSDB are not in the backup, recreate them as described below.) Note! In this case there is no way to restore to a later stage than the backup timestamp. If you know what you are doing! If only some of the user databanks are damaged, there may be a possibility to take only those files from the backup. But be careful here! Since the databanks taken from the backup cannot be restored from any LOGDB there is a significant risk for inconsistencies in the database. 1) Move TRANSDB, LOGDB and the damaged user databank file(s) to a temporary storage. 2) Retrieve the required user databank(s) from the latest backup. 3) Recreate LOGDB and TRANSDB (see below). 4) Take a full backup of your database immediately. Note! In this scenario there is no guarantee for the state of the database. Without LOGDB, damaged user databanks are not restored which can lead to data inconsistency. In addition, since TRANSDB was lost, there might be a small number of transactions that cannot be restored. |
|
Sysdb lost | Transdb lost | Logdb lost | Userdb lost | SYSDB, TRANSDB, LOGDB and user databanks are damaged! 1) Move all current databank files to temporary storage. 2) Retrieve all databanks from the latest backup. (If LOGDB and TRANSDB are not in the backup, recreate them as described below.) Note! In this case there is no way to restore to a later stage than what is on the backup. |
Restoring SYSDB
If the system databank SYSDB is lost or corrupted, Mimer SQL cannot start. You must restore SYSDB. You do this using the BSQL program and the information contained in LOGDB.
- Retrieve the backup of the SYSDB databank to its original location and ensure that the databank file has the same name as the original SYSDB databank.
Note! Sometimes, you might not be able to place the SYSDB backup databank in the original location. If so, change the home directory specified for the database in the SQLHOSTS file, on Windows this is maintained through the Mimer Administrator. - Start the database server.
- Start BSQL and login as SYSADM. A message is displayed, saying that you have a version of SYSDB that must be restored.
- Answer “Y” to the question “Restore SYSDB?” to restore the copy of SYSDB. Since the SYSDB databank is defined with the LOG option, this will restore SYSDB to the state it had before it was lost.
Restoring a User Databank
You restore a user databank by using SQL commands.
- Retrieve the backup of the databank to be restored to its original location and ensure that the databank file has the same name as the original databank.
Note! Sometimes, you might not be able to place the backup databank in the original location. If so, the ident that was the creator of the databank must be used to alter the databank to reference the new location using the following command:SQL> ALTER DATABANK databank-name INTO 'new-file-name'
- You must now update the databank so that it contains the latest information. You do this using the information stored in the current LOGDB. You must be connected as an ident that holds the BACKUP privilege (e.g. SYSADM):
SQL> ALTER DATABANK databank-name RESTORE USING LOG
- If you haven’t regularly backed-up the user databanks in your database, hopefully you have been backing up the system databanks, and in particular LOGDB. You will have to systematically update the databank using the sequence of LOGDB backups, with the oldest backup first and the current LOGDB the last. The current LOGDB is processed as in point 2 above:
SQL> ALTER DATABANK databank-name RESTORE USING 'logdb-backup-file-name'
Re-creating TRANSDB, LOGDB and SQLDB
In certain situations, you may need to recreate a system databank:
- If TRANSDB, LOGDB and/or SQLDB have been lost or corrupted, you will have to re-create them.
- If they have been physically moved, you will have to specify their new location(s).
Caution! Since system databanks are so vital to database consistency, we strongly recommend that you keep these files intact whenever possible.
The following sections describe how to re-create each of the system databanks.
To create a new LOGDB:
- Shut down the database server if it is still running.
- Run DBC (Databank Check program) on SYSDB and all the user databank files to ensure that none of them are corrupted.
- Delete the original LOGDB file from disk.
- Start the database server.
- Start BSQL and log on as SYSADM.
- When prompted, select the CREATE option and specify the size (in 2K pages) for the new LOGDB databank file.
- Take a valid backup of the whole database including LOGDB.
To create a new TRANSDB:
- Shut down the database server if it is still running.
- Ensure that all pending transactions have been flushed to the user databanks on disk by running DBOPEN (Databank Open program) in single-user mode.
- Archive a copy of the TRANSDB databank file and delete the original file from disk.
- Start the database server.
- Start BSQL and log on as SYSADM.
- When prompted, select the CREATE option and specify the size (in 2K pages) for the new TRANSDB databank file.
To create a new SQLDB:
- Shut down the database server if it is still running.
- Delete the SQLDB file from disk.
- Start the database server.
- Start BSQL and log on as SYSADM.
- When prompted, select the CREATE option and specify the size (in 2K pages) for the new SQLDB databank file.
Specifying a New Databank Location
If any of the system databanks (TRANSDB, LOGDB or SQLDB) have been moved, you must specify their new location(s):
- Start the database server.
- Start BSQL and log on as SYSADM.
- When prompted, select the ALTER option and specify the full path name for the new system databank file. If the specified databank file is not found (i.e. the existing file hasn’t been moved) then you will be offered the opportunity to create the file at the new location.
Notes
In Mimer SQL versions earlier than 9.2 the UTIL program was used instead of BSQL to recreate and restore system databanks.
Links
Further in the subject, read our article Doing online backup with Mimer SQL for a description on how to take backup while still keeping your database fully operational.
See the System Management Handbook found in the Mimer SQL Documentation Set. Here you can read more about backing-up and restoring data. In addition, you will find information on Mimer SQL Shadowing, introducing an additional level of resilience by maintaining multiple copies of a databank (including the system databanks).
See our documentation page for all documents available »