Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


SQL Shadowing Commands - an Example Session


In the following sections, we use an example session to show how to:

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:
  1. Set the shadows offline, as shown in the previous example.
  2. Use your operating system's functionality to copy the shadow files. For example, on UNIX:
 $ 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
 
  1. Set the shadows online and reset the log, as shown in the next example.
  2. Move 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.

  1. We recommend that you stop the Mimer SQL database server when replacing databanks.
  2. Copy the shadow's backup file to the position of the damaged databank file, for example, on a UNIX system:
 $ cp articles_sh.bac articles.dbf
 
  1. Restart 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:

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.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX