Mimer SQL Shadowing makes it possible to create and maintain one or more simultaneously updated copies of a databank. This allows for a higher degree of data availability by giving extra protection from disk crashes, etc.
This chapter describes the functions and benefits of databank shadowing, how to use Mimer SQL Shadowing, and how to handle problems.
Note:Mimer SQL Shadowing is not included in the standard Mimer SQL distribution. In order to implement shadowing, you must have the correct type of license. Contact Mimer Information Technology AB for more information.
Databank shadowing means updating one or more copies of a databank simultaneously.
The master is the ‘normal’ databank file which is accessed for data storage and retrieval. The copies are called shadows. A databank can have more than one shadow.
Any changes to the master databank are automatically made to the shadow, thus protecting data from a disk crash or other event that might cause a databank to be lost.
If a master databank is lost, a shadow will automatically take over from the master and operations can be resumed immediately, assuming the shadow is not also damaged.
A shadow can be transformed to a master databank to permanently replace it and this process is much faster than restoring a databank from a backup copy.
Using offline shadows provides a straightforward way of backing-up your databanks. Once you have completed the backup and set the shadow online, all operations performed on the master databank are applied to the shadow automatically.
Databank shadowing is entirely invisible to an application. This means that shadows can be added to existing applications. No special handling is needed to access tables in a shadowed databank.
Shadowing Requirements
A databank must support transaction control, see Transaction Control, to be shadowed.
Databanks with the WORK option cannot be shadowed, because shadowing requires transaction handling.
The databank to be shadowed cannot be used by any other users while a shadow is being created.
The shadow name cannot be the same as the name of the master databank, of any other shadow, or of any shadow that has been transformed to a master.
SYSDB and Shadowing
Because the SYSDB databank holds all the data dictionary information about your database, protecting it with shadowing and/or backups is essential.
Otherwise, if SYSDB is lost, the whole database will be unreachable.
SQLDB and Shadowing
Shadowing SQLDB is not allowed, as it is a TEMPORARY databank, and not needed because SQLDB only contains temporary data.
Creating Shadows
When you create a shadow for a databank, all tables and indexes in the databank are copied to the shadow.
Creating a shadow for a large databank may take some time, and thus should be carefully planned.
Altering Shadows
When you alter a shadow to a master, it only affects the Mimer SQL data dictionary. The databank file names are not changed. The databank cannot be used by any other user when a shadow is being transformed into the master (this is not very likely to happen since this function is normally used when the master has been lost or damaged).
Backups
We recommend that you take conventional backups as a supplement to databank shadows to protect data in the event of a crash that destroys both the master and the shadow.
Because operations are not interrupted when shadow-backups are taken, and because Mimer SQL databanks are automatically reorganized, you get true 24 hour-a-day operation.
Dropping Shadows
When a shadow is dropped, the file where the shadow is stored is usually deleted from the file system. If it is not deleted, you can use operating system facilities to delete it.
The databank cannot be used by any other users while a shadow is being dropped.
Backing-up and Restoring Data, describes the role of the system databanks LOGDB and TRANSDB when used in conjunction with backup and restore, in protecting data against loss.
Databank shadowing provides an even higher level of protection. Listed below are the different ways in which data can be protected from loss (from the least amount of protection to the highest).
All Databanks on One Disk and No Logging
If a databank is lost with this level of protection, it is only possible to continue operations from the last backup copy (all changes since the last backup was taken are lost).
Databanks can be lost due to accidental deletion, disk crashes (which can destroy all files on a disk), etc. This level of protection is not recommended except for trash databanks with unimportant contents.
Logging, with LOGDB and TRANSDB on a Separate Disk from the Data
LOGDB and TRANSDB are vital databanks if the system stops or if any databanks are lost. Because of this, LOGDB and TRANSDB should be stored on separate disks, as shown in the following figure:
Application data should be stored on the TRANSDB disk if it cannot be stored separately.
If a databank is lost, it can be restored to its original state by applying the transactions in LOGDB and TRANSDB to a restored backup of the databank.
This may take some time, especially if the databank is large and if there is a lot of transaction information stored in LOGDB.
|
Caution:If the databank disk and the TRANSDB or LOGDB disk are handled by the same disk controller, a disk controller failure may cause both disks to crash. If this happens, the databanks can only be restarted from the state of the last backup copy. Therefore, we advise you to use separate disks with separate disk controllers. |
This security level gives a high degree of security and is recommended for databanks containing important data used in a system where the delay before the system is restored after a crash is not critical.
To assure this high degree of security, backup files should always be stored on separate removable media (e.g. CD/RW).
Shadowing, with Shadows on a Separate Disk
Shadows should always be stored on a separate disk from the masters to protect them from a total disk crash that could destroy both the master and shadow databanks.
It is also advisable to use separate disk controllers to assure that a corrupt disk controller does not destroy the disks holding both the masters and the shadows.
If a databank is lost, its shadow can be transformed into a master and the shadow automatically takes over with no loss of data.
Since shadows are updated after the master, and operations are saved in TRANSDB until the shadow is updated, it is important that TRANSDB is consistent when a shadow is transformed.
To ensure this, you should shadow TRANSDB. We strongly recommend that TRANSDB and its shadow are stored on separate disks, as shown in the following diagram:
This arrangement gives a high degree of security and is recommended for databanks containing important data used in a system where it is vital to be able to get the system running again quickly after a disk crash.
Combining shadowing and logging, see Backing-up and Restoring Data, gives the highest level of data protection.
If logging is used, the data is protected if both the master and the shadow disks become corrupted.
And, when shadowing is combined with logging (with LOGDB on a third disk) and backups are regularly taken to separate media (CD/RW, etc.), then data is always protected if any two of the disks crash, for example:
Of course, additional disks can be used, just as long as the databanks that are separated above are not placed on the same disks. If you only have two disk drives available and all the databanks are shadowed, then logging is of little value. Shadowing LOGDB will not increase data protection significantly in this configuration.
Different degrees of data security can be used for different databanks, depending on the importance of the data. It is however important that all inter-dependent databanks (because of foreign key relationships, etc.) have the same level of protection. Otherwise logical inconsistencies may result if there is a disk crash.
Shadows are managed using the SQL shadowing commands:
CREATE SHADOW – creates a shadow
SET SHADOW – sets shadows on- or offline
ALTER SHADOW – swaps a shadow with its master
DROP SHADOW – drops a shadow.
Note:For shadow information, use the LIST SHADOWS command in BSQL.
If you have SHADOW or BACKUP privilege, you can list shadowing information for all databanks.
Users can always backup and restore, set shadows offline and online, and list shadowing information for any databank that they have created.
SYSADM is initially granted SHADOW and BACKUP privilege with the WITH GRANT OPTION.
The following table shows the privileges you need to work with shadows:
|
Tasks |
Privilege |
|
|---|---|---|
|
|
Backup |
Shadow |
|
Create a shadow *) |
|
X |
|
Drop a shadow *) |
|
X |
|
Transform a shadow *) |
|
X |
|
List shadow info |
X |
X |
|
Backup and restore |
X |
|
|
Set shadow offline |
X |
|
*) You must have exclusive use of the databank. This means that no other user can access the databank until the operation is finished.
SQL Shadowing Commands – an Example Session
In the following sections, we use an example session to show how to:
•create shadows
•set them offline and take backup
•set them online again
•restore both user databanks and shadows
•drop shadows.
About the Examples
The examples in the following sections are based on:
|
Ident: |
BACADM |
|
Ident Privileges: |
DATABANK, SHADOW and BACKUP privileges |
|
User databanks: |
ARTICLES, CUSTOMERS with the LOG option enabled |
|
System databanks: |
TRANSDB, SYSDB and LOGDB |
We assume that you are the ident BACADM and have connected to the database using Mimer BSQL, as follows:
SQL> CONNECT;
User: BACADM
Password: Masterp1ece
Creating a Shadow
The following example creates shadows for the user and system databanks:
SQL> CREATE SHADOW TRANSDB_SH FOR TRANSDB IN 'transdb_sh.dbf';
SQL> CREATE SHADOW SYSDB_SH FOR SYSDB IN 'sysdb_sh.dbf';
SQL> CREATE SHADOW LOGDB_SH FOR LOGDB IN 'logdb_sh.dbf';
SQL> CREATE SHADOW ARTICLES_SH FOR ARTICLES IN 'articles_sh.dbf';
SQL> CREATE SHADOW CUSTOMERS_SH FOR CUSTOMERS IN 'customers_sh.dbf';
For information on the CREATE SHADOW command, see the Mimer SQL Reference Manual, CREATE SHADOW.
Setting a Shadow Offline
You must set a shadow offline, for example, when backing-up a databank, to ensure that the databank shadow file is in a consistent state when the backup copy is taken.
The following example sets all the shadows created in the previous example offline:
SQL> SET SHADOW TRANSDB_SH,LOGDB_SH,SYSDB_SH,ARTICLES_SH,CUSTOMERS_SH OFFLINE;
For information on the SET SHADOW command, see the Mimer SQL Reference Manual, SET SHADOW.
Caution:TRANSDB stores all operations carried out while shadows are offline. We recommend that you always set shadows online as soon as possible. If you do not, you risk TRANSDB filling disk capacity.
You can back-up a databank using its shadow instead of the master databank.
This allows the backup process to proceed without affecting the users working with data contained in the databank.
When a shadow is set offline, the relevant transactions will be written to the online databank and remain in TRANSDB until the shadow is set online again and the transactions can be written to it.
To back-up your database from shadows:
1Set the shadows offline, as shown in the previous example.
2Use your operating system’s functionality to copy the shadow files. For example, on Linux:
$ cp sysdb_sh.dbf sysdb_sh.bac
$ cp transdb_sh.dbf transdb_sh.bac
$ cp logdb_sh.dbf logdb_sh.bac
$ cp articles_sh.dbf articles_sh.bac
$ cp customers_sh.dbf customers_sh.bac
3Set the shadows online and reset the log, as shown in the next example.
4Move the backup files to a safe medium, such as CD/RW. Your backup is complete.
Setting a Shadow Online
When you set databank shadows online, they are automatically updated in the background to the current state of the master database.
Continuing with the previous example, you can set the shadows online, as follows:
SQL> SET SHADOW SYSDB_SH,LOGDB_SH,TRANSDB_SH,ARTICLES_SH,CUSTOMERS_SH ONLINE RESET LOG;
Note:To get a backup timestamp, to be able to use the log when restoring from this backup, the RESET LOG option is used.
For information on the SET SHADOW command, see the Mimer SQL Reference Manual, SET SHADOW.
Restoring a User Databank
If an error is encountered on a user databank, the system will continue to operate using the databank shadow. You can restore the damaged user databank by swapping it with its shadow using the ALTER SHADOW command.
For example, if the ARTICLES databank has been damaged, you can restore it by swapping it with its shadow ARTICLES_SH using the ALTER SHADOW command:
SQL> ALTER SHADOW ARTICLES_SH TO MASTER;
Now, the faulty ARTICLES databank is the shadow. However, it has the same name as the master databank.
To return to our original situation, we must delete (drop) the faulty shadow, create a new shadow and swap the shadow with the master so that the databanks are correctly named.
SQL> DROP SHADOW ARTICLES;
SQL> CREATE SHADOW ARTICLES FOR ARTICLES_SH IN 'articles.dbf';
SQL> ALTER SHADOW ARTICLES TO MASTER;
The first command deletes the shadow.
The second command creates a shadow for the master with the original name and location.
The third command swaps the shadow with the master.
Note:If the original situation is not restored as shown above, the shadow name, in this case ARTICLES_SH, will remain allocated internally which could be confusing.
For information on the ALTER SHADOW command, see the Mimer SQL Reference Manual, ALTER SHADOW.
Restoring Both a User Databank and Its Shadow
If both a user databank and its shadow are lost or damaged, you can restore the data using the shadow’s backup files and LOGDB.
1We recommend that you stop the Mimer SQL database server when replacing databanks.
2Copy the shadow’s backup file to the position of the damaged databank file, for example, on a Linux system:
$ cp articles_sh.bac articles.dbf
3Restart the Mimer SQL database server and use the ALTER DATABANK RESTORE command to restore the databank:
SQL> ALTER DATABANK ARTICLES RESTORE USING LOG;
Note:The restore command above will automatically recreate the corresponding shadow databank.
For more information on ALTER DATABANK RESTORE, see the Mimer SQL Reference Manual, ALTER DATABANK RESTORE
You cannot use the ALTER SHADOW command to swap the system databanks SYSDB, TRANSDB and LOGDB with their shadows in order to restore them. You must alter them using the BSQL program.
For more information, see:
•Transforming a SYSDB Shadow to a Master
•Transforming a TRANSDB Shadow to a Master
•Transforming a LOGDB Shadow to a Master.
Dropping a Shadow
As seen in a previous example, you can delete a shadow by dropping it, for example:
SQL> DROP SHADOW ARTICLES_SH;
For information on the DROP SHADOW command, see the Mimer SQL Reference Manual, DROP.
The system databanks (SYSDB, TRANSDB, LOGDB and SQLDB) require special handling in some situations.
If a problem occurs with these databanks or their shadows, the only permitted login is SYSADM logging into the BSQL program. The BSQL program will then recognize the problem and help you correct it.
System databanks are handled in the same way as other databanks, with the following exceptions:
•If an error is encountered on a user databank, automatic shadowing fail-over takes place. However, if there is a problem with SYSDB, TRANSDB, SQLDB or LOGDB, new users cannot login. Users already active will receive an error message when attempting operations that depend on the affected system databank, while other operations continue to work. The error state is held until Mimer SQL is stopped and the error is corrected.
•If there is a problem with SYSDB, TRANSDB or LOGDB shadows, new users cannot login until the faulty shadow is dropped or suspended, see If a Shadow for SYSDB, TRANSDB or LOGDB Is Not Accessible.
•No users can be connected while a shadow for SYSDB, TRANSDB, or LOGDB is being created, altered or dropped.
•You cannot create a shadow for SQLDB as it is a TEMPORARY databank.
Transforming a SYSDB Shadow to a Master
If SYSDB is lost or corrupt, any existing SYSDB shadow can be altered to become the master in order to allow Mimer SQL to start again.
The SYSDB shadow file should be renamed and/or moved to the location where the master SYSDB was. Then the BSQL is started and login is performed as SYSADM. Enter the name of the shadow to be transformed into the master, and exit.
Example
Mimer SQL command line utility, version 11.0.7A
Username: SYSADM
Password:
MIMER/DB warning -18013 in function CONNECT
MIMER/DB started from SYSDB shadow. Transform SYSDB shadow to master
with BSQL, or restart system from master SYSDB
-- Transform shadow --
DATABANK
SHADOW
OFFLINE
FILE
========================================
SYSDB
SYSSH
N
SYSDB_S
---
One shadow found
Name of shadow to transform (<CR> = skip): sysdb_s
Shadow SYSSH transformed to master
If the disk where SYSDB is located becomes inaccessible, it may be more suitable to redefine the database home directory (to point out the SYSDB shadow) instead of restoring the original directory structure.
Note:In this case the ALTER DATABANK statement must be used for all databanks explicitly defined to be located on the halted disk, i.e. with an absolute file specification in the data dictionary.
If SYSDB is lost and no shadows exist, a backup copy of SYSDB can be restored to allow Mimer SQL to start again, an example of how to do this is given in Backing-up and Restoring Data.
Shadowing TRANSDB assures that you can bring your database up-to-date if the TRANSDB master is lost or damaged.
Transforming a TRANSDB Shadow to a Master
Start the BSQL program and login as SYSADM. A message is displayed saying that TRANSDB cannot be opened, and a shadow must be transformed to the master, this is similar to the example in Transforming a SYSDB Shadow to a Master.
If there are uncompleted transactions they will be completed, as if the original TRANSDB was still functioning.
If some databanks are not shadowed but backup copies of the databanks exist, then a shadow of LOGDB is useful since LOGDB, in this case, is even more important from a restore perspective.
Transforming a LOGDB Shadow to a Master
Start the BSQL program and login as SYSADM. A message is displayed saying that LOGDB cannot be opened, and a shadow must be transformed to the master, this is similar to the example in Transforming a SYSDB Shadow to a Master.
If there are transactions not yet written to the log, they will be written automatically.
Shadowing SQLDB is not necessary because SQLDB only contains temporary data.
However, SQLDB is required when a user logs on to Mimer SQL. If SQLDB is corrupt or lost, you must recreate it by logging on to the BSQL program as SYSADM. This automatically recreates SQLDB if the databank is not found.
If a Shadow for SYSDB, TRANSDB or LOGDB Is Not Accessible
If a shadow for SYSDB, TRANSDB or LOGDB is not accessible, SYSADM should login to the BSQL program.
An error message is given followed by the option to drop the shadow or set it offline. If the shadow is corrupt or missing, you should drop it. For example:
Mimer SQL command line utility, version 11.0.7A
Username: SYSADM
Password:
MIMER/DB fatal error -16142 in function CONNECT
Cannot open databank LOGDB_S,
file logdb_s not found
Inaccessible shadow encountered. DROP or SET OFFLINE? (D/S): D
-- Drop shadow --
Shadow LOGDB_S dropped
If the shadow is only temporarily unavailable, it may be enough to set it offline for a short period of time.
So far, this chapter has described the facilities that are available for a system manager to maintain the system.
But how do you know that you are using the functionality in the right way, and that you will be able to get the system running again if something happens?
The answer is planning and practice. When you have planned a data protection strategy, you should put it into practice by simulating a disk crash and restoring the databanks according to your strategy.
Check that the contents of all tables are the same as before the simulated crash. Do not forget to simulate a crash of the system databanks (SYSDB, TRANSDB, LOGDB and SQLDB) and then restore or recreate them.
Once you have a successful strategy, build command files (scripts) that perform the correct operations regularly.
In Levels of Data Protection, we discussed various levels of data security.
When you configure your system, there are some additional questions to be answered:
•How do I divide the workload over several disks to get the best performance?
TRANSDB and its shadows should preferably be on fast disks, see Performance Aspects of Shadowing.
•How much disk space do I need for shadowing?
A shadow file occupies the same amount of disk space as the master databank file.
In general, you will have problems if TRANSDB or LOGDB run out of disk space. When this happens the system cannot continue. Therefore, it is important to make sure that there is enough space for these databanks.
How large TRANSDB grows depends on the number of transactions and how fast the background threads are able to perform the transactions on the shadows. When the transactions are performed on the shadows, the space in TRANSDB where they were stored is released.
How large LOGDB grows depends on whether the LOG option is used and how often backups are taken and DROP LOG is performed, since DROP LOG clears LOGDB and releases space (but the file size remain unchanged).
Note:Try and keep the length of time the shadows are offline to a minimum. If you do not, you risk TRANSDB growing and filling the disk to capacity.
Performance Aspects of Shadowing
In multi-user systems, performance is not noticeably affected by shadowing, even though the shadowing system needs more machine resources because more files need to be updated.
Applications do not have to wait for the shadows to be updated as this is done in the background. Actually, all updates to the disk, even to master databanks, are performed by background processes (except for updates to TRANSDB and its shadows).
In single-user systems, no background process is used. This means that an application has to wait for the shadows to be updated.
If shadow updating is delayed TRANSDB grows. This can happen for several reasons:
•A shadow has been set offline and forgotten. If this happens, transactions will be buffered until the shadow is set online again.
To check if a shadow is offline, in BSQL use the LIST SHADOWS command or the The Performance Report.
•A shadow is corrupt. Updates to the shadow result in an I/O error, and are buffered in TRANSDB.
When this happens, the operator is notified by the system. To check, for notification, use the The Performance Report and check your Mimer SQL database server log, see Database Server Log.
•There are too few background threads to update the shadows, or they get too little machine resources.