Restoring a Databank

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:

Bring a valid backup copy of the databank from the backup media to disk.
If 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'
 
If 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'
 
Finally, 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 10.1.0
 Username: SYSADM
 Password: 
 2009-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

Shut down the database server (if not already stopped).
Run the DBC tool (Databank Check Utility) on SYSDB and all the user databank files to ensure that none are corrupted.
Take a valid backup of the whole database.
Archive a copy of the LOGDB databank file and delete the original file from disk.
Start the database server.
Start 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.

Shut down the database server (if not already stopped).
Ensure that all pending transactions have been flushed to the user-databank files on disk by successful execution of DBOPEN in single-user mode.
Archive a copy of the TRANSDB databank file and delete the original file from disk.
Start the database server.
Start 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

Shut down the database server (if not already stopped).
Delete SQLDB from disk.
Start the database server.
Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a size for the new SQLDB databank file.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se