Mimer SQL Shadowing

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.

About Databank Shadowing

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.

inset_3.jpg

 

 

 

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.

Levels of Data Protection

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:

inset_2.jpg

 

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:

inset_000031.jpg

 

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.

Shadowing and Logging

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:

inset_1.jpg

 

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.

Creating and Managing Shadows

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.

Privileges

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.

Backing-up from Shadows

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

Restoring System Databanks

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.

Shadowing System Databanks

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.

Restoring SYSDB

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.

TRANSDB and Shadowing

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.

LOGDB and Shadowing

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.

SQLDB and Shadowing

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.

Data Protection Strategy

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.

Configuring Your System

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.

Troubleshooting

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.